script.sql

application/sql

Download

drop table if exists t1;
drop table if exists t2;
drop table if exists t3;

create table t1 (i int, j int, k int);
create table t2 (i int, j int, k int);
create table t3 (i int, j int, k int);

insert into t1 values (generate_series(1,100)*random(), generate_series(5,900)*random(), generate_series(8,800)*random());
insert into t2 values (generate_series(4,100)*random(), generate_series(5,900)*random(), generate_series(2,1000)*random());
insert into t3 values (generate_series(1,100)*random(), generate_series(3,900)*random(), generate_series(1,900)*random());

SET min_parallel_table_scan_size = 0;
SET min_parallel_index_scan_size = 0;
set parallel_tuple_cost = 0;
set parallel_setup_cost = 0;

--initPlan is attached and used under same root.
explain analyze select * from t1 where t1.k=(select max(k) from t3);

--initPlan inside an initPlan
explain analyze select * from t1 where t1.k = (select max(t3.k) from t3 where t3.j = (select avg(j) from t2));

--Same initPlan used at different node
explain analyse select sum(t1.i) from t1, t2 where t1.j=t2.j and t1.k = (select sum(k) from t3) and t1.k=t2.k;

--initPlan contains a referrence to outer node.
explain analyze select * from t1 where t1.i in (select t2.i from t2 where t1.k = (select max(k) from t3 where t3.i=t1.i));

--initPlan is attached and used under different root.
explain analyze select * from t1 where t1.i in ( select 1 + (select max(j) from t3));

--one uncorrelated subplan, output multiple rows
explain analyze select * from t1 where t1.i in (select t2.i from t2 where t2.j=1 and t1.k = (select max(k) from t3));

--one correlated subplan, output multiple rows
explain analyze select * from t1 where t1.i in (select t2.i from t2 where t2.i=t1.i);

--initplan inside subplan
explain analyze select * from t1 where t1.i in (select t2.i from t2 where t2.j=1 and t1.k = (select max(k) from t3));

--subplan inside subplan
explain analyze select * from t1 where t1.i in (select t2.i from t2 where t2.i=t1.i and t2.i in (select t3.i from t3 where t3.i=t2.i));

--gather-merge
explain analyze select * from t1 where t1.i=(select max(i) from t2) or t1.i=2 order by t1.j;

--simple nested loop
set enable_hashjoin = off;
set enable_mergejoin = off;
explain select * from t1,t2 where t1.i=t2.i;

-- nested loop inside a correlated subplan
explain analyze select * from t1 where t1.j = (select max(t3.j) from t2,t3 where t3.k=t1.k and t2.j=t3.j);