Postgres: Queries are too slow after upgrading to PG17 from PG15

Hi,

Most of the queries got slower after upgrading our postgres from version 15 to 17 using pg_upgrade. I reconfirmed that "vacuum full, analyze" were all taken care.

To debug, instead of upgrade, I installed two instances one with postgres 15 and another postgres 17 with the same application dump restored.

Now surprisingly one of the query i took from application which used to execute in 2s in PG15, is now taking 1min+ in PG17. I also observed that some of the operations involving DML operations slowed down too in PG17.

Explain plan of the two queries almost same, all the joins and paths used are exactly same.

Could anybody please provide some insights here?

PG15 Plan:

https://explain.depesz.com/s/5PGX

________________________________

PG17 Plan:

https://explain.depesz.com/s/27vD

Update: I installed PG16 with same dump, and verified that everything seems normal here in fact better than PG15, so i just want rule out possibility of PG16 impact here.

PG16 plan: https://explain.depesz.com/s/v0Gc

Sajith P Shetty
Principal Engineer
Black Duck
M +91 9448389989<tel:+919448389989>| ssajith(at)blackduck(dot)com<mailto:ssajith(at)blackduck(dot)com>
[signature_778616162]