From: Johan Fredriksson <eskil@kth.se>
To: pgsql-performance@lists.postgresql.org
Subject: Re: SV: bad plan using nested loops
Date: Fri, 02 Feb 2018 10:02:07 +0100
Message-ID: <1517562127.315.1.camel@kth.se> (raw)
In-Reply-To: <1517517264764.92820@kth.se>
References: <1517481727.23675.3.camel@kth.se>
<23735.1517497241@sss.pgh.pa.us>
<1517517264764.92820@kth.se>
tor 2018-02-01 klockan 20:34 +0000 skrev Johan Fredriksson:
> > 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#P
> > LANNER-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.
It looks like you are right, Tom. There actually exists full
correlation between memberid, groupid and disabled.
rt4=# SELECT stxname, stxkeys, stxdependencies FROM pg_statistic_ext;
stxname | stxkeys | stxdependencies
-----------+---------+----------------------
cgm_stat2 | 2 6 | {"2
=> 6": 1.000000}
cgm_stat1 | 3 6 | {"3 => 6": 1.000000}
(2 rows)
However, this does not help the planner. It still picks the bad plan.
/ 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, pgsql-performance@lists.postgresql.org
Subject: Re: SV: bad plan using nested loops
In-Reply-To: <1517562127.315.1.camel@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