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

11–20 of 29 posts

Re: PostgreSQL EXPLAIN Output Explained

#11

Post by samokhvalov »

samokhvalov wrote: post #10
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…

> Different table statistics can cause the planner to go in wildly different directions Exactly. That's why my team and I (Postgres.ai) have developed Database Lab Engine [1] and a chatops tool for SQL optimization, Joe bot [2], both are open-source (AGPLv3). EXPLAIN (ANALYZE, BUFFERS) has to be executed on the same-size DB, with properly adjusted Postgres configuration. Interesting, that the machine you might using…

> tools to screw up table statistics

Perhaps you already know these, but just in case:

- https://github.com/ossc-db: pg_dbms_stats, pg_store_plans, pg_hint_plan

- https://github.com/HypoPG/hypopg

Re: PostgreSQL EXPLAIN Output Explained

#12

Post by ganomi »

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.

Re: PostgreSQL EXPLAIN Output Explained

#13

Post by michristofides »

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

Thanks for the shout out, I’m half the team behind pgMustard, happy to answer questions here if anyone has any

Re: PostgreSQL EXPLAIN Output Explained

#14

Post by munk-a »

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.

Auto_explain is a pretty great tool to spread knowledge on yea - I've actually built out a lot of functionality related to our DB handle where I work and one of the features I added was a software configuration to establish a threshold that could also be impacted by other runtime variables. We've used this to track specific classes of queries over time and figure out what's going wrong and it can be advantageous (if you know a query sometimes does run long) to capture explains of it executing quickly - sometimes you'll get really helpful information like the query planner changing it's mind when passing a threshold of so many rows and know clearly what you want the query planner to decide to do.

If you're a small enough shop to consider it I highly recommend setting up something to automatically explain queries meeting some criteria on production or using some analysis stack (like new relic) to just capture all the query executions within certain time windows.

These tools all come with costs and should never just run continuously on production if you're getting no benefit from them, but the value can be quite significant.

Re: PostgreSQL EXPLAIN Output Explained

#15

Post by michristofides »

airstrike wrote: post #4

I appreciate the first image in TFA is supposed to just be funny but it would actually be useful to have an output like that. Some of those analyses are tougher than others to code but a subset of them are not entirely out of the realm of possibility.

I hope we’re not truly a consultants nightmare, but we’ve got quite a few of these covered in pgMustard (15+ tip types) and working to add more.

Re: PostgreSQL EXPLAIN Output Explained

#16

Post by samokhvalov »

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.

Great extension, yes. There is overhead when enabling the timing and buffers options, but sometimes it's not big [1]

But auto_explain solves only part of the task – you can see what happened, but cannot see the answers to "what if" questions. ("What if I used this index?")

[1] https://www.pgmustard.com/blog/auto-explain-overhead-with-ti...

Re: PostgreSQL EXPLAIN Output Explained

#18

Post by comboy »

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…

I would say you need it on production environment.

Exact same configuration is not enough. You want shared buffers and disk cache to look the same as it looks on production and you also want the same common queries running in the background.

I mean, "need" in case of a busy database and being at a high optimization level where small details matter. You can catch more obvious stuff with much less care.

Re: PostgreSQL EXPLAIN Output Explained

#19

Post by efxhoy »

One thing I learned about EXPLAIN this week is that it doesn't show constraint checks. I was trying to delete about 40k rows from a table and it was taking hours and I couldn't figure out why. ANALYZE EXPLAIN showed nothing indicating anything about reading any of the other tables than the FROM and the USING table.

The table I was deleting from had 20 foreign key constraints referencing it, and a couple of them didn't have an index on the referencing column and were big (a few million rows). Added indexes to all of them, took a couple of minutes to build, and the DELETE ran in a few seconds.

Sometimes the answer to a performance issue can't be found in EXPLAIN. And always remember to properly index your foreign key constraints.

Re: PostgreSQL EXPLAIN Output Explained

#20

Post by michristofides »

efxhoy wrote: post #19

One thing I learned about EXPLAIN this week is that it doesn't show constraint checks. I was trying to delete about 40k rows from a table and it was taking hours and I couldn't figure out why. ANALYZE EXPLAIN showed nothing indicating anything about reading any of the other tables than the FROM and the USING table. The table I was deleting from had 20 foreign key constraints referencing it, and a couple of them didn'…

EXPLAIN ANALYZE would have shown you referential integrity (RI) triggers taking most of the time, but it’s still a bit of a leap to work out that it’s due to missing foreign key indexes if you don’t already know
Post reply on HN