BUG #16885: PostgreSQL upgrade to 12+ changes hash join to slow nested loop

The following bug has been logged on the website:

Bug reference: 16885
Logged by: Tavin Cole
Email address: tavincole+pgbg(at)gmail(dot)com
PostgreSQL version: 13.2
Operating system: Amazon Linux 2
Description:

We're trying to upgrade from the version 9 series and have a deal-breaking
slow query, which runs okay in 10 and 11 but is many times slower in 12+.
I've tested across minor versions from 12.1 through 12.6, and the minor
version doesn't affect it. I've also found the same behavior in 13.2.

The problem lies in the planner choosing a nested loop join instead of the
hash join it should use. Please see this stackoverflow post for the explain
analyze query plans illustrating the difference:
https://stackoverflow.com/questions/66248669/postgresql-upgrade-to-12-changes-hash-join-to-slow-nested-loop

I note the nested loop join filter removes some 60 million rows. This is 60x
the row count of the tables involved.

Postgres is installed from the official pgdg rpm. The upgrade in our test
environment is done with pg_upgrade. Then a full analyze is done before
testing the query. I've tried setting default_statistics_target as high as
500 with no improvement. In fact the query only runs fast before the
statistics are gathered.

Thanks for your attention!