EXPLAIN, EXPLAIN ANALYZE, and Statistics

EXPLAIN shows the planner's estimated execution plan. EXPLAIN ANALYZE executes the statement and reports actual timing and row counts alongside estimates. Use it to inspect sequential scans, index scans, join strategies, sorts, and estimated-versus-actual row counts. Remember that EXPLAIN ANALYZE really executes the query; for data-changing statements, it can change data unless wrapped in a transaction and rolled back.

Planner statistics are maintained by ANALYZE and autovacuum. A poor plan may reflect inaccurate statistics, skewed data, missing indexes, an unsuitable query, or a genuinely cheaper sequential scan. A sequential scan is not inherently bad, especially when a query needs much of the table.

Inspect a query plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 101;

💡 The plan includes actual execution information and buffer usage. Compare estimated rows with actual rows and inspect where time and I/O are spent before changing indexes or SQL.

Key Takeaways

EXPLAIN shows estimated plans; EXPLAIN ANALYZE executes the query.
Compare estimated and actual row counts.
A sequential scan can be the best plan for broad queries.

Knowledge Check

What is the key caution when using EXPLAIN ANALYZE on a data-changing statement?

EXPLAIN, EXPLAIN ANALYZE, and Statistics

STAY UPDATED WITH BYTES!