merge-fraction-cheapest_example.sql

-- create two identical partitioned tables with 100 partitions each
create table a (id bigint, primary key (id)) partition by range (id);
create table a0 partition of a for values from ('0') to ('10000');

create table b (id bigint, primary key (id)) partition by range (id);
create table b0 partition of b for values from ('0') to ('10000');

-- insert data
insert into a (id) (select generate_series(0, 9999));
analyze a;
insert into b (id) (select generate_series(0, 9999));
analyze b;

-- verify plan; nested index only scans
set max_parallel_workers_per_gather = 0;
set enable_partitionwise_join = on;
explain (costs off) select * from a left join b using (id) order by id asc limit 10;
explain (costs off) select * from b left join a using (id) order by id desc limit 10;

-- cleanup
drop table a;
drop table b;
reset max_parallel_workers_per_gather;
reset enable_partitionwise_join;