[munk-a]

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.

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.

[samokhvalov]

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

[1] https://postgres.ai/products/how-it-works

[2] https://postgres.ai/products/joe

[samokhvalov]

> 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

[ganomi]

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.

[munk-a]

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.

[samokhvalov]

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

[skunkworker]

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.

[comboy]

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.

[mrjaeger]

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!