From: Jean-Christophe Boggio <postgresql@thefreecat.org>
To: John Naylor <johncnaylorls@gmail.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Performance degradation with CTEs, switching from PG 11 to PG 15
Date: Wed, 22 Nov 2023 14:48:07 +0100
Message-ID: <ebef7634-346b-423b-8d84-41afdb6005ba@thefreecat.org> (raw)
In-Reply-To: <CANWCAZZJT_RMSZ-6261o2Vkq_3h=F2kXJtVDZMqnBLdCasVeOw@mail.gmail.com>
References: <6245f4b6-e608-4d87-9cd8-d0a7a1b5f9d4@thefreecat.org>
<CANWCAZZJT_RMSZ-6261o2Vkq_3h=F2kXJtVDZMqnBLdCasVeOw@mail.gmail.com>
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,
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@thefreecat.org, johncnaylorls@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Performance degradation with CTEs, switching from PG 11 to PG 15
In-Reply-To: <ebef7634-346b-423b-8d84-41afdb6005ba@thefreecat.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox