test-tidrangescan.sql

create extension pg_buffercache;
drop table if exists t1;
drop table if exists t2;
create table t1 (a text);
create table t2 (a text);
insert into t1 select md5(i::text) from generate_series(1,10000) s(i);
insert into t2 select md5(i::text) from generate_series(1,10000) s(i);
create index on t1 (a);
create index on t2 (a);
vacuum analyze t1;
vacuum analyze t2;

set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set enable_indexscan = off;
set enable_indexonlyscan = off;
set enable_bitmapscan = off;
set enable_seqscan = off;
set enable_mergejoin = off;
set enable_hashjoin = off;
set enable_nestloop = off;
set parallel_leader_participation = off;

reset debug_parallel_query;

alter table t1 set (parallel_workers = 0);
alter table t2 set (parallel_workers = 0);

select pg_buffercache_evict_all();
explain (analyze, verbose, costs off, timing off) select * from t1 where t1.ctid < '(20,0)'::tid;

select pg_buffercache_evict_all();
explain (analyze, verbose) select * from t1 join t2 using (a) where t1.ctid < '(20,0)'::tid and t2.ctid < '(20,0)'::tid;

alter table t1 set (parallel_workers = 2);
alter table t2 set (parallel_workers = 2);

select pg_buffercache_evict_all();
explain (analyze, verbose) select * from t1 where t1.ctid < '(20,0)'::tid;

select pg_buffercache_evict_all();
explain (analyze, verbose) select * from t1 join t2 using (a) where t1.ctid < '(20,0)'::tid and t2.ctid < '(20,0)'::tid;

alter table t1 set (parallel_workers = 0);
alter table t2 set (parallel_workers = 0);

set debug_parallel_query = regress;

select pg_buffercache_evict_all();
explain (analyze, verbose) select * from t1 where t1.ctid < '(20,0)'::tid;

select pg_buffercache_evict_all();
explain (analyze, verbose) select * from t1 join t2 using (a) where t1.ctid < '(20,0)'::tid and t2.ctid < '(20,0)'::tid;

reset debug_parallel_query;