|
On 05/29/21 07:08, Dean Gibson (DB
Administrator) wrote:
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.
BTW what is the planner reason to not use
index in v13.2? Is index in corrupted state? Have you try to
reindex index "FccLookup"."_LicStatus_pkey" ?
1.5M of seqscan's are
looking really bad.
SubPlan 2
-> Limit (cost=0.15..8.17 rows=1
width=32) (actual time=0.006..0.007 rows=1 loops=55)
-> Index Scan using
"_LicStatus_pkey" on "_LicStatus" (cost=0.15..8.17 rows=1
width=32) (actual time=0.005..0.005 rows=1 loops=55)
Index Cond:
("_HD".license_status = status_id)
SubPlan 2
-> Limit (cost=0.00..1.07 rows=1
width=13) (actual time=0.001..0.001 rows=1 loops=1487153)
-> Seq Scan on
"_LicStatus" (cost=0.00..1.07 rows=1 width=13) (actual
time=0.000..0.000 rows=1 loops=1487153)
Filter:
("_HD".license_status = status_id)
Rows Removed by Filter: 1
|
