The query that was fine for a year and is now 8 seconds

The orders dashboard was the fastest page in the product at launch. Nobody has touched the query since. Over fourteen months the orders table went from a few hundred thousand rows to about nine million, and the page now takes roughly eight seconds. The query has one WHERE clause over two columns, and there is an index on one of them. Somebody already added an index last month. It did not help.

Predict

An index exists on one of the two filtered columns, and adding another one changed nothing. Which explanation would you bet on before looking at a plan?

Choose one answer

The table outgrew memory, so every read now reaches disk. The answer is a bigger instance.The planner is scanning the whole table, because no single index covers the predicate it was given.The index is being used, and nine million rows is just more work than a few hundred thousand.Lock contention. Writers hold the rows the dashboard wants, and the query spends its time waiting.

Lock in answer

The one command

In Postgres, put EXPLAIN (ANALYZE, BUFFERS) in front of the query. ANALYZE actually runs it, so for anything that writes, wrap it in BEGIN and ROLLBACK. The numbers below are illustrative — the shape is the part you read, not the timings:

Sort  (cost=215120.44..215120.95 rows=204 width=97)
      (actual time=7984.102..7984.141 rows=1186 loops=1)
  Sort Key: created_at DESC
  ->  Seq Scan on orders  (cost=0.00..215118.00 rows=204 width=97)
          (actual time=0.412..7983.019 rows=1186 loops=1)
        Filter: ((customer_id = 90210) AND (status = 'open'))
        Rows Removed by Filter: 8998814
        Buffers: shared hit=1204 read=118914
Planning Time: 0.140 ms
Execution Time: 7984.221 ms

Four things in there decide everything, roughly in this order.

Seq Scan versus Index Scan. Seq Scan means every page of the table was read. Index Scan means the index was walked and matching rows fetched individually. Bitmap Heap Scan is the middle case: walk the index, collect the page numbers, then read those pages in physical order — which is what Postgres picks when a predicate matches too many rows for one-at-a-time fetches but too few to justify reading everything.

rows= estimated against rows= actual. The first is the planner's guess, the second is what happened. Here it guessed 204 and got 1186, which is close enough not to be the problem. When those two differ by orders of magnitude, stop reading the plan and go fix the statistics — that is the last section of this lesson.

Rows Removed by Filter. Nine million rows were read and thrown away to produce 1186. That single line is the whole diagnosis. Any time this number is hugely larger than rows= actual, work is being done to produce nothing.

Buffers. hit came from cache, read did not. A hundred and nineteen thousand block reads for 1186 rows is the I/O cost of the same mistake, counted a second way.

MySQL differs in two ways worth knowing. EXPLAIN ANALYZE exists there too and prints a similar tree, but plain EXPLAIN gives you estimates only — no actual rows, no actual time. And the column to read in plain EXPLAIN is type: ALL is the sequential scan, ref and eq_ref are index lookups, and filtered is the percentage of rows the engine expects to survive the predicate after the index has done its part.

The three reasons an index you already have is not used

The sequential scan has an easy fix: a composite index in the right column order, equality columns first, then the range or sort column.

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

CONCURRENTLY is not optional in production. Without it the index build takes a lock that blocks every write to the table for the duration of the build, which on a table of this size is minutes. With it, the build takes longer, cannot run inside a transaction block, and can leave an invalid index behind if it fails — check pg_index.indisvalid and drop the failed one before retrying.

The harder case is the one where the index exists, looks right, and the plan still says Seq Scan. There are three usual reasons.

One: something wraps the column. A B-tree index stores the column's value. WHERE lower(email) = $1 asks about a different value, so the index does not apply. Same for WHERE created_at::date = '2026-10-01' — the cast is on the column, so the index on created_at is unusable. The fixes are an expression index (CREATE INDEX ON users (lower(email))) or a rewrite that leaves the column bare (created_at >= '2026-10-01' AND created_at < '2026-10-02'). I reach for the rewrite first: it needs no new index and no new maintenance.

Two: a type mismatch. This one bites hardest in MySQL, where comparing a VARCHAR column to a numeric literal converts the column to a number for every row and the index goes unused, silently and with no warning. Postgres usually refuses such a comparison outright rather than doing it quietly, which is friendlier, but it will still cast the column when you ask it to, and the ::date example above is exactly that. Check the driver too: a parameter bound as the wrong type produces the same plan from code that reads correctly.

Three: low selectivity. The index is usable and the planner declines it, because it estimates the predicate matches a large share of the table. If status = 'open' is true of 60% of rows, walking an index to find 60% of the table and then fetching those rows one at a time is genuinely more expensive than reading the table in order. This is the planner being right. The answer is not to force the index; it is a partial index over the selective part (WHERE status = 'open' as an index predicate) or a different query.

What would you do

The plan above, on a nine-million-row table taking around four hundred writes a minute during the working day. One Postgres primary, one streaming replica, no connection pooler in front.

You have pg_stat_statements installed, so you know this query is the single largest consumer of database time. The dashboard is used by about forty internal staff. Nobody is paging you; the complaint arrived through a manager.

Optimising for

The dashboard returns in well under a second at the ninety-fifth percentile, within one deploy, with no statement that blocks writes to the orders table for longer than a couple of seconds, and without adding an index the planner will decline to use.

You cannot know

• What share of the orders table has status = 'open', which nobody has measured
• Whether the index somebody added last month is used by any other query
• How long the longest-running transaction on this table usually holds open
• Whether the dashboard needs live data or would accept a figure from this morning

Choose an option

Add the composite index on (customer_id, status, created_at DESC) with CREATE INDEX CONCURRENTLY, then re-run the plan to confirm the scan changed.Move the dashboard query to the read replica. The problem is contention with the write path, and a second copy removes it.Cache the dashboard result for five minutes in Redis, keyed by customer and status.Raise work_mem and effective_cache_size so the planner costs index access more cheaply and picks the index it already has.

Check

A plan shows an Index Scan with an estimate of 3 rows against 4.1 million actual, and the query takes about thirty seconds. What is the first thing you change?

Choose one answer

The index. It is clearly the wrong index for this predicate.The statistics. An estimate off by six orders of magnitude means every plan on this table is being chosen from fiction.The query. It returns four million rows, and no page needs four million rows.Nothing yet. Thirty seconds for four million rows is roughly the expected cost, so the query is doing what it was asked.

Check answer

When the plan stops being trustworthy

Everything above assumes the planner's estimates are roughly right, so that Seq Scan means "a scan was genuinely cheaper" rather than "a scan looked cheaper from inside a misunderstanding". That assumption is the floor this lesson stands on, and it fails in three recognisable ways.

Stale statistics. Autovacuum's analyze step is driven by the proportion of the table that changed, so a table that grows steadily and is never updated can go a long time between samples. pg_stat_user_tables.last_autoanalyze is the column to check first.

Correlated columns. Postgres assumes independence between predicates by default, so WHERE city = 'Leeds' AND postcode LIKE 'LS%' gets estimated as the product of two selectivities when it is really one. CREATE STATISTICS on the column pair teaches it the correlation.

A value the sample never saw. Statistics keep the most common values and a histogram. A query for a value outside both gets a generic estimate, which is how you end up with rows=3 against four million actual.

Once the estimates are wrong, reading the plan tells you what the database decided and not what it should have decided, and the fix is upstream of anything in this lesson.

›The query you should run before any of thisOptional

EXPLAIN tells you about a query you already suspect. Finding out which query to suspect is a different question, and the answer is pg_stat_statements — an extension that has to be loaded in shared_preload_libraries, so it is worth enabling before you need it rather than during an incident.

Order by total_exec_time rather than mean_exec_time. The query that matters is rarely the slowest one; it is usually a moderately slow query called a great many times, and the slowest single statement is often a nightly job nobody is waiting for. In MySQL the equivalent is performance_schema.events_statements_summary_by_digest, ordered by SUM_TIMER_WAIT.

The same reasoning applies to auto_explain, which logs the plan of anything exceeding a duration you set. It costs a little overhead on every statement and it means the plan for the eight-second page is already in your logs the first time somebody complains, taken under real load rather than reproduced by hand on a Tuesday.

Worth remembering

• Rows Removed by Filter is the diagnosis: work done to produce nothing.
• Estimated rows against actual rows decides whether the plan is worth reading.
• A composite index orders equality columns first, then the range or sort column.
• CREATE INDEX CONCURRENTLY, always, and check indisvalid if it fails.
• An index is skipped when something wraps the column, the types mismatch, or the predicate is unselective.
• A function or cast on the column side of a comparison disables the index on it.
• MySQL converts a string column to a number to compare it, and loses the index silently.
• Low selectivity is the planner being right; answer it with a partial index, not a hint.
• pg_stat_statements ordered by total time finds the query worth explaining.