drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 as select generate_Series(1,300000) as c1;
create table t2 as select generate_Series(1,400) as c1;
create table t3 as select floor(random()*100+1) as c1, c2 from generate_Series(1,200000) c2;
create table t4 as select generate_Series(1,200000) as c1;
alter table t1 add PRIMARY KEY (c1);
alter table t2 add PRIMARY KEY (c1);
alter table t3 add PRIMARY KEY (c1,c2);
create index on t3 (c1);
create index on t3 (c2);
alter table t4 add PRIMARY KEY (c1);
analyze verbose t1;
analyze verbose t2;
analyze verbose t3;
analyze verbose t4;
EXPLAIN (analyze on, buffers on, verbose on)
select
*
from
t1 t1
inner join t2 on t1.c1=t2.c1
inner join t3 on t2.c1=t3.c1
inner join t4 on t3.c2=t4.c1