The plan is the evidence, the time is only the symptom
A query that takes four seconds could be reading too many rows, sorting a large result, joining in an expensive order or following a plan chosen from stale statistics. The elapsed time cannot tell these apart. The plan can, because it shows each step, the number of rows the planner expected and, when the statement is actually run, the rows it really handled and the time each step took.
Start by getting the plan on a copy of the data, with parameter values that match the slow case. A plan taken against ten rows in a development database tells you nothing about ten million. The PostgreSQL manual warns that results from a very different data size may not carry over, because costs are not linear and the planner may choose a different plan.
- Use a restored copy with realistic row counts and personal data removed or replaced.
- Use the same parameter values that were slow, plus one that is fast, to see the difference.
- Record the engine version, because plan output differs between versions.
EXPLAIN shows a guess, ANALYZE runs the statement
Plain EXPLAIN asks the planner what it would do and prints its estimates. Adding ANALYZE makes the database execute the statement and report what really happened. This is where most people are caught out: PostgreSQL states that the statement is actually executed, so an INSERT, UPDATE or DELETE really changes data. Its manual shows wrapping such a statement in a transaction that is rolled back. MySQL 8.4 documents the same idea for EXPLAIN ANALYZE, which runs the statement and shows the optimizer's expectations beside the measured result.
Even a read-only statement can be heavy. Running ANALYZE on a production database during busy hours can add load to the very system that is already slow, so run it on the copy.
- On PostgreSQL, the BUFFERS option shows how many blocks came from cache and how many were read, which separates a cold-cache run from a warm one.
- Timing every plan node has its own cost; PostgreSQL notes it can slow some systems, and offers a way to switch node timing off when you only need row counts.
- Repeat the run several times and compare the median, not the first run.
Compare estimated rows with actual rows
The most useful comparison in a plan is the expected number of rows against the actual number at each step. A large gap usually means the planner is working from statistics that no longer describe the data, so it picks a poor join order or access method. The fix may then be fresh statistics or a better-fitting index, not a rewrite.
Two cautions from the PostgreSQL manual stop you misreading it. Where a step loops, the actual rows and time are averages per loop, so multiply by the loop count to get the total. And cost numbers are in arbitrary units, so they are for comparing plans, not for predicting milliseconds. In MySQL, the tabular form labels a full table scan as type ALL and its rows column is an estimate, so a large estimate beside type ALL is a lead to check, not a measurement.
- A sequential scan is not always wrong; it can win when most of the table is read or the table is very small.
- A step that returns few rows after reading many is where a better access path pays most.
What counts as fixed, and when to ask for help
A fix is real when the same statement, run with the same parameters on the same data, returns identical rows in less time and the new plan shows why. Check three things before you accept any change: that the result rows are identical, that the time target is met over repeated runs, and that anything you added, such as an index, does not slow inserts and updates unacceptably.
The one-slow-query outcome is built around exactly that acceptance: the before and after plans, a rows-identical check, an undo script for any index and a note on write cost, measured on a staging copy you prepare. It does not cover queries that are slow only in production for reasons of load or locking, many queries per request, or a general index review; those have their own guides and outcomes. No change should reach production without a restore point you have tested.
- If the slow step is outside the database, such as an application loop, a plan will not show it.
- If a restored copy runs the query quickly, look at locks, connections and load before indexes.
Sources and limits
- PostgreSQL 18: Using EXPLAIN Checked 2026-10-11.
- Estimated row counts should be compared with actual ones, and planner cost is in arbitrary units rather than milliseconds.
- A sequential scan can be the best plan when most of the table is read or the table is tiny, and plans from very different data sizes may not transfer.
- PostgreSQL 18: EXPLAIN Checked 2026-10-11.
- With ANALYZE the statement is actually executed, so side effects occur; a data-changing statement can be wrapped in a transaction that is rolled back.
- BUFFERS reports shared, local and temporary block hits and reads, and TIMING can add measurement overhead on some systems.
- MySQL 8.4: EXPLAIN Checked 2026-10-11.
- EXPLAIN ANALYZE runs the statement and reports estimated cost and rows beside actual time, rows and loops.
- MySQL 8.4: EXPLAIN output format Checked 2026-10-11.
- In tabular EXPLAIN, type ALL is a full table scan and rows is the optimizer's estimate of rows to examine.