Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7e53a83

Browse files
committed
add some new regression tests
2 parentsf66dc31 +1013500 commit7e53a83

File tree

3 files changed

+314
-35
lines changed

3 files changed

+314
-35
lines changed

‎expected/pg_pathman.out

Lines changed: 230 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -469,7 +469,10 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel_1 UNION ALL SELECT * FROM test.
469469
* Join
470470
*/
471471
SET enable_hashjoin = OFF;
472+
set enable_nestloop = OFF;
472473
SET enable_mergejoin = ON;
474+
SET pg_pathman.enable_runtimeappend = OFF;
475+
SET pg_pathman.enable_runtimemergeappend = OFF;
473476
EXPLAIN (COSTS OFF)
474477
SELECT * FROM test.range_rel j1
475478
JOIN test.range_rel j2 on j2.id = j1.id
@@ -560,6 +563,218 @@ SELECT * FROM ttt;
560563
Filter: (value = 2)
561564
(5 rows)
562565

566+
/*
567+
* Test RuntimeAppend
568+
*/
569+
create or replace function test.pathman_assert(smt bool, error_msg text) returns text as $$
570+
begin
571+
if not smt then
572+
raise exception '%', error_msg;
573+
end if;
574+
575+
return 'ok';
576+
end;
577+
$$ language plpgsql;
578+
create or replace function test.pathman_equal(a text, b text, error_msg text) returns text as $$
579+
begin
580+
if a != b then
581+
raise exception '''%'' is not equal to ''%'', %', a, b, error_msg;
582+
end if;
583+
584+
return 'equal';
585+
end;
586+
$$ language plpgsql;
587+
create or replace function test.pathman_test(query text) returns jsonb as $$
588+
declare
589+
plan jsonb;
590+
begin
591+
execute 'explain (analyze, format json)' || query into plan;
592+
593+
return plan;
594+
end;
595+
$$ language plpgsql;
596+
create or replace function test.pathman_test_1() returns text as $$
597+
declare
598+
plan jsonb;
599+
num int;
600+
begin
601+
plan = test.pathman_test('select * from test.runtime_test_1 where id = (select * from test.run_values limit 1)');
602+
603+
perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,
604+
'"Custom Scan"',
605+
'wrong plan type');
606+
607+
perform test.pathman_equal((plan->0->'Plan'->'Custom Plan Provider')::text,
608+
'"RuntimeAppend"',
609+
'wrong plan provider');
610+
611+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Relation Name')::text,
612+
'"runtime_test_1_1"',
613+
'wrong partition');
614+
return 'ok';
615+
end;
616+
$$ language plpgsql;
617+
create or replace function test.pathman_test_2() returns text as $$
618+
declare
619+
plan jsonb;
620+
num int;
621+
begin
622+
plan = test.pathman_test('select * from test.runtime_test_1 where id = any (select * from test.run_values limit 6)');
623+
624+
perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,
625+
'"Nested Loop"',
626+
'wrong plan type');
627+
628+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Node Type')::text,
629+
'"Custom Scan"',
630+
'wrong plan type');
631+
632+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Custom Plan Provider')::text,
633+
'"RuntimeAppend"',
634+
'wrong plan provider');
635+
636+
select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;
637+
perform test.pathman_equal(num::text, '6', 'expected 6 child plans for custom scan');
638+
639+
for i in 0..5 loop
640+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->i->'Relation Name')::text,
641+
format('"runtime_test_1_%s"', i + 1),
642+
'wrong partition');
643+
644+
num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';
645+
perform test.pathman_equal(num::text, '1', 'expected 1 loop');
646+
end loop;
647+
648+
return 'ok';
649+
end;
650+
$$ language plpgsql;
651+
create or replace function test.pathman_test_3() returns text as $$
652+
declare
653+
plan jsonb;
654+
num int;
655+
begin
656+
plan = test.pathman_test('select * from test.runtime_test_1 a join test.run_values b on a.id = b.val');
657+
658+
perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,
659+
'"Nested Loop"',
660+
'wrong plan type');
661+
662+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Node Type')::text,
663+
'"Custom Scan"',
664+
'wrong plan type');
665+
666+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Custom Plan Provider')::text,
667+
'"RuntimeAppend"',
668+
'wrong plan provider');
669+
670+
select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans') into num;
671+
perform test.pathman_equal(num::text, '128', 'expected 128 child plans for custom scan');
672+
673+
for i in 0..127 loop
674+
num = plan->0->'Plan'->'Plans'->1->'Plans'->i->'Actual Loops';
675+
perform test.pathman_assert(num <= 79, 'expected no more than 79 loops');
676+
end loop;
677+
678+
return 'ok';
679+
end;
680+
$$ language plpgsql;
681+
create or replace function test.pathman_test_4() returns text as $$
682+
declare
683+
plan jsonb;
684+
num int;
685+
begin
686+
plan = test.pathman_test('select * from test.category c, lateral' ||
687+
'(select * from test.runtime_test_2 g where g.category_id = c.id order by rating limit 10) as tg');
688+
689+
perform test.pathman_equal((plan->0->'Plan'->'Node Type')::text,
690+
'"Nested Loop"',
691+
'wrong plan type');
692+
693+
/* Limit -> Custom Scan */
694+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->0->'Node Type')::text,
695+
'"Custom Scan"',
696+
'wrong plan type');
697+
698+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->0->'Custom Plan Provider')::text,
699+
'"RuntimeMergeAppend"',
700+
'wrong plan provider');
701+
702+
select count(*) from jsonb_array_elements_text(plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans') into num;
703+
perform test.pathman_equal(num::text, '10', 'expected 10 child plans for custom scan');
704+
705+
for i in 0..9 loop
706+
perform test.pathman_equal((plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Relation Name')::text,
707+
format('"runtime_test_2_%s"', i + 1),
708+
'wrong partition');
709+
710+
num = plan->0->'Plan'->'Plans'->1->'Plans'->0->'Plans'->i->'Actual Loops';
711+
perform test.pathman_assert(num = 1, 'expected no more than 1 loops');
712+
end loop;
713+
714+
return 'ok';
715+
end;
716+
$$ language plpgsql;
717+
create table test.run_values as select generate_series(1, 10000) val;
718+
create table test.runtime_test_1(id serial primary key, val real);
719+
insert into test.runtime_test_1 select generate_series(1, 10000), random();
720+
select pathman.create_hash_partitions('test.runtime_test_1', 'id', 128);
721+
NOTICE: function test.runtime_test_1_insert_trigger_func() does not exist, skipping
722+
NOTICE: function test.runtime_test_1_update_trigger_func() does not exist, skipping
723+
NOTICE: Copying data to partitions...
724+
create_hash_partitions
725+
------------------------
726+
128
727+
(1 row)
728+
729+
create table test.category as (select id, 'cat' || id::text as name from generate_series(1, 10) id);
730+
create table test.runtime_test_2 (id serial, category_id int not null, name text, rating real);
731+
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);
732+
create index on test.runtime_test_2 (category_id, rating);
733+
select pathman.create_hash_partitions('test.runtime_test_2', 'category_id', 128);
734+
NOTICE: function test.runtime_test_2_insert_trigger_func() does not exist, skipping
735+
NOTICE: function test.runtime_test_2_update_trigger_func() does not exist, skipping
736+
NOTICE: Copying data to partitions...
737+
create_hash_partitions
738+
------------------------
739+
128
740+
(1 row)
741+
742+
analyze test.run_values;
743+
analyze test.runtime_test_1;
744+
set enable_mergejoin = off;
745+
set enable_hashjoin = off;
746+
set pg_pathman.enable_runtimeappend = on;
747+
set pg_pathman.enable_runtimemergeappend = on;
748+
select test.pathman_test_1(); /* RuntimeAppend (select ... where id = (subquery)) */
749+
pathman_test_1
750+
----------------
751+
ok
752+
(1 row)
753+
754+
select test.pathman_test_2(); /* RuntimeAppend (select ... where id = any(subquery)) */
755+
pathman_test_2
756+
----------------
757+
ok
758+
(1 row)
759+
760+
select test.pathman_test_3(); /* RuntimeAppend (a join b on a.id = b.val) */
761+
pathman_test_3
762+
----------------
763+
ok
764+
(1 row)
765+
766+
select test.pathman_test_4(); /* RuntimeMergeAppend (lateral) */
767+
pathman_test_4
768+
----------------
769+
ok
770+
(1 row)
771+
772+
set pg_pathman.enable_runtimeappend = off;
773+
set pg_pathman.enable_runtimemergeappend = off;
774+
set enable_mergejoin = on;
775+
set enable_hashjoin = on;
776+
drop table test.run_values, test.runtime_test_1, test.runtime_test_2 cascade;
777+
NOTICE: drop cascades to 256 other objects
563778
/*
564779
* Test split and merge
565780
*/
@@ -632,6 +847,13 @@ NOTICE: Done!
632847
test.num_range_rel_6
633848
(1 row)
634849

850+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id >= 4000;
851+
QUERY PLAN
852+
-----------------------------------
853+
Append
854+
-> Seq Scan on num_range_rel_6
855+
(2 rows)
856+
635857
SELECT pathman.prepend_range_partition('test.num_range_rel');
636858
NOTICE: Prepending new partition...
637859
NOTICE: Done!
@@ -640,6 +862,13 @@ NOTICE: Done!
640862
test.num_range_rel_7
641863
(1 row)
642864

865+
EXPLAIN (COSTS OFF) SELECT * FROM test.num_range_rel WHERE id < 0;
866+
QUERY PLAN
867+
-----------------------------------
868+
Append
869+
-> Seq Scan on num_range_rel_7
870+
(2 rows)
871+
643872
SELECT pathman.drop_range_partition('test.num_range_rel_7');
644873
drop_range_partition
645874
----------------------
@@ -1071,7 +1300,7 @@ NOTICE: drop cascades to 5 other objects
10711300
SELECT * FROM pathman.pathman_config;
10721301
id | relname | attname | parttype | range_interval
10731302
----+--------------------+---------+----------+----------------
1074-
6 | test.num_range_rel | id | 2 | 1000
1303+
8 | test.num_range_rel | id | 2 | 1000
10751304
(1 row)
10761305

10771306
CREATE TABLE test."RangeRel" (

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp