From: Johan Fredriksson <eskil@kth.se>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "pgsql-performance@lists.postgresql.org"
	<pgsql-performance@lists.postgresql.org>
Subject: SV: bad plan using nested loops
Thread-Topic: bad plan using nested loops
Thread-Index: AQHTm0lhwDr/VkjvJUWizG4PpAMD+aOPk4SAgABpk+c=
Date: Thu, 1 Feb 2018 20:34:24 +0000
Message-ID: <1517517264764.92820@kth.se>
References: <1517481727.23675.3.camel@kth.se>,<23735.1517497241@sss.pgh.pa.us>
In-Reply-To: <23735.1517497241@sss.pgh.pa.us>
Accept-Language: sv-SE, en-US
Content-Language: sv-SE
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

=0A=
> Johan Fredriksson <eskil@kth.se> writes:=0A=
> > Bad plan: https://explain.depesz.com/s/avtZ=0A=
> > Good plan: https://explain.depesz.com/s/SJSt=0A=
> > Any suggestions on how to make the planner make better decisions for=0A=
> > this query?=0A=
> =0A=
> Core of the problem looks to be the misestimation here:=0A=
> =0A=
>        Index Only Scan using shredder_cgm1 on public.cachedgroupmembers c=
achedgroupmembers_4=0A=
> (cost=3D0.43..2.33 rows=3D79 width=3D8) (actual time=3D0.020..0.903 rows=
=3D1492 loops=3D804)=0A=
>          Output: cachedgroupmembers_4.memberid, cachedgroupmembers_4.grou=
pid,=0A=
> cachedgroupmembers_4.disabled=0A=
>          Index Cond: ((cachedgroupmembers_4.memberid =3D principals_1.id)=
 AND=0A=
> (cachedgroupmembers_4.disabled =3D 0))=0A=
>          Heap Fetches: 5018=0A=
>=0A=
> Probably, memberid and disabled are correlated but the planner doesn't=0A=
> know that, so it thinks the index condition is way more selective than it=
=0A=
> actually is.  In PG 10, you could very possibly fix that by installing=0A=
> extended statistics on that pair of columns.  See=0A=
> =0A=
> https://www.postgresql.org/docs/current/static/planner-stats.html#PLANNER=
-STATS-EXTENDED=0A=
=0A=
I'm not sure what you mean by correlated, but there are only a handful (164=
 when I check it) disabled groupmembers out of total 7.5 million.=0A=
I'll give CREATE STATISTICS on those columns a shot and see if it gets any =
better.=0A=
=0A=
        / Eskil=0A=