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