Explain analyze and query optimization

EXPLAIN shows what the database plans to do. EXPLAIN ANALYZE actually runs the query and shows real timing. Together, they are your X-ray vision for understanding why a query is slow and how to fix it.

Reading a query plan

explain_read.sql
sql
-- EXPLAIN: see the plan (does not execute)
EXPLAIN
SELECT * FROM orders
WHERE order_status = 'delivered'
ORDER BY order_purchase_timestamp
LIMIT 10;

-- EXPLAIN ANALYZE: run the query and show real timing
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_status = 'delivered'
ORDER BY order_purchase_timestamp
LIMIT 10;

-- Key fields:
-- Seq Scan = reading every row (slow for large tables)
-- Index Scan = using an index (fast)
-- cost = estimated work units
-- actual time = real milliseconds
-- rows = estimated vs actual row count

Read plans bottom-up: inner operations feed into outer ones. Look for Seq Scan on large tables as the main bottleneck.

Knowing how to read plans is only half the battle. You also need to recognize common anti-patterns that silently prevent index usage.

anti_patterns.sql
sql
-- Anti-pattern 1: function on indexed column
-- BAD: index on order_purchase_timestamp is NOT used
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_purchase_timestamp) = 2018;

-- GOOD: rewrite to use the index
SELECT * FROM orders
WHERE order_purchase_timestamp >= '2018-01-01'
  AND order_purchase_timestamp < '2019-01-01';

-- Anti-pattern 2: implicit type cast
-- BAD: comparing text column with integer
-- SELECT * FROM t WHERE text_col = 123;
-- GOOD: match types explicitly
-- SELECT * FROM t WHERE text_col = '123';

-- Anti-pattern 3: SELECT *
-- BAD for production: fetches all columns
-- GOOD: SELECT only columns you need

Common anti-patterns that prevent index usage: wrapping indexed columns in functions, implicit type casts, and SELECT *.

PostgreSQL uses table statistics to estimate row counts. If statistics are outdated, estimates can be wildly wrong. Run ANALYZE on the table to update statistics. PostgreSQL auto-analyzes periodically, but after bulk inserts you may need to trigger it manually.

AI prompt: Query optimization assistant

Loading practice…

Quiz: Quiz

Loading practice…

Hints: Hints

Loading practice…