Skip to Content

EXPLAIN ANALYZE & Index Strategies

When to use

A feature query is slow and you need to know WHY — what Postgres does, in what order, with what indexes, and where time is spent.

Analogy

EXPLAIN ANALYZE is the flight data recorder of a query: the plan is the route, ANALYZE adds measured timing, BUFFERS shows cache hits/misses.

Data-flow diagram

  EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
    SELECT ... FROM events WHERE user_id = 42 ...;

  Plan tree:
  Index Scan using idx_events_user_ts on events
     (cost=0.43..8.45 rows=100) (actual time=0.4..1.2 rows=95)
     Buffers: shared hit=4

Deep explanation

EXPLAIN shows the planned execution tree; adding ANALYZE runs the query and reports actual vs estimated rows, total time, and buffer hits. The biggest red flags: Seq Scan on a large filter, actual rows wildly different from estimated, nested loops over huge inputs, and ‘Filter:’ appended to an index scan (meaning the index found the row but couldn’t satisfy the predicate). Index strategies: B-tree for equality and range; GIN for full-text and JSONB containment; BRIN for huge append-only time-series; HNSW/IVFFlat in pgvector for ANN.

Examples

Example 1

-- 14a: read the plan with timing info
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
  FROM events
 WHERE user_id = 42
   AND ts >= NOW() - INTERVAL '7 days'
 ORDER BY ts DESC
 LIMIT 100;

BUFFERS shared hit=4 tells you the index is in cache; shared read=N tells you disk I/O happened.

Example 2

-- 14b: choose the right index for the predicate
CREATE INDEX idx_events_user_ts ON events (user_id, ts DESC);
 
-- for jsonb containment:
CREATE INDEX idx_metadata_tags ON events USING gin (metadata jsonb_path_ops);
 
-- for very large append-only time-series:
CREATE INDEX idx_events_ts_brin ON events USING brin (ts) WITH (pages_per_range=32);

Composite index on (user_id, ts DESC) matches the WHERE+ORDER BY perfectly: index-only scan, no separate sort.

Example 3

-- 14c: HNSW for ANN recall, IVFFlat for memory-tight
CREATE INDEX idx_chunks_vec_hnsw
   ON chunks USING hnsw (embedding vector_cosine_ops)
   WITH (m=16, ef_construction=64);
 
-- pg_stat_statements helps find the worst offenders:
SELECT query, calls, mean_exec_time, rows
  FROM pg_stat_statements
 ORDER BY mean_exec_time DESC LIMIT 10;

BRIN compresses to a tiny footprint for natural-order append-only tables — perfect for IoT, logs, event streams.

Common mistake

Adding btree indexes on every column ‘just in case’ — slows writes, bloats storage. Another trap: trusting row estimates — Postgres’ planner uses ANALYZE statistics; if they’re stale, the plan is wrong even with a perfect index. Refresh statistics with ANALYZE table_name after big ingest. Also: low-cost items in EXPLAIN can still dominate runtime if executed millions of times.

Key takeaway

EXPLAIN (ANALYZE, BUFFERS) is mandatory for any production query slow-down; match index TYPE to predicate: B-tree for ranges/equality, GIN for JSONB/tsvector, BRIN for huge append-only, HNSW for vectors; verify plans against ANALYZE-updated stats.

Production Failure Playbook

Failure scenario 1: seq-scan-on-filtered-lookup

Failure scenario 2: hnsw-build-blocks-writes