On Wed, Nov 22, 2023 at 6:39 PM Jean-Christophe Boggio
<postgresql@thefreecat.org> wrote:
>
> Hello,
>
> I just switched from PG11 to PG15 on our production server (Version is
> 15.5). Just made a vacuum full analyze on the DB.
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.
> Also, adding "materialized" to both "withcwrack" and "withcwrack0" CTEs
> gets the result in acceptable timings (a few seconds). The problem with
> this is that we have some clients with older versions of PG and I guess
> blindly adding the "materialized" keyword will cause errors.
Yes, meaning 11 and earlier don't recognize that keyword keyword.
> 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.