Reading EXPLAIN and B-Tree Indexes

Plans tell you what will happen

EXPLAIN shows the plan the planner chose: a sequential scan reads the whole table; an index scan or bitmap index scan uses an index to read only matching rows. EXPLAIN ANALYZE actually runs the query and reports real row counts and timings. A default B-tree index speeds up equality and range filters, sorting and joins on its columns. Indexes cost disk space and slow writes slightly, so add them for real query patterns, and keep statistics fresh (autovacuum runs ANALYZE).

Make the right queries fast

EXPLAIN shows how PostgreSQL runs a query; indexes give it faster paths.

Before and after a B-tree index, run

I ran this with psql against PostgreSQL 16.2 on a fresh database with a 200,000-row events table (created with generate_series and analysed). Looking up one user's events scans the whole table; after creating an index on user_id, the planner uses a bitmap index scan instead. COSTS OFF hides cost estimates to keep the output short.

EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42;
CREATE INDEX events_user_id_idx ON events (user_id);
EXPLAIN (COSTS OFF) SELECT * FROM events WHERE user_id = 42;

Output:

        QUERY PLAN        
--------------------------
 Seq Scan on events
   Filter: (user_id = 42)
(2 rows)

                  QUERY PLAN                   
-----------------------------------------------
 Bitmap Heap Scan on events
   Recheck Cond: (user_id = 42)
   ->  Bitmap Index Scan on events_user_id_idx
         Index Cond: (user_id = 42)
(4 rows)

Use EXPLAIN (ANALYZE, BUFFERS)

Real timings and buffer counts reveal whether a plan is slow because of reading too much data.

Quick check: What does a Seq Scan in a plan mean?

• The table is empty
• It uses an index
• The query failed
• PostgreSQL reads every row of the table