mlpartjoin.sql

application/octet-stream

Download

drop table foo, bar, baz;

create table foo (a int, b numeric, c text) partition by list (a);
create table foo1 partition of foo for values in (1) partition by range (b);
create table foo2 partition of foo for values in (2) partition by range (b);
create table foo3 partition of foo for values in (3) partition by range (b);
create table foo1a partition of foo1 for values from (0) to (1000);
create table foo1b partition of foo1 for values from (1000) to (2000);
create table foo2a partition of foo2 for values from (0) to (1000);
create table foo2b partition of foo2 for values from (1000) to (2000);
create table foo3a partition of foo3 for values from (0) to (1000);
create table foo3b partition of foo3 for values from (1000) to (2000);

create table bar (a int, b numeric, c text) partition by list (a);
create table bar1 partition of bar for values in (1);
create table bar2 partition of bar for values in (2) partition by range (b);
create table bar2a partition of bar2 for values from (0) to (1000);
create table bar2b partition of bar2 for values from (1000) to (2000);
create table bar3 partition of bar for values in (3) partition by range (b);
create table bar3a partition of bar3 for values from (0) to (1000);
create table bar3b partition of bar3 for values from (1000) to (2000);

create table baz (a int, b numeric, c text) partition by list (a);
create table baz1 partition of baz for values in (1);
create table baz2 partition of baz for values in (2);
create table baz3 partition of baz for values in (3) partition by range (b);
create table baz3a partition of baz3 for values from (0) to (1000);
create table baz3b partition of baz3 for values from (1000) to (2000);

insert into foo
  select k, l, k || ' ' || l
  from generate_series(1, 3) k, generate_series(0, 1999) l;

insert into bar
  select k, l, k || ' ' || l || ' ' || m
  from generate_series(1, 3) k, generate_series(0, 1999) l, generate_series(1, 2) m;

insert into baz
  select k, l, k || ' ' || l || ' ' || m
  from generate_series(1, 3) k, generate_series(0, 1999) l, generate_series(1, 3) m;

analyze foo;
analyze bar;
analyze baz;

set enable_partition_wise_join = true;
explain select * from foo
    join bar on foo.a = bar.a and foo.b = bar.b
    left join baz on foo.a = baz.a and foo.b = baz.b;