Reading PostgreSQL EXPLAIN

  • Concept. EXPLAIN shows PostgreSQL’s chosen plan and estimated costs. Read from the leaves upward.

  • Intuition. Follow the rows from scans through joins, grouping, and sorting to see how PostgreSQL answers the query.

  • Resources. Interactive query explainer (Colab).

The same recommendation query

-- Highly recommended songs
SELECT r.song_id, COUNT(r.recommendation_id) AS recommendation_count
FROM Recommendations AS r
JOIN Listens AS l ON r.user_id = l.user_id AND r.song_id = l.song_id
GROUP BY r.song_id
ORDER BY COUNT(r.recommendation_id) DESC;

What PostgreSQL chose

Actual recommendation-query plan: scan Listens and Recommendations, hash join on user and song, hash aggregate by song, then sort by count.

Figure 1. Actual PostgreSQL 17.9 output, using the Query Plans Colab’s schema and nine listens plus four added sample recommendations. The original Colab leaves Recommendations empty. After ANALYZE, PostgreSQL chose Hash Join, HashAggregate, and Sort. The groups fit in RAM, so this plan avoids the extra repartition in the hand-worked Plan 2. These tiny-data costs are not predictions for large tables.

Reading the estimates

  • rows: estimated rows emitted by that operator, not the actual count.

  • cost=startup..total: planner cost units; they include more than IO and are not milliseconds. A parent’s cost includes its children: do not sum all node costs.

  • EXPLAIN ANALYZE: executes the query and reports actual rows and timing alongside estimates.

The join estimates and returns three rows. HashAggregate estimates three groups but returns two: song 2 has count 2; song 1 has count 1. The final Sort orders those counts.

Where do the estimates come from?

PostgreSQL collects table and column statistics with ANALYZE. Row counts, distinct-value counts, common-value frequencies, and histograms help predict the data flowing through each operator. The next section explains those summaries.

Common EXPLAIN Operations Reference

Operation What it does IO cost Output rows Output size
Sort Order rows BigSort cost same as input = input
WindowAgg ROW_NUMBER, RANK, etc. C_r × P(input) + sort same as input slightly larger
CTE Scan Reads WITH result C_r × P(CTE) = CTE rows = CTE size
Subquery Scan Reads subquery output C_r × P(subquery) = subquery rows = subquery size
Index Scan Uses index log(N) + matched rows filtered rows ≪ input
Hash Join Build hash, probe Depends on memory and spill; not always external HPJ join matches varies
HashAggregate Group via hash table C_r × P(input) one per group ≪ input
Materialize Cache result in memory C_w + n × C_r same as input = input