> 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.