Slow Postgres, part 2 of 8. The SQL and raw numbers for every test are on GitHub.
Expected and actual rows per node, read from the bottom up.
Postgres guessed that 300 users live in Lisbon. There are 15,000. That one line of the plan explains the rest: a Nested Loop that ran an index lookup 15,000 times, a sort that spilled to disk, and a query that takes 159 ms with the data in memory and 2.15 seconds when it comes off disk.
Part 1 was about how the planner prices a plan. This one reads what happened when it ran, and how much EXPLAIN ANALYZE itself adds.
One note before the numbers: the AlphaVPS link at the end is a referral link, so I may get a commission at no extra cost to you, and the netcup voucher I offer there comes from my own account.
The short version:
- Compare estimated and actual rows on every node, from the bottom up. Here a guess of 300 users picked a Nested Loop that won in memory (159 ms against 344) and lost from disk (2.15 s against 455 ms).
actual timeandrowsare per loop: an index scan that saysrows=0 loops=15000returned 3,071 rows.readinBuffersmeans "not in shared_buffers", not "from disk".- EXPLAIN ANALYZE made
count(*)2.4 to 3.0 times slower on all four of my machines, so compare plans withTIMING OFFor the plain query.
The lab: a million users, five million orders
PostgreSQL 17.12 in Docker, on the same 4-vCPU, 8 GB cloud VM as part 1. Every user has five orders, and orders.user_id has an index. The city decides the country: 15,000 users live in Lisbon, 5,000 in Porto, and those 20,000 are all of PT. The rows come from generate_series and setseed, so yours match mine. shared_buffers is 1 GB so everything fits in memory, and JIT and parallel workers are off to keep the plans short.
The report counts orders of Lisbon users by status:
SELECT o.status, count(*) AS orders, sum(o.amount_cents) AS cents FROM users u JOIN orders o ON o.user_id = u.id WHERE u.country = 'PT' AND u.city = 'Lisbon' GROUP BY o.status ORDER BY orders DESC; EXPLAIN guesses, EXPLAIN ANALYZE runs
EXPLAIN prints the plan with the planner's forecast for each node, cost and rows. EXPLAIN ANALYZE runs the query and adds what happened: actual time, actual rows and loops. BUFFERS adds the pages each node touched, and PostgreSQL 18 turns it on by default with ANALYZE. The result rows are thrown away, so Execution Time leaves out sending them to the client and turning values into text (the SERIALIZE option, new in PostgreSQL 17, measures the second part).
Writes run too, so wrap them in a transaction:
BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE FROM orders WHERE status = 'pending'; ROLLBACK; ROLLBACK brought back all 149,701 pending orders, but the work still happened: the DELETE dirtied 36,469 pages that still get written to disk. Sequences don't roll back either. After a rolled-back EXPLAIN ANALYZE INSERT of 1,000 rows, the next real row got id 1001.
Read it from the inside out
The report's plan, with some lines cut (the full output is in 02_plans.out):
Sort (cost=31759.19..31759.20 rows=4 width=21) (actual time=209.555..209.560 rows=4 loops=1) Buffers: shared hit=129440, temp read=205 written=205 -> GroupAggregate (cost=31744.11..31759.15 rows=4 width=21) (actual time=201.392..209.511 rows=4 loops=1) -> Sort (cost=31744.11..31747.86 rows=1500 width=9) (actual time=201.037..205.049 rows=75000 loops=1) Sort Method: external merge Disk: 1640kB -> Nested Loop (cost=0.43..31664.98 rows=1500 width=9) (actual time=0.030..189.952 rows=75000 loops=1) -> Seq Scan on users u (cost=0.00..24434.00 rows=300 width=4) (actual time=0.013..47.068 rows=15000 loops=1) Filter: ((country = 'PT'::text) AND (city = 'Lisbon'::text)) Rows Removed by Filter: 985000 -> Index Scan using orders_user_id_idx on orders o (cost=0.43..24.05 rows=5 width=13) (actual time=0.003..0.009 rows=5 loops=15000) Index Cond: (user_id = u.id) Buffers: shared hit=120000 Planning Time: 0.247 ms Execution Time: 209.990 ms A plan prints top-down, but rows flow bottom-up. The users scan read a million rows and kept 15,000. For each of them, the Nested Loop ran the index scan on orders. The inner Sort ordered the 75,000 orders by status, GroupAggregate collapsed them into four rows, and the top Sort ordered those.
Each node has two sets of parentheses. cost=31744.11..31747.86 rows=1500 is the forecast, in planner units that are priced in pages and don't convert to milliseconds. actual time=201.037..205.049 rows=75000 is what happened: milliseconds until the node's first row and until its last. The inner Sort's first row came at 201 ms because a sort returns nothing until it has read all its input.
Times are cumulative. The Nested Loop finished at 190 ms, 47 ms of which was the users scan, so the 15,000 index lookups took about 143 ms. To find the slow node, subtract each node's children from it and look for the biggest remainder. Buffers add up the same way.
Estimated rows against actual rows
The users scan expected 300 rows and returned 15,000. The 300 comes from pg_stats: PT is 2% of users, Lisbon is 1.5%, and the planner treats the two conditions as independent and multiplies them, 1,000,000 × 0.02 × 0.015 = 300. Every Lisbon user is also in PT, so the country condition removes nobody.
Go up one node. The Nested Loop was planned for 300 index lookups returning 1,500 orders and ran 15,000 lookups returning 75,000. The Sort above it was planned for 1,500 rows, got 75,000 and spilled to disk. So walk up from the leaves comparing the two rows= and start at the lowest node where they differ by 10x or more. Under a LIMIT a node stops early, so fewer actual rows there is expected.
Did the wrong guess hurt? Drop the redundant country = 'PT' and the planner estimates 74,999 joined rows against 75,000 actual, and switches to a Hash Join: read all five million orders once and match them against a hash of the 15,000 users. I timed both plans without EXPLAIN, with the data in three places:
Plain query time, median of 7 warm runs and of 5 runs after each restart, on the 4-vCPU cloud VM. The OS cache was dropped before every disk run.
In memory the plan built on the wrong guess won, 159 ms against 344, because 15,000 index lookups in RAM cost less than reading five million rows. From disk it lost, 2.15 s against 455 ms. The planner can't see what's cached, and its page costs put 15,000 scattered lookups far above one pass over the table, which is how they behaved cold. A warm EXPLAIN ANALYZE shows only one side of that trade. Where estimates come from and why they drift is part 3.
Loops: the numbers are per loop
The index scan on orders says rows=5 loops=15000: the Nested Loop ran it once per user. actual time and rows are averages per loop, like the estimate next to them: 5 × 15,000 = 75,000 rows in about 0.009 × 15,000 = 135 ms, close to the 143 ms from the subtraction above.
Buffers is a total over all loops. Its 120,000 pages are 8 per lookup: three levels of the B-tree plus five heap pages for five orders spread through the table. PostgreSQL 18 adds an Index Searches line to index scans, also a total.
Per-loop averages are rounded, too. The same join, keeping only failed orders:
Nested Loop (cost=0.43..31656.73 rows=62 width=20) (actual time=0.240..147.007 rows=3071 loops=1) -> Index Scan using orders_user_id_idx on orders o (cost=0.43..24.07 rows=1 width=24) (actual time=0.006..0.007 rows=0 loops=15000) Index Cond: (user_id = u.id) Filter: (status = 'failed'::text) Rows Removed by Filter: 5 rows=0 doesn't mean the scan found nothing. 3,071 rows over 15,000 loops is 0.2 per loop, which PostgreSQL 17 rounds to 0 and 18 prints as rows=0.20. Rows Removed by Filter: 5 is per loop too, about 72,000 rows in all. Parallel workers count as loops: with them on, a Parallel Seq Scan on orders showed rows=1666667 loops=3, the leader and two workers.
Rows Removed by Filter
In the report, Rows Removed by Filter: 985000 on the users scan means Postgres fetched a million rows and checked each one to keep 15,000. The orders scan has an Index Cond instead: the index found the rows, and nothing was thrown away.
On a Seq Scan, a large count is where an index might help (part 1 covers when it won't). On an index scan, it means the index covers only part of the WHERE clause and the rest is checked row by row. In the failed-orders join, the index found all five orders of each user and the filter threw away 96% of them. Which index fits a query like that is part 4.
BUFFERS: hit, read, and a cold cache
shared hit is a page found in shared_buffers. read is a page Postgres asked the OS for, which came from the OS page cache or from disk, and BUFFERS can't tell which. The counts aren't distinct pages: a page visited twenty times is twenty hits. The report's Nested Loop plan in all three states:
Buffers I/O wait query time in shared_buffers hit=129440 - 159 ms restart, OS cache warm hit=87639 read=41801 164 ms 304 ms restart, OS cache empty hit=87639 read=41801 2,011 ms 2.15 s I/O wait is the median from the EXPLAIN runs, query time from the plain ones. Both restarted runs read the same 41,801 pages, and the second was seven times slower because those reads went to disk.
The I/O wait comes from track_io_timing, which is off by default and which a superuser can turn on for the session. Every node then gets an I/O Timings line. From the median cold run of each plan, costs cut:
Nested Loop plan -> Index Scan using orders_user_id_idx on orders o (actual time=0.032..0.141 rows=5 loops=15000) Buffers: shared hit=87633 read=32367 I/O Timings: shared read=1965.141 Hash Join plan -> Seq Scan on orders o (actual time=0.125..291.938 rows=5000000 loops=1) Buffers: shared read=37101 I/O Timings: shared read=65.418 The index scan waited almost 2 seconds for 32,367 pages, about 60 µs each, as its lookups jumped around the table. The Seq Scan read 37,101 pages of the same table front to back in 65 ms, under 2 µs each. All 87,633 hits are repeat visits inside the index scan, like the top of the B-tree on every lookup.
Sort and Hash: memory or disk
Two kinds of lines say an operation didn't fit in memory. A sort gets work_mem, 4 MB by default, and a hash twice that, since hash_mem_multiplier defaults to 2. The report's inner Sort shows Sort Method: external merge Disk: 1640kB: it wrote sorted runs to temp files, the temp read=205 written=205 in its Buffers (205 pages of 8 kB). A Hash shows it as batches. This one joins all 200,120 failed orders to users, sorted by amount:
Hash (cost=19434.00..19434.00 rows=1000000 width=26) (actual time=186.969..186.971 rows=1000000 loops=1) Buckets: 131072 Batches: 16 Memory Usage: 4695kB Batches: 16 means the hash table was split into 16 parts and 15 of them waited in temp files. The final sort spilled too, 7,448 kB.
At 64 MB of work_mem (128 MB for the hash) both fit in memory, the hash in one 66,782 kB batch, and on the cloud VM the query got slower: 425 ms to 578 ms with TIMING OFF, median of seven. On my M3 Max laptop it got faster, 418 ms to 341 ms. I haven't found out why. CPU cache was my first guess, since a 4 MB batch fits in it and a 66 MB table doesn't, but that doesn't explain the laptop, so it stays unconfirmed.
The report's own 1.6 MB spill cost nothing I could measure: 158.6 ms at 4 MB, 158.0 ms at 64 MB. A Disk: line says a node spilled, and only a rerun with more work_mem, on the machine that serves the query, says whether the spill is slow. Each sort and hash in each connection can take that much, so raise it per session or per role.
Planning time and execution time
The report spent 0.25 ms planning and 210 ms executing, the usual split for a slow query. Planning Time counts on queries that run in microseconds, like a primary-key lookup on users right after a restart:
first run in a new connection: Planning Time: 0.430 ms Execution Time: 0.078 ms Planning: Buffers: shared hit=52 read=21 second run: Planning Time: 0.013 ms Execution Time: 0.006 ms The first plan in a new connection reads table and index details from the system catalogs, 73 pages here, and took five times longer than the query. After that, planning still took twice as long as execution. If a lookup like this runs thousands of times a second over short-lived connections, a pooler that keeps connections open removes that first cost.
What EXPLAIN ANALYZE costs
To time each node, EXPLAIN ANALYZE reads the clock before and after every call into it, twice for every row each node returns. If millions of rows pass through a cheap node, the clock reads can cost more than the work.
On my laptop, the cloud VM, an AlphaVPS server in Los Angeles and an ARM server at netcup, I ran pg_test_timing, which ships with Postgres and times a loop around one clock read, then SELECT count(*) over 10 million rows, seven times each way.
Median of 7 runs per variant, data in memory, one backend, PostgreSQL 17.7 on the laptop and 17.12 on the three servers.
pg_test_timing gave 18 to 40 ns per loop, and EXPLAIN ANALYZE still made count(*) 2.4 to 3.0 times slower everywhere. The extra time per row followed the clock: 24 ns on the laptop, 44 on the VM, 59 on the Xeon, 74 on the ARM server, or 1.3 to 1.9 times the pg_test_timing figure. On the VM, switching the clock source from tsc to kvm-clock raised both, to 28 and 50 ns, and the slowdown to 3.2 times. A nested loop with a million index lookups does more work per row, and there EXPLAIN ANALYZE added 4% to 15%. The report lost 7%, 159 ms to 170.
I expected a bigger gap between machines, but all four clocks were fast. The pg_test_timing docs show a slow one, acpi_pm, where a loop took 723 ns and EXPLAIN ANALYZE turned a 9.8 ms count into 115.9 ms. The AlphaVPS server offers acpi_pm too, but I didn't switch a live server's clock.
The overhead also skews comparisons between plans. Under EXPLAIN ANALYZE the Hash Join version of the report took 591 ms and the Nested Loop 170 ms, 3.5 times apart. Run plain, they took 344 ms and 159 ms, 2.2 times apart. The Hash Join pushes five million orders through its Seq Scan node, ten million clock reads, while no node in the Nested Loop plan returns more than 75,000 rows.
TIMING OFF keeps rows, loops and buffers and drops the per-node times. It still counts every row, so count(*) ran 16% to 19% slower than plain even then. Inside one plan the per-node times are the best map you have, but the nodes passing the most rows carry the most overhead.
The order I read a plan in
- Run
EXPLAIN (ANALYZE, BUFFERS), insideBEGINandROLLBACKif it writes. - From the innermost nodes up, find the lowest one where estimated and actual
rowsdiffer by 10x or more. - Multiply per-loop numbers by
loops, then subtract each node's children to see where the time went. - Compare
Rows Removed by Filterwith the rows kept. - Look for
Disk:andBatches:above 1, and rerun with morework_membefore blaming the spill. - Read
Bufferswithtrack_io_timingon to see which node waited for reads. - Before comparing two plans, time the plain queries, warm and cold.
Run it on your own server
The walkthrough plans come from one 4-vCPU AMD EPYC cloud VM with 8 GB of RAM and PostgreSQL 17.12 in Docker, so your milliseconds will differ while the row counts match. The work_mem check also ran on my M3 Max laptop (Homebrew PostgreSQL 17.7), and the overhead test ran on the laptop, the VM and an AlphaVPS server in Los Angeles, a 4-vCPU Xeon Platinum 8176 with 8 GB of RAM (PostgreSQL 17.12 from conda-forge), and a netcup server with 6 ARM Neoverse-N1 cores and 8 GB of RAM (PostgreSQL 17.12 in Docker). The SQL, scripts, raw output and chart code are on GitHub, and bench/overhead.sh runs the overhead test on a new Linux box with one command. Run it on your own server, and if you get a different picture or think I got something wrong, open an issue or a PR.
My own projects run on netcup (VPS from €2.21 a month) and AlphaVPS (VPS from €2.54 a month). For netcup, message me on LinkedIn and I'll send you a €5 voucher for new customers.
Good luck with your plans. Part 3 is next: why Postgres statistics go stale.
Finish
I hope you understood the instructions correctly. If you have any edits to this article, I will be waiting for your comments. If I really helped you, I kindly ask you to subscribe to my LinkedIn and provide feedback on this article.
Link to follow me:
All my articles and links: fomo.moe
