Getting Postgres Query Performance Right With EXPLAIN ANALYZE
A dashboard query that used to return in under a second started taking eight, then twelve, as a table grew past a few million rows, and the instinct to just "add an index" without reading the query plan first is exactly the habit that wastes time. Actually learning to read EXPLAIN ANALYZE output properly turned query tuning from guesswork into something closer to a checklist.
EXPLAIN vs EXPLAIN ANALYZE, and Why the Difference Matters
Plain EXPLAIN shows the planner's estimated query plan without running the query, which is useful but tells you what Postgres thinks will happen, not what actually happened. EXPLAIN ANALYZE actually executes the query and reports real timing and row counts alongside the plan, which means it has real side effects for write queries, wrapping it in a transaction with a rollback is the safe habit for anything beyond a plain SELECT.
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 4821 AND created_at > now() - interval '30 days';Reading Seq Scan vs Index Scan
A Seq Scan on a large table in a query filtering on a specific value is usually, though not always, the first sign something's missing an index. The genuinely useful comparison is between the planner's estimated row count and the actual rows returned, shown as rows=X in the plan versus the "actual rows" figure; a large gap between those two numbers means the planner's statistics are stale, which points toward running ANALYZE on the table rather than immediately adding an index.
The BUFFERS Option Nobody Uses by Default
Adding BUFFERS to the EXPLAIN ANALYZE call, which isn't on by default, shows shared hit and read counts, telling you whether the query is served from cache or hitting disk. A query that looks fast in milliseconds but shows a high read count relative to hits is one that will get dramatically slower under memory pressure or on a colder cache, something plain timing numbers alone don't reveal.
Why the Index I Added Didn't Get Used
I've added an index and then watched the planner ignore it entirely, still choosing a sequential scan. The most common reason in my experience is a function or type mismatch, filtering on a column cast to a different type than the index was built on, which silently prevents the index from being usable. The second most common reason is that the table is small enough the planner correctly decides a sequential scan is cheaper than an index scan plus the lookups it would require, which is actually the right call, not a bug.
Composite Indexes and Column Order
A composite index on (customer_id, created_at) supports queries filtering on customer_id alone or on both columns together, but not efficiently on created_at alone, because composite indexes are only useful as a left-to-right prefix. Getting the column order backward, putting the less selective column first, is a mistake I've made and only caught by comparing planner cost estimates between two candidate orderings before deciding.
The N+1 Pattern Shows Up in the Plan Too
An application-level N+1 query problem isn't something EXPLAIN ANALYZE on a single query catches by definition, but turning on statement logging temporarily and counting near-identical queries fired in a tight loop is what actually surfaced it on a report page that felt slow for reasons the individual query plans looked fine for.
pg_stat_statements for Finding What to Even Look At
Before profiling any specific query, enabling the pg_stat_statements extension and querying it for total execution time summed across calls, not just the slowest single call, pointed at a moderately-fast query running thousands of times per minute as the actual biggest contributor to database load, not the occasional slow outlier I'd been chasing first.
Partial Indexes for Queries That Only Care About a Subset
A query that only ever filters for status = 'pending' orders, a small fraction of the total table, was still paying for a full-column index covering every status value, most of which the query never touches. Rebuilding it as a partial index with a WHERE clause matching the actual query condition cut the index size dramatically and made it more likely to stay resident in memory, something a full index competing with everything else for cache space wasn't managing as well.
VACUUM and Bloat Nobody Thinks About Until It's a Problem
A table with heavy update and delete traffic accumulates dead tuples that autovacuum handles most of the time, until a burst of activity outpaces the default autovacuum settings and query plans start reflecting a bloated table rather than the actual live row count. Running EXPLAIN ANALYZE showed a Seq Scan cost that didn't match the table's real data volume, and checking pg_stat_user_tables for the dead tuple count is what actually pointed at bloat rather than a missing index as the real cause that time.
Using auto_explain to Catch Slow Queries You Didn't Know to Look For
Manually running EXPLAIN ANALYZE only helps on queries you already suspect are slow. Enabling the auto_explain extension with a minimum duration threshold logs the plan automatically for any query that crosses it in production, which surfaced a slow query on a rarely-hit admin page that nobody had thought to profile because it wasn't part of the regular user-facing traffic anyone was watching.
Final Verdict
EXPLAIN ANALYZE stops being intimidating once you know which few numbers actually matter: the gap between estimated and actual rows, whether buffers are hit or read, and whether the index you expect to be used actually shows up in the plan. Reaching for it before reaching for a new index turned query tuning from trial and error into something I can actually explain to a teammate afterward.
