set enable_nestloop = OFF;

SET pg_pathman.enable_runtimeappend = OFF;

SET pg_pathman.enable_runtimemergeappend = OFF;

/*

* Test RuntimeAppend

*/

create or replace function test.pathman_assert(smt bool, error_msg text) returns text as $$

begin

if not smt then

raise exception '%', error_msg;

end if;

return 'ok';

end;

$$ language plpgsql;

create or replace function test.pathman_equal(a text, b text, error_msg text) returns text as $$

begin

if a != b then

raise exception '''%'' is not equal to ''%'', %', a, b, error_msg;

end if;

return 'equal';

end;

$$ language plpgsql;

create or replace function test.pathman_test(query text) returns jsonb as $$

declare

plan jsonb;

begin

execute 'explain (analyze, format json)' || query into plan;

return plan;

end;

$$ language plpgsql;

create or replace function test.pathman_test_1() returns text as $$

declare

plan jsonb;

num int;

begin

plan = test.pathman_test('select * from test.runtime_test_1 where id = (select * from test.run_values limit 1)');

perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Custom Scan"',

'wrong plan type');

perform test.pathman_equal((plan->0->'Plan'->'Custom Plan Provider')::text,

'"RuntimeAppend"',

'wrong plan provider');

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Relation Name')::text,

'"runtime_test_1_1"',

'wrong partition');

return 'ok';

end;

$$ language plpgsql;

create or replace function test.pathman_test_2() returns text as $$

declare

plan jsonb;

num int;

begin

plan = test.pathman_test('select * from test.runtime_test_1 where id = any (select * from test.run_values limit 6)');

perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Nested Loop"',

'wrong plan type');

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Node Type')::text,

'"Custom Scan"',

'wrong plan type');

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Custom Plan Provider')::text,

'"RuntimeAppend"',

'wrong plan provider');

select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;

perform test.pathman_equal(num::text, '6', 'expected 6 child plans for custom scan');

for i in 0..5 loop

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->i->'Relation Name')::text,

format('"runtime_test_1_%s"', i + 1),

'wrong partition');

num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';

perform test.pathman_equal(num::text, '1', 'expected 1 loop');

end loop;

return 'ok';

end;

$$ language plpgsql;

create or replace function test.pathman_test_3() returns text as $$

declare

plan jsonb;

num int;

begin

plan = test.pathman_test('select * from test.runtime_test_1 a join test.run_values b on a.id = b.val');

perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Nested Loop"',

'wrong plan type');

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Node Type')::text,

'"Custom Scan"',

'wrong plan type');

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Custom Plan Provider')::text,

'"RuntimeAppend"',

'wrong plan provider');

select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;

perform test.pathman_equal(num::text, '128', 'expected 128 child plans for custom scan');

for i in 0..127 loop

num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';

perform test.pathman_assert(num <= 79, 'expected no more than 79 loops');

end loop;

return 'ok';

end;

$$ language plpgsql;

create or replace function test.pathman_test_4() returns text as $$

declare

plan jsonb;

num int;

begin

plan = test.pathman_test('select * from test.category c, lateral' ||

'(select * from test.runtime_test_2 g where g.category_id = c.id order by rating limit 10) as tg');

perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,

'"Nested Loop"',

'wrong plan type');

/* Limit -> Custom Scan */

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->0->'Node Type')::text,

'"Custom Scan"',

'wrong plan type');

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->0->'Custom Plan Provider')::text,

'"RuntimeMergeAppend"',

'wrong plan provider');

select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans') into num;

perform test.pathman_equal(num::text, '10', 'expected 10 child plans for custom scan');

for i in 0..9 loop

perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Relation Name')::text,

format('"runtime_test_2_%s"', i + 1),

'wrong partition');

num = plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Actual Loops';

perform test.pathman_assert(num = 1, 'expected no more than 1 loops');

end loop;

return 'ok';

end;

$$ language plpgsql;

create table test.run_values as select generate_series(1, 10000) val;

create table test.runtime_test_1(id serial primary key, val real);

insert into test.runtime_test_1 select generate_series(1, 10000), random();

select pathman.create_hash_partitions('test.runtime_test_1', 'id', 128);

NOTICE: function test.runtime_test_1_insert_trigger_func() does not exist, skipping

NOTICE: function test.runtime_test_1_update_trigger_func() does not exist, skipping

NOTICE: Copying data to partitions...

create_hash_partitions

------------------------

128

(1 row)

create table test.category as (select id, 'cat' || id::text as name from generate_series(1, 10) id);

create table test.runtime_test_2 (id serial, category_id int not null, name text, rating real);

insert into test.runtime_test_2 (select id, (id % 10) + 1 as category_id, 'good' || id::text as name, random() as rating from generate_series(1, 1000000) id);

create index on test.runtime_test_2 (category_id, rating);

select pathman.create_hash_partitions('test.runtime_test_2', 'category_id', 128);

NOTICE: function test.runtime_test_2_insert_trigger_func() does not exist, skipping

NOTICE: function test.runtime_test_2_update_trigger_func() does not exist, skipping

NOTICE: Copying data to partitions...

create_hash_partitions

------------------------

128

(1 row)

analyze test.run_values;

analyze test.runtime_test_1;

set enable_mergejoin = off;

set enable_hashjoin = off;

set pg_pathman.enable_runtimeappend = on;

set pg_pathman.enable_runtimemergeappend = on;

select test.pathman_test_1(); /* RuntimeAppend (select ... where id = (subquery)) */

pathman_test_1

----------------

ok

(1 row)

select test.pathman_test_2(); /* RuntimeAppend (select ... where id = any(subquery)) */

pathman_test_2

----------------

ok

(1 row)

select test.pathman_test_3(); /* RuntimeAppend (a join b on a.id = b.val) */

pathman_test_3

----------------

ok

(1 row)

select test.pathman_test_4(); /* RuntimeMergeAppend (lateral) */

pathman_test_4

----------------

ok

(1 row)

set pg_pathman.enable_runtimeappend = off;

set pg_pathman.enable_runtimemergeappend = off;

set enable_mergejoin = on;

set enable_hashjoin = on;

drop table test.run_values, test.runtime_test_1, test.runtime_test_2 cascade;

NOTICE: drop cascades to 256 other objects

EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 4000;

QUERY PLAN

-----------------------------------

Append

-> Seq Scan on num_range_rel_6

(2 rows)

EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id < 0;

QUERY PLAN

-----------------------------------

Append

-> Seq Scan on num_range_rel_7

(2 rows)

8 | test.num_range_rel | id | 2 | 1000