SQL EXPLAIN Analyzer

EXPLAIN shows the query planner's execution plan without running the query. EXPLAIN ANALYZE actually executes the query and reports the real execution times and row counts alongside the planner's estimates. The key insight is comparing estimated vs. actual rows — large discrepancies indicate stale table statistics. Run ANALYZE on a table to refresh statistics, or enable autovacuum to keep them current.

A Seq Scan reads every row in a table from disk. On a table with millions of rows this is very slow. PostgreSQL chooses a Seq Scan when it thinks it will be faster than an index scan — this happens when a large fraction of rows match the WHERE clause, or when table statistics are stale and it underestimates the row count. Solutions: add an index on the filtered column, run ANALYZE to refresh stats, or rewrite the query to be more selective.

Cost is a unitless estimate used by the planner to compare plans — it is not time in milliseconds. It's calculated from disk page reads (seq_page_cost / random_page_cost), CPU tuple processing (cpu_tuple_cost), and function call costs. The format is (startup_cost..total_cost): startup_cost is the cost before the first row is returned (important for LIMIT queries), total_cost is the cost to return all rows. Higher cost = more expensive, but actual execution time depends on hardware.

Row estimate mismatches (estimated 100, actual 50000) mean the query planner is making a bad plan based on wrong statistics. Fix steps: (1) Run ANALYZE table_name to refresh column statistics. (2) Increase default_statistics_target for highly skewed columns: ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; ANALYZE orders;. (3) For complex predicates, consider creating a statistics object: CREATE STATISTICS orders_status_amount ON status, total_amount FROM orders; ANALYZE orders;.

Add BUFFERS to see cache hit vs. disk read information: Buffers: shared hit=12345 read=678. A high 'hit' number means data is in shared_buffers (fast). A high 'read' number means disk I/O (slow). This helps diagnose whether a slow query is CPU-bound or I/O-bound. The full recommended command for diagnosis is: EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) your_query_here; Note: EXPLAIN ANALYZE actually executes the query, so avoid it on slow mutating queries (INSERT/UPDATE/DELETE) without wrapping in BEGIN/ROLLBACK.