CodePark/ questions / Query plans & optimisation
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Query plans & optimisation

Walk through how you read this EXPLAIN ANALYZE output and what you would conclude.

Seq Scan on orders  (cost=0.00..25480.00 rows=12 width=48) (actual time=0.031..412.552 rows=48210 loops=1)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 951790
Planning Time: 0.110 ms
Execution Time: 415.020 ms

Compare the estimated rows with the actual rows first, then look at how much work the filter wasted.

Each node shows the planner's estimate first:

  • cost is startup..total, in arbitrary planner units.
  • rows is the number of rows the planner expects.
  • width is the average row size in bytes.

The actual part shows what really happened:

  • real time in milliseconds (startup..total)
  • real rows
  • loops, the number of times the node ran

For nodes inside a nested loop, the figures are per loop, so multiply them by loops. In larger plans, read the tree from the innermost nodes outward.

Two things stand out here. First, the planner expected 12 rows but got 48,210. This means the statistics are stale or misleading. Run ANALYZE orders, and maybe raise the column's statistics target. Bad estimates matter because they lead to bad join choices further up the plan.

Second, the query read about a million rows to keep only 5%. An index on status would avoid this full scan. A partial index for pending orders would also work. Add the BUFFERS option to see how much I/O each node did.

  • Compare estimated rows with actual rows. A large gap means bad statistics.
  • cost is in planner units. Actual time is in milliseconds, per loop.
  • Rows Removed by Filter shows wasted work.
  • Fix it with ANALYZE and a suitable index.
  • The BUFFERS option shows I/O detail for each node.