cluster_tests.sql

\timing
set work_mem='100MB';
set maintenance_work_mem='100MB';
create table orig1 as select ceil(2000000*random())::integer as a, 'kjhkjfhgkdfhgkjhgkdfhgkdhgdffdkjgdhdkfghfdkhkdgjkdjghhdkghkdfghdkfkdfkghdkfgh'::text from generate_series(1,2000000);
create table test1 as select * from orig1;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
create index pidx on test1(a);
select sum(a) from test1;
set enable_indexscan=false;
set enable_seqscan=true;
cluster test1 using pidx;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
drop table test1;
create table test1 as select * from orig1;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
create index pidx on test1(a);
select sum(a) from test1;
set enable_indexscan=true;
set enable_seqscan=false;
cluster test1 using pidx;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';


drop table orig1;
drop table test1;
create table orig2 as select ceil(5000000*random())::integer as a, 'kjhkjfhgkdfhgkjhgkdfhgkdhgdffdkjgdhdkfghfdkhkdgjkdjghhdkghkdfghdkfkdfkghdkfgh'::text from generate_series(1,5000000);
create table test1 as select * from orig2;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
create index pidx on test1(a);
select sum(a) from test1;
set enable_indexscan=false;
set enable_seqscan=true;
cluster test1 using pidx;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
drop table test1;
create table test1 as select * from orig2;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
create index pidx on test1(a);
select sum(a) from test1;
set enable_indexscan=true;
set enable_seqscan=false;
cluster test1 using pidx;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';





drop table orig2;
drop table test1;
create table orig3 as select ceil(10000000*random())::integer as a, 'kjhkjfhgkdfhgkjhgkdfhgkdhgdffdkjgdhdkfghfdkhkdgjkdjghhdkghkdfghdkfkdfkghdkfgh'::text from generate_series(1,10000000);
create table test1 as select * from orig3;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
create index pidx on test1(a);
select sum(a) from test1;
set enable_indexscan=false;
set enable_seqscan=true;
cluster test1 using pidx;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
drop table test1;
create table test1 as select * from orig3;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
create index pidx on test1(a);
select sum(a) from test1;
set enable_indexscan=true;
set enable_seqscan=false;
cluster test1 using pidx;
analyze test1;
select attname, correlation from pg_stats where tablename='test1';
drop table orig3;
drop table test1;