On 26 March 2018 at 20:17, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 03/26/2018 09:01 PM, Dean Rasheed wrote: >> Also, just above that, in statext_clauselist_selectivity(), it >> computes the list stat_clauses, then doesn't appear to use it >> anywhere. I think that would have been the appropriate thing to pass >> to mcv_clauselist_selectivity(). Otherwise, passing unrelated clauses >> into mcv_clauselist_selectivity() will cause it to fail to find any >> matches and then underestimate. > > Will check. >
Here's a test case demonstrating this bug: drop table if exists foo; create table foo(a int, b int, c int); insert into foo select 0,0,0 from generate_series(1,100000); insert into foo select 1,1,1 from generate_series(1,10000); insert into foo select 2,2,2 from generate_series(1,1000); insert into foo select 3,3,3 from generate_series(1,100); insert into foo select x,x,x from generate_series(4,1000) g(x); insert into foo select x,x,x from generate_series(4,1000) g(x); insert into foo select x,x,x from generate_series(4,1000) g(x); insert into foo select x,x,x from generate_series(4,1000) g(x); insert into foo select x,x,x from generate_series(4,1000) g(x); analyse foo; explain analyse select * from foo where a=1 and b=1 and c=1; create statistics foo_mcv_ab (mcv) on a,b from foo; analyse foo; explain analyse select * from foo where a=1 and b=1 and c=1; With the multivariate MCV statistics, the estimate gets worse because it passes the c=1 clause to mcv_clauselist_selectivity(), and nothing matches. There's also another bug, arising from the fact that statext_is_compatible_clause() says that NOT clauses are supported, but mcv_clauselist_selectivity() doesn't support them. So with the above table: select * from foo where (a=0 or b=0) and not (b in (1,2)); ERROR: unknown clause type: 111 Regards, Dean