pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbad plan using nested loops
2+ messages / 2 participants
[nested] [flat]
* bad plan using nested loops
@ 2018-02-01 10:42 Johan Fredriksson <eskil@kth.se>
2018-02-01 15:00 ` Re: bad plan using nested loops Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Johan Fredriksson @ 2018-02-01 10:42 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: bad plan using nested loops
2018-02-01 10:42 bad plan using nested loops Johan Fredriksson <eskil@kth.se>
@ 2018-02-01 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2018-02-01 15:00 UTC (permalink / raw)
To: Johan Fredriksson <eskil@kth.se>; +Cc: pgsql-performance@lists.postgresql.org
Johan Fredriksson <eskil@kth.se> writes:
> 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?
Core of the problem looks to be the misestimation here:
Index Only Scan using shredder_cgm1 on public.cachedgroupmembers cachedgroupmembers_4 (cost=0.43..2.33 rows=79 width=8) (actual time=0.020..0.903 rows=1492 loops=804)
Output: cachedgroupmembers_4.memberid, cachedgroupmembers_4.groupid, cachedgroupmembers_4.disabled
Index Cond: ((cachedgroupmembers_4.memberid = principals_1.id) AND (cachedgroupmembers_4.disabled = 0))
Heap Fetches: 5018
Probably, memberid and disabled are correlated but the planner doesn't
know that, so it thinks the index condition is way more selective than it
actually is. In PG 10, you could very possibly fix that by installing
extended statistics on that pair of columns. See
https://www.postgresql.org/docs/current/static/planner-stats.html#PLANNER-STATS-EXTENDED
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-02-01 15:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-01 10:42 bad plan using nested loops Johan Fredriksson <eskil@kth.se>
2018-02-01 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
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