Dissecting an Execution Plan

Goal

You read EXPLAIN output from beginning to end and get a feel for which numbers to look at and in what order.

Why it matters

In an execution plan, a node's name by itself does not tell you whether it is good or bad. There are clearly situations in which a Seq Scan is faster than an index scan, and there are many cases where a Nested Loop is the best. Within the statistics it has, the optimizer almost always judges reasonably.

So when a plan looks strange, the question to ask is not "why is the optimizer stupid" but "what did I wrongly tell the optimizer". The gap between the expected and actual row counts tells you that answer.

Summarizing the reading order: first look at Execution Time and Planning Time. Next compare the expected rows and actual rows of each node and find the innermost node that is off by an order of magnitude or more. For nodes where loops is not 1, do the multiplication to work out the actual contribution. Look for an index opportunity at nodes where Rows Removed by Filter is large, and finally use the read ratio in Buffers to tell whether it is a plan problem or a cache problem.

Steps

Save all output files under /root/exp/.

Run any query that reads orders with only EXPLAIN in front of it, and save the output to /root/exp/plan_basic.txt. There must be no measured values (actual time).
Add the option that actually executes the same query, and save it to /root/exp/plan_analyze.txt. actual time and Execution Time must be present.
Save the plan of a join query that includes block read statistics to /root/exp/plan_buffers.txt. The Buffers: shared ... line must be present.
Create a table called skewed_events. Its columns are id and kind, it has 100,000 rows or more, and rows whose kind is rare must be 1 percent or less of the total. After creating it, collect statistics so that the planner knows the row counts.
With the other join methods temporarily turned off, run a join query and save the plan to /root/exp/plan_loops.txt. Nested Loop and a loops= with three or more digits must be present.
Run with measurement a query for which a hash join is chosen, and save it to /root/exp/plan_hash.txt. Hash Join and Batches: must be present.
Run the same query with measurement in the default state and in the state with sequential scans blocked, and append the two results to /root/exp/plan_forced.txt. Seq Scan, a node that uses an index, and Execution Time must each appear at least twice.
Run with measurement a plan in which the condition is handled as Index Cond and there is no Rows Removed by Filter, and save it to /root/exp/plan_fixed.txt.

Notes

Useful combination: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
Turn off join methods: SET enable_hashjoin = off; SET enable_mergejoin = off;
Turn off sequential scans: SET enable_seqscan = off; — this is for diagnosis. Do not leave it as a production setting.
Common mistake 1: if you add ANALYZE in step 1, measured values are included and grading fails.
Common mistake 2: if in step 4 you only create the table and skip the statistics collection, the planner does not know the row counts.

Look at only the plan without executing

Run any query that reads orders with only EXPLAIN in front of it, and save the output to /root/exp/plan_basic.txt. There must be no measured values (actual time).

If you attach just one keyword with no options, it does not execute the query and shows only the estimates.

Look at a plan with measured values

Add the option that actually executes the same query, and save it to /root/exp/plan_analyze.txt. actual time and Execution Time must be present.

There is an option that executes and measures for real. When you use it on a write query, wrap it in a transaction.

Turn on block read statistics

Save the plan of a join query that includes block read statistics to /root/exp/plan_buffers.txt. The Buffers: shared ... line must be present.

There is an option that distinguishes cache hits from disk reads. You can list several options in the parentheses separated by commas.

Create a table with skewed values and collect statistics

Create a table called skewed_events. Its columns are id and kind, it has 100,000 rows or more, and rows whose kind is rare must be 1 percent or less of the total. After creating it, collect statistics so that the planner knows the row counts.

Create 100,000 rows or more with generate_series, and make a particular value appear very rarely, at 1 percent or less. After creating it, you must collect statistics for the planner to know.

Observe a nested loop with many iterations

With the other join methods temporarily turned off, run a join query and save the plan to /root/exp/plan_loops.txt. Nested Loop and a loops= with three or more digits must be present.

If you block the other join methods for a moment, a nested loop is chosen. Check the loops value in the plan.

Check the number of batches of a hash join

Run with measurement a query for which a hash join is chosen, and save it to /root/exp/plan_hash.txt. Hash Join and Batches: must be present.

The measured information of a hash node appears only if you actually execute. Look at whether Batches is 1 or more.

Compare before and after forcing with measurements

Run the same query with measurement in the default state and in the state with sequential scans blocked, and append the two results to /root/exp/plan_forced.txt. Seq Scan, a node that uses an index, and Execution Time must each appear at least twice.

Run the same query in the default state and in the state with sequential scans blocked, and append them into one file.

Move a Filter to an Index Cond

Run with measurement a plan in which the condition is handled as Index Cond and there is no Rows Removed by Filter, and save it to /root/exp/plan_fixed.txt.

The goal is to make it not read in the first place, instead of reading and discarding. The Rows Removed by Filter line must disappear from the plan.