Johan Fredriksson <es...@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

                        regards, tom lane

Reply via email to