From: Tom Lane <tgl@sss.pgh.pa.us>
To: Johan Fredriksson <eskil@kth.se>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: bad plan using nested loops
Date: Thu, 01 Feb 2018 10:00:41 -0500
Message-ID: <23735.1517497241@sss.pgh.pa.us> (raw)
In-Reply-To: <1517481727.23675.3.camel@kth.se>
References: <1517481727.23675.3.camel@kth.se>
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
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: tgl@sss.pgh.pa.us, eskil@kth.se, pgsql-performance@lists.postgresql.org
Subject: Re: bad plan using nested loops
In-Reply-To: <23735.1517497241@sss.pgh.pa.us>
* 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