Re: Performance degradation with CTEs, switching from PG 11 to PG 15

John,

Le 22/11/2023 à 14:30, John Naylor a écrit :
> Note that "vacuum full" is not recommended practice in most > situations. Among the downsides, it removes the visibility map, >
which is necessary to allow index-only scans. Plain vacuum should >
always be used except for certain dire situations. Before proceeding >
further, please perform a plain vacuum on the DB. After that, check > if
there are still problems with your queries.
Did both VACUUM ANALYZE and VACUUM (which one did you recommend
exactly?) and things go much faster now, thanks a lot. I will also check
why autovacuum did not do its job.

>> Is there anything I can do to prevent that kind of behaviour ? I'm >> a little afraid to have to review all the queries in my softwares
>> to keep good performances with PG 15 ? Maybe there's a way to >>
configure the server so that CTEs are materialized by default ? > >
There is no such a way. It would be surely be useful for some users > to
have a way to slowly migrate query plans to new planner versions, > but
that's not how it works today.
Thanks for your input so I know I did not miss a parameter. And yes,
that would be handy.

Best regards,