tor 2018-02-01 klockan 20:34 +0000 skrev Johan Fredriksson: > > Johan Fredriksson <eskil@xxxxxx> 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
