From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Fri, 28 May 2021 21:08:28 -0700
Message-ID: <cf15c19d-43d8-95ff-17a0-ded099f70861@mailpen.com> (raw)
In-Reply-To: <E007EEB7-097D-4D0E-9C4D-C8BB28810E7F@thebuild.com>
References: <88dec70b-eb63-0b72-0b5d-253851ef127b@mailpen.com>
<163214e4-0588-230b-9ed5-f682087e0e18@wi3ck.info>
<4e238cc5-f63e-0435-63bf-2515500d5424@dunslane.net>
<20210528213052.GC5649@momjian.us>
<E007EEB7-097D-4D0E-9C4D-C8BB28810E7F@thebuild.com>
On 2021-05-28 19:43, Christophe Pettus wrote:
> ...
> The most common reason for this kind of inexplicable stuff after an RDS upgrade is, as others have said, parameter changes, since you get a new default parameter group after the upgrade.
>
> That being said, this does look like something happened to the planner to cause it to pick a worse plan in v13. The deeply nested views make it kind of hard to pin down, but the core issue appears to be in the "good" plan, it evaluates the _Club.club_count > 5 relatively early, which greatly limits the number of rows that it handles elsewhere in the query. Why the plan change, I can't say.
>
> It might be worth creating a materialized CTE that grabs the "club_count > 5" set and uses that, instead of having it at the top level predicates.
I spent quite a bit of time over the past five days experimenting with
various parameter values, to no avail, but I don't mind trying some more.
I have other queries that fail even more spectacularly, & they all seem
to involve a generated table like the "club" one in my example. I have
an idea that I might try, in effectively changing the order of
evaluation. I'll have to think about that. Thanks for the suggestion!
However, one "shouldn't" have to tinker with the order of stuff in SQL;
that's one of the beauties of the language: the "compiler" (planner) is
supposed to figure that all out. And for me, that's been true for the
past 15 years with PostgreSQL.
Note that this problem is not unique to v13. It happened with upgrades
to v10, 11, &12. So, some fundamental change was made back then (at
least in the RDS version). Since I need a bulletproof backup past next
January, I think my next task will be to get an EC2 instance running
v9.6, where AWS can't try to upgrade it. Then, at my leisure, I can
fiddle with upgrading.
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@mailpen.com, pgsql-performance@lists.postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <cf15c19d-43d8-95ff-17a0-ded099f70861@mailpen.com>
* 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