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
visibilitymap, > 
 
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
queriesin 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,