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

> On May 28, 2021, at 14:30, Bruce Momjian <bruce(at)momjian(dot)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.