PostgreSQL EXPLAIN Output Explained
cybertec-postgresql.com
PostgreSQL EXPLAIN Output Explained
1–10 of 29 posts
Re: PostgreSQL EXPLAIN Output Explained
#2Great writeup. I use EXPLAIN a lot in development as a gut check — “does this descending index do what I thought it would? how expensive is that subquery?” Highly recommend looking at it early on, it helps me catch silly mistakes well before production.
Re: PostgreSQL EXPLAIN Output Explained
#3The site mentioned in the article also has a series that goes more in depth how to read and understand explain output:
Re: PostgreSQL EXPLAIN Output Explained
#4I 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.
Re: PostgreSQL EXPLAIN Output Explained
#5An alternative to https://explain.dalibo.com/ is https://tatiyants.com/pev
Both have pros and cons about how they visualize things.
Re: PostgreSQL EXPLAIN Output Explained
#6An alternative to https://explain.dalibo.com/ is https://tatiyants.com/pev Both have pros and cons about how they visualize things.
PEV is great, helped me figure out and fix specific query issues that only happened on production
Re: PostgreSQL EXPLAIN Output Explained
#7The 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 running slow due to thrashing in ram on a dev-box.
Explain (analyze at least - which you should always use) is a lot less theoretical than you might assume. That can make it a bit more onerous to execute but it ends up adding a lot of value to the statistics when you gather them.
Oh also - query caching on postgres is a thing so if you're worried about performance from a cold state don't forget to clear caches before executing. And if anyone has any good suggestions around tools to screw up table statistics I haven't found a good one that I like yet.
Re: PostgreSQL EXPLAIN Output Explained
#8An alternative to https://explain.dalibo.com/ is https://tatiyants.com/pev Both have pros and cons about how they visualize things.
Came here to suggest the same. If you hit the gears on the left and choose view: compact and graph: cost you can get a decent overview of the hot spots in complicated queries quickly.
Re: PostgreSQL EXPLAIN Output Explained
#9I 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.
Re: PostgreSQL EXPLAIN Output Explained
#10The 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 for query plan troubleshooting, can have less RAM and different hardware in general – it doesn't matter for the planner. Even shared_buffers doesn't matter – you can set effective_cache_size matching production (this trick we use in Database Lab when hardware is weaker than on production).
As for the cache states – very good point as well. I'm advocating for buffers- or rows-centric approach: first, optimization should be done to reduce the numbers of buffers or, if you're working with "logical" (dump/restore) copy of the database rather than "physical" (PGDATA copy, keeping the same data layout, including bloat, etc.) – the fewer the numbers, the better. Only then, you pay attention to timing – and keep in mind what can happen under the worst conditions (everything is read from disk), if it makes sense.