Appendix C — EXPLAIN ANALYZE Reference

EXPLAIN ANALYZE is PostgreSQL’s primary tool for understanding why a query is fast or slow. It runs the query and returns the execution plan, including actual timings and row counts at each step. For analytical work — and especially for spatial queries where indexes can fail in subtle ways — being able to read its output is a core skill.

This appendix covers what the output looks like, what to look for, and the specific gotchas that have come up in the tutorial.

The basics

EXPLAIN returns a query plan. EXPLAIN ANALYZE runs the query and adds actual measured timings.

1
2

EXPLAIN ANALYZE
SELECT * FROM jobs WHERE status = 'pending';

A simple plan might look like:

Seq Scan on jobs  (cost=0.00..160631.21 rows=15000 width=165) 
                  (actual time=0.044..120.032 rows=14823 loops=1)
  Filter: ((status)::text = 'pending'::text)
  Rows Removed by Filter: 2485177
Planning Time: 0.285 ms
Execution Time: 173.811 ms

Reading bottom-up:

• Execution Time: how long the query took (173ms)
• Planning Time: how long PostgreSQL spent figuring out the plan
• Seq Scan on jobs: the operation — sequential scan of the jobs table
• cost=0.00..160631.21: the planner’s estimated cost (arbitrary units, but useful for comparing plans)
• rows=15000: estimated number of rows the operation would produce
• actual time=0.044..120.032: real measured time (start..end in milliseconds)
• rows=14823: actual rows produced
• loops=1: how many times the operation ran (matters for nested operations)
• Filter: the row-level condition being applied
• Rows Removed by Filter: rows the operation read but discarded

Compare planner estimates (rows=15000) against actual values (rows=14823). When estimates are wildly off, the planner has likely picked a suboptimal plan.

Plan shape

Plans are trees. Each node feeds rows to its parent. Read them bottom-up:

Sort                              <- ordered output
  Sort Key: completed_at DESC
  ->  Index Scan on jobs          <- selects rows via index
        Index Cond: ...
        Filter: ...

The Index Scan produces rows; the Sort consumes them and produces sorted rows. The top-level node is what the query actually returns.

Indentation indicates parent-child relationships in the tree. Each child feeds its parent.

Recognising index engagement

The most common question when looking at EXPLAIN output: is the index being used?

Index Scan — the index is engaging

Index Scan using index_jobs_on_completed_at on jobs
  Index Cond: (completed_at >= '2026-01-01')

PostgreSQL is using the index index_jobs_on_completed_at to find matching rows. The Index Cond is the predicate the index can answer directly.

Index Scan is good for selective queries — you want a small fraction of the table. Becomes less efficient than Seq Scan when the predicate matches a large fraction of rows (the planner often switches to Seq Scan in that case).

Bitmap Heap Scan — the index is engaging differently

Bitmap Heap Scan on jobs
  Recheck Cond: (status = 'pending')
  ->  Bitmap Index Scan on index_jobs_on_status
        Index Cond: (status = 'pending')

Two-stage: first build a bitmap of matching row positions from the index, then read the heap (table) to fetch those rows. PostgreSQL picks this for medium-selectivity queries — too many rows for plain Index Scan, but enough that Seq Scan is worse.

The index is being used. This is fine.

Seq Scan — the index is NOT engaging

Seq Scan on jobs
  Filter: (status = 'pending')
  Rows Removed by Filter: 2485177

PostgreSQL is reading every row in the table and applying the filter. No index involved.

This is sometimes correct (if the table is small, or the predicate matches most rows). But if you expected the index to engage and it isn’t, something’s wrong. Common causes are in Common gotchas below.

Spatial index variant

Spatial indexes show with the bounding-box && operator:

Index Scan using index_jobs_on_location
  Index Cond: (location && st_expand(query_point, 0.05))
  Filter: st_dwithin(location, query_point, 0.05)

The Index Cond: (... && ...) is the GIST index doing its bounding-box pre-filter work. The Filter is the precise check on the candidates the index returned. This is the pattern you want to see for indexed proximity queries.

If the spatial query shows Seq Scan with Filter: st_dwithin(...) or Filter: st_contains(...), the spatial index isn’t engaging. Common cause: a ::geography cast on the geometry column blocks the geometry index. See Cast blocks index below.

Common gotchas

The specific issues that have come up in the tutorial. Knowing these saves hours of “why is this query slow?”

Cast blocks spatial index

The most subtle PostGIS gotcha. The ::geography cast that’s correct for accurate distance measurement disables the geometry index for proximity filters.

1
2

-- Looks right, runs slow at scale
WHERE ST_DWithin(a.location::geography, b.location::geography, 5000)

EXPLAIN reveals:

Seq Scan on table
  Filter: st_dwithin((location)::geography, ..., '5000'::double precision)

Sequential scan, no index. The cast prevents the geometry’s GIST index from applying.

Fix: filter on geometry, order/measure on geography.

1
2
3

WHERE ST_DWithin(a.location, b.location, 0.05)            -- geometry, indexed
ORDER BY ST_Distance(a.location::geography,                -- geography, accurate
                     b.location::geography)

EXPLAIN should now show Index Scan ... Index Cond: (... && st_expand(...)).

This catches a lot of teams the first time they take a working small-scale query to production.

Planner picks wrong plan when FK is available

PostgreSQL’s planner doesn’t know that jobs.service_area_id and ST_Contains(sa.boundary, jobs.location) produce the same join. It evaluates each option as if they were unrelated.

For ad-hoc spatial joins on data without FKs, ST_Contains is correct. For data where the spatial relationship has been resolved into a foreign key column (the tutorial’s jobs.service_area_id), the FK join is dramatically faster — sometimes 20x.

EXPLAIN comparison reveals it. The spatial-join version:

Nested Loop Left Join (95M filter operations)
  Join Filter: st_contains(sa.boundary, j.location)
  Rows Removed by Join Filter: 95202726

The FK version:

Hash Right Join
  Hash Cond: (j.service_area_id = sa.id)

Same logical query, different plan, different time. When the FK exists, use it. Reserve ST_Contains for ad-hoc analysis and data without pre-computed assignments.

Stale statistics

The planner’s row-count estimates come from PostgreSQL’s statistics, which are gathered during autovacuum or explicit ANALYZE runs. After bulk loading, statistics can be out of date, leading to suboptimal plans.

If EXPLAIN’s estimated rows= values are wildly different from the actual rows= values, run:

1

ANALYZE table_name;

This refreshes statistics and gives the planner better information.

In the tutorial we hit a case where the planner picked a bad plan even with fresh statistics — at large scale, the choropleth queries needed a query rewrite (FK join) rather than a statistics refresh. But for typical optimisation work, ANALYZE is the first thing to try.

NOT IN / NOT EXISTS slow on large tables

Negative predicates often confuse the planner. WHERE status NOT IN ('complete', 'cancelled') on a 2.5M-row table can perform poorly because the planner has to consider all rows and exclude matches.

The flip is usually faster: WHERE status IN ('pending', 'scheduled', 'in_progress'). Same logical result if you know all possible values; better-handled by indexes.

Saw this in the dashboard’s active_jobs count.

Query parameters and plan caching

PostgreSQL caches plans for prepared statements. The first execution might use a generic plan that handles any parameter value; this can be slower than a custom plan for specific values.

ActiveRecord uses prepared statements, so a query that’s fast in psql might be slower when called from Rails. The planner usually recovers after a few executions, but if a query is consistently slow only when called via prepared statements, this can be why.

EXPLAIN (ANALYZE) EXECUTE my_prepared_statement(...) shows the prepared-statement plan if you’ve prepared one explicitly.

What “fast” looks like

For analytical queries on tables in the millions of rows:

• <10ms: index-driven query returning a few rows. Typical primary key lookup, indexed filter on small result.
• 10-100ms: index scan with reasonable result size, simple joins, well-tuned aggregations on indexed columns.
• 100-500ms: complex queries with aggregations, multiple joins, spatial operations on indexed data. Acceptable for analytical reports.
• 500ms-2s: heavy aggregations, large result sets, or operations that genuinely need to scan large parts of a table.
• >2s: usually a sign something can be improved. Check for missing indexes, cast issues, planner mis-estimates.

These are guidelines, not rules. A query that touches a million rows is going to take time no matter how well-tuned. But if a “simple-looking” query is taking seconds, the plan is worth examining.

Workflow for diagnosing slow queries

A standard workflow when a query feels too slow:

1. Run EXPLAIN ANALYZE with realistic input.
2. Look at total Execution Time — confirm it’s actually slow.
3. Find the most expensive node — usually the deepest one, or the one with the largest actual time and rows.
4. Check if expected indexes are engaging. Look for Index Scan or Bitmap Index Scan on relevant operations. If you see Seq Scan where you expected an index, investigate why.
5. Compare estimated vs actual rows. Wildly off estimates point to stale statistics.
6. Check for surprising plans — Nested Loop where Hash Join would be faster, Materialize on large result sets, sequential scans of large tables.
7. Try targeted fixes — ANALYZE, query rewrites, missing indexes, removing unnecessary casts.
8. Re-run EXPLAIN ANALYZE to confirm the improvement.

EXPLAIN options worth knowing

EXPLAIN accepts options in parentheses:

1
2

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT ...;

• ANALYZE — actually run the query (otherwise EXPLAIN just shows the plan without measurements)
• BUFFERS — shows buffer usage. Useful for identifying I/O bottlenecks.
• VERBOSE — shows extra detail (output columns, schema names)
• FORMAT JSON — output in JSON form. Easier to parse programmatically; harder to read by eye.
• SETTINGS — shows non-default planner settings in effect
• WAL — shows WAL activity for queries that modify data

For most diagnostic work, EXPLAIN ANALYZE (with the default text format) is enough. Reach for BUFFERS when you suspect I/O issues or want to see how much of the data was already in cache.

When EXPLAIN ANALYZE doesn’t help

A few situations where the tool falls short:

• Concurrent contention: a query that’s slow because of locks held by another transaction won’t show that in EXPLAIN. Look at pg_stat_activity and pg_locks.
• Cold cache effects: the first run of a query reads from disk; subsequent runs hit the cache. EXPLAIN ANALYZE timings include cache effects — run multiple times for stable numbers.
• JIT compilation overhead: PostgreSQL 11+ JIT-compiles complex queries. The first run includes compilation time. Usually irrelevant; can dominate for simple queries with complex expressions.
• Network and serialisation: EXPLAIN measures the database’s work, not the round-trip to your application. A 100ms database query that returns 50MB of JSON might appear fast in EXPLAIN but feel slow in the browser. Check the application-side breakdown too.

For end-to-end performance, EXPLAIN tells you about the database; the Rails log’s (Views: ... | ActiveRecord: ... | GC: ...) breakdown tells you about everything else.