test.sql

application/sql

Download

\set ECHO all
drop table if exists foo;
create table foo(a int, b int);
insert into foo select 1,1 from generate_series(1,50000);
insert into foo select 1,2 from generate_series(1,40000);
insert into foo select 1,x/10 from generate_series(30,250000) g(x);
insert into foo select 2,1 from generate_series(1,30000);
insert into foo select 2,2 from generate_series(1,20000);
insert into foo select 2,x/10 from generate_series(30,500000) g(x);
insert into foo select 3,1 from generate_series(1,10000);
insert into foo select 3,2 from generate_series(1,5000);
insert into foo select 3,x from generate_series(3,600000) g(x);
insert into foo select x,x/10 from generate_series(4,750000) g(x);

analyse foo;
explain analyse select * from foo where a=1 and b=1;
explain analyse select * from foo where a=1 and b=2;
explain analyse select * from foo where a=1 and (b=1 or b=2);
explain analyse select * from foo where (a=1 or a=2) and (b=1 or b=2);
explain analyse select * from foo where (a=1 or a=2) and (b<=2);
explain analyse select * from foo where (a=1 or a=2 or a=4) and (b=1 or b=2);
explain analyse select * from foo where (a=1 or a=2) and not (b=2);
explain analyse select * from foo where (a=1 or a=2) and not (b=1 or b=2);
explain analyse select * from foo where a=3 and b>0 and b<3;
explain analyse select * from foo where a=3 and b>0 and b<1000;

create statistics foo_mcv_ab (mcv) on a,b from foo;
analyse foo;
select pg_mcv_list_items(stxmcv) from pg_statistic_ext;

explain analyse select * from foo where a=1 and b=1;
explain analyse select * from foo where a=1 and b=2;
explain analyse select * from foo where a=1 and (b=1 or b=2);
explain analyse select * from foo where (a=1 or a=2) and (b=1 or b=2);
explain analyse select * from foo where (a=1 or a=2) and (b<=2);
explain analyse select * from foo where (a=1 or a=2 or a=4) and (b=1 or b=2);
explain analyse select * from foo where (a=1 or a=2) and not (b=2);
explain analyse select * from foo where (a=1 or a=2) and not (b=1 or b=2);
explain analyse select * from foo where a=3 and b>0 and b<3;
explain analyse select * from foo where a=3 and b>0 and b<1000;