PostgreSQL EXPLAIN Output Explained

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 running slow due to thrashing in ram on a dev-box.<p>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.<p>Oh also - query caching on postgres is a thing so if you&#x27;re worried about performance from a cold state don&#x27;t forget to clear caches before executing. And if anyone has any good suggestions around tools to screw up table statistics I haven&#x27;t found a good one that I like yet.

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

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.<p>[0]: <a href="https:&#x2F;&#x2F;pgmustard.com" rel="nofollow">https:&#x2F;&#x2F;pgmustard.com</a>

The site mentioned in the article also has a series that goes more in depth how to read and understand explain output:<p><a href="https:&#x2F;&#x2F;www.depesz.com&#x2F;tag&#x2F;unexplainable&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.depesz.com&#x2F;tag&#x2F;unexplainable&#x2F;</a>

An alternative to <a href="https:&#x2F;&#x2F;explain.dalibo.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;explain.dalibo.com&#x2F;</a> is <a href="https:&#x2F;&#x2F;tatiyants.com&#x2F;pev" rel="nofollow">https:&#x2F;&#x2F;tatiyants.com&#x2F;pev</a><p>Both have pros and cons about how they visualize things.

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&#x27;ve found Postgres EXPLAIN output completely unhelpful for as long as I&#x27;ve been using Postgres, and... this article didn&#x27;t help.<p>&gt; Find the lowest node where the estimated row count is significantly different from the actual row count.<p>&gt; ...<p>&gt; 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.<p>Am I missing something? Everything actually shown displays identical row count estimates&#x2F;actual, and red&#x2F;yellow&#x2F;orange associated with accurate estimates. What am I not seeing??

One thing I learned about EXPLAIN this week is that it doesn&#x27;t show constraint checks. I was trying to delete about 40k rows from a table and it was taking hours and I couldn&#x27;t figure out why. ANALYZE EXPLAIN showed nothing indicating anything about reading any of the other tables than the FROM and the USING table.<p>The table I was deleting from had 20 foreign key constraints referencing it, and a couple of them didn&#x27;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.<p>Sometimes the answer to a performance issue can&#x27;t be found in EXPLAIN. And always remember to properly index your foreign key constraints.

Probably one of the best resources to understand indexes and the output of `EXPLAIN ANALYZE` would be <a href="https:&#x2F;&#x2F;use-the-index-luke.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;use-the-index-luke.com&#x2F;</a>

Wish I had a tool that could suggest things to do like the “cartoon” at the top of the article.