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
Date: Thu, 1 Feb 2018 20:34:24 +0000
Message-ID: <1517517264764.92820@kth.se> (raw)
In-Reply-To: <23735.1517497241@sss.pgh.pa.us>
References: <1517481727.23675.3.camel@kth.se>
<23735.1517497241@sss.pgh.pa.us>
> 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
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.
I'll give CREATE STATISTICS on those columns a shot and see if it gets any better.
/ Eskil
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: eskil@kth.se, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: SV: bad plan using nested loops
In-Reply-To: <1517517264764.92820@kth.se>
* 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