Menu

1 / 4

Reading Postgres EXPLAIN ANALYZE when the planner expects 300 rows and gets 15,000

#hackernoon#technology#rows#time#index
Reading 0:00
15s threshold

Slow Postgres, part 2 of 8. The SQL and raw numbers for every test are on GitHub .

The users scan expected 300 rows and got 15,000

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.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.