From: Johan Fredriksson <eskil@kth.se>
To: pgsql-performance@lists.postgresql.org
Subject: bad plan using nested loops
Date: Thu, 01 Feb 2018 11:42:07 +0100
Message-ID: <1517481727.23675.3.camel@kth.se> (raw)
Hello!
I brought this issue up about two years ago but without getting any
real explanation or solution. The problem is that PostgreSQL does
really bad plans using nested loops. With "enable_nestloop = 0" the
same query is run about 20 times faster.
The sugested solution I got back then was to upgrade to the latest
version of PostgreSQL (then 9.5). It did not help. The solution we
finally applied was a horribly ugly patch to the perl-module
SearchBuilder that recognized queries that would perform badly and put
them inside transaction blocks with "SET LOCAL enable_nestloop = 0".
Last week I upgraded PostgreSQL for this application (Request Tracker)
to version 10.1 and just for fun I decied to test to remove the patch
to see if the problem still persisted. For two cases it did not. The
planner handled them just fine. For one case however, the same problem
still remains.
Bad plan: https://explain.depesz.com/s/avtZ
Good plan: https://explain.depesz.com/s/SJSt
Any suggestions on how to make the planner make better decisions for
this query?
/ Eskil
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-performance@postgresql.org
Cc: eskil@kth.se, pgsql-performance@lists.postgresql.org
Subject: Re: bad plan using nested loops
In-Reply-To: <1517481727.23675.3.camel@kth.se>
* 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