Joe, a Postgres query optimization bot

Semi related, SQL Server have query store feature which records query insights on production db and in SSMS you can see the query statistics on various metrics like resource uses, execution counts etc along with query plan applied.

I haven't dug deep into SQL server yet, but query store suggestions are good enough for now.

We call it macro-analysis -- the analysis of SQL workload as a whole, with call counts, timing, memory-related metrics, etc.

Currently, Joe helps with micro analysis: you take just one SQL query, don't think about others and solve the task of optimizing this particular query.

For "macro", Postgres ecosystem has pg_stat_statements (and our tool postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup for automated health checks builds reports on top of it), pgBadger to analyze logs, auto_explain to capture execution plans.

Building the "bridge" between macro- and macro-analyses is a very interesting topic. I'm going to give a talk about this particular topic at the next PGCon.

> I find this hard to believe. Maybe on the very rare occasion where the problem is tied to production and irreproducible elsewhere, but my experience has been that most troubleshooting happens on development, testing, staging, etc servers.

Let me rephrase: many may troubleshoot in dev/staging, but that's not the right thing to do. Query execution plans can change completely based on data volume and even with the data itself. Databases use detailed statistics about the data to influence the planner's decision.

Hence, tuning a query on an environment without production data can lead to query optimizations that are either irrelevant or totally wrong in production. I have seen this countless times in my professional experience.

> Other than for small toy databases, I don't see how this would work for large databases.

Since it uses cloning at the volume/fs layer, the size of the database is irrelevant --only the rate of changes matter.

> You can test, optimize, etc queries on non-production servers.

No, you cannot, as I mentioned above. It's a waste of effort and can only lead to the false illusion that your query is good --and then beat you in production.

> Let me rephrase: many may troubleshoot in dev/staging, but that's not the right thing to do.

Maybe we have a different definition of "troubleshoot". Also, dev and/or staging should have similar data/specs/etc to production.

> Query execution plans can change completely based on data volume and even with the data itself.

Yes. That's why you could have your production server generate/log actual query plans for queries that are causing you problems. You could even set performance (cpu,io,bandwidth,etc) thresholds for your server to log. Of course that's in addition to the profile/monitoring data you have on the server.

> Hence, tuning a query on an environment without production data can lead to query optimizations that are either irrelevant or totally wrong in production. I have seen this countless times in my professional experience.

Well then your issue is that your dev/staging setup is poor. How or what do you even develop, test and troubleshoot? Might as well develop, test and troubleshoot straight on production.

> Since it uses cloning at the volume/fs layer, the size of the database is irrelevant --only the rate of changes matter.

Size still does matters. And as I mentioned, it still has a problem with security.

> No, you cannot, as I mentioned above. It's a waste of effort and can only lead to the false illusion that your query is good --and then beat you in production.

"waste of effort"? Developing, testing, troubleshooting, etc should be done off production. It's only on the rare cases that you should troubleshoot on production. This is best practices and basic security.

Unless you are working on internal office setup which isn't facing the outside world and where security and uptime doesn't matter. Your comment reminds me of people saying using "admin/root" account for everything is fine because everything else is "waste of effort". Do you even have a dev/staging setup? What do you use that for?

Everyone's professional experience is different, but I've never heard anyone claim "many may troubleshoot in dev/staging, but that's not the right thing to do". Not the right thing to do?

In my professional experience, the testing/troubleshooting is done on dev/staging. Once we feel everything is up to snuff, code gets pushed to production. If there are issues on production, we try to replicate it on dev/staging/etc and troubleshoot it. Most of the time, we find the bug/issue. On the rarest of occasions do we have to troubleshoot directly on production which serve our company and especially our clients who have SLAs with us. Your cavalier attitude about production is something I've yet to come across. But if it works for you, then I guess that's all that matters.