Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL EXPLAIN Output Explained

cybertec-postgresql.com

Post reply on HN
Print view View original

21–29 of 29 posts

Re: PostgreSQL EXPLAIN Output Explained

#21

Post by eyelidlessness »

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??

Re: PostgreSQL EXPLAIN Output Explained

#22

Post by firloop »

eyelidlessness wrote: post #21

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

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.

Re: PostgreSQL EXPLAIN Output Explained

#23

Post by eyelidlessness »

firloop wrote: post #22
eyelidlessness wrote: post #21

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

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.

Re: PostgreSQL EXPLAIN Output Explained

#24

Post by michristofides »

eyelidlessness wrote: post #21

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

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.

[1]: https://youtu.be/mCwwFAl1pBU

Re: PostgreSQL EXPLAIN Output Explained

#25

Post by eyelidlessness »

michristofides wrote: post #24
eyelidlessness wrote: post #21

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

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. [1]: h…

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.

Re: PostgreSQL EXPLAIN Output Explained

#26

Post by skunkworker »

ganomi wrote: post #12
munk-a wrote: post #7

The article touched on some caveats but missed what I think is a big one - you really want to capture any detailed explains from environments as close to production as possible. Different table statistics can cause the planner to go in wildly different directions and while faster is always better it is very easy to accidentally get caught up trying to sink a lot of effort into making a query more performant that was…

To get production EXPLAINS for problematic queries you can activate auto_explain on a postgres instance. For my transactional system i have set it up to log EXPLAINS for all queries that take more than 2000 ms.

It's good to auto explain but I would also add that going in and running explain (analyze, buffers) is really beneficial to seeing how much the query uses buffers and how many pages it has to load from disk.

Re: PostgreSQL EXPLAIN Output Explained

#27

Post by mrjaeger »

munk-a wrote: post #7

The article touched on some caveats but missed what I think is a big one - you really want to capture any detailed explains from environments as close to production as possible. Different table statistics can cause the planner to go in wildly different directions and while faster is always better it is very easy to accidentally get caught up trying to sink a lot of effort into making a query more performant that was…

Do you know how to clear the cache inside of a running Postgres instance? All of the articles online say to just restart the db, but that isn’t feasible in some cases I’ve come across, such as when trying to do testing against a remote db spun up to test against more prod like data. Like you said the query perf against a cold cache vs. something that has had a lot of rows loaded into the shared buffed can be quite different!

Re: PostgreSQL EXPLAIN Output Explained

#29

Post by goodpoint »

ezekg wrote: post #9

I use an awesome service called PgMustard [0] for parsing and debugging slow queries. It has saved me a lot of time, and has helped me resolve some pretty big (and complicated) bottlenecks. [0]: https://pgmustard.com

Warning: it's paid and requires sign-up with github/google even for a test.
Post reply on HN