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

[Christophe Pettus]

> On May 28, 2021, at 14:30, Bruce Momjian <br...@momjian.us> wrote:
> I think it uses pg_upgrade.

It does.  It does not, however, do the vacuum analyze step afterwards.  A 
VACUUM (FULL, ANALYZE) should take care of that, and I believe the OP said he 
had done that after the pg_upgrade.

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.