> 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
oftotal 7.5 million.
I'll give CREATE STATISTICS on those columns a shot and see if it gets any better.
/ Eskil