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
In-reply-to: <1517481727.23675.3.camel@kth.se>
References: <1517481727.23675.3.camel@kth.se>
Comments: In-reply-to Johan Fredriksson <eskil@kth.se>
	message dated "Thu, 01 Feb 2018 11:42:07 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <23734.1517497241.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 01 Feb 2018 10:00:41 -0500
Message-ID: <23735.1517497241@sss.pgh.pa.us>
Precedence: bulk

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 cachedgr=
oupmembers_4 (cost=3D0.43..2.33 rows=3D79 width=3D8) (actual time=3D0.020.=
.0.903 rows=3D1492 loops=3D804)
	  Output: cachedgroupmembers_4.memberid, cachedgroupmembers_4.groupid, ca=
chedgroupmembers_4.disabled
	  Index Cond: ((cachedgroupmembers_4.memberid =3D principals_1.id) AND (c=
achedgroupmembers_4.disabled =3D 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