Indexes and the Query Planner

B-tree is the default and handles equality, ranges, sorting and prefix LIKE. Hash handles equality only. GIN indexes the elements inside values (JSONB keys, array elements, full-text lexemes). GiST supports ranges, geometry and nearest-neighbour. BRIN stores min/max per block range and is tiny, which suits huge append-only tables where values correlate with physical order.

8 min 1 code practice

A composite index (a, b, c) is sorted by a, then b within a, then c. It serves queries that constrain a leftmost prefix of the columns, with equality columns first, then the sort column, then a range column. A range on an earlier column stops later columns from narrowing the scan.

9 min 2 code practice

Covering indexes (INCLUDE) let queries be answered from the index alone (index-only scans). Partial indexes cover just the rows queries care about. Expression indexes index a computed value. Unique indexes enforce rules. Every index costs write amplification, storage and cache, so unused and duplicate indexes should be dropped.

8 min 1 code practice

PostgreSQL's cost-based planner estimates how many rows each step returns (cardinality) from table statistics, assigns costs to candidate plans, and picks the cheapest. Scan types (sequential, index, index-only, bitmap) and join algorithms (nested loop, hash, merge) each win in different row-count regimes; wrong estimates lead to wrong choices.

8 min 1 code practice

EXPLAIN shows the chosen plan with estimated costs and rows; EXPLAIN (ANALYZE, BUFFERS) runs the query and adds actual times, row counts, loops and buffer hits and reads. Read plans inside-out, compare estimated with actual rows to find misestimates, and look for the node where time and buffers concentrate.

8 min 2 code practice