I've found Postgres EXPLAIN output completely unhelpful for as long as I've been using Postgres, and... this article didn't help.
> Find the lowest node where the estimated row count is significantly different from the actual row count.
> ...
> Under the heading “rows x” you see by what factor PostgreSQL overestimated or underestimated the row count. Bad estimates are highlighted with a red background.
Am I missing something? Everything actually shown displays identical row count estimates/actual, and red/yellow/orange associated with accurate estimates. What am I not seeing??
You’re quite right, the example given doesn’t have bad row estimates, and other cells are highlighted in red/orange/yellow for different reasons (proportion of time taken, in the case shown).
For an intro to this that goes through several examples, I highly recommend a conference talk[1] by Josh Berkus in 2015/16 that he gave a few times. It has aged pretty well and I’ve not yet seen the basics covered better.
Thank you for validating that I’m not crazy for not seeing the things described in the article in its examples! It’s become kind of a running non-joke of “I don’t think I should be feeling impostor syndrome but I keep being scolded to read the EXPLAIN, it keeps being mystery meat every time I try, and I keep having better outcomes applying what I’ve learned every other way”.
If I have time this weekend I’ll check out the video.
The numbers can be useful, but I mainly pay attention to the steps the query planner takes. Is it doing an expensive loop over data? Is it using an index? Those can often be more illuminating than the numbers – you get a feel over time for what operations are actually expensive.
I’m glad it’s helped you. I’m saying that I never got that feel over time, and I was hoping for something illuminating in the article, but it is describing things that aren’t actually in the examples it provides... or there’s something I’m not seeing? It does me no good to be told “look for where the planner and execution are different”, and they’re the same in the example, or “you’ll see this tool highlight those differences red” and there’s no difference I can find. I’m open to the possibility I’m missing something but I’ve stared at these and similar EXPLAIN results and tooling analyses for endless hours and can’t see what I’m supposed to learn from them.
> Find the lowest node where the estimated row count is significantly different from the actual row count.
> ...
> Under the heading “rows x” you see by what factor PostgreSQL overestimated or underestimated the row count. Bad estimates are highlighted with a red background.
Am I missing something? Everything actually shown displays identical row count estimates/actual, and red/yellow/orange associated with accurate estimates. What am I not seeing??