An execution plan is the database engine’s planned sequence of operations for producing a query result. SQL describes the requested result, while PostgreSQL chooses how to perform it; the written query and executed operations can differ. Use EXPLAIN to inspect that choice before changing a query or creating an index. section 3.3.2 section 4.4
Read a plan by following its operations from the deepest scan or join toward the final result:
- Identify access methods such as Seq Scan, Index Scan, Bitmap Index Scan, or Bitmap Heap Scan. section 4.4 section 4.6.1
- Trace joins, filters, aggregation, sorting, uniqueness, and limiting to understand the execution flow. section 3.3.2 section 4.6.1
- Use EXPLAIN ANALYZE to add actual times, row counts, loops, planning time, and execution time; use buffers when I/O details matter. section 4.4
- Compare plans rather than assuming an index is faster: PostgreSQL may prefer a sequential scan when a predicate matches many rows. section 4.6.1 section 4.10
Compare a sequential scan with an index scan
For `SELECT count(*) FROM game.player_stats WHERE win_count > 100`, EXPLAIN reports an Aggregate over a Seq Scan because no index exists on `win_count`. A lookup by `player_id = 250` instead uses `Index Scan using player_stats_pkey`. EXPLAIN ANALYZE adds measured execution details, reporting one returned row and an execution time of 0.077 ms in the example. section 4.4
Inspecting a multi-CTE query
A least-popular-songs query defines `plays_cte` and `user_play_counts`, then ranks results. Its plan shows a Hash Join, GroupAggregate, Subquery Scan, Sort, and Limit. The separately defined `plays_cte` does not appear as its own plan node because PostgreSQL folds it into the later computation. section 3.3.2
Listing 3.3 Finding least-popular songs across three or more users
WITH plays_cte AS (
SELECT s.title, s.duration, p.play_duration, p.user_id
FROM streaming.plays p
JOIN streaming.songs s ON p.song_id = s.id
WHERE p.play_start_time::DATE BETWEEN '2024-09-15' AND '2024-09-16'
AND p.play_duration < (s.duration / 2)
),
user_play_counts AS (
SELECT title, duration, COUNT(DISTINCT user_id) AS user_count,
MIN(play_duration) AS min_play_duration,
COUNT(*) AS total_play_count
FROM plays_cte
GROUP BY title, duration
)
SELECT title, duration, min_play_duration, total_play_count
FROM user_play_counts
WHERE user_count >= 3
ORDER BY min_play_duration ASC
LIMIT 3;
Run the least-popular-songs query, then prepend `EXPLAIN (analyze, costs off, timing off)` to compare its readable SQL structure with PostgreSQL’s chosen operations. section 3.3.2
