--- title: "Slow PostgreSQL query? Read EXPLAIN ANALYZE without breaking your data" description: "EXPLAIN shows PostgreSQL's estimated plan for a query, and EXPLAIN ANALYZE actually runs it and puts the real numbers next to the estimates. Here is how to read the gap, and how to try it on an UPDATE without changing your data." url: https://ihsanalapsi.dev/articles/postgresql-explain-analyze lang: en date: 2026-10-31 updated: 2026-10-31 tags: ["Developer tools", "Web development"] author: Ihsan Alapsi (إحسان العبسي) --- # Slow PostgreSQL query? Read EXPLAIN ANALYZE without breaking your data A query that finishes in a blink on your laptop stalls the page once the table grows in production. And the tool that tells you why can change your data if you run it on an UPDATE without care. Here is how to read EXPLAIN ANALYZE, and how to try it safely. ## What is the difference between EXPLAIN and EXPLAIN ANALYZE? EXPLAIN shows the plan PostgreSQL chose and its estimate for each step, without running anything. EXPLAIN ANALYZE actually runs the query and puts the real numbers next to the estimates. The plan is chosen by the planner, the part of PostgreSQL that decides how to run a query before it starts: read the whole table or use an index, and in which order to join tables. Its decision rests on estimates, and estimates can be wrong. According to [the Using EXPLAIN page in the PostgreSQL docs](https://www.postgresql.org/docs/current/using-explain.html), the output has one line for each node in the plan: its type and the planner's estimates for it. With ANALYZE, each node also gets the real row count and the real time it took. ## What do the numbers in parentheses mean? The numbers in parentheses are the planner's estimates for the node: start-up cost, total cost, rows and row width. This is the docs' own example: ```sql EXPLAIN SELECT * FROM tenk1; Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244) ``` | Number | What it means | | ----------- | -------------------------------------------------------------------------------------- | | first cost | Start-up cost: the work before the first row comes out, such as sorting in a Sort node | | second cost | Total cost, if the node runs to its last row | | rows | The number of rows the node is expected to output | | width | The average width of a row, in bytes | Cost is not in seconds: it is in arbitrary units set by the planner. The example has no WHERE clause, so the planner chose a Seq Scan, which reads every row of the table. ## Why is EXPLAIN ANALYZE dangerous on UPDATE and DELETE? EXPLAIN ANALYZE really runs the query, so any change in it happens. EXPLAIN throws away the rows a SELECT returns, but an UPDATE still updates and a DELETE still deletes, as if you had run them alone. This is the mistake that happens: someone wants to know why a DELETE is slow, puts EXPLAIN ANALYZE in front of it on the production database, and gets the report with the rows gone. [The EXPLAIN command page in the docs](https://www.postgresql.org/docs/current/sql-explain.html) gives the fix: wrap it in a transaction and roll it back. The same goes for INSERT, UPDATE, DELETE and MERGE: ```sql BEGIN; EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; ROLLBACK; ``` You see the plan and the real numbers, and ROLLBACK puts the table back as it was. ## How do you read actual time and loops? Actual time is the real time spent in the node, in milliseconds, and loops is the number of times the node ran. When a node runs more than once, the time and row count you see are averages per run. So a node inside a loop can look fast while it took most of the query's time. Multiply actual time by loops to get the total time it took. Do not compare cost with actual time: one is in arbitrary units and the other in milliseconds. The useful comparison is between the estimated rows and the actual rows of the same node. When the gap is large, the planner chose its plan on a wrong number, and that is often where the slowness comes from. ## What does BUFFERS give you? BUFFERS shows the I/O work of each node: the number of blocks hit, read, dirtied and written. In the current docs, ANALYZE adds it automatically. These numbers help when the time differs between your machine and production with the same plan: they show whether the node works on a different number of blocks. If you do not see Buffers lines in your output, ask for it yourself: EXPLAIN (ANALYZE, BUFFERS). ## Where do you start now? Start with the slow query itself, on a copy of the data close to production. 1. Run `EXPLAIN ANALYZE` in front of it, and if it is an INSERT, UPDATE or DELETE, wrap it in `BEGIN` and `ROLLBACK`. 2. Find the node with the largest gap between estimated and actual rows. 3. Multiply its time by loops, and check whether it really is the one that took the time. 4. If it is a Seq Scan on a large table with a WHERE condition, try an index on the condition's column, and read the plan again. If you are building your foundations in programming and databases, [Developer Roadmap 2026](/products/roadmap-2026) lays out what to learn next. All our developer tools articles are in one place: [Developer tools](/articles?tag=developer-tools). If the slow query sits behind a page on your site, our articles on building sites are under [Web development](/articles?tag=web-development). ## FAQ **Does plain EXPLAIN run the query?** No. EXPLAIN without ANALYZE only plans the query and shows the estimates. With ANALYZE the query actually runs. **Why doesn't the cost number match the actual time?** Because cost is in arbitrary units set by the planner, while actual time is in milliseconds. PostgreSQL's docs say they are unlikely to match, so compare estimate and reality by row counts instead. **Can I get the output as JSON?** Yes. The FORMAT option takes TEXT, XML, JSON or YAML, and the default is TEXT. It holds the same information, just easier for a program to parse.