Original-Subject: Re: Performance degradation with CTEs,
 switching from PG 11 to PG 15
Original-From: Jean-Christophe Boggio <postgresql@thefreecat.org>
Original-Cc: 
  "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Message-ID: <ebef7634-346b-423b-8d84-41afdb6005ba@thefreecat.org>
Date: Wed, 22 Nov 2023 14:48:07 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Performance degradation with CTEs, switching from PG 11 to PG 15
Content-Language: fr-FR, en-US
To: John Naylor <johncnaylorls@gmail.com>
Cc: 
  "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
References: <6245f4b6-e608-4d87-9cd8-d0a7a1b5f9d4@thefreecat.org>
 <CANWCAZZJT_RMSZ-6261o2Vkq_3h=F2kXJtVDZMqnBLdCasVeOw@mail.gmail.com>
From: Jean-Christophe Boggio <postgresql@thefreecat.org>
In-Reply-To: 
 <CANWCAZZJT_RMSZ-6261o2Vkq_3h=F2kXJtVDZMqnBLdCasVeOw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/ebef7634-346b-423b-8d84-41afdb6005ba%40thefreecat.org>
Precedence: bulk

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,