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

> On May 28, 2021, at 14:30, Bruce Momjian <bruce@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
deeplynested 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
atthe top level predicates.