Databases SQL Performance PostgreSQL Query Optimization

Database Indexing: B-Trees, Composites, and EXPLAIN

Core Concept LearningJuly 4, 20265 min readUpdated July 21, 2026

A selective lookup that once scanned most of a large table can become an index walk plus a few heap fetches — often the difference between a snappy API and a timeout. Indexes are the highest-impact performance tool most application developers control, and the easiest to misuse: add them reactively, never drop unused ones, and ignore column order.

This article explains B-tree mechanics, which columns deserve indexes, composite leftmost-prefix rules, how to read EXPLAIN ANALYZE, and mistakes that hurt writes. Soften any war stories with your own EXPLAIN — do not trust borrowed speedup ratios.

Index strategy starts with the storage model, so compare SQL and NoSQL databases. If repeated reads still dominate after query tuning, evaluate the Redis and CDN caching strategies.

Five PostgreSQL index types and what they're for

How B-Tree Indexes Work

A B-tree keeps indexed values in a balanced tree; leaf entries point at table rows (heap tuples in PostgreSQL). Lookups cost roughly logarithmic tree height, not a full scan of every row. The planner chooses sequential scan vs index scan by estimated cost: highly selective predicates favor indexes; queries that touch most of the table often favor a sequential pass (random I/O from bouncing through the index can be worse).

Query trace: API GET /orders?user_id=123 → SQL WHERE user_id = $1 → planner picks Index Scan on idx_orders_user_id → fetch matching heap rows → JSON response. Without the index, the same request becomes a Seq Scan — fine on tiny tables, painful as row counts grow.

One indexed lookup: descend the B-tree, then fetch the matching heap row

Quick reference

B-tree: default; supports =, ranges, BETWEEN, LIKE 'prefix%', ORDER BY.
Hash: equality only — limited use versus B-tree in modern Postgres.
GIN: arrays, JSONB, full-text inverted lookups.
GiST: geometry, some text/spatial (PostGIS) patterns.
BRIN: bulky naturally ordered columns (e.g. time) with small index size.
Every index adds write work on INSERT/UPDATE/DELETE.

Remember this

B-tree is the default; pick GIN/GiST/BRIN only when the access method matches the type.

Which Columns to Index

Index columns that appear in hot WHERE, JOIN, or ORDER BY clauses on large tables. High-cardinality columns (many distinct values) benefit most. A three-value status column alone is usually a poor standalone index — selectivity is too low.

Foreign keys are commonly missed: PostgreSQL does not auto-index FK columns, yet JOINs and cascades scan them constantly. When not to index: tiny tables, write-heavy columns never filtered, or “just in case” indexes you never verified with EXPLAIN.

Quick reference

High selectivity → good index candidate (user_id, email, uuid).
Low selectivity alone → often skip (boolean, tiny enums).
Always consider indexing foreign keys.
Partial index: WHERE status = 'active' for hot subsets.
Expression index: lower(email) for case-insensitive match.
pg_stat_user_indexes: idx_scan = 0 after real traffic → drop candidate.

Common slow queries that need indexes

1-- Seq Scan risk: no index on user_id2SELECT * FROM orders WHERE user_id = 123;3 4-- FK join without index on orders.user_id5SELECT o.*, u.email6FROM orders o7JOIN users u ON u.id = o.user_id;8 9-- Range filter without index on created_at10SELECT * FROM events11WHERE created_at BETWEEN '2026-01-01' AND '2026-02-01';12 13-- Sort without supporting index14SELECT * FROM products ORDER BY price ASC LIMIT 10;

Indexes that support the queries above

1CREATE INDEX idx_orders_user_id ON orders(user_id);2CREATE INDEX idx_events_created_at ON events(created_at);3CREATE INDEX idx_products_price ON products(price);4 5EXPLAIN (ANALYZE, BUFFERS)6SELECT * FROM orders WHERE user_id = 123;7-- Prefer: Index Scan / Index Only Scan using idx_orders_user_id8-- Investigate: Seq Scan on a large table with few rows returned

Remember this

Index hot filters and foreign keys; skip low-selectivity vanity indexes.

Composite Indexes and Column Order

A composite B-tree on (a, b, c) is most efficient when predicates constrain leading columns. PostgreSQL can still use later-column conditions, and PostgreSQL 18 added skip-scan plans that can help when omitted leading columns have few distinct values, but that is cost-based rather than guaranteed. Put equality columns before a range when that matches the hot query shape.

Failure path: you index (status, created_at) but the dashboard filters only created_at. The planner may choose a sequential scan; PostgreSQL 18 may choose skip scan when status has low cardinality. Verify the real plan before adding a dedicated index.

Leftmost prefix rule: (status, created_at) index usage

Quick reference

Leading constraints are usually most efficient; PostgreSQL 18 skip scan can sometimes make later-only predicates useful.
Equality before range in the same composite.
INCLUDE columns enable index-only scans for hot narrow queries.
Put ORDER BY columns at the end when the query sorts the same way.
Verify with EXPLAIN — never assume column order from instinct.

Wrong expectation — missing leftmost column

1CREATE INDEX idx_orders_status_date ON orders(status, created_at);2 3-- Uses index (leads with status)4SELECT * FROM orders5WHERE status = 'pending' AND created_at > '2026-01-01';6 7-- Often cannot use that composite alone (skips status)8SELECT * FROM orders WHERE created_at > '2026-01-01';

Equality then range + optional dedicated index

1CREATE INDEX idx_orders_status_date ON orders(status, created_at);2CREATE INDEX idx_orders_created_at ON orders(created_at);3 4-- SaaS list pattern: tenant + user + recency5CREATE INDEX idx_orders_tenant_user_date6  ON orders(tenant_id, user_id, created_at DESC);7 8-- Covering-style: INCLUDE avoids heap fetch for narrow selects (Postgres 11+)9CREATE INDEX idx_orders_user_covering10  ON orders(user_id) INCLUDE (status, total_cents);

Remember this

Respect leftmost prefix; equality before range; confirm with EXPLAIN.

Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE runs the query and shows actual timings, row counts, and loops. Compare estimated vs actual rows — large gaps mean stale statistics (ANALYZE) or a bad plan. Seq Scan on a large table returning few rows usually means a missing or defeated index (function on the column, type mismatch, leading-wildcard LIKE).

Treat timings as local evidence for your data and hardware. Re-run before/after on the same machine; do not cite another blog’s “N× faster” as your result.

EXPLAIN ANALYZE shape before/after an expression index — illustrative, measure your own data

Quick reference

EXPLAIN = estimate; EXPLAIN ANALYZE = real run (careful on prod writes).
actual time and loops: multiply to see total node cost.
Rows Removed by Filter high → missing/non-sargable predicate.
Buffers hit vs read: cache behavior and I/O pressure.
Run ANALYZE after bulk loads so estimates stay honest.

Predicate defeats a plain email index

1EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)2SELECT id, email FROM users3WHERE lower(email) = 'alice@example.com';4 5-- Typical smell: Seq Scan + Rows Removed by Filter on a large table6-- (exact costs/timings depend on your data — read YOUR output)

Expression index matches the predicate

1CREATE INDEX idx_users_email_lower ON users(lower(email));2 3EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)4SELECT id, email FROM users5WHERE lower(email) = 'alice@example.com';6 7-- Look for Index Scan / Index Only Scan using idx_users_email_lower8-- Compare actual total time before vs after on the same database

Remember this

Diagnose with EXPLAIN ANALYZE on your data — estimate vs actual and Seq Scan smells first.

Four Indexing Mistakes That Kill Performance

Over-indexing slows writes: each INSERT/UPDATE/DELETE maintains every index. Unused indexes still cost disk, backups, and planner confusion. Classic defeats: function on an indexed column without a matching expression index, implicit type mismatch, leading-wildcard LIKE on a B-tree, and OR across unrelated columns.

When not to add another index: you have not seen Seq Scan or slow actual time on a production-shaped dataset, or idx_scan shows an existing index already unused.

Four indexing mistakes that silently kill performance

Quick reference

Profile first; index second.
Drop zero-scan indexes after steady traffic (with a backup plan).
LIKE 'prefix%' can use B-tree; '%middle%' needs trigram/GIN or FTS.
OR → often UNION ALL of indexed legs.
CREATE INDEX CONCURRENTLY avoids blocking ordinary writes but still takes brief locks, waits on transactions, and cannot run inside a transaction block.
If a concurrent build fails, inspect and drop/rebuild the INVALID index; it is unusable for queries but can still add update overhead.
Partial indexes shrink size when filters are stable.

Patterns that defeat indexes

1-- 1. Function on column — plain email index unused2SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';3 4-- 2. Type mismatch risk — keep literals aligned with column types5SELECT * FROM orders WHERE user_id = '123'; -- if user_id is integer, prefer 1236 7-- 3. Leading wildcard — B-tree cannot seek8SELECT * FROM products WHERE name LIKE '%widget%';9 10-- 4. OR across columns — may not use one neat index path11SELECT * FROM orders WHERE status = 'pending' OR user_id = 456;

Fixes that restore index use

1CREATE INDEX ON users(upper(email));2SELECT * FROM users WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';3 4SELECT * FROM orders WHERE user_id = 123;5 6CREATE EXTENSION IF NOT EXISTS pg_trgm;7CREATE INDEX ON products USING GIN (name gin_trgm_ops);8SELECT * FROM products WHERE name LIKE '%widget%';9 10SELECT * FROM orders WHERE status = 'pending'11UNION ALL12SELECT * FROM orders WHERE user_id = 456 AND status IS DISTINCT FROM 'pending';13 14SELECT indexrelname, idx_scan15FROM pg_stat_user_indexes16WHERE idx_scan = 0 AND schemaname = 'public'17ORDER BY pg_relation_size(indexrelid) DESC;

Remember this

Remove unused indexes, match expression indexes to predicates, and verify every new index with EXPLAIN.

Key takeaway

Indexes trade write overhead for read speed. Add the minimum set that fixes measured plans: foreign keys, hot filters, composites with correct order, expression indexes when predicates wrap columns. Audit unused indexes on a schedule.

Practice (25 min): Take one slow list/detail query from your app. Capture EXPLAIN (ANALYZE, BUFFERS) before changes. Add one index (or fix a defeating predicate), re-run on the same database, and record estimated vs actual rows plus the chosen node type. Bonus: query pg_stat_user_indexes for a zero-scan index and decide keep vs drop with one sentence of evidence.