EXPLAIN ANALYSE and query tuning — SQL & Relational Databases

EXPLAIN is PostgreSQL's window into the query planner's decision-making. Before executing a query, the planner generates multiple candidate execution plans and selects the one with the lowest estimated cost. EXPLAIN shows the chosen plan as a tree of nodes — each node is an operation (Seq Scan, Index Scan, Hash Join, Sort, Aggregate) with estimated cost, row count, and width in bytes. EXPLAIN ANALYZE executes the query and adds actual timing and row counts alongside the estimates, revealing whether the planner's model of the data matches reality. EXPLAIN (ANALYZE, BUFFERS) additionally shows cache hit and disk read statistics per node, completing the diagnostic picture needed to identify I/O-bound versus CPU-bound bottlenecks.

Reading an EXPLAIN plan is a foundational data engineering skill because it is the primary tool for understanding why a query is slow and what specific change would make it faster. A Seq Scan on a 100-million-row table where an Index Scan was expected reveals a missing index or non-sargable predicate. An estimated 10 rows that produces 10 million actual rows reveals stale table statistics causing the planner to choose an inefficient join algorithm. A Sort node consuming 500MB of memory before a LIMIT 10 reveals a missing sort-direction index that would eliminate the sort entirely. Each observation in the EXPLAIN plan points to one specific, actionable fix.

Query tuning is the iterative process of identifying the most expensive operation in the plan, hypothesising its root cause, applying the targeted fix, and measuring the improvement. Common fixes include: creating a missing index, rewriting a non-sargable predicate into sargable form, updating statistics with ANALYZE after a bulk load, increasing work_mem to prevent disk sort spill, rewriting a correlated subquery as a JOIN, or partitioning a large table to enable partition pruning. The discipline of reading the plan before acting — rather than guessing which fix to try — is what separates systematic query tuning from random experimentation.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.