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

On Mon, Feb 22, 2021 at 11:46 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
>
> 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!

You'll have to post your actual query to get proper feedback.

That said, a typical case for this specifically when going to version
12 would be that CTEs are no longer optimization barriers. If you do
use CTEs in your query, try changing them from "WITH x AS (...)" to
"WITH x AS MATERIALIZED (...)" to return to the previous behaviour and
see if that improves things.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/