SQL Query Optimization, in depth: finding the queries that matter, reading EXPLAIN ANALYZE, and a catalogue of fixes that actually work

The loop

Optimization goes wrong when it starts from a guess ("we need an index on created_at") instead of a measurement. The loop below keeps you honest. Each pass changes one thing, so when a change helps or hurts you know why.

Step 1: find the queries worth fixing

Rank by total time, not mean time. A 2 ms query called 40 million times a day costs more than a 30-second report run twice. The pg_stat_statements extension normalises statements (literals become $1, $2) and accumulates per-statement counters. It must be listed in shared_preload_libraries and created with CREATE EXTENSION. In PostgreSQL 13 and later the timing columns are total_exec_time and mean_exec_time; older versions call them total_time and mean_time.

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 0)             AS total_ms,
       round(mean_exec_time::numeric, 2)              AS mean_ms,
       round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct,
       shared_blks_read,
       rows / NULLIF(calls, 0)                        AS rows_per_call,
       left(query, 80)                                AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Three columns guide the triage. pct tells you where the server's time goes. shared_blks_read (blocks read from outside PostgreSQL's buffer cache) points to I/O-bound queries. rows_per_call catches statements that return far more rows than the application can use, which is often an application bug rather than a database one.

For queries whose plans you cannot reproduce by hand, the auto_explain module logs the plan of any statement slower than auto_explain.log_min_duration. Turn on its ANALYZE option with care: timing every node adds overhead to every logged statement. Reset statistics after each deployment so you can compare before and after.

Step 2: read the plan

Run EXPLAIN (ANALYZE, BUFFERS) on the statement with realistic parameter values. ANALYZE executes the query, so wrap data-modifying statements in a transaction you roll back. PostgreSQL 18 includes buffer counts by default when ANALYZE is used; on older versions ask for BUFFERS explicitly. A plan is a tree, and each node reports estimates and actuals:

Limit  (cost=0.43..1520.10 rows=50 width=48) (actual time=1812.4..1812.5 rows=50 loops=1)
  Buffers: shared hit=1204 read=88213
  ->  Sort  (cost=... rows=61200 ...) (actual time=1812.4..1812.4 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        ->  Seq Scan on orders o  (cost=... rows=61200 ...) (actual time=0.03..1790.2 rows=58410 loops=1)
              Filter: ((tenant_id = 42) AND (status = 'open'))
              Rows Removed by Filter: 11941590

Read it from the most expensive node outwards:

• Actual time is inclusive. A node's time includes its children. The Seq Scan above costs about 1.79 s of the 1.81 s total; the Sort is almost free.
• Estimated versus actual rows. Here the planner expected 61,200 rows and got 58,410, which is close, so the plan choice is reasonable for the indexes that exist. When the two differ by ten times or more, suspect statistics before anything else.
• Rows Removed by Filter. Twelve million rows read to keep 58,000 means no index serves this predicate. That is the target.
• loops. On the inner side of a nested loop, per-row figures are multiplied by loops. A 0.05 ms index probe with loops=200000 is ten seconds.
• Buffers read versus hit. 88,213 blocks read is roughly 690 MB of 8 kB pages fetched from the OS or disk. The query is I/O-bound, and it will run faster on a second, warm execution. Compare cold and warm runs before you celebrate.

Step 3: the fix catalogue

Most production fixes fall into a small number of patterns. Learn to recognise each one in a plan.

Sargability. An index on created_at cannot serve WHERE date(created_at) = '2026-10-01' because the index stores created_at, not its date. Rewrite it as a range, created_at >= '2026-10-01' AND created_at < '2026-10-02'. The same applies to lower(email) = $1 (use an expression index on lower(email)) and to a text column compared with a numeric parameter, where the cast lands on the column side. LIKE '%abc' cannot use a B-tree at all; trigram GIN indexes can.

Composite index order. For WHERE tenant_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT 50, the index (tenant_id, status, created_at DESC) serves all three clauses: two equality columns narrow the range, and the third column delivers rows already sorted, so the scan stops after 50 rows. Equality columns go first, then the range or sort column. An index on (created_at, tenant_id) would serve the sort but scan every tenant's rows. B-tree indexes in depth explains why the order matters at page level.

Covering indexes. If the query only needs id, total, add them with INCLUDE (id, total) (PostgreSQL 11 and later) and the planner can choose an Index Only Scan. It only avoids the heap when pages are marked all-visible, so a table with heavy churn and lagging vacuum still pays heap fetches; the plan reports them as Heap Fetches. See index-only scans.

-- OFFSET pagination: page 2,000 reads and throws away 100,000 rows
SELECT id, created_at, total FROM orders
WHERE tenant_id = $1
ORDER BY created_at DESC, id DESC
OFFSET 100000 LIMIT 50;

-- Keyset pagination: every page costs the same
SELECT id, created_at, total FROM orders
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)      -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- served by: CREATE INDEX ON orders (tenant_id, created_at DESC, id DESC);

N+1. An ORM that loads 200 orders and then fetches each customer separately issues 201 statements. Each one is fast, so none shows up as slow, but pg_stat_statements shows a huge call count. Replace the loop with one statement, SELECT * FROM customers WHERE id = ANY($1), or use the ORM's eager-loading feature.

Correlated columns. The planner assumes predicates are independent. If city = 'Paris' and country = 'FR' are each 1% selective, it estimates 0.01% for both together, when the true figure is 1%. CREATE STATISTICS orders_geo (dependencies) ON city, country FROM orders; followed by ANALYZE teaches it the dependency.

Worked example: a dashboard query from 1.8 seconds to milliseconds

The plan in Step 2 belongs to a dashboard that shows a tenant's 50 newest open orders. The table holds 12 million rows across 300 tenants. The figures below are illustrative of the shape of the improvement on a typical mid-sized instance, not a benchmark.

1. Measure. pg_stat_statements shows the statement at 31% of total execution time, mean 1.8 s, 0.9 million blocks read per hour.
2. Explain. A Seq Scan removes 11.9 million rows by filter. Estimates are accurate, so statistics are not the problem; a missing index is.
3. Change one thing. CREATE INDEX CONCURRENTLY orders_tenant_status_created ON orders (tenant_id, status, created_at DESC); CONCURRENTLY avoids blocking writes during the build, at the cost of a slower build and the possibility of leaving an INVALID index behind if it fails; check pg_index.indisvalid afterwards.
4. Re-explain. The new plan is a Limit over an Index Scan that touches about 55 index and heap pages and stops after 50 rows. Actual time drops to a few milliseconds cold and well under a millisecond warm. No Sort node remains.
5. Verify results. Run the old and new forms against a snapshot and compare with EXCEPT in both directions. They must return the same rows in the same order; ties in created_at are why the production query also sorts by id.
6. Verify write cost. Every insert and status update now maintains one more index. Watch insert latency and WAL volume for a day. If status changes often, updates that touch an indexed column can no longer be HOT updates (heap-only tuple updates that skip index maintenance), which is the real cost to weigh.

A partial index, ON orders (tenant_id, created_at DESC) WHERE status = 'open', is a stronger alternative when open orders are a small fraction of the table: smaller, cheaper to maintain, and only usable by queries that repeat the predicate. PostgreSQL index strategies compares partial, expression and BRIN indexes.

Plans that change under you

A query that was fast yesterday can be slow today with no code change. The usual causes:

• Statistics drift. Autovacuum runs ANALYZE after a fraction of the table changes. A table that grows by bulk load can be queried before analyze catches up, and estimates for new values (yesterday's dates) fall off the end of the histogram. Run ANALYZE after bulk loads.
• Generic plans for prepared statements. After five executions PostgreSQL may switch a prepared statement to a generic plan that ignores the parameter value. For skewed data, where one tenant has 40% of rows, the generic plan can be terrible for some values. plan_cache_mode = force_custom_plan (PostgreSQL 12 and later) is the escape hatch; test the planning overhead it adds.
• Data skew crossing a threshold. When a predicate's selectivity crosses the point where a sequential scan becomes cheaper than an index scan, the plan flips. This is often correct, and sometimes the cost settings are wrong for your storage: random_page_cost defaults to 4, which models spinning disks; many SSD deployments lower it towards 1.1 to 1.5. Change it after measuring, not by folklore.
• Join method changes. An underestimate on the outer side of a join turns a hash join into a nested loop with a huge loops count. Hash joins in depth covers when each method wins.

MySQL users get the same loop with different tools: the performance_schema statement digests rank queries, EXPLAIN ANALYZE (MySQL 8.0.18 and later) gives actual timings, and optimizer hints exist where PostgreSQL deliberately has none in core.

Failure modes and trade-offs

• Index sprawl. Every index speeds some reads and slows every write, uses memory and is vacuumed. Audit pg_stat_user_indexes for indexes with idx_scan = 0 over a full business cycle, including replicas, before dropping them.
• Optimizing on a laptop. A plan on 10,000 rows tells you nothing about 12 million. Use a production-sized copy, or at least production statistics.
• Warm-cache illusions. Running a query twice and keeping the second timing hides I/O cost. Record buffers read, not only milliseconds.
• Fixing the query instead of the question. A report that scans a year of data on every page load may need a summary table or a materialised view refreshed on a schedule, not a better index.
• Forcing plans. Disabling sequential scans session-wide or adding hint extensions can rescue an incident, but the forced plan does not adapt as data grows. Treat it as a temporary patch with an owner and a removal date.
• Changing several things at once. If you add two indexes and rewrite the query in one deploy and the latency drops, you do not know which change mattered, and you keep the dead weight.

What to do next

1. Enable pg_stat_statements (and auto_explain with a sensible threshold) on your main database if they are not already on.
2. Pull the top 15 statements by total execution time and record their share, mean, calls and blocks read as a baseline.
3. For the top three, run EXPLAIN (ANALYZE, BUFFERS) with realistic parameters and write down the most expensive node and whether estimates match actuals.
4. Match each to a row in the fix catalogue, apply one change, and verify that the result set is identical and that write latency is acceptable.
5. Replace any OFFSET pagination deeper than a few pages with keyset pagination.
6. Search pg_stat_statements for very high call counts with tiny row counts: those are your N+1 patterns.
7. List indexes with zero scans over a month and schedule their removal.
8. Repeat monthly, and after every large data load or major version upgrade.