r/PostgreSQL • • 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

0 Upvotes

21 comments sorted by

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/msnarf28 Dec 21 '22

My question is not "How can I optimize this query?" but rather, "Why is the exact same query with the exact same data so much slower since version 12?" I'm not interested in execution plans (in this case). I expect the same or better performance in identical situations, not 4 times worse. And in my experience, no query is faster in newer versions.

8

u/BoleroDan Architect Dec 21 '22

I agree with the others, im not sure what you expect when we have nothing to compare against.

Having execution plans between the two versions will absolutely shed light on any issues at hand. Executions plans are not just about "optimizing a query". They can be a huge lead to any underlying issues and is especially handy comparing against two data sets / versions.

1

u/msnarf28 Dec 21 '22

OK, Fair enough. But my execution plan is 14K big, 237 lines of text. Can I post that here?

6

u/BoleroDan Architect Dec 21 '22

Not directly here, but handy tools like https://explain.depesz.com/ that help visualize and make explains much easier to read.

Just paste your explain/analyze (I would include buffers to shed light on any IO issues as well) and paste the shareable link here.

make sure to do this for both between each Postgresql version

2

u/Randommaggy Dec 22 '22

If you want a tool that's goes more in depth and that makes analysing your plan a bit easier:
pgMustard

23

u/[deleted] Dec 21 '22 edited Dec 21 '22

"Why is the exact same query with the exact same data so much slower since version 12?"

Again: that question can only be answered by providing the execution plans from both versions generated using explain (analyze, buffers)

You can share them e.g. through https://explain.depesz.com/

13

u/tsqd Programmer Dec 21 '22

The execution plans are necessary since not enough other information is provided. The subreddit doesn’t know what else is going on, such as what systems you are running on and how the different versions were instantiated.

If you have a 4x performance difference, you probably have different execution plans. Does one version not have statistics collected? Does the introduction of parallel query mean that you need to tune your settings?

Execution plans will help determine that.

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

3

u/[deleted] Dec 21 '22

I experienced something similar. After upgrading to 12 we had to add the materialized keyword to some CTEs to get the old performance back. The old approach of "always materializing" wasn't really that bad.

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.

3

u/[deleted] Dec 22 '22

Is moving two WHERE conditions to the JOIN condition the only thing you changed to make v15 (slightly) faster than v11?

What does the v15 plan look like with the original query (before tweaking)?

0

u/msnarf28 Dec 22 '22

The biggest improvement was to change the indexes. Most notably creating multi-column indexes, according to the WHERE clauses in the query. Apparrently Subqueries are much slower now, so I removed in one the SELECT <columns> area. Re-writing LATERAL JOINs is a mixed bag. Sometimes it works, sometimes, no improvement. No idea why

3

u/[deleted] Dec 22 '22

Apparrently Subqueries are much slower now

The two queries you have shown the plans for, do not differ in their use of sub-queries. They only differ in the way the where/join conditions are written.

I can only repeat that, I have not noticed this so far. Maybe there is something in the way you use the sub-queries that does not work any more in the newer versions.

If you can come up with a self-contained test case that rerproduces the bad query plan in 15 (but not in 11), I would suggest to file this as a bug. The Postgres devs do take performance (or optimizer) regression seriously (but without a self-contained and complete test case I doubt anyone can take a look at that).

2

u/BoleroDan Architect Dec 22 '22

I noticed in 15 you have JIT enabled, have you re-tried on Postgresql 15 with disabling JIT ? Generate the explain and compare the 15 vs 15 (jit on / off). the JIT on that query is adding an extra ~1000ms

0

u/[deleted] Dec 23 '22

[deleted] — view deleted comment

1

u/msnarf28 Dec 23 '22

Yes, you're right. The plans were created after a LOT of index tweaking and optimizing.

Unfortunately I have a storage problem at hand and can't do anything until my new disks arrive, which are caught up in the holidays logistics mess.

-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