Kaushik's Notes

Indexes and Query Performance

12 min read

Overview

Lab Table

-- 200k synthetic events (deterministic, ~1 second to build)
DROP TABLE IF EXISTS events;
CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    int NOT NULL,
    kind       text NOT NULL,             -- view 79%, click 15%, purchase 5%, refund 1%
    email      text NOT NULL,
    amount     numeric(10,2),
    props      jsonb NOT NULL,
    created_at timestamptz NOT NULL
);
INSERT INTO events (user_id, kind, email, amount, props, created_at)
SELECT (g * 7919) % 10000 + 1,
       CASE WHEN g % 97 = 0 THEN 'refund' WHEN g % 20 = 0 THEN 'purchase'
            WHEN g % 5 = 0 THEN 'click' ELSE 'view' END,
       'User' || g || '@Example.com',
       CASE WHEN g % 20 = 0 THEN (g % 500) + 0.99 END,
       jsonb_build_object('device', (ARRAY['ios','android','web'])[1 + g % 3], 'campaign', 'c' || (g % 50)),
       timestamptz '2025-01-01 00:00+00' + g * interval '1 minute'
FROM generate_series(1, 200000) AS g;
VACUUM ANALYZE events;    -- fresh statistics + visibility map (needed for index-only scans)

Reading EXPLAIN

Variants

CommandRuns QueryShows
EXPLAINNoEstimated plan + costs
EXPLAIN ANALYZEYes (wrap writes in BEGIN … ROLLBACK)Actual rows, time, loops
EXPLAIN (ANALYZE, BUFFERS)Yes+ pages hit in cache / read from disk (default with ANALYZE in PG 18)
EXPLAIN (COSTS OFF)NoPlan shape only — handy for notes/tests
EXPLAIN (FORMAT JSON)NoMachine-readable, paste into visualisers (explain.dalibo.com)

How to Read a Node

Index Scan using events_user_created_idx on events  (cost=0.42..21.30 rows=20 width=16) (actual time=0.010..0.015 rows=5 loops=1)
│                                                    │     │      │       │     │               │                     │       │
│                                                    │     │      │       │     │               │                     │       └ times this node ran (multiply!)
│                                                    │     │      │       │     │               │                     └ rows actually returned (per loop)
│                                                    │     │      │       │     │               └ ms to first row .. ms to last row (per loop)
│                                                    │     │      │       │     └ avg row width in bytes
│                                                    │     │      │       └ ESTIMATED rows
│                                                    │     │      └ total cost (arbitrary units)
│                                                    │     └ startup cost
└ node type: how rows are produced
  • Read Inside-Out: The most indented node runs first and feeds its parent
  • Estimate vs Actual: rows=20 estimated vs rows=20000 actual → stale stats or correlated columns → ANALYZE, extended statistics
  • Rows Removed by Filter: Large number = the index didn’t cover that condition
  • loops: Inner side of a Nested Loop runs once per outer row — actual time × loops is the real cost
  • Buffers shared read: Pages fetched from disk/OS cache; hit = Postgres buffer cache

Common Node Types

NodeMeaningGood When
Seq ScanRead whole tableSmall table or most rows needed
Index ScanWalk index, fetch each heap rowFew rows, or ordered output needed
Index Only ScanAnswer from index alone (Heap Fetches: 0 ideal)Covering index + well-vacuumed table
Bitmap Index/Heap ScanCollect TIDs, then read heap pages in orderMedium selectivity, OR / AND of indexes
Nested LoopFor each outer row, probe innerSmall outer, indexed inner
Hash JoinBuild hash of one side, probe with otherBig unsorted equi-joins
Merge JoinMerge two sorted inputsBoth already sorted
Sort / Incremental SortSort rows (Sort Method: external merge = spilled to disk)Raise work_mem or add an index on the order
HashAggregate / GroupAggregateGROUP BY via hash or sorted input
Gather / Parallel Seq ScanParallel workersLarge scans
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, sum(oi.quantity * oi.unit_price)
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 1
GROUP BY o.id;

CREATE INDEX Variants

B-tree (Default)

  • Supports: =, <, <=, >, >=, BETWEEN, IN, IS NULL, ORDER BY, prefix LIKE 'abc%' (needs text_pattern_ops or C collation)
  • Before: Seq Scan … Rows Removed by Filter: 199980
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE user_id = 42;
 
CREATE INDEX events_user_id_idx ON events (user_id);
 
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE user_id = 42;
-- before
Seq Scan on events (actual rows=20 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 199980
-- after
Bitmap Heap Scan on events (actual rows=20 loops=1)
  Recheck Cond: (user_id = 42)
  Heap Blocks: exact=20
  ->  Bitmap Index Scan on events_user_id_idx (actual rows=20 loops=1)
        Index Cond: (user_id = 42)

Composite Index and Column Order

  • Leftmost Prefix Rule: Index on (a, b) serves WHERE a = … and WHERE a = … AND b …, but not WHERE b = … alone (efficiently)
  • Order Matters: Equality columns first, then the range / ORDER BY column
  • Removes Sorts: WHERE user_id = ? ORDER BY created_at DESC LIMIT n becomes a short index walk
-- Single-column index: needs a Sort on top
EXPLAIN (COSTS OFF)
SELECT id, created_at FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;
 
CREATE INDEX events_user_created_idx ON events (user_id, created_at DESC);
 
-- Composite: rows come out of the index already ordered — no Sort node
EXPLAIN (COSTS OFF)
SELECT id, created_at FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;
-- before                                        -- after
Limit                                            Limit
  ->  Sort                                         ->  Index Scan using events_user_created_idx on events
        Sort Key: created_at DESC                        Index Cond: (user_id = 42)
        ->  Bitmap Heap Scan on events
              ->  Bitmap Index Scan on events_user_id_idx

Covering Index (INCLUDE)

  • What: Store extra non-key columns in the index leaf so the query never touches the table → Index Only Scan
  • INCLUDE vs Adding to Key: Included columns don’t affect ordering/uniqueness and keep the upper tree small
  • Needs Vacuum: Index-only scans check the visibility map; un-vacuumed pages show up as Heap Fetches
CREATE INDEX events_user_created_cov ON events (user_id, created_at DESC) INCLUDE (kind, amount);
VACUUM events;
 
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT created_at, kind, amount FROM events WHERE user_id = 42 ORDER BY created_at DESC LIMIT 5;
Limit (actual rows=5 loops=1)
  ->  Index Only Scan using events_user_created_cov on events (actual rows=5 loops=1)
        Index Cond: (user_id = 42)
        Heap Fetches: 0

Partial Index

  • What: Index only rows matching a WHERE — smaller, faster, cheaper to maintain
  • Use: Hot subsets (status = 'queued', deleted_at IS NULL, rare kinds), partial uniqueness
  • Rule: The query’s WHERE must imply the index predicate
CREATE INDEX events_refunds_idx ON events (created_at) WHERE kind = 'refund';
 
EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE kind = 'refund' AND created_at >= '2025-03-01';
 
SELECT pg_size_pretty(pg_relation_size('events_refunds_idx')) AS partial_idx,     -- 64 kB
       pg_size_pretty(pg_relation_size('events_user_id_idx')) AS full_idx;        -- ~1.5 MB
Index Scan using events_refunds_idx on events
  Index Cond: (created_at >= '2025-03-01 00:00:00+00'::timestamp with time zone)

Expression Index

  • What: Index the result of an expression; the query must use the exact same expression
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE lower(email) = 'user42@example.com';   -- Parallel Seq Scan
 
CREATE INDEX events_lower_email_idx ON events (lower(email));
ANALYZE events;                         -- expression indexes get their own statistics
 
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE lower(email) = 'user42@example.com';   -- Index Scan
Index Scan using events_lower_email_idx on events
  Index Cond: (lower(email) = 'user42@example.com'::text)

GIN for JSONB, Arrays and Full-Text

Operator ClassSupportsSize
jsonb_ops (default)@>, ?, `?, ?&, @?, @@`
jsonb_path_ops@>, @?, @@ onlySmaller, faster for containment
array_ops@>, <@, &&, = on arrays
gin_trgm_ops (pg_trgm)LIKE '%x%', ILIKE, ~, similarity
CREATE INDEX events_props_gin ON events USING gin (props jsonb_path_ops);
 
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE props @> '{"campaign": "c7"}';
Aggregate
  ->  Bitmap Heap Scan on events
        Recheck Cond: (props @> '{"campaign": "c7"}'::jsonb)
        ->  Bitmap Index Scan on events_props_gin
              Index Cond: (props @> '{"campaign": "c7"}'::jsonb)

Other Index Types

TypeBest For
HashPure equality on big keys (WAL-logged since PG 10); B-tree usually just as good
GiSTRanges, geometry, exclusion constraints, nearest-neighbour
SP-GiSTPartitioned spaces — IPs, quad-trees, text prefixes
BRINHuge append-only tables where values correlate with physical order (timestamps) — tiny index

UNIQUE and CONCURRENTLY

  • Plain CREATE INDEX: Blocks writes (SHARE lock) for the whole build — OK in dev, dangerous on hot tables
  • CREATE INDEX CONCURRENTLY: Doesn’t block writes; takes longer (two table scans), can’t run inside a transaction block
  • If It Fails: Leaves an INVALID index — drop it and retry (DROP INDEX CONCURRENTLY)
  • REINDEX CONCURRENTLY: Rebuild a bloated index without blocking (PG 12+)
CREATE INDEX CONCURRENTLY IF NOT EXISTS events_kind_idx ON events (kind);
 
-- Find invalid leftovers from failed concurrent builds
SELECT indexrelid::regclass AS index_name FROM pg_index WHERE NOT indisvalid;

Index Anti-Patterns

1. Function or Cast on the Indexed Column

CREATE INDEX events_created_idx ON events (created_at);
 
-- Bad: cast on the column → Filter over the entire index, no Index Cond
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE created_at::date = '2025-02-01';
 
-- Good: half-open range on the raw column → Index Cond
EXPLAIN (COSTS OFF) SELECT count(*) FROM events
WHERE created_at >= '2025-02-01' AND created_at < '2025-02-02';
-- bad                                                        -- good
Parallel Index Only Scan using events_created_idx              Index Only Scan using events_created_idx
  Filter: ((created_at)::date = '2025-02-01'::date)              Index Cond: ((created_at >= …) AND (created_at < …))
  • Same for: lower(col), date_trunc(…, col), col + 1 = 5, coalesce(col, 0) = 0 — rewrite or add an expression index

2. Implicit Casts / Type Mismatch

-- user_id is int; a numeric literal casts the COLUMN to numeric → index unusable
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42.0;
-- Filter: ((user_id)::numeric = 42.0)   ← Parallel Seq Scan
 
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42;      -- index used
  • Real-World Causes: ORMs binding bigint vs numeric, comparing varchar to text[], joining int to bigint across tables (fine) vs int to text (not fine), UUIDs stored as text

3. Leading Wildcard LIKE

-- B-tree can serve a prefix match only with text_pattern_ops (or C collation)
CREATE INDEX events_email_pattern_idx ON events (email text_pattern_ops);
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE email LIKE 'User4211%';   -- Index Scan (range on prefix)
 
-- Leading % can't use a B-tree at all → Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE email LIKE '%4211@%';
 
-- Fix: trigram GIN index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX events_email_trgm_idx ON events USING gin (email gin_trgm_ops);
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE email LIKE '%4211@%';     -- Bitmap Index Scan on events_email_trgm_idx

4. OR Across Columns

  • Problem: WHERE a = 1 OR b = 2 needs an index on both columns (combined with BitmapOr); if one side is unindexed the whole query falls back to a Seq Scan
  • Fixes: Index both columns, rewrite as UNION, or use IN (…) / = ANY for same-column ORs
-- amount has no index → Parallel Seq Scan for the whole OR
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42 OR amount > 498;
 
-- Both sides indexed → BitmapOr of two index scans
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42 OR email = 'User1@Example.com';
 
-- UNION rewrite (each branch uses its own best plan)
EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE user_id = 42
UNION
SELECT * FROM events WHERE email = 'User1@Example.com';

5. Low Selectivity

  • Rule of Thumb: If a predicate matches more than ~5–20% of the table, a Seq Scan is cheaper than random index lookups — the planner is right to ignore your index
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE kind = 'view';     -- 79% of rows → Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE kind = 'refund';   -- 1% → Index Scan using events_refunds_idx (the partial index)

6. Other Classics

Anti-patternWhy It HurtsFix
Unindexed FK columnsJoins scan; DELETE on parent scans childIndex child FK columns
SELECT *Prevents index-only scans, more I/OSelect needed columns
NOT IN (subquery)No anti-join, NULL bugNOT EXISTS (Joins)
Deep OFFSETReads and discards rowsKeyset pagination (SELECT Fundamentals)
Too many indexesEvery write updates every index; blocks HOT updatesDrop unused (pg_stat_user_indexes.idx_scan = 0)
Stale statisticsBad row estimates → bad plansANALYZE; tune autovacuum
count(*) on huge tables for UIFull scan under MVCCEstimate from pg_class.reltuples

Planner Statistics Tools

-- What the planner believes about a column
SELECT attname, n_distinct, null_frac, most_common_vals
FROM pg_stats
WHERE tablename = 'events' AND attname IN ('kind', 'user_id');
 
-- Correlated columns: teach the planner they move together
CREATE STATISTICS IF NOT EXISTS events_kind_amount (dependencies) ON kind, amount FROM events;
ANALYZE events;
 
-- Session-level experiments (never in production config)
SET enable_seqscan = off;
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE kind = 'view' LIMIT 1;
RESET enable_seqscan;

Interview Questions

  • “Index exists but isn’t used — why?”: Function/cast on the column, type mismatch, leading wildcard, low selectivity, stale stats, OR with an unindexed column
  • “EXPLAIN vs EXPLAIN ANALYZE?”: Estimate only vs actually execute and report real rows/time (careful with writes)
  • “Composite index column order?”: Equality filters first, then range/sort column; leftmost prefix rule
  • “What is a covering index?”: Index containing every column the query needs (INCLUDE) → index-only scan
  • “Add an index to a busy production table?”: CREATE INDEX CONCURRENTLY, outside a transaction, check for INVALID afterwards
  • “Index a JSONB column?”: GIN (jsonb_path_ops for @>), or a B-tree expression index on (data->>'key')

Summary

  • EXPLAIN ANALYZE + BUFFERS: Compare estimated vs actual rows, watch Rows Removed by Filter, loops and Sort spills
  • Index Toolkit: B-tree, composite, INCLUDE covering, partial, expression, GIN (jsonb/trgm), BRIN for time series
  • Production: CONCURRENTLY for create/reindex/drop; clean up INVALID indexes
  • Anti-patterns: Functions/casts on columns, implicit type casts, LIKE '%x', mixed-column ORs, low selectivity
  • Balance: Every index speeds reads but slows writes — measure with pg_stat_user_indexes (Admin and Inspection Queries)

Graph View

Backlinks