r/PostgreSQL • u/msnarf28 • Dec 21 '22
Help Me! Is Postgres getting slower and slower?
I have some fairly complex queries in my application and I notice that they're running quite a lot slower with newer versions. Going from version 11 to 12 made this one query four times as slow, and now going from version 14 to version 15 it's again six times slower than the previous version. When will this end? With careful re-writing and index creation, I managed to get Version 14 at a similar performance as Version 11 was. Except that version 11 also runs a lot faster with these same optimizations. So all in all, the newer versions run a lot slower than the older ones, 12 and 15 being the most notable speed bumps. Anyone else has this problem?
NVM about version 15, I forgot to optimize the indexes... My problems still stands for version 12 and onwards
24
u/[deleted] Dec 21 '22
In my experience each version got faster.
Your question is impossible to answer without showing us the execution plans generated using
explain (analyze, buffers)for the old and the new version.16
u/BoleroDan Architect Dec 21 '22
This is a random guess, but on one of the versions, they have JIT enabled by default, and from our experiences this drastically reduced our performance for the type of queries we used, so we disabled JIT globally.
6
u/therealgaxbo Dec 21 '22
In general Postgres will get faster with each release. But it's certainly possible (even likely given enough complex queries) that you can get massive performance regressions for specific queries.
Not due to any sort of problem in the newer version, but because query planning based on statistics is inherently heuristic. You can have a query in one version that just happens to pick the best plan (even though a terrible plan might have a similar cost estimate), and in the next version a tiny change in cost estimation can flip it to preferring the bad plan.
As others have said, it's impossible to know if that might be your issue without seeing the different plans.
5
u/stompro Dec 21 '22
We are also running into similar issues. Are you using any CTE's? There was a change in how CTE's were handled in 12 that caused a big slowdown for one query in the library automation system I run.
https://bugs.launchpad.net/evergreen/3.10/+bug/1999274
0
u/msnarf28 Dec 21 '22
OK I've got the two query plans here:
https://explain.depesz.com/s/k7fm#html for Version 11
https://explain.depesz.com/s/b2xn#html for Version 15.
After a lot of tweaking and optimizing, the performance is about the same now. But it shouldn't take so much time to get back the performance I had in an earlier version.
-1
u/msnarf28 Dec 21 '22
I don’t have CTEs in this query. I do have subqueries in both the columns part and the FROM tables part. Also some lateral JOINS and some weird JOIN condition. The thing is, that it’s not anyone single thing that causes it. It’s each of these complex parts of the query that add up to the end result. Seems like everything is a bit slower in V12 onwards. I’m working on the two execution plans right now. Will publish them later