Read an EXPLAIN plan and pick the one fix

At a glance

Learning objectives

• Read a plan bottom-up and name the expensive node
• Detect cardinality misestimation from stale statistics
• Separate scan cost from compute time before choosing a fix

What you need

• EXPLAIN or EXPLAIN ANALYZE output for a slow query
• The target dialect (postgres/duckdb)
• [object Object]

Steps

1. Read a plan bottom-up and name the expensive node. (from skill-sql-query-plan )
2. Detect cardinality misestimation from stale statistics. (from skill-sql-query-plan )
3. Separate scan cost from compute time (bytes/partitions scanned vs queue time). (from runbook-warehouse-query-slow )
4. Compare the plan against the clustering/partition key. (from runbook-warehouse-query-slow )
5. Over-scan → fix the predicate or add/adjust the clustering key. (from runbook-warehouse-query-slow )
6. Decide between an index, a rewrite, or a stats refresh. (from skill-sql-query-plan )

Check yourself

• You can point to the single most expensive node and quote its estimated vs actual rows.
• The one recommended change states its expected effect and a way to measure it.

Worked example

Method
1. Read bottom-up; the deepest node is the first data touched.
2. Compare rows (estimate) to actual rows. >10x off means stale stats.
3. Look for Seq Scan on a large table that should use an index.
4. Look for Sort / Hash spilling to disk (disk usage in the plan).

Output: one sentence per expensive node, then exactly one recommended
change with the expected effect and a way to measure it.

SELECT query_id, partitions_scanned, partitions_total, bytes_scanned
FROM table(information_schema.query_history_by_session())
ORDER BY start_time DESC LIMIT 10;

Over-scan → fix the predicate or add/adjust the clustering key.
Compute-bound with high queue time → add clusters, not size.
Rebuild clustering on a low-selectivity key.

Sourced from

• SQL Query Plan Explainer
• Warehouse query is slow

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• >10x off between estimated and actual rows means stale stats — refresh them before buying an index.
• Bytes scanned and queue time are different diseases — sizing up a scan-bound query buys nothing.
• Clustering on a low-selectivity key stops pruning entirely.

Production considerations

• Alert on the partitions_scanned/partitions_total ratio.
• Review weekly query history for the top-cost queries.

Diagram

query plan read
   |
   |- read bottom-up --> name the expensive node
   |
   |- rows vs actual rows --> >10x off --> stale stats --> refresh
   |
   |- scan cost vs compute --> over-scan --> fix predicate / recluster
   |
   |- one change --> index, rewrite, or stats refresh --> measure

Registry stats