thebench.bio #1 Ecosystem for the Bio-Tech Industry
All SQL questions

Question 14 of 20 Intermediate Troubleshooting

A query becomes much slower after adding a date filter. How would you investigate the execution plan?

Short answer

Run EXPLAIN ANALYZE on both versions and compare plans node by node. Look for a switch from an index scan to a sequential scan, a function wrapped around the date column, and estimated row counts far from actual ones.

Why

The planner picks a plan from its estimates of how many rows each step returns. A date filter can change that estimate, change which index applies, or stop an index applying at all. The PostgreSQL documentation explains that EXPLAIN shows the chosen plan with estimated costs and rows, while EXPLAIN ANALYZE actually runs the query and adds real row counts and times for each node.

Since PostgreSQL 18, EXPLAIN ANALYZE also includes buffer counts by default, which show how much data was read. The usual culprits after adding a date filter are an index that no longer applies because the column is wrapped in a function, and a row estimate that is badly wrong because statistics are stale or the range is outside what the planner has seen. Either can push the planner from a cheap index lookup to reading the whole table.

How to work it through

  1. Capture EXPLAIN ANALYZE output for the query with and without the date filter on the same data.
  2. Find the node where most time is spent and compare its estimated and actual row counts.
  3. Check whether the filter wraps the column in a function or cast, such as date(collected_at) = '2026-03-01', which prevents a plain index on collected_at from being used.
  4. Rewrite such filters as a range on the raw column, collected_at >= '2026-03-01' AND collected_at < '2026-03-02', or create an index on the expression.
  5. If estimates are badly off, run ANALYZE on the table so the planner has fresh statistics, and recheck.
  6. Confirm the improved plan on realistic data volumes before changing anything in production.

An example

A results query over 40 million rows takes 0.2 seconds by sample but 95 seconds once a filter on date(measured_at) is added. EXPLAIN ANALYZE shows a sequential scan reading the whole table with an estimate of 200,000 rows and an actual of 18,000. Rewriting the filter as a half-open range on measured_at lets the planner use the existing index, and the query returns in 0.3 seconds.

A later ANALYZE corrects the estimate as well.

Where the answer stops holding

EXPLAIN ANALYZE executes the statement, so for an UPDATE or DELETE wrap it in a transaction and roll back, as the documentation shows. Plans depend on data volume and statistics, so a plan from a small test database can differ from production. Timing overhead from ANALYZE itself can distort very fast nodes.

An index is not always faster, because reading most of a table through an index can be slower than a sequential scan.

A strong answer covers

  • Compare EXPLAIN ANALYZE plans with and without the filter
  • Compare estimated and actual row counts
  • Functions on the column defeat a plain index
  • Rewrite as a half-open range or add an expression index
  • Refresh statistics with ANALYZE

Common mistakes

  • Adding indexes by guesswork without reading the plan
  • Running EXPLAIN ANALYZE on a DELETE outside a transaction and changing data
  • Using BETWEEN with an end date at midnight and silently excluding the last day's measurements