Database indexes: B-trees, composites and reading EXPLAIN

HOW IT FITS TOGETHER

KEY TERMS

Composite column order

A B-tree on (a, b, c) is sorted by a, then b, then c. Equality conditions on leading columns plus one range condition on the next column bound the scan; conditions on later columns are only checked inside that range. Put equality-filtered columns first and the range or ORDER BY column last, so (tenant_id, status, created_at) serves WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20 without a sort.

Covering indexes and index-only scans

If every column a query reads is in the index, PostgreSQL can answer from the index alone, provided the visibility map marks the heap pages all-visible (keep VACUUM healthy). INCLUDE (col) adds non-key payload columns that are returned but not searchable and not part of uniqueness. Only B-tree, GiST and SP-GiST support INCLUDE; GIN never does index-only scans. "Heap Fetches" in EXPLAIN shows how often the shortcut failed.

Partial and expression indexes

A partial index (WHERE status = 'pending') stores only the rows a hot query touches, so it stays small; a partial UNIQUE index enforces rules such as one active subscription per user. An expression index on lower(email) or (data->>'tenant_id') serves queries that use exactly that expression. Functions must be IMMUTABLE, and expression indexes have no statistics until the next ANALYZE.

Write cost

Every INSERT, and every UPDATE that cannot be HOT, must also update each index, write WAL for it and later vacuum it. A table with ten indexes pays ten times on writes, and an indexed column blocks HOT updates (heap-only tuple updates that skip index maintenance) for rows that change it. Unused indexes are pure cost, including on replicas and backups.

Reading EXPLAIN (ANALYZE, BUFFERS)

Compare estimated rows with actual rows per node: a 100x gap means stale or missing statistics, and the plan built on it is suspect. Buffers shared hit means pages in shared memory and read means pages fetched from the OS or disk; that I/O, not cost units, is what users feel. Watch for Rows Removed by Filter, Sort Method: external merge, and loops multiplying a per-loop time. In PostgreSQL 18, ANALYZE includes BUFFERS automatically.

MySQL InnoDB clustered index

InnoDB stores the table itself inside the primary key B-tree (or the first NOT NULL UNIQUE index, or a hidden 6-byte row ID). Secondary index entries hold the primary key, not a row pointer, so a secondary lookup is two B-tree descents, a wide primary key bloats every secondary index, and random UUIDv4 keys fragment the table. PostgreSQL has no clustered index; its heap is unordered and CLUSTER is a one-time rewrite.

IN YOUR STACK

-- Hot query: a tenant's latest paid orders.
SELECT id, total, created_at
FROM orders
WHERE tenant_id = $1 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

-- Equality columns first, then the sort column in the query's direction.
-- INCLUDE makes it covering, so the scan can be index-only.
CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
  ON orders (tenant_id, status, created_at DESC)
  INCLUDE (id, total);

-- Also served (leading-column prefix):
--   WHERE tenant_id = $1
--   WHERE tenant_id = $1 AND status = 'paid' AND created_at >= now() - interval '7 days'
-- Not served efficiently:
--   WHERE status = 'paid'            (no leading column; skip scan only if few tenants)
--   WHERE tenant_id = $1 ORDER BY total

-- 99% of jobs are done; the worker only ever looks at pending ones.
CREATE INDEX CONCURRENTLY jobs_pending_run_at_idx
  ON jobs (run_at)
  WHERE status = 'pending';

-- Uses it: the WHERE clause matches the predicate literally.
SELECT id FROM jobs
WHERE status = 'pending' AND run_at <= now()
ORDER BY run_at
LIMIT 100;

-- Business rule as an index: one active subscription per user.
CREATE UNIQUE INDEX subscriptions_one_active_per_user
  ON subscriptions (user_id)
  WHERE canceled_at IS NULL;

-- Soft deletes: unique email among live users only.
CREATE UNIQUE INDEX users_email_live_key
  ON users (lower(email))
  WHERE deleted_at IS NULL;

-- Case-insensitive login lookup.
CREATE INDEX CONCURRENTLY users_lower_email_idx ON users (lower(email));
SELECT id FROM users WHERE lower(email) = lower($1);   -- uses the index
SELECT id FROM users WHERE email ILIKE $1;            -- does not

-- One field out of a jsonb document, filtered and sorted as text.
CREATE INDEX CONCURRENTLY events_tenant_idx ON events ((payload->>'tenant_id'));
SELECT * FROM events WHERE payload->>'tenant_id' = $1;

-- Day bucket: date_trunc on timestamptz depends on TimeZone, so it is not IMMUTABLE.
-- Fix the zone explicitly to make the expression immutable.
CREATE INDEX CONCURRENTLY orders_day_utc_idx
  ON orders ((date_trunc('day', created_at AT TIME ZONE 'UTC')));

-- Expression indexes have no statistics until the table is analyzed.
ANALYZE users, events, orders;

-- jsonb containment. jsonb_path_ops: smaller and faster for @>, @?, @@,
-- but does not support the key-exists operators ?, ?| and ?&.
CREATE INDEX CONCURRENTLY products_attrs_gin
  ON products USING GIN (attrs jsonb_path_ops);
SELECT id FROM products WHERE attrs @> '{"color": "red", "size": "M"}';

-- Full-text search on a stored generated tsvector.
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')), 'B')
  ) STORED;
CREATE INDEX CONCURRENTLY articles_search_gin ON articles USING GIN (search);

SELECT id, ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at FROM orders
WHERE tenant_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

-- Before (abbreviated): reads ~10k pages, sorts, throws away 2M rows.
-- Limit  (actual time=213.9..214.0 rows=20 loops=1)
--   Buffers: shared hit=1204 read=9811          <- read = not in shared buffers
--   ->  Sort  (actual time=213.9..213.9 rows=20 loops=1)
--         Sort Key: created_at DESC
--         Sort Method: top-N heapsort  Memory: 27kB
--         ->  Seq Scan on orders  (rows=310) (actual rows=296 loops=1)
--               Filter: ((tenant_id = 42) AND (status = 'paid'::text))
--               Rows Removed by Filter: 1999704   <- the index is missing

-- After the composite covering index (abbreviated):
-- Limit  (actual time=0.031..0.052 rows=20 loops=1)
--   Buffers: shared hit=5
--   ->  Index Only Scan using orders_tenant_status_created_idx on orders
--         Index Cond: ((tenant_id = 42) AND (status = 'paid'::text))
--         Heap Fetches: 0                         <- visibility map did its job

-- Unused: never scanned since the last stats reset, largest first.
SELECT s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan, s.last_idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique          -- unique and PK indexes enforce constraints
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- Missing: tables read mostly by sequential scans of many rows.
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       seq_tup_read / greatest(seq_scan, 1) AS rows_per_seq_scan
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Which statements cost the most overall (requires pg_stat_statements).
SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       shared_blks_read, left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Must run outside a transaction block: disable your migration tool's
-- per-migration transaction for this step.
SET lock_timeout = '5s';        -- do not queue behind a long-running DDL lock
SET maintenance_work_mem = '1GB';

CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);

-- Watch progress from another session.
SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index;

-- On failure (deadlock, duplicate for UNIQUE, cancel) an INVALID index remains.
-- It is not used by queries but still slows every write.
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY IF EXISTS orders_customer_idx;
CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);
-- or: REINDEX INDEX CONCURRENTLY orders_customer_idx;

WHERE IT BITES

• Indexing every column one by one. Single-column indexes rarely combine as well as one composite index in the right order, and each one taxes every write.
• Wrapping the indexed column in a function or cast in the query (WHERE date(created_at) = ..., WHERE id::text = $1, mismatched parameter types), which silently stops the index from being used.
• Expecting an index to help low-selectivity filters. If a condition matches a large share of the table, a sequential scan is cheaper and the planner is right to ignore the index.
• Adding indexes in a migration without CONCURRENTLY on a busy table, which blocks writes for minutes and queues every later query behind the lock.
• Leaving INVALID indexes behind after a failed concurrent build. They are skipped by queries but still maintained on every write.
• Dropping an index because idx_scan = 0 on the primary while replicas, monthly jobs or a unique constraint depend on it.

WHEN IT BREAKS IN PRODUCTION

• A query that was fast gets slow as the table grows
• A migration locks a table and requests pile up
• An endpoint gets slower as results grow
• Concurrent writes fail with "deadlock detected"

SOURCES

• PostgreSQL 18: Index Types (opens in new tab)Checked 2026-11-06
• PostgreSQL 18: Multicolumn Indexes (opens in new tab)Checked 2026-11-06
• PostgreSQL 18: Index-Only Scans and Covering Indexes (opens in new tab)Checked 2026-11-06
• PostgreSQL 18: CREATE INDEX (opens in new tab)Checked 2026-11-06
• PostgreSQL 18: EXPLAIN (opens in new tab)Checked 2026-11-06
• MySQL 8.4: Clustered and Secondary Indexes (opens in new tab)Checked 2026-11-06