PostgreSQL Indexing & Query Optimization: B-Tree, GIN, BRIN and Execution Plan Analysis
Table of Contents
1. B-Tree Anatomy & Multi-Column Index Ordering
The standard B-Tree index in PostgreSQL is a self-balancing tree structure designed for equality and range lookups. Each leaf page contains key-pointer pairs referencing physical table Heap Tuples (CTIDs). In multi-column composite indexes, column ordering is paramount: queries must filter by the leftmost prefix to utilize the index hierarchy effectively.
-- Composite index ordering example
CREATE INDEX idx_orders_user_created
ON orders (user_id, created_at DESC)
INCLUDE (total_amount);By leveraging the INCLUDE clause (Index-Only Scans), PostgreSQL avoids fetching the heap page altogether if all queried columns reside within the index leaf payload, slashing random I/O operations.
2. GIN vs. GiST Indexes for Semi-Structured JSONB
When indexing nested JSONB attributes, array elements, or full-text search tsvectors, Generalized Inverted Indexes (GIN) provide unparalleled lookup speed at the cost of higher write amplification and disk footprint.
| Index Type | Lookup Speed | Build / Write Cost | Ideal Workload |
|---|---|---|---|
| GIN (jsonb_ops) | O(1) to O(log N) | High write amplification | Deep key-value lookups, JSON containment @> |
| GiST | O(log N) | Moderate write cost | Geospatial queries, overlapping range types |
| B-Tree Expression | O(log N) | Minimal overhead | Specific scalar JSON fields (e.g. (data->>'status')) |
3. BRIN Indexes: High-Density Time-Series Storage
Block Range Indexes (BRIN) summarize min/max values for sequential physical block ranges (typically 128 pages per entry). For append-only, chronologically sorted telemetry and event logs, a BRIN index requires less than 1% of the disk space consumed by an equivalent B-Tree, reducing RAM footprint and buffer cache churn.
4. Decoding EXPLAIN (ANALYZE, BUFFERS) Output
Optimizing slow queries requires inspecting the query planner's chosen execution nodes. Always run EXPLAIN (ANALYZE, BUFFERS, COSTS VERBOSE) to observe actual runtime durations and buffer hits versus disk reads:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount FROM orders
WHERE user_id = 42 AND created_at >= '2026-01-01';