Indexes That Actually Get Used

Indexes aren't free — every one makes reads faster and writes slower, and the wrong index just costs you write throughput for nothing. The skill isn't knowing index types; it's knowing which predicate the planner can use an index for.

B-tree is for ordered lookups

The default index type answers =, <, >, BETWEEN, LIKE 'prefix%', ORDER BY. It does not help LIKE '%suffix' — a B-tree can't jump into the middle of keys. For that, pg_trgm's GIN index turns LIKE '%substring%' into trigram lookups.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
-- usable for: WHERE user_id = 5 ORDER BY created_at DESC
-- NOT usable for: WHERE created_at > now() (user_id missing — leftmost prefix rule)

Composite indexes obey the leftmost prefix rule: the index on (a, b, c) helps WHERE a=, a AND b, a AND b AND c — not WHERE b= alone.

The specialized arsenal

• Partial index — index only rows that matter: CREATE INDEX ON orders (user_id) WHERE status = 'open' — small, fast, answers the hot query.
• Covering index — INCLUDE carries extra columns so the planner never touches the heap: CREATE INDEX ON users (email) INCLUDE (name) enables index-only scans.
• GIN — for containment: jsonb, arrays, tsvector full-text search, trigrams.
• GiST — geometric/range types; exclusion constraints (tsrange && overlap checks) are built on it.
• BRIN — for huge tables where the physical order correlates with the column (append-only time series): tiny index, scans ranges of blocks.

When the index exists but isn't used

• Wrong estimate — run ANALYZE on the table; stale stats make the planner pessimistic.
• The index returns more than ~5-10% of rows — a sequential scan is honestly faster.
• Function on the column — WHERE lower(email) = 'x' can't use email unless you indexed lower(email) as an expression.

::exercise(index-selectivity)

Field notes

• CREATE INDEX CONCURRENTLY builds without locking writes — slower, but the only option on a busy table.
• pg_stat_user_indexes shows idx_scan counts — an index never used is dead weight to drop.