Re: AWS forcing PG upgrade from v9.6 a disaster - Mailing list pgsql-performance

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