Skip to main content

Query Profiling and EXPLAIN

Learning Focus

Use this lesson to diagnose slow queries with EXPLAIN (ANALYZE, BUFFERS) and find real offenders with pg_stat_statements.

Concept Overview​

Query tuning is evidence-driven:

  1. identify the slow query
  2. inspect the plan
  3. fix the root cause (indexes, query shape, stats)
  4. measure again

The Profiling Toolkit​

ToolWhat it shows
EXPLAINestimated plan
EXPLAIN (ANALYZE)actual execution time and row counts
EXPLAIN (ANALYZE, BUFFERS)buffer hits/reads (I/O behavior)
pg_stat_statementstop queries by time/calls

EXPLAIN Basics​

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, amount
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;

Look for:

  • Seq Scan vs Index Scan
  • estimated vs actual rows
  • sorts that spill to disk

pg_stat_statements​

Enable (if available):

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Top queries by total time:

SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Common Mistakes & Troubleshooting​

MistakeWhat happensFix
Adding indexes without checking plansExtra write cost, no benefitEXPLAIN before/after
Ignoring statsPlanner chooses bad plansRun ANALYZE, keep autovacuum healthy
Sorting huge result setsSlow queriesIndex ORDER BY columns or reduce rows earlier

Best Practices​

  • Use EXPLAIN (ANALYZE, BUFFERS) for real evidence.
  • Optimize the queries that matter (use stats/logs).
  • Prefer small schema/query changes over blind tuning.

Quick Reference​

EXPLAIN SELECT ...;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

CREATE EXTENSION pg_stat_statements;
SELECT * FROM pg_stat_statements;

What's Next​