Re: AWS forcing PG upgrade from v9.6 a disaster

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