pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: tavincole+pgbg@gmail.com
Subject: BUG #16885: PostgreSQL upgrade to 12+ changes hash join to slow nested loop
Date: Mon, 22 Feb 2021 10:15:32 +0000
Message-ID: <16885-b543bcd340b23f86@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16885
Logged by:          Tavin Cole
Email address:      tavincole+pgbg@gmail.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-nest...

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!



view thread (8+ messages)  latest in thread

Message-ID: <16885-b543bcd340b23f86@postgresql.org>
Permalink:  ../16885-b543bcd340b23f86@postgresql.org/
Also on:    postgresql.org/message-id/16885-b543bcd340b23f86@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, tavincole+pgbg@gmail.com
  Subject: Re: BUG #16885: PostgreSQL upgrade to 12+ changes hash join to slow nested loop
  In-Reply-To: <16885-b543bcd340b23f86@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox