diff options
| author | Andres Freund | 2018-12-05 19:15:38 +0000 |
|---|---|---|
| committer | Andres Freund | 2018-12-11 01:36:58 +0000 |
| commit | db3061fc29477e2806ad3507dd7f9670ffe9d042 (patch) | |
| tree | e0d1cb8253491d256124893399a2271391ad816b | |
| parent | db0cd1434f894f748aefa7780ff029975476ca65 (diff) | |
regression differerencespluggable-zheap
27 files changed, 3677 insertions, 123 deletions
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out index 2aaa4df53b..844305e1bf 100644 --- a/contrib/pageinspect/expected/btree.out +++ b/contrib/pageinspect/expected/btree.out @@ -31,30 +31,28 @@ btpo_flags | 3 SELECT * FROM bt_page_stats('test1_a_idx', 2); ERROR: block number out of range -SELECT * FROM bt_page_items('test1_a_idx', 0); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items('test1_a_idx', 0); ERROR: block 0 is a meta page -SELECT * FROM bt_page_items('test1_a_idx', 1); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items('test1_a_idx', 1); -[ RECORD 1 ]----------------------- itemoffset | 1 -ctid | (0,1) itemlen | 16 nulls | f vars | f data | 01 00 00 00 00 00 00 01 -SELECT * FROM bt_page_items('test1_a_idx', 2); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items('test1_a_idx', 2); ERROR: block number out of range -SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 0)); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items(get_raw_page('test1_a_idx', 0)); ERROR: block is a meta page -SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1)); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items(get_raw_page('test1_a_idx', 1)); -[ RECORD 1 ]----------------------- itemoffset | 1 -ctid | (0,1) itemlen | 16 nulls | f vars | f data | 01 00 00 00 00 00 00 01 -SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 2)); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items(get_raw_page('test1_a_idx', 2)); ERROR: block number 2 is out of range for relation "test1_a_idx" DROP TABLE test1; diff --git a/contrib/pageinspect/expected/hash.out b/contrib/pageinspect/expected/hash.out index 75d7bcfad5..9d4b57b0e3 100644 --- a/contrib/pageinspect/expected/hash.out +++ b/contrib/pageinspect/expected/hash.out @@ -140,23 +140,22 @@ SELECT live_items, dead_items, page_size, hasho_prevblkno, hasho_nextblkno, hasho_bucket, hasho_flag, hasho_page_id FROM hash_page_stats(get_raw_page('test_hash_a_idx', 5)); ERROR: page is not a hash bucket or overflow page -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 0)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 0)); ERROR: page is not a hash bucket or overflow page -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 1)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 1)); (0 rows) -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 2)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 2)); (0 rows) -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 3)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 3)); -[ RECORD 1 ]---------- itemoffset | 1 -ctid | (0,1) data | 2389907270 -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 4)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 4)); (0 rows) -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 5)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 5)); ERROR: page is not a hash bucket or overflow page DROP TABLE test_hash; diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out index 9c8b26709c..2d6c79f365 100644 --- a/contrib/pageinspect/expected/page.out +++ b/contrib/pageinspect/expected/page.out @@ -85,19 +85,19 @@ SELECT * FROM fsm_page_contents(get_raw_page('test1', 'fsm', 0)); DROP TABLE test1; -- check that using any of these functions with a partitioned table or index -- would fail -create table test_partitioned (a int) partition by range (a); +create table test_partitioned (a int) partition by range (a) USING heap; create index test_partitioned_index on test_partitioned (a); select get_raw_page('test_partitioned', 0); -- error about partitioned table ERROR: cannot get raw page from partitioned table "test_partitioned" select get_raw_page('test_partitioned_index', 0); -- error about partitioned index ERROR: cannot get raw page from partitioned index "test_partitioned_index" -- a regular table which is a member of a partition set should work though -create table test_part1 partition of test_partitioned for values from ( 1 ) to (100); +create table test_part1 partition of test_partitioned for values from ( 1 ) to (100) USING heap; select get_raw_page('test_part1', 0); -- get farther and error about empty table ERROR: block number 0 is out of range for relation "test_part1" drop table test_partitioned; -- check null bitmap alignment for table whose number of attributes is multiple of 8 -create table test8 (f1 int, f2 int, f3 int, f4 int, f5 int, f6 int, f7 int, f8 int); +create table test8 (f1 int, f2 int, f3 int, f4 int, f5 int, f6 int, f7 int, f8 int) USING heap; insert into test8(f1, f8) values (x'7f00007f'::int, 0); select t_bits, t_data from heap_page_items(get_raw_page('test8', 0)); t_bits | t_data diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql index 8eac64c7b3..d1e1e3fd8d 100644 --- a/contrib/pageinspect/sql/btree.sql +++ b/contrib/pageinspect/sql/btree.sql @@ -10,12 +10,12 @@ SELECT * FROM bt_page_stats('test1_a_idx', 0); SELECT * FROM bt_page_stats('test1_a_idx', 1); SELECT * FROM bt_page_stats('test1_a_idx', 2); -SELECT * FROM bt_page_items('test1_a_idx', 0); -SELECT * FROM bt_page_items('test1_a_idx', 1); -SELECT * FROM bt_page_items('test1_a_idx', 2); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items('test1_a_idx', 0); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items('test1_a_idx', 1); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items('test1_a_idx', 2); -SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 0)); -SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1)); -SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 2)); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items(get_raw_page('test1_a_idx', 0)); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items(get_raw_page('test1_a_idx', 1)); +SELECT itemoffset, itemlen, nulls, vars, data FROM bt_page_items(get_raw_page('test1_a_idx', 2)); DROP TABLE test1; diff --git a/contrib/pageinspect/sql/hash.sql b/contrib/pageinspect/sql/hash.sql index 87ee549a7b..ecab865fc7 100644 --- a/contrib/pageinspect/sql/hash.sql +++ b/contrib/pageinspect/sql/hash.sql @@ -69,12 +69,12 @@ SELECT live_items, dead_items, page_size, hasho_prevblkno, hasho_nextblkno, hasho_bucket, hasho_flag, hasho_page_id FROM hash_page_stats(get_raw_page('test_hash_a_idx', 5)); -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 0)); -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 1)); -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 2)); -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 3)); -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 4)); -SELECT * FROM hash_page_items(get_raw_page('test_hash_a_idx', 5)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 0)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 1)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 2)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 3)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 4)); +SELECT itemoffset, data FROM hash_page_items(get_raw_page('test_hash_a_idx', 5)); DROP TABLE test_hash; diff --git a/contrib/pageinspect/sql/page.sql b/contrib/pageinspect/sql/page.sql index 8f0ef62cdc..a8ae901e51 100644 --- a/contrib/pageinspect/sql/page.sql +++ b/contrib/pageinspect/sql/page.sql @@ -35,18 +35,18 @@ DROP TABLE test1; -- check that using any of these functions with a partitioned table or index -- would fail -create table test_partitioned (a int) partition by range (a); +create table test_partitioned (a int) partition by range (a) USING heap; create index test_partitioned_index on test_partitioned (a); select get_raw_page('test_partitioned', 0); -- error about partitioned table select get_raw_page('test_partitioned_index', 0); -- error about partitioned index -- a regular table which is a member of a partition set should work though -create table test_part1 partition of test_partitioned for values from ( 1 ) to (100); +create table test_part1 partition of test_partitioned for values from ( 1 ) to (100) USING heap; select get_raw_page('test_part1', 0); -- get farther and error about empty table drop table test_partitioned; -- check null bitmap alignment for table whose number of attributes is multiple of 8 -create table test8 (f1 int, f2 int, f3 int, f4 int, f5 int, f6 int, f7 int, f8 int); +create table test8 (f1 int, f2 int, f3 int, f4 int, f5 int, f6 int, f7 int, f8 int) USING heap; insert into test8(f1, f8) values (x'7f00007f'::int, 0); select t_bits, t_data from heap_page_items(get_raw_page('test8', 0)); select tuple_data_split('test8'::regclass, t_data, t_infomask, t_infomask2, t_bits) diff --git a/contrib/pg_visibility/expected/pg_visibility.out b/contrib/pg_visibility/expected/pg_visibility.out index f0dcb897c4..b898a63611 100644 --- a/contrib/pg_visibility/expected/pg_visibility.out +++ b/contrib/pg_visibility/expected/pg_visibility.out @@ -3,7 +3,7 @@ CREATE EXTENSION pg_visibility; -- check that using the module's functions with unsupported relations will fail -- -- partitioned tables (the parent ones) don't have visibility maps -create table test_partitioned (a int) partition by list (a); +create table test_partitioned (a int) partition by list (a) using heap; -- these should all fail select pg_visibility('test_partitioned', 0); ERROR: "test_partitioned" is not a table, materialized view, or TOAST table @@ -15,7 +15,7 @@ select pg_check_frozen('test_partitioned'); ERROR: "test_partitioned" is not a table, materialized view, or TOAST table select pg_truncate_visibility_map('test_partitioned'); ERROR: "test_partitioned" is not a table, materialized view, or TOAST table -create table test_partition partition of test_partitioned for values in (1); +create table test_partition partition of test_partitioned for values in (1) using heap; create index test_index on test_partition (a); -- indexes do not, so these all fail select pg_visibility('test_index', 0); @@ -67,7 +67,7 @@ ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table select pg_truncate_visibility_map('test_foreign_table'); ERROR: "test_foreign_table" is not a table, materialized view, or TOAST table -- check some of the allowed relkinds -create table regular_table (a int); +create table regular_table (a int) using heap; insert into regular_table values (1), (2); vacuum regular_table; select count(*) > 0 from pg_visibility('regular_table'); @@ -83,7 +83,7 @@ select count(*) > 0 from pg_visibility('regular_table'); f (1 row) -create materialized view matview_visibility_test as select * from regular_table; +create materialized view matview_visibility_test using heap as select * from regular_table; vacuum matview_visibility_test; select count(*) > 0 from pg_visibility('matview_visibility_test'); ?column? diff --git a/contrib/pg_visibility/sql/pg_visibility.sql b/contrib/pg_visibility/sql/pg_visibility.sql index c2a7f1d9e4..edc01b8c5a 100644 --- a/contrib/pg_visibility/sql/pg_visibility.sql +++ b/contrib/pg_visibility/sql/pg_visibility.sql @@ -5,7 +5,7 @@ CREATE EXTENSION pg_visibility; -- -- partitioned tables (the parent ones) don't have visibility maps -create table test_partitioned (a int) partition by list (a); +create table test_partitioned (a int) partition by list (a) using heap; -- these should all fail select pg_visibility('test_partitioned', 0); select pg_visibility_map('test_partitioned'); @@ -13,7 +13,7 @@ select pg_visibility_map_summary('test_partitioned'); select pg_check_frozen('test_partitioned'); select pg_truncate_visibility_map('test_partitioned'); -create table test_partition partition of test_partitioned for values in (1); +create table test_partition partition of test_partitioned for values in (1) using heap; create index test_index on test_partition (a); -- indexes do not, so these all fail select pg_visibility('test_index', 0); @@ -49,14 +49,14 @@ select pg_check_frozen('test_foreign_table'); select pg_truncate_visibility_map('test_foreign_table'); -- check some of the allowed relkinds -create table regular_table (a int); +create table regular_table (a int) using heap; insert into regular_table values (1), (2); vacuum regular_table; select count(*) > 0 from pg_visibility('regular_table'); truncate regular_table; select count(*) > 0 from pg_visibility('regular_table'); -create materialized view matview_visibility_test as select * from regular_table; +create materialized view matview_visibility_test using heap as select * from regular_table; vacuum matview_visibility_test; select count(*) > 0 from pg_visibility('matview_visibility_test'); insert into regular_table values (1), (2); diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out index 9858ea69d4..72f9c3400c 100644 --- a/contrib/pgstattuple/expected/pgstattuple.out +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -4,7 +4,7 @@ CREATE EXTENSION pgstattuple; -- the pgstattuple functions, but the results for empty tables and -- indexes should be that. -- -create table test (a int primary key, b int[]); +create table test (a int primary key, b int[]) using heap; select * from pgstattuple('test'); table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent -----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+-------------- @@ -151,7 +151,7 @@ ERROR: relation "test_hashidx" is not a btree index select pgstatginindex('test_hashidx'); ERROR: relation "test_hashidx" is not a GIN index -- check that using any of these functions with unsupported relations will fail -create table test_partitioned (a int) partition by range (a); +create table test_partitioned (a int) partition by range (a) using heap; create index test_partitioned_index on test_partitioned(a); -- these should all fail select pgstattuple('test_partitioned'); @@ -199,7 +199,7 @@ ERROR: relation "test_foreign_table" is not a GIN index select pgstathashindex('test_foreign_table'); ERROR: "test_foreign_table" is not an index -- a partition of a partitioned table should work though -create table test_partition partition of test_partitioned for values from (1) to (100); +create table test_partition partition of test_partitioned for values from (1) to (100) using heap; select pgstattuple('test_partition'); pgstattuple --------------------- diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql index cfa540302d..cca3abe3b7 100644 --- a/contrib/pgstattuple/sql/pgstattuple.sql +++ b/contrib/pgstattuple/sql/pgstattuple.sql @@ -6,7 +6,7 @@ CREATE EXTENSION pgstattuple; -- indexes should be that. -- -create table test (a int primary key, b int[]); +create table test (a int primary key, b int[]) using heap; select * from pgstattuple('test'); select * from pgstattuple('test'::text); @@ -63,7 +63,7 @@ select pgstatindex('test_hashidx'); select pgstatginindex('test_hashidx'); -- check that using any of these functions with unsupported relations will fail -create table test_partitioned (a int) partition by range (a); +create table test_partitioned (a int) partition by range (a) using heap; create index test_partitioned_index on test_partitioned(a); -- these should all fail select pgstattuple('test_partitioned'); @@ -95,7 +95,7 @@ select pgstatginindex('test_foreign_table'); select pgstathashindex('test_foreign_table'); -- a partition of a partitioned table should work though -create table test_partition partition of test_partitioned for values from (1) to (100); +create table test_partition partition of test_partitioned for values from (1) to (100) using heap; select pgstattuple('test_partition'); select pgstattuple_approx('test_partition'); select pg_relpages('test_partition'); diff --git a/contrib/test_decoding/Makefile b/contrib/test_decoding/Makefile index 4afb1d963e..056394a3c7 100644 --- a/contrib/test_decoding/Makefile +++ b/contrib/test_decoding/Makefile @@ -12,6 +12,9 @@ ISOLATION = mxact delayed_startup ondisk_startup concurrent_ddl_dml \ REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/test_decoding/logical.conf ISOLATION_OPTS = --temp-config $(top_srcdir)/contrib/test_decoding/logical.conf +# Temp fix while zheap is not supported +PGOPTIONS += -c default_table_access_method=heap + # Disabled because these tests require "wal_level=logical", which # typical installcheck users do not have (e.g. buildfarm clients). NO_INSTALLCHECK = 1 diff --git a/src/pl/plperl/expected/plperl_transaction.out b/src/pl/plperl/expected/plperl_transaction.out index 7ca0ef35fb..3a3c25091a 100644 --- a/src/pl/plperl/expected/plperl_transaction.out +++ b/src/pl/plperl/expected/plperl_transaction.out @@ -12,7 +12,7 @@ foreach my $i (0..9) { } $$; CALL transaction_test1(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | @@ -35,7 +35,7 @@ foreach my $i (0..9) { } } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | @@ -63,7 +63,7 @@ $$; SELECT transaction_test2(); ERROR: invalid transaction termination at line 5. CONTEXT: PL/Perl function "transaction_test2" -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -78,7 +78,7 @@ $$; SELECT transaction_test3(); ERROR: invalid transaction termination at line 5. at line 2. CONTEXT: PL/Perl function "transaction_test3" -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -105,7 +105,7 @@ while (defined($row = spi_fetchrow($sth))) { spi_commit(); } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | @@ -133,11 +133,11 @@ while (defined($row = spi_fetchrow($sth))) { $$; ERROR: division by zero at line 5. CONTEXT: PL/Perl anonymous code block -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b -----+--- - -6 | -12 | + -6 | (2 rows) SELECT * FROM pg_cursors; @@ -155,7 +155,7 @@ while (defined($row = spi_fetchrow($sth))) { spi_rollback(); } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -179,7 +179,7 @@ while (defined($row = spi_fetchrow($sth))) { } } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | diff --git a/src/pl/plperl/expected/plperl_trigger.out b/src/pl/plperl/expected/plperl_trigger.out index 28011cd9f6..ca72b07c7d 100644 --- a/src/pl/plperl/expected/plperl_trigger.out +++ b/src/pl/plperl/expected/plperl_trigger.out @@ -167,7 +167,7 @@ INSERT INTO trigger_test (i, v, foo) VALUES (2,'second line', '("(2)")'); INSERT INTO trigger_test (i, v, foo) VALUES (3,'third line', '("(3)")'); INSERT INTO trigger_test (i, v, foo) VALUES (4,'immortal', '("(4)")'); INSERT INTO trigger_test (i, v) VALUES (101,'bad id'); -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; i | v | foo ---+----------------------------------+--------- 1 | first line(modified by trigger) | ("(2)") @@ -178,7 +178,7 @@ SELECT * FROM trigger_test; UPDATE trigger_test SET i = 5 where i=3; UPDATE trigger_test SET i = 100 where i=1; -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; i | v | foo ---+------------------------------------------------------+--------- 1 | first line(modified by trigger) | ("(2)") @@ -205,15 +205,15 @@ $$ LANGUAGE plperl; CREATE TRIGGER "test_trigger_recurse" BEFORE INSERT ON trigger_test FOR EACH ROW EXECUTE PROCEDURE "trigger_recurse"(); INSERT INTO trigger_test (i, v) values (10000, 'top'); -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; i | v | foo -------+------------------------------------------------------+--------- 1 | first line(modified by trigger) | ("(2)") 2 | second line(modified by trigger) | ("(3)") 4 | immortal | ("(4)") 5 | third line(modified by trigger)(modified by trigger) | ("(5)") - 20000 | child | 10000 | top | + 20000 | child | (6 rows) CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$ @@ -229,7 +229,7 @@ $$ LANGUAGE plperl; CREATE TRIGGER "immortal_trig" BEFORE DELETE ON trigger_test FOR EACH ROW EXECUTE PROCEDURE immortal('immortal'); DELETE FROM trigger_test; -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; i | v | foo ---+----------+--------- 4 | immortal | ("(4)") diff --git a/src/pl/plperl/sql/plperl_transaction.sql b/src/pl/plperl/sql/plperl_transaction.sql index 0a60799805..95a9dae773 100644 --- a/src/pl/plperl/sql/plperl_transaction.sql +++ b/src/pl/plperl/sql/plperl_transaction.sql @@ -16,7 +16,7 @@ $$; CALL transaction_test1(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; TRUNCATE test1; @@ -34,7 +34,7 @@ foreach my $i (0..9) { } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; TRUNCATE test1; @@ -56,7 +56,7 @@ $$; SELECT transaction_test2(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- also not allowed if procedure is called from a function @@ -69,7 +69,7 @@ $$; SELECT transaction_test3(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- DO block inside function @@ -98,7 +98,7 @@ while (defined($row = spi_fetchrow($sth))) { } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- check that this doesn't leak a holdable portal SELECT * FROM pg_cursors; @@ -116,7 +116,7 @@ while (defined($row = spi_fetchrow($sth))) { } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; SELECT * FROM pg_cursors; @@ -133,7 +133,7 @@ while (defined($row = spi_fetchrow($sth))) { } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; SELECT * FROM pg_cursors; @@ -154,7 +154,7 @@ while (defined($row = spi_fetchrow($sth))) { } $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; SELECT * FROM pg_cursors; diff --git a/src/pl/plperl/sql/plperl_trigger.sql b/src/pl/plperl/sql/plperl_trigger.sql index 624193b9d0..7991ffc517 100644 --- a/src/pl/plperl/sql/plperl_trigger.sql +++ b/src/pl/plperl/sql/plperl_trigger.sql @@ -114,13 +114,13 @@ INSERT INTO trigger_test (i, v, foo) VALUES (4,'immortal', '("(4)")'); INSERT INTO trigger_test (i, v) VALUES (101,'bad id'); -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; UPDATE trigger_test SET i = 5 where i=3; UPDATE trigger_test SET i = 100 where i=1; -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; DROP TRIGGER "test_valid_id_trig" ON trigger_test; @@ -144,7 +144,7 @@ FOR EACH ROW EXECUTE PROCEDURE "trigger_recurse"(); INSERT INTO trigger_test (i, v) values (10000, 'top'); -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; CREATE OR REPLACE FUNCTION immortal() RETURNS trigger AS $$ if ($_TD->{old}{v} eq $_TD->{args}[0]) @@ -162,7 +162,7 @@ FOR EACH ROW EXECUTE PROCEDURE immortal('immortal'); DELETE FROM trigger_test; -SELECT * FROM trigger_test; +SELECT * FROM trigger_test ORDER BY i; CREATE FUNCTION direct_trigger() RETURNS trigger AS $$ return; diff --git a/src/pl/plpython/expected/plpython_transaction.out b/src/pl/plpython/expected/plpython_transaction.out index 14152993c7..1fbd452045 100644 --- a/src/pl/plpython/expected/plpython_transaction.out +++ b/src/pl/plpython/expected/plpython_transaction.out @@ -10,7 +10,7 @@ for i in range(0, 10): plpy.rollback() $$; CALL transaction_test1(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | @@ -31,7 +31,7 @@ for i in range(0, 10): else: plpy.rollback() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | @@ -57,7 +57,7 @@ $$; SELECT transaction_test2(); ERROR: invalid transaction termination CONTEXT: PL/Python function "transaction_test2" -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -75,7 +75,7 @@ CONTEXT: Traceback (most recent call last): PL/Python function "transaction_test3", line 2, in <module> plpy.execute("CALL transaction_test1()") PL/Python function "transaction_test3" -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -111,7 +111,7 @@ for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) plpy.commit() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | @@ -139,11 +139,11 @@ CONTEXT: Traceback (most recent call last): PL/Python anonymous code block, line 3, in <module> plpy.execute("INSERT INTO test1 (a) VALUES (12/(%s-2))" % row['x']) PL/Python anonymous code block -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b -----+--- - -6 | -12 | + -6 | (2 rows) SELECT * FROM pg_cursors; @@ -158,7 +158,7 @@ for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x']) plpy.rollback() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -178,7 +178,7 @@ for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): else: plpy.rollback() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | diff --git a/src/pl/plpython/sql/plpython_transaction.sql b/src/pl/plpython/sql/plpython_transaction.sql index 33b37e5b7f..d8668771dc 100644 --- a/src/pl/plpython/sql/plpython_transaction.sql +++ b/src/pl/plpython/sql/plpython_transaction.sql @@ -14,7 +14,7 @@ $$; CALL transaction_test1(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; TRUNCATE test1; @@ -30,7 +30,7 @@ for i in range(0, 10): plpy.rollback() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; TRUNCATE test1; @@ -50,7 +50,7 @@ $$; SELECT transaction_test2(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- also not allowed if procedure is called from a function @@ -63,7 +63,7 @@ $$; SELECT transaction_test3(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- DO block inside function @@ -97,7 +97,7 @@ for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): plpy.commit() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- check that this doesn't leak a holdable portal SELECT * FROM pg_cursors; @@ -112,7 +112,7 @@ for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): plpy.commit() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; SELECT * FROM pg_cursors; @@ -126,7 +126,7 @@ for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): plpy.rollback() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; SELECT * FROM pg_cursors; @@ -143,7 +143,7 @@ for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"): plpy.rollback() $$; -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; SELECT * FROM pg_cursors; diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.out index 17e821bb4c..bb1feb8555 100644 --- a/src/pl/tcl/expected/pltcl_queries.out +++ b/src/pl/tcl/expected/pltcl_queries.out @@ -12,7 +12,7 @@ insert into T_pkey2 values (1, 'key1-3', 'test key'); insert into T_pkey2 values (2, 'key2-1', 'test key'); insert into T_pkey2 values (2, 'key2-2', 'test key'); insert into T_pkey2 values (2, 'key2-3', 'test key'); -select * from T_pkey1; +select * from T_pkey1 order by 1, 2; key1 | key2 | txt ------+----------------------+------------------------------------------ 1 | key1-1 | test key @@ -24,7 +24,7 @@ select * from T_pkey1; (6 rows) -- key2 in T_pkey2 should have upper case only -select * from T_pkey2; +select * from T_pkey2 order by 1, 2; key1 | key2 | txt ------+----------------------+------------------------------------------ 1 | KEY1-1 | test key @@ -79,24 +79,24 @@ NOTICE: updated 1 entries in T_dta2 for new key in T_pkey2 delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; NOTICE: deleted 1 entries from T_dta2 -select * from T_pkey1; +select * from T_pkey1 order by 1, 2; key1 | key2 | txt ------+----------------------+------------------------------------------ 1 | key1-1 | test key 1 | key1-2 | test key 1 | key1-3 | test key - 2 | key2-3 | test key 1 | KEY1-3 | should work + 2 | key2-3 | test key 2 | key2-9 | test key (6 rows) -select * from T_pkey2; +select * from T_pkey2 order by 1, 2; key1 | key2 | txt ------+----------------------+------------------------------------------ 1 | KEY1-3 | test key + 1 | KEY1-9 | test key 2 | KEY2-3 | test key 2 | KEY2-9 | test key - 1 | KEY1-9 | test key (4 rows) select * from T_dta1; @@ -110,8 +110,8 @@ select * from T_dta1; select * from T_dta2; tkey | ref1 | ref2 ------------+------+---------------------- - trec 3 | 1 | KEY1-3 trec 1 | 1 | KEY1-9 + trec 3 | 1 | KEY1-3 (2 rows) select tcl_avg(key1) from T_pkey1; diff --git a/src/pl/tcl/expected/pltcl_transaction.out b/src/pl/tcl/expected/pltcl_transaction.out index 007204b99a..e0c8180b89 100644 --- a/src/pl/tcl/expected/pltcl_transaction.out +++ b/src/pl/tcl/expected/pltcl_transaction.out @@ -14,7 +14,7 @@ for {set i 0} {$i < 10} {incr i} { } $$; CALL transaction_test1(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- 0 | @@ -41,7 +41,7 @@ return 1 $$; SELECT transaction_test2(); ERROR: invalid transaction termination -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -55,7 +55,7 @@ return 1 $$; SELECT transaction_test3(); ERROR: invalid transaction termination -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -74,7 +74,7 @@ spi_exec -array row "SELECT * FROM test2 ORDER BY x" { $$; CALL transaction_test4a(); ERROR: cannot commit while a subtransaction is active -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) @@ -91,7 +91,7 @@ spi_exec -array row "SELECT * FROM test2 ORDER BY x" { $$; CALL transaction_test4b(); ERROR: cannot roll back while a subtransaction is active -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; a | b ---+--- (0 rows) diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sql index 7390de6bd6..de9ba1ffdc 100644 --- a/src/pl/tcl/sql/pltcl_queries.sql +++ b/src/pl/tcl/sql/pltcl_queries.sql @@ -15,10 +15,10 @@ insert into T_pkey2 values (2, 'key2-1', 'test key'); insert into T_pkey2 values (2, 'key2-2', 'test key'); insert into T_pkey2 values (2, 'key2-3', 'test key'); -select * from T_pkey1; +select * from T_pkey1 order by 1, 2; -- key2 in T_pkey2 should have upper case only -select * from T_pkey2; +select * from T_pkey2 order by 1, 2; insert into T_pkey1 values (1, 'KEY1-3', 'should work'); @@ -53,8 +53,8 @@ update T_pkey2 set key2 = 'KEY1-9' where key1 = 1 and key2 = 'KEY1-1'; delete from T_pkey2 where key1 = 2 and key2 = 'KEY2-2'; delete from T_pkey2 where key1 = 1 and key2 = 'KEY1-2'; -select * from T_pkey1; -select * from T_pkey2; +select * from T_pkey1 order by 1, 2; +select * from T_pkey2 order by 1, 2; select * from T_dta1; select * from T_dta2; diff --git a/src/pl/tcl/sql/pltcl_transaction.sql b/src/pl/tcl/sql/pltcl_transaction.sql index c752faf665..c1ce9b1634 100644 --- a/src/pl/tcl/sql/pltcl_transaction.sql +++ b/src/pl/tcl/sql/pltcl_transaction.sql @@ -19,7 +19,7 @@ $$; CALL transaction_test1(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; TRUNCATE test1; @@ -41,7 +41,7 @@ $$; SELECT transaction_test2(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- also not allowed if procedure is called from a function @@ -54,7 +54,7 @@ $$; SELECT transaction_test3(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- commit inside cursor loop @@ -74,7 +74,7 @@ $$; CALL transaction_test4a(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; -- rollback inside cursor loop @@ -91,7 +91,7 @@ $$; CALL transaction_test4b(); -SELECT * FROM test1; +SELECT * FROM test1 ORDER BY 1, 2; DROP TABLE test1; diff --git a/src/test/isolation/expected/eval-plan-qual_1.out b/src/test/isolation/expected/eval-plan-qual_1.out index 9a1c38aba3..43442c0d33 100644 --- a/src/test/isolation/expected/eval-plan-qual_1.out +++ b/src/test/isolation/expected/eval-plan-qual_1.out @@ -116,7 +116,7 @@ step c2: COMMIT; step partiallock: <... completed> accountid balance accountid balance -checking 1050 checking 1050 +checking 1050 checking 600 savings 600 savings 600 step c1: COMMIT; step read: SELECT * FROM accounts ORDER BY accountid; @@ -156,7 +156,7 @@ step c2: COMMIT; step partiallock_ext: <... completed> accountid balance other newcol newcol2 accountid balance other newcol newcol2 -checking 1050 other 42 checking 1050 other 42 +checking 1050 other 42 checking 600 other 42 savings 1150 42 savings 700 42 step c1: COMMIT; step read_ext: SELECT * FROM accounts_ext ORDER BY accountid; diff --git a/src/test/regress/expected/partition_aggregate_1.out b/src/test/regress/expected/partition_aggregate_1.out new file mode 100644 index 0000000000..e6a8eca5e4 --- /dev/null +++ b/src/test/regress/expected/partition_aggregate_1.out @@ -0,0 +1,1527 @@ +-- +-- PARTITION_AGGREGATE +-- Test partitionwise aggregation on partitioned tables +-- +-- Enable partitionwise aggregate, which by default is disabled. +SET enable_partitionwise_aggregate TO true; +-- Enable partitionwise join, which by default is disabled. +SET enable_partitionwise_join TO true; +-- Disable parallel plans. +SET max_parallel_workers_per_gather TO 0; +-- +-- Tests for list partitioned tables. +-- +CREATE TABLE pagg_tab (a int, b int, c text, d int) PARTITION BY LIST(c); +CREATE TABLE pagg_tab_p1 PARTITION OF pagg_tab FOR VALUES IN ('0000', '0001', '0002', '0003'); +CREATE TABLE pagg_tab_p2 PARTITION OF pagg_tab FOR VALUES IN ('0004', '0005', '0006', '0007'); +CREATE TABLE pagg_tab_p3 PARTITION OF pagg_tab FOR VALUES IN ('0008', '0009', '0010', '0011'); +INSERT INTO pagg_tab SELECT i % 20, i % 30, to_char(i % 12, 'FM0000'), i % 30 FROM generate_series(0, 2999) i; +ANALYZE pagg_tab; +-- When GROUP BY clause matches; full aggregation is performed for each partition. +EXPLAIN (COSTS OFF) +SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3; + QUERY PLAN +----------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)), (avg(pagg_tab_p1.b)) + -> Append + -> HashAggregate + Group Key: pagg_tab_p1.c + Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + -> Seq Scan on pagg_tab_p1 + -> HashAggregate + Group Key: pagg_tab_p2.c + Filter: (avg(pagg_tab_p2.d) < '15'::numeric) + -> Seq Scan on pagg_tab_p2 + -> HashAggregate + Group Key: pagg_tab_p3.c + Filter: (avg(pagg_tab_p3.d) < '15'::numeric) + -> Seq Scan on pagg_tab_p3 +(15 rows) + +SELECT c, sum(a), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY c HAVING avg(d) < 15 ORDER BY 1, 2, 3; + c | sum | avg | count | min | max +------+------+---------------------+-------+-----+----- + 0000 | 2000 | 12.0000000000000000 | 250 | 0 | 24 + 0001 | 2250 | 13.0000000000000000 | 250 | 1 | 25 + 0002 | 2500 | 14.0000000000000000 | 250 | 2 | 26 + 0006 | 2500 | 12.0000000000000000 | 250 | 2 | 24 + 0007 | 2750 | 13.0000000000000000 | 250 | 3 | 25 + 0008 | 2000 | 14.0000000000000000 | 250 | 0 | 26 +(6 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for each partition. +EXPLAIN (COSTS OFF) +SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3; + QUERY PLAN +----------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_p1.a, (sum(pagg_tab_p1.b)), (avg(pagg_tab_p1.b)) + -> Finalize HashAggregate + Group Key: pagg_tab_p1.a + Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + -> Append + -> Partial HashAggregate + Group Key: pagg_tab_p1.a + -> Seq Scan on pagg_tab_p1 + -> Partial HashAggregate + Group Key: pagg_tab_p2.a + -> Seq Scan on pagg_tab_p2 + -> Partial HashAggregate + Group Key: pagg_tab_p3.a + -> Seq Scan on pagg_tab_p3 +(15 rows) + +SELECT a, sum(b), avg(b), count(*), min(a), max(b) FROM pagg_tab GROUP BY a HAVING avg(d) < 15 ORDER BY 1, 2, 3; + a | sum | avg | count | min | max +----+------+---------------------+-------+-----+----- + 0 | 1500 | 10.0000000000000000 | 150 | 0 | 20 + 1 | 1650 | 11.0000000000000000 | 150 | 1 | 21 + 2 | 1800 | 12.0000000000000000 | 150 | 2 | 22 + 3 | 1950 | 13.0000000000000000 | 150 | 3 | 23 + 4 | 2100 | 14.0000000000000000 | 150 | 4 | 24 + 10 | 1500 | 10.0000000000000000 | 150 | 10 | 20 + 11 | 1650 | 11.0000000000000000 | 150 | 11 | 21 + 12 | 1800 | 12.0000000000000000 | 150 | 12 | 22 + 13 | 1950 | 13.0000000000000000 | 150 | 13 | 23 + 14 | 2100 | 14.0000000000000000 | 150 | 14 | 24 +(10 rows) + +-- Check with multiple columns in GROUP BY +EXPLAIN (COSTS OFF) +SELECT a, c, count(*) FROM pagg_tab GROUP BY a, c; + QUERY PLAN +------------------------------------------------- + Append + -> HashAggregate + Group Key: pagg_tab_p1.a, pagg_tab_p1.c + -> Seq Scan on pagg_tab_p1 + -> HashAggregate + Group Key: pagg_tab_p2.a, pagg_tab_p2.c + -> Seq Scan on pagg_tab_p2 + -> HashAggregate + Group Key: pagg_tab_p3.a, pagg_tab_p3.c + -> Seq Scan on pagg_tab_p3 +(10 rows) + +-- Check with multiple columns in GROUP BY, order in GROUP BY is reversed +EXPLAIN (COSTS OFF) +SELECT a, c, count(*) FROM pagg_tab GROUP BY c, a; + QUERY PLAN +------------------------------------------------- + Append + -> HashAggregate + Group Key: pagg_tab_p1.c, pagg_tab_p1.a + -> Seq Scan on pagg_tab_p1 + -> HashAggregate + Group Key: pagg_tab_p2.c, pagg_tab_p2.a + -> Seq Scan on pagg_tab_p2 + -> HashAggregate + Group Key: pagg_tab_p3.c, pagg_tab_p3.a + -> Seq Scan on pagg_tab_p3 +(10 rows) + +-- Check with multiple columns in GROUP BY, order in target-list is reversed +EXPLAIN (COSTS OFF) +SELECT c, a, count(*) FROM pagg_tab GROUP BY a, c; + QUERY PLAN +------------------------------------------------- + Append + -> HashAggregate + Group Key: pagg_tab_p1.a, pagg_tab_p1.c + -> Seq Scan on pagg_tab_p1 + -> HashAggregate + Group Key: pagg_tab_p2.a, pagg_tab_p2.c + -> Seq Scan on pagg_tab_p2 + -> HashAggregate + Group Key: pagg_tab_p3.a, pagg_tab_p3.c + -> Seq Scan on pagg_tab_p3 +(10 rows) + +-- Test when input relation for grouping is dummy +EXPLAIN (COSTS OFF) +SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; + QUERY PLAN +-------------------------------- + HashAggregate + Group Key: pagg_tab.c + -> Result + One-Time Filter: false +(4 rows) + +SELECT c, sum(a) FROM pagg_tab WHERE 1 = 2 GROUP BY c; + c | sum +---+----- +(0 rows) + +EXPLAIN (COSTS OFF) +SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; + QUERY PLAN +-------------------------------- + GroupAggregate + Group Key: pagg_tab.c + -> Result + One-Time Filter: false +(4 rows) + +SELECT c, sum(a) FROM pagg_tab WHERE c = 'x' GROUP BY c; + c | sum +---+----- +(0 rows) + +-- Test GroupAggregate paths by disabling hash aggregates. +SET enable_hashagg TO false; +-- When GROUP BY clause matches full aggregation is performed for each partition. +EXPLAIN (COSTS OFF) +SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; + QUERY PLAN +----------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)), (avg(pagg_tab_p1.b)) + -> Append + -> GroupAggregate + Group Key: pagg_tab_p1.c + Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + -> Sort + Sort Key: pagg_tab_p1.c + -> Seq Scan on pagg_tab_p1 + -> GroupAggregate + Group Key: pagg_tab_p2.c + Filter: (avg(pagg_tab_p2.d) < '15'::numeric) + -> Sort + Sort Key: pagg_tab_p2.c + -> Seq Scan on pagg_tab_p2 + -> GroupAggregate + Group Key: pagg_tab_p3.c + Filter: (avg(pagg_tab_p3.d) < '15'::numeric) + -> Sort + Sort Key: pagg_tab_p3.c + -> Seq Scan on pagg_tab_p3 +(21 rows) + +SELECT c, sum(a), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; + c | sum | avg | count +------+------+---------------------+------- + 0000 | 2000 | 12.0000000000000000 | 250 + 0001 | 2250 | 13.0000000000000000 | 250 + 0002 | 2500 | 14.0000000000000000 | 250 + 0006 | 2500 | 12.0000000000000000 | 250 + 0007 | 2750 | 13.0000000000000000 | 250 + 0008 | 2000 | 14.0000000000000000 | 250 +(6 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for each partition. +EXPLAIN (COSTS OFF) +SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; + QUERY PLAN +----------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_p1.a, (sum(pagg_tab_p1.b)), (avg(pagg_tab_p1.b)) + -> Finalize GroupAggregate + Group Key: pagg_tab_p1.a + Filter: (avg(pagg_tab_p1.d) < '15'::numeric) + -> Merge Append + Sort Key: pagg_tab_p1.a + -> Partial GroupAggregate + Group Key: pagg_tab_p1.a + -> Sort + Sort Key: pagg_tab_p1.a + -> Seq Scan on pagg_tab_p1 + -> Partial GroupAggregate + Group Key: pagg_tab_p2.a + -> Sort + Sort Key: pagg_tab_p2.a + -> Seq Scan on pagg_tab_p2 + -> Partial GroupAggregate + Group Key: pagg_tab_p3.a + -> Sort + Sort Key: pagg_tab_p3.a + -> Seq Scan on pagg_tab_p3 +(22 rows) + +SELECT a, sum(b), avg(b), count(*) FROM pagg_tab GROUP BY 1 HAVING avg(d) < 15 ORDER BY 1, 2, 3; + a | sum | avg | count +----+------+---------------------+------- + 0 | 1500 | 10.0000000000000000 | 150 + 1 | 1650 | 11.0000000000000000 | 150 + 2 | 1800 | 12.0000000000000000 | 150 + 3 | 1950 | 13.0000000000000000 | 150 + 4 | 2100 | 14.0000000000000000 | 150 + 10 | 1500 | 10.0000000000000000 | 150 + 11 | 1650 | 11.0000000000000000 | 150 + 12 | 1800 | 12.0000000000000000 | 150 + 13 | 1950 | 13.0000000000000000 | 150 + 14 | 2100 | 14.0000000000000000 | 150 +(10 rows) + +-- Test partitionwise grouping without any aggregates +EXPLAIN (COSTS OFF) +SELECT c FROM pagg_tab GROUP BY c ORDER BY 1; + QUERY PLAN +------------------------------------------- + Merge Append + Sort Key: pagg_tab_p1.c + -> Group + Group Key: pagg_tab_p1.c + -> Sort + Sort Key: pagg_tab_p1.c + -> Seq Scan on pagg_tab_p1 + -> Group + Group Key: pagg_tab_p2.c + -> Sort + Sort Key: pagg_tab_p2.c + -> Seq Scan on pagg_tab_p2 + -> Group + Group Key: pagg_tab_p3.c + -> Sort + Sort Key: pagg_tab_p3.c + -> Seq Scan on pagg_tab_p3 +(17 rows) + +SELECT c FROM pagg_tab GROUP BY c ORDER BY 1; + c +------ + 0000 + 0001 + 0002 + 0003 + 0004 + 0005 + 0006 + 0007 + 0008 + 0009 + 0010 + 0011 +(12 rows) + +EXPLAIN (COSTS OFF) +SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1; + QUERY PLAN +------------------------------------------------- + Group + Group Key: pagg_tab_p1.a + -> Merge Append + Sort Key: pagg_tab_p1.a + -> Group + Group Key: pagg_tab_p1.a + -> Sort + Sort Key: pagg_tab_p1.a + -> Seq Scan on pagg_tab_p1 + Filter: (a < 3) + -> Group + Group Key: pagg_tab_p2.a + -> Sort + Sort Key: pagg_tab_p2.a + -> Seq Scan on pagg_tab_p2 + Filter: (a < 3) + -> Group + Group Key: pagg_tab_p3.a + -> Sort + Sort Key: pagg_tab_p3.a + -> Seq Scan on pagg_tab_p3 + Filter: (a < 3) +(22 rows) + +SELECT a FROM pagg_tab WHERE a < 3 GROUP BY a ORDER BY 1; + a +--- + 0 + 1 + 2 +(3 rows) + +RESET enable_hashagg; +-- ROLLUP, partitionwise aggregation does not apply +EXPLAIN (COSTS OFF) +SELECT c, sum(a) FROM pagg_tab GROUP BY rollup(c) ORDER BY 1, 2; + QUERY PLAN +------------------------------------------------- + Sort + Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.a)) + -> MixedAggregate + Hash Key: pagg_tab_p1.c + Group Key: () + -> Append + -> Seq Scan on pagg_tab_p1 + -> Seq Scan on pagg_tab_p2 + -> Seq Scan on pagg_tab_p3 +(9 rows) + +-- ORDERED SET within the aggregate. +-- Full aggregation; since all the rows that belong to the same group come +-- from the same partition, having an ORDER BY within the aggregate doesn't +-- make any difference. +EXPLAIN (COSTS OFF) +SELECT c, sum(b order by a) FROM pagg_tab GROUP BY c ORDER BY 1, 2; + QUERY PLAN +------------------------------------------------------------------------ + Sort + Sort Key: pagg_tab_p1.c, (sum(pagg_tab_p1.b ORDER BY pagg_tab_p1.a)) + -> Append + -> GroupAggregate + Group Key: pagg_tab_p1.c + -> Sort + Sort Key: pagg_tab_p1.c + -> Seq Scan on pagg_tab_p1 + -> GroupAggregate + Group Key: pagg_tab_p2.c + -> Sort + Sort Key: pagg_tab_p2.c + -> Seq Scan on pagg_tab_p2 + -> GroupAggregate + Group Key: pagg_tab_p3.c + -> Sort + Sort Key: pagg_tab_p3.c + -> Seq Scan on pagg_tab_p3 +(18 rows) + +-- Since GROUP BY clause does not match with PARTITION KEY; we need to do +-- partial aggregation. However, ORDERED SET are not partial safe and thus +-- partitionwise aggregation plan is not generated. +EXPLAIN (COSTS OFF) +SELECT a, sum(b order by a) FROM pagg_tab GROUP BY a ORDER BY 1, 2; + QUERY PLAN +------------------------------------------------------------------------ + Sort + Sort Key: pagg_tab_p1.a, (sum(pagg_tab_p1.b ORDER BY pagg_tab_p1.a)) + -> GroupAggregate + Group Key: pagg_tab_p1.a + -> Sort + Sort Key: pagg_tab_p1.a + -> Append + -> Seq Scan on pagg_tab_p1 + -> Seq Scan on pagg_tab_p2 + -> Seq Scan on pagg_tab_p3 +(10 rows) + +-- JOIN query +CREATE TABLE pagg_tab1(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE pagg_tab1_p1 PARTITION OF pagg_tab1 FOR VALUES FROM (0) TO (10); +CREATE TABLE pagg_tab1_p2 PARTITION OF pagg_tab1 FOR VALUES FROM (10) TO (20); +CREATE TABLE pagg_tab1_p3 PARTITION OF pagg_tab1 FOR VALUES FROM (20) TO (30); +CREATE TABLE pagg_tab2(x int, y int) PARTITION BY RANGE(y); +CREATE TABLE pagg_tab2_p1 PARTITION OF pagg_tab2 FOR VALUES FROM (0) TO (10); +CREATE TABLE pagg_tab2_p2 PARTITION OF pagg_tab2 FOR VALUES FROM (10) TO (20); +CREATE TABLE pagg_tab2_p3 PARTITION OF pagg_tab2 FOR VALUES FROM (20) TO (30); +INSERT INTO pagg_tab1 SELECT i % 30, i % 20 FROM generate_series(0, 299, 2) i; +INSERT INTO pagg_tab2 SELECT i % 20, i % 30 FROM generate_series(0, 299, 3) i; +ANALYZE pagg_tab1; +ANALYZE pagg_tab2; +-- When GROUP BY clause matches; full aggregation is performed for each partition. +EXPLAIN (COSTS OFF) +SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------- + Sort + Sort Key: t1.x, (sum(t1.y)), (count(*)) + -> Append + -> HashAggregate + Group Key: t1.x + -> Hash Join + Hash Cond: (t1.x = t2.y) + -> Seq Scan on pagg_tab1_p1 t1 + -> Hash + -> Seq Scan on pagg_tab2_p1 t2 + -> HashAggregate + Group Key: t1_1.x + -> Hash Join + Hash Cond: (t1_1.x = t2_1.y) + -> Seq Scan on pagg_tab1_p2 t1_1 + -> Hash + -> Seq Scan on pagg_tab2_p2 t2_1 + -> HashAggregate + Group Key: t1_2.x + -> Hash Join + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on pagg_tab2_p3 t2_2 + -> Hash + -> Seq Scan on pagg_tab1_p3 t1_2 +(24 rows) + +SELECT t1.x, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + x | sum | count +----+------+------- + 0 | 500 | 100 + 6 | 1100 | 100 + 12 | 700 | 100 + 18 | 1300 | 100 + 24 | 900 | 100 +(5 rows) + +-- Check with whole-row reference; partitionwise aggregation does not apply +EXPLAIN (COSTS OFF) +SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------- + Sort + Sort Key: t1.x, (sum(t1.y)), (count(((t1.*)::pagg_tab1))) + -> HashAggregate + Group Key: t1.x + -> Hash Join + Hash Cond: (t1.x = t2.y) + -> Append + -> Seq Scan on pagg_tab1_p1 t1 + -> Seq Scan on pagg_tab1_p2 t1_1 + -> Seq Scan on pagg_tab1_p3 t1_2 + -> Hash + -> Append + -> Seq Scan on pagg_tab2_p1 t2 + -> Seq Scan on pagg_tab2_p2 t2_1 + -> Seq Scan on pagg_tab2_p3 t2_2 +(15 rows) + +SELECT t1.x, sum(t1.y), count(t1) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.x ORDER BY 1, 2, 3; + x | sum | count +----+------+------- + 0 | 500 | 100 + 6 | 1100 | 100 + 12 | 700 | 100 + 18 | 1300 | 100 + 24 | 900 | 100 +(5 rows) + +-- GROUP BY having other matching key +EXPLAIN (COSTS OFF) +SELECT t2.y, sum(t1.y), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t2.y ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------- + Sort + Sort Key: t2.y, (sum(t1.y)), (count(*)) + -> Append + -> HashAggregate + Group Key: t2.y + -> Hash Join + Hash Cond: (t1.x = t2.y) + -> Seq Scan on pagg_tab1_p1 t1 + -> Hash + -> Seq Scan on pagg_tab2_p1 t2 + -> HashAggregate + Group Key: t2_1.y + -> Hash Join + Hash Cond: (t1_1.x = t2_1.y) + -> Seq Scan on pagg_tab1_p2 t1_1 + -> Hash + -> Seq Scan on pagg_tab2_p2 t2_1 + -> HashAggregate + Group Key: t2_2.y + -> Hash Join + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on pagg_tab2_p3 t2_2 + -> Hash + -> Seq Scan on pagg_tab1_p3 t1_2 +(24 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for each partition. +-- Also test GroupAggregate paths by disabling hash aggregates. +SET enable_hashagg TO false; +EXPLAIN (COSTS OFF) +SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------------------- + Sort + Sort Key: t1.y, (sum(t1.x)), (count(*)) + -> Finalize GroupAggregate + Group Key: t1.y + Filter: (avg(t1.x) > '10'::numeric) + -> Merge Append + Sort Key: t1.y + -> Partial GroupAggregate + Group Key: t1.y + -> Sort + Sort Key: t1.y + -> Hash Join + Hash Cond: (t1.x = t2.y) + -> Seq Scan on pagg_tab1_p1 t1 + -> Hash + -> Seq Scan on pagg_tab2_p1 t2 + -> Partial GroupAggregate + Group Key: t1_1.y + -> Sort + Sort Key: t1_1.y + -> Hash Join + Hash Cond: (t1_1.x = t2_1.y) + -> Seq Scan on pagg_tab1_p2 t1_1 + -> Hash + -> Seq Scan on pagg_tab2_p2 t2_1 + -> Partial GroupAggregate + Group Key: t1_2.y + -> Sort + Sort Key: t1_2.y + -> Hash Join + Hash Cond: (t2_2.y = t1_2.x) + -> Seq Scan on pagg_tab2_p3 t2_2 + -> Hash + -> Seq Scan on pagg_tab1_p3 t1_2 +(34 rows) + +SELECT t1.y, sum(t1.x), count(*) FROM pagg_tab1 t1, pagg_tab2 t2 WHERE t1.x = t2.y GROUP BY t1.y HAVING avg(t1.x) > 10 ORDER BY 1, 2, 3; + y | sum | count +----+------+------- + 2 | 600 | 50 + 4 | 1200 | 50 + 8 | 900 | 50 + 12 | 600 | 50 + 14 | 1200 | 50 + 18 | 900 | 50 +(6 rows) + +RESET enable_hashagg; +-- Check with LEFT/RIGHT/FULL OUTER JOINs which produces NULL values for +-- aggregation +-- LEFT JOIN, should produce partial partitionwise aggregation plan as +-- GROUP BY is on nullable column +EXPLAIN (COSTS OFF) +SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Group Key: b.y + -> Sort + Sort Key: b.y + -> Append + -> Partial HashAggregate + Group Key: b.y + -> Hash Left Join + Hash Cond: (a.x = b.y) + -> Seq Scan on pagg_tab1_p1 a + -> Hash + -> Seq Scan on pagg_tab2_p1 b + -> Partial HashAggregate + Group Key: b_1.y + -> Hash Left Join + Hash Cond: (a_1.x = b_1.y) + -> Seq Scan on pagg_tab1_p2 a_1 + -> Hash + -> Seq Scan on pagg_tab2_p2 b_1 + -> Partial HashAggregate + Group Key: b_2.y + -> Hash Right Join + Hash Cond: (b_2.y = a_2.x) + -> Seq Scan on pagg_tab2_p3 b_2 + -> Hash + -> Seq Scan on pagg_tab1_p3 a_2 +(26 rows) + +SELECT b.y, sum(a.y) FROM pagg_tab1 a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; + y | sum +----+------ + 0 | 500 + 6 | 1100 + 12 | 700 + 18 | 1300 + 24 | 900 + | 900 +(6 rows) + +-- RIGHT JOIN, should produce full partitionwise aggregation plan as +-- GROUP BY is on non-nullable column +EXPLAIN (COSTS OFF) +SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; + QUERY PLAN +------------------------------------------------------------ + Sort + Sort Key: b.y + -> Append + -> HashAggregate + Group Key: b.y + -> Hash Right Join + Hash Cond: (a.x = b.y) + -> Seq Scan on pagg_tab1_p1 a + -> Hash + -> Seq Scan on pagg_tab2_p1 b + -> HashAggregate + Group Key: b_1.y + -> Hash Right Join + Hash Cond: (a_1.x = b_1.y) + -> Seq Scan on pagg_tab1_p2 a_1 + -> Hash + -> Seq Scan on pagg_tab2_p2 b_1 + -> HashAggregate + Group Key: b_2.y + -> Hash Left Join + Hash Cond: (b_2.y = a_2.x) + -> Seq Scan on pagg_tab2_p3 b_2 + -> Hash + -> Seq Scan on pagg_tab1_p3 a_2 +(24 rows) + +SELECT b.y, sum(a.y) FROM pagg_tab1 a RIGHT JOIN pagg_tab2 b ON a.x = b.y GROUP BY b.y ORDER BY 1 NULLS LAST; + y | sum +----+------ + 0 | 500 + 3 | + 6 | 1100 + 9 | + 12 | 700 + 15 | + 18 | 1300 + 21 | + 24 | 900 + 27 | +(10 rows) + +-- FULL JOIN, should produce partial partitionwise aggregation plan as +-- GROUP BY is on nullable column +EXPLAIN (COSTS OFF) +SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST; + QUERY PLAN +------------------------------------------------------------------ + Finalize GroupAggregate + Group Key: a.x + -> Sort + Sort Key: a.x + -> Append + -> Partial HashAggregate + Group Key: a.x + -> Hash Full Join + Hash Cond: (a.x = b.y) + -> Seq Scan on pagg_tab1_p1 a + -> Hash + -> Seq Scan on pagg_tab2_p1 b + -> Partial HashAggregate + Group Key: a_1.x + -> Hash Full Join + Hash Cond: (a_1.x = b_1.y) + -> Seq Scan on pagg_tab1_p2 a_1 + -> Hash + -> Seq Scan on pagg_tab2_p2 b_1 + -> Partial HashAggregate + Group Key: a_2.x + -> Hash Full Join + Hash Cond: (b_2.y = a_2.x) + -> Seq Scan on pagg_tab2_p3 b_2 + -> Hash + -> Seq Scan on pagg_tab1_p3 a_2 +(26 rows) + +SELECT a.x, sum(b.x) FROM pagg_tab1 a FULL OUTER JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x ORDER BY 1 NULLS LAST; + x | sum +----+------ + 0 | 500 + 2 | + 4 | + 6 | 1100 + 8 | + 10 | + 12 | 700 + 14 | + 16 | + 18 | 1300 + 20 | + 22 | + 24 | 900 + 26 | + 28 | + | 500 +(16 rows) + +-- LEFT JOIN, with dummy relation on right side, +-- should produce full partitionwise aggregation plan as GROUP BY is on +-- non-nullable columns +EXPLAIN (COSTS OFF) +SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; + QUERY PLAN +----------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab1_p1.x, y + -> Append + -> HashAggregate + Group Key: pagg_tab1_p1.x, y + -> Hash Left Join + Hash Cond: (pagg_tab1_p1.x = y) + Filter: ((pagg_tab1_p1.x > 5) OR (y < 20)) + -> Seq Scan on pagg_tab1_p1 + Filter: (x < 20) + -> Hash + -> Result + One-Time Filter: false + -> HashAggregate + Group Key: pagg_tab1_p2.x, pagg_tab2_p2.y + -> Hash Left Join + Hash Cond: (pagg_tab1_p2.x = pagg_tab2_p2.y) + Filter: ((pagg_tab1_p2.x > 5) OR (pagg_tab2_p2.y < 20)) + -> Seq Scan on pagg_tab1_p2 + Filter: (x < 20) + -> Hash + -> Seq Scan on pagg_tab2_p2 + Filter: (y > 10) +(23 rows) + +SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a LEFT JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; + x | y | count +----+----+------- + 6 | | 10 + 8 | | 10 + 10 | | 10 + 12 | 12 | 100 + 14 | | 10 + 16 | | 10 + 18 | 18 | 100 +(7 rows) + +-- FULL JOIN, with dummy relations on both sides, +-- should produce partial partitionwise aggregation plan as GROUP BY is on +-- nullable columns +EXPLAIN (COSTS OFF) +SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; + QUERY PLAN +----------------------------------------------------------------------------------- + Finalize GroupAggregate + Group Key: pagg_tab1_p1.x, y + -> Sort + Sort Key: pagg_tab1_p1.x, y + -> Append + -> Partial HashAggregate + Group Key: pagg_tab1_p1.x, y + -> Hash Full Join + Hash Cond: (pagg_tab1_p1.x = y) + Filter: ((pagg_tab1_p1.x > 5) OR (y < 20)) + -> Seq Scan on pagg_tab1_p1 + Filter: (x < 20) + -> Hash + -> Result + One-Time Filter: false + -> Partial HashAggregate + Group Key: pagg_tab1_p2.x, pagg_tab2_p2.y + -> Hash Full Join + Hash Cond: (pagg_tab1_p2.x = pagg_tab2_p2.y) + Filter: ((pagg_tab1_p2.x > 5) OR (pagg_tab2_p2.y < 20)) + -> Seq Scan on pagg_tab1_p2 + Filter: (x < 20) + -> Hash + -> Seq Scan on pagg_tab2_p2 + Filter: (y > 10) + -> Partial HashAggregate + Group Key: x, pagg_tab2_p3.y + -> Hash Full Join + Hash Cond: (pagg_tab2_p3.y = x) + Filter: ((x > 5) OR (pagg_tab2_p3.y < 20)) + -> Seq Scan on pagg_tab2_p3 + Filter: (y > 10) + -> Hash + -> Result + One-Time Filter: false +(35 rows) + +SELECT a.x, b.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x < 20) a FULL JOIN (SELECT * FROM pagg_tab2 WHERE y > 10) b ON a.x = b.y WHERE a.x > 5 or b.y < 20 GROUP BY a.x, b.y ORDER BY 1, 2; + x | y | count +----+----+------- + 6 | | 10 + 8 | | 10 + 10 | | 10 + 12 | 12 | 100 + 14 | | 10 + 16 | | 10 + 18 | 18 | 100 + | 15 | 10 +(8 rows) + +-- Empty join relation because of empty outer side, no partitionwise agg plan +EXPLAIN (COSTS OFF) +SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; + QUERY PLAN +--------------------------------------- + GroupAggregate + Group Key: pagg_tab1.x, pagg_tab1.y + -> Sort + Sort Key: pagg_tab1.y + -> Result + One-Time Filter: false +(6 rows) + +SELECT a.x, a.y, count(*) FROM (SELECT * FROM pagg_tab1 WHERE x = 1 AND x = 2) a LEFT JOIN pagg_tab2 b ON a.x = b.y GROUP BY a.x, a.y ORDER BY 1, 2; + x | y | count +---+---+------- +(0 rows) + +-- Partition by multiple columns +CREATE TABLE pagg_tab_m (a int, b int, c int) PARTITION BY RANGE(a, ((a+b)/2)); +CREATE TABLE pagg_tab_m_p1 PARTITION OF pagg_tab_m FOR VALUES FROM (0, 0) TO (10, 10); +CREATE TABLE pagg_tab_m_p2 PARTITION OF pagg_tab_m FOR VALUES FROM (10, 10) TO (20, 20); +CREATE TABLE pagg_tab_m_p3 PARTITION OF pagg_tab_m FOR VALUES FROM (20, 20) TO (30, 30); +INSERT INTO pagg_tab_m SELECT i % 30, i % 40, i % 50 FROM generate_series(0, 2999) i; +ANALYZE pagg_tab_m; +-- Partial aggregation as GROUP BY clause does not match with PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3; + QUERY PLAN +----------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_m_p1.a, (sum(pagg_tab_m_p1.b)), (avg(pagg_tab_m_p1.c)) + -> Finalize HashAggregate + Group Key: pagg_tab_m_p1.a + Filter: (avg(pagg_tab_m_p1.c) < '22'::numeric) + -> Append + -> Partial HashAggregate + Group Key: pagg_tab_m_p1.a + -> Seq Scan on pagg_tab_m_p1 + -> Partial HashAggregate + Group Key: pagg_tab_m_p2.a + -> Seq Scan on pagg_tab_m_p2 + -> Partial HashAggregate + Group Key: pagg_tab_m_p3.a + -> Seq Scan on pagg_tab_m_p3 +(15 rows) + +SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a HAVING avg(c) < 22 ORDER BY 1, 2, 3; + a | sum | avg | count +----+------+---------------------+------- + 0 | 1500 | 20.0000000000000000 | 100 + 1 | 1600 | 21.0000000000000000 | 100 + 10 | 1500 | 20.0000000000000000 | 100 + 11 | 1600 | 21.0000000000000000 | 100 + 20 | 1500 | 20.0000000000000000 | 100 + 21 | 1600 | 21.0000000000000000 | 100 +(6 rows) + +-- Full aggregation as GROUP BY clause matches with PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_m_p1.a, (sum(pagg_tab_m_p1.b)), (avg(pagg_tab_m_p1.c)) + -> Append + -> HashAggregate + Group Key: pagg_tab_m_p1.a, ((pagg_tab_m_p1.a + pagg_tab_m_p1.b) / 2) + Filter: (sum(pagg_tab_m_p1.b) < 50) + -> Seq Scan on pagg_tab_m_p1 + -> HashAggregate + Group Key: pagg_tab_m_p2.a, ((pagg_tab_m_p2.a + pagg_tab_m_p2.b) / 2) + Filter: (sum(pagg_tab_m_p2.b) < 50) + -> Seq Scan on pagg_tab_m_p2 + -> HashAggregate + Group Key: pagg_tab_m_p3.a, ((pagg_tab_m_p3.a + pagg_tab_m_p3.b) / 2) + Filter: (sum(pagg_tab_m_p3.b) < 50) + -> Seq Scan on pagg_tab_m_p3 +(15 rows) + +SELECT a, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY a, (a+b)/2 HAVING sum(b) < 50 ORDER BY 1, 2, 3; + a | sum | avg | count +----+-----+---------------------+------- + 0 | 0 | 20.0000000000000000 | 25 + 1 | 25 | 21.0000000000000000 | 25 + 10 | 0 | 20.0000000000000000 | 25 + 11 | 25 | 21.0000000000000000 | 25 + 20 | 0 | 20.0000000000000000 | 25 + 21 | 25 | 21.0000000000000000 | 25 +(6 rows) + +-- Full aggregation as PARTITION KEY is part of GROUP BY clause +EXPLAIN (COSTS OFF) +SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------------------------------------------------ + Sort + Sort Key: pagg_tab_m_p1.a, pagg_tab_m_p1.c, (sum(pagg_tab_m_p1.b)) + -> Append + -> HashAggregate + Group Key: ((pagg_tab_m_p1.a + pagg_tab_m_p1.b) / 2), pagg_tab_m_p1.c, pagg_tab_m_p1.a + Filter: ((sum(pagg_tab_m_p1.b) = 50) AND (avg(pagg_tab_m_p1.c) > '25'::numeric)) + -> Seq Scan on pagg_tab_m_p1 + -> HashAggregate + Group Key: ((pagg_tab_m_p2.a + pagg_tab_m_p2.b) / 2), pagg_tab_m_p2.c, pagg_tab_m_p2.a + Filter: ((sum(pagg_tab_m_p2.b) = 50) AND (avg(pagg_tab_m_p2.c) > '25'::numeric)) + -> Seq Scan on pagg_tab_m_p2 + -> HashAggregate + Group Key: ((pagg_tab_m_p3.a + pagg_tab_m_p3.b) / 2), pagg_tab_m_p3.c, pagg_tab_m_p3.a + Filter: ((sum(pagg_tab_m_p3.b) = 50) AND (avg(pagg_tab_m_p3.c) > '25'::numeric)) + -> Seq Scan on pagg_tab_m_p3 +(15 rows) + +SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1 HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3; + a | c | sum | avg | count +----+----+-----+---------------------+------- + 0 | 30 | 50 | 30.0000000000000000 | 5 + 0 | 40 | 50 | 40.0000000000000000 | 5 + 10 | 30 | 50 | 30.0000000000000000 | 5 + 10 | 40 | 50 | 40.0000000000000000 | 5 + 20 | 30 | 50 | 30.0000000000000000 | 5 + 20 | 40 | 50 | 40.0000000000000000 | 5 +(6 rows) + +-- Test with multi-level partitioning scheme +CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a); +CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO (10); +CREATE TABLE pagg_tab_ml_p2 PARTITION OF pagg_tab_ml FOR VALUES FROM (10) TO (20) PARTITION BY LIST (c); +CREATE TABLE pagg_tab_ml_p2_s1 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0000', '0001'); +CREATE TABLE pagg_tab_ml_p2_s2 PARTITION OF pagg_tab_ml_p2 FOR VALUES IN ('0002', '0003'); +-- This level of partitioning has different column positions than the parent +CREATE TABLE pagg_tab_ml_p3(b int, c text, a int) PARTITION BY RANGE (b); +CREATE TABLE pagg_tab_ml_p3_s1(c text, a int, b int); +CREATE TABLE pagg_tab_ml_p3_s2 PARTITION OF pagg_tab_ml_p3 FOR VALUES FROM (5) TO (10); +ALTER TABLE pagg_tab_ml_p3 ATTACH PARTITION pagg_tab_ml_p3_s1 FOR VALUES FROM (0) TO (5); +ALTER TABLE pagg_tab_ml ATTACH PARTITION pagg_tab_ml_p3 FOR VALUES FROM (20) TO (30); +INSERT INTO pagg_tab_ml SELECT i % 30, i % 10, to_char(i % 4, 'FM0000') FROM generate_series(0, 29999) i; +ANALYZE pagg_tab_ml; +-- For Parallel Append +SET max_parallel_workers_per_gather TO 2; +-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY +-- for level 1 only. For subpartitions, GROUP BY clause does not match with +-- PARTITION KEY, but still we do not see a partial aggregation as array_agg() +-- is not partial agg safe. +EXPLAIN (COSTS OFF) +SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (array_agg(DISTINCT pagg_tab_ml_p1.c)) + -> Append + -> GroupAggregate + Group Key: pagg_tab_ml_p1.a + Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p1.a + -> Seq Scan on pagg_tab_ml_p1 + -> GroupAggregate + Group Key: pagg_tab_ml_p2_s1.a + Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p2_s1.a + -> Append + -> Seq Scan on pagg_tab_ml_p2_s1 + -> Seq Scan on pagg_tab_ml_p2_s2 + -> GroupAggregate + Group Key: pagg_tab_ml_p3_s1.a + Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p3_s1.a + -> Append + -> Seq Scan on pagg_tab_ml_p3_s1 + -> Seq Scan on pagg_tab_ml_p3_s2 +(25 rows) + +SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; + a | sum | array_agg | count +----+------+-------------+------- + 0 | 0 | {0000,0002} | 1000 + 1 | 1000 | {0001,0003} | 1000 + 2 | 2000 | {0000,0002} | 1000 + 10 | 0 | {0000,0002} | 1000 + 11 | 1000 | {0001,0003} | 1000 + 12 | 2000 | {0000,0002} | 1000 + 20 | 0 | {0000,0002} | 1000 + 21 | 1000 | {0001,0003} | 1000 + 22 | 2000 | {0000,0002} | 1000 +(9 rows) + +-- Without ORDER BY clause, to test Gather at top-most path +EXPLAIN (COSTS OFF) +SELECT a, sum(b), array_agg(distinct c), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3; + QUERY PLAN +----------------------------------------------------------- + Append + -> GroupAggregate + Group Key: pagg_tab_ml_p1.a + Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p1.a + -> Seq Scan on pagg_tab_ml_p1 + -> GroupAggregate + Group Key: pagg_tab_ml_p2_s1.a + Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p2_s1.a + -> Append + -> Seq Scan on pagg_tab_ml_p2_s1 + -> Seq Scan on pagg_tab_ml_p2_s2 + -> GroupAggregate + Group Key: pagg_tab_ml_p3_s1.a + Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p3_s1.a + -> Append + -> Seq Scan on pagg_tab_ml_p3_s1 + -> Seq Scan on pagg_tab_ml_p3_s2 +(23 rows) + +-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY +-- for level 1 only. For subpartitions, GROUP BY clause does not match with +-- PARTITION KEY, thus we will have a partial aggregation for them. +EXPLAIN (COSTS OFF) +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) + -> Append + -> HashAggregate + Group Key: pagg_tab_ml_p1.a + Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + -> Seq Scan on pagg_tab_ml_p1 + -> Finalize GroupAggregate + Group Key: pagg_tab_ml_p2_s1.a + Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p2_s1.a + -> Append + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s1.a + -> Seq Scan on pagg_tab_ml_p2_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s2.a + -> Seq Scan on pagg_tab_ml_p2_s2 + -> Finalize GroupAggregate + Group Key: pagg_tab_ml_p3_s1.a + Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + -> Sort + Sort Key: pagg_tab_ml_p3_s1.a + -> Append + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s1.a + -> Seq Scan on pagg_tab_ml_p3_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s2.a + -> Seq Scan on pagg_tab_ml_p3_s2 +(31 rows) + +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; + a | sum | count +----+------+------- + 0 | 0 | 1000 + 1 | 1000 | 1000 + 2 | 2000 | 1000 + 10 | 0 | 1000 + 11 | 1000 | 1000 + 12 | 2000 | 1000 + 20 | 0 | 1000 + 21 | 1000 | 1000 + 22 | 2000 | 1000 +(9 rows) + +-- Partial aggregation at all levels as GROUP BY clause does not match with +-- PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; + QUERY PLAN +------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_ml_p1.b, (sum(pagg_tab_ml_p1.a)), (count(*)) + -> Finalize GroupAggregate + Group Key: pagg_tab_ml_p1.b + -> Sort + Sort Key: pagg_tab_ml_p1.b + -> Append + -> Partial HashAggregate + Group Key: pagg_tab_ml_p1.b + -> Seq Scan on pagg_tab_ml_p1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s1.b + -> Seq Scan on pagg_tab_ml_p2_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s2.b + -> Seq Scan on pagg_tab_ml_p2_s2 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s1.b + -> Seq Scan on pagg_tab_ml_p3_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s2.b + -> Seq Scan on pagg_tab_ml_p3_s2 +(22 rows) + +SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; + b | sum | count +---+-------+------- + 0 | 30000 | 3000 + 1 | 33000 | 3000 + 2 | 36000 | 3000 + 3 | 39000 | 3000 + 4 | 42000 | 3000 +(5 rows) + +-- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; + QUERY PLAN +---------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) + -> Append + -> HashAggregate + Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c + Filter: (avg(pagg_tab_ml_p1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p1 + -> HashAggregate + Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c + Filter: (avg(pagg_tab_ml_p2_s1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s1 + -> HashAggregate + Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c + Filter: (avg(pagg_tab_ml_p2_s2.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s2 + -> HashAggregate + Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c + Filter: (avg(pagg_tab_ml_p3_s1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s1 + -> HashAggregate + Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c + Filter: (avg(pagg_tab_ml_p3_s2.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s2 +(23 rows) + +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; + a | sum | count +----+------+------- + 8 | 4000 | 500 + 8 | 4000 | 500 + 9 | 4500 | 500 + 9 | 4500 | 500 + 18 | 4000 | 500 + 18 | 4000 | 500 + 19 | 4500 | 500 + 19 | 4500 | 500 + 28 | 4000 | 500 + 28 | 4000 | 500 + 29 | 4500 | 500 + 29 | 4500 | 500 +(12 rows) + +-- Parallelism within partitionwise aggregates +SET min_parallel_table_scan_size TO '8kB'; +SET parallel_setup_cost TO 0; +-- Full aggregation at level 1 as GROUP BY clause matches with PARTITION KEY +-- for level 1 only. For subpartitions, GROUP BY clause does not match with +-- PARTITION KEY, thus we will have a partial aggregation for them. +EXPLAIN (COSTS OFF) +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; + QUERY PLAN +---------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) + -> Append + -> Finalize GroupAggregate + Group Key: pagg_tab_ml_p1.a + Filter: (avg(pagg_tab_ml_p1.b) < '3'::numeric) + -> Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_ml_p1.a + -> Partial HashAggregate + Group Key: pagg_tab_ml_p1.a + -> Parallel Seq Scan on pagg_tab_ml_p1 + -> Finalize GroupAggregate + Group Key: pagg_tab_ml_p2_s1.a + Filter: (avg(pagg_tab_ml_p2_s1.b) < '3'::numeric) + -> Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_ml_p2_s1.a + -> Parallel Append + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s1.a + -> Parallel Seq Scan on pagg_tab_ml_p2_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s2.a + -> Parallel Seq Scan on pagg_tab_ml_p2_s2 + -> Finalize GroupAggregate + Group Key: pagg_tab_ml_p3_s1.a + Filter: (avg(pagg_tab_ml_p3_s1.b) < '3'::numeric) + -> Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_ml_p3_s1.a + -> Parallel Append + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s1.a + -> Parallel Seq Scan on pagg_tab_ml_p3_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s2.a + -> Parallel Seq Scan on pagg_tab_ml_p3_s2 +(41 rows) + +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a HAVING avg(b) < 3 ORDER BY 1, 2, 3; + a | sum | count +----+------+------- + 0 | 0 | 1000 + 1 | 1000 | 1000 + 2 | 2000 | 1000 + 10 | 0 | 1000 + 11 | 1000 | 1000 + 12 | 2000 | 1000 + 20 | 0 | 1000 + 21 | 1000 | 1000 + 22 | 2000 | 1000 +(9 rows) + +-- Partial aggregation at all levels as GROUP BY clause does not match with +-- PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b ORDER BY 1, 2, 3; + QUERY PLAN +---------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_ml_p1.b, (sum(pagg_tab_ml_p1.a)), (count(*)) + -> Finalize GroupAggregate + Group Key: pagg_tab_ml_p1.b + -> Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_ml_p1.b + -> Parallel Append + -> Partial HashAggregate + Group Key: pagg_tab_ml_p1.b + -> Parallel Seq Scan on pagg_tab_ml_p1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s1.b + -> Parallel Seq Scan on pagg_tab_ml_p2_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p2_s2.b + -> Parallel Seq Scan on pagg_tab_ml_p2_s2 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s1.b + -> Parallel Seq Scan on pagg_tab_ml_p3_s1 + -> Partial HashAggregate + Group Key: pagg_tab_ml_p3_s2.b + -> Parallel Seq Scan on pagg_tab_ml_p3_s2 +(24 rows) + +SELECT b, sum(a), count(*) FROM pagg_tab_ml GROUP BY b HAVING avg(a) < 15 ORDER BY 1, 2, 3; + b | sum | count +---+-------+------- + 0 | 30000 | 3000 + 1 | 33000 | 3000 + 2 | 36000 | 3000 + 3 | 39000 | 3000 + 4 | 42000 | 3000 +(5 rows) + +-- Full aggregation at all levels as GROUP BY clause matches with PARTITION KEY +EXPLAIN (COSTS OFF) +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; + QUERY PLAN +---------------------------------------------------------------------------------------------- + Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_ml_p1.a, (sum(pagg_tab_ml_p1.b)), (count(*)) + -> Parallel Append + -> HashAggregate + Group Key: pagg_tab_ml_p1.a, pagg_tab_ml_p1.b, pagg_tab_ml_p1.c + Filter: (avg(pagg_tab_ml_p1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p1 + -> HashAggregate + Group Key: pagg_tab_ml_p2_s1.a, pagg_tab_ml_p2_s1.b, pagg_tab_ml_p2_s1.c + Filter: (avg(pagg_tab_ml_p2_s1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s1 + -> HashAggregate + Group Key: pagg_tab_ml_p2_s2.a, pagg_tab_ml_p2_s2.b, pagg_tab_ml_p2_s2.c + Filter: (avg(pagg_tab_ml_p2_s2.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p2_s2 + -> HashAggregate + Group Key: pagg_tab_ml_p3_s1.a, pagg_tab_ml_p3_s1.b, pagg_tab_ml_p3_s1.c + Filter: (avg(pagg_tab_ml_p3_s1.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s1 + -> HashAggregate + Group Key: pagg_tab_ml_p3_s2.a, pagg_tab_ml_p3_s2.b, pagg_tab_ml_p3_s2.c + Filter: (avg(pagg_tab_ml_p3_s2.b) > '7'::numeric) + -> Seq Scan on pagg_tab_ml_p3_s2 +(25 rows) + +SELECT a, sum(b), count(*) FROM pagg_tab_ml GROUP BY a, b, c HAVING avg(b) > 7 ORDER BY 1, 2, 3; + a | sum | count +----+------+------- + 8 | 4000 | 500 + 8 | 4000 | 500 + 9 | 4500 | 500 + 9 | 4500 | 500 + 18 | 4000 | 500 + 18 | 4000 | 500 + 19 | 4500 | 500 + 19 | 4500 | 500 + 28 | 4000 | 500 + 28 | 4000 | 500 + 29 | 4500 | 500 + 29 | 4500 | 500 +(12 rows) + +-- Parallelism within partitionwise aggregates (single level) +-- Add few parallel setup cost, so that we will see a plan which gathers +-- partially created paths even for full aggregation and sticks a single Gather +-- followed by finalization step. +-- Without this, the cost of doing partial aggregation + Gather + finalization +-- for each partition and then Append over it turns out to be same and this +-- wins as we add it first. This parallel_setup_cost plays a vital role in +-- costing such plans. +SET parallel_setup_cost TO 10; +CREATE TABLE pagg_tab_para(x int, y int) PARTITION BY RANGE(x); +CREATE TABLE pagg_tab_para_p1 PARTITION OF pagg_tab_para FOR VALUES FROM (0) TO (10); +CREATE TABLE pagg_tab_para_p2 PARTITION OF pagg_tab_para FOR VALUES FROM (10) TO (20); +CREATE TABLE pagg_tab_para_p3 PARTITION OF pagg_tab_para FOR VALUES FROM (20) TO (30); +INSERT INTO pagg_tab_para SELECT i % 30, i % 20 FROM generate_series(0, 29999) i; +ANALYZE pagg_tab_para; +-- When GROUP BY clause matches; full aggregation is performed for each partition. +EXPLAIN (COSTS OFF) +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) + -> Finalize GroupAggregate + Group Key: pagg_tab_para_p1.x + Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + -> Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_para_p1.x + -> Parallel Append + -> Partial HashAggregate + Group Key: pagg_tab_para_p1.x + -> Parallel Seq Scan on pagg_tab_para_p1 + -> Partial HashAggregate + Group Key: pagg_tab_para_p2.x + -> Parallel Seq Scan on pagg_tab_para_p2 + -> Partial HashAggregate + Group Key: pagg_tab_para_p3.x + -> Parallel Seq Scan on pagg_tab_para_p3 +(19 rows) + +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + x | sum | avg | count +----+------+--------------------+------- + 0 | 5000 | 5.0000000000000000 | 1000 + 1 | 6000 | 6.0000000000000000 | 1000 + 10 | 5000 | 5.0000000000000000 | 1000 + 11 | 6000 | 6.0000000000000000 | 1000 + 20 | 5000 | 5.0000000000000000 | 1000 + 21 | 6000 | 6.0000000000000000 | 1000 +(6 rows) + +-- When GROUP BY clause does not match; partial aggregation is performed for each partition. +EXPLAIN (COSTS OFF) +SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_para_p1.y, (sum(pagg_tab_para_p1.x)), (avg(pagg_tab_para_p1.x)) + -> Finalize HashAggregate + Group Key: pagg_tab_para_p1.y + Filter: (avg(pagg_tab_para_p1.x) < '12'::numeric) + -> Gather + Workers Planned: 2 + -> Parallel Append + -> Partial HashAggregate + Group Key: pagg_tab_para_p1.y + -> Parallel Seq Scan on pagg_tab_para_p1 + -> Partial HashAggregate + Group Key: pagg_tab_para_p2.y + -> Parallel Seq Scan on pagg_tab_para_p2 + -> Partial HashAggregate + Group Key: pagg_tab_para_p3.y + -> Parallel Seq Scan on pagg_tab_para_p3 +(17 rows) + +SELECT y, sum(x), avg(x), count(*) FROM pagg_tab_para GROUP BY y HAVING avg(x) < 12 ORDER BY 1, 2, 3; + y | sum | avg | count +----+-------+---------------------+------- + 0 | 15000 | 10.0000000000000000 | 1500 + 1 | 16500 | 11.0000000000000000 | 1500 + 10 | 15000 | 10.0000000000000000 | 1500 + 11 | 16500 | 11.0000000000000000 | 1500 +(4 rows) + +-- Test when parent can produce parallel paths but not any (or some) of its children +ALTER TABLE pagg_tab_para_p1 SET (parallel_workers = 0); +ALTER TABLE pagg_tab_para_p3 SET (parallel_workers = 0); +ANALYZE pagg_tab_para; +EXPLAIN (COSTS OFF) +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) + -> Finalize GroupAggregate + Group Key: pagg_tab_para_p1.x + Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + -> Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_para_p1.x + -> Partial HashAggregate + Group Key: pagg_tab_para_p1.x + -> Parallel Append + -> Seq Scan on pagg_tab_para_p1 + -> Seq Scan on pagg_tab_para_p3 + -> Parallel Seq Scan on pagg_tab_para_p2 +(15 rows) + +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + x | sum | avg | count +----+------+--------------------+------- + 0 | 5000 | 5.0000000000000000 | 1000 + 1 | 6000 | 6.0000000000000000 | 1000 + 10 | 5000 | 5.0000000000000000 | 1000 + 11 | 6000 | 6.0000000000000000 | 1000 + 20 | 5000 | 5.0000000000000000 | 1000 + 21 | 6000 | 6.0000000000000000 | 1000 +(6 rows) + +ALTER TABLE pagg_tab_para_p2 SET (parallel_workers = 0); +ANALYZE pagg_tab_para; +EXPLAIN (COSTS OFF) +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) + -> Finalize GroupAggregate + Group Key: pagg_tab_para_p1.x + Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + -> Gather Merge + Workers Planned: 2 + -> Sort + Sort Key: pagg_tab_para_p1.x + -> Partial HashAggregate + Group Key: pagg_tab_para_p1.x + -> Parallel Append + -> Seq Scan on pagg_tab_para_p1 + -> Seq Scan on pagg_tab_para_p2 + -> Seq Scan on pagg_tab_para_p3 +(15 rows) + +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + x | sum | avg | count +----+------+--------------------+------- + 0 | 5000 | 5.0000000000000000 | 1000 + 1 | 6000 | 6.0000000000000000 | 1000 + 10 | 5000 | 5.0000000000000000 | 1000 + 11 | 6000 | 6.0000000000000000 | 1000 + 20 | 5000 | 5.0000000000000000 | 1000 + 21 | 6000 | 6.0000000000000000 | 1000 +(6 rows) + +-- Reset parallelism parameters to get partitionwise aggregation plan. +RESET min_parallel_table_scan_size; +RESET parallel_setup_cost; +EXPLAIN (COSTS OFF) +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + QUERY PLAN +-------------------------------------------------------------------------------------- + Sort + Sort Key: pagg_tab_para_p1.x, (sum(pagg_tab_para_p1.y)), (avg(pagg_tab_para_p1.y)) + -> Append + -> HashAggregate + Group Key: pagg_tab_para_p1.x + Filter: (avg(pagg_tab_para_p1.y) < '7'::numeric) + -> Seq Scan on pagg_tab_para_p1 + -> HashAggregate + Group Key: pagg_tab_para_p2.x + Filter: (avg(pagg_tab_para_p2.y) < '7'::numeric) + -> Seq Scan on pagg_tab_para_p2 + -> HashAggregate + Group Key: pagg_tab_para_p3.x + Filter: (avg(pagg_tab_para_p3.y) < '7'::numeric) + -> Seq Scan on pagg_tab_para_p3 +(15 rows) + +SELECT x, sum(y), avg(y), count(*) FROM pagg_tab_para GROUP BY x HAVING avg(y) < 7 ORDER BY 1, 2, 3; + x | sum | avg | count +----+------+--------------------+------- + 0 | 5000 | 5.0000000000000000 | 1000 + 1 | 6000 | 6.0000000000000000 | 1000 + 10 | 5000 | 5.0000000000000000 | 1000 + 11 | 6000 | 6.0000000000000000 | 1000 + 20 | 5000 | 5.0000000000000000 | 1000 + 21 | 6000 | 6.0000000000000000 | 1000 +(6 rows) + diff --git a/src/test/regress/expected/partition_join_1.out b/src/test/regress/expected/partition_join_1.out new file mode 100644 index 0000000000..c390b976fb --- /dev/null +++ b/src/test/regress/expected/partition_join_1.out @@ -0,0 +1,2027 @@ +-- +-- PARTITION_JOIN +-- Test partitionwise join between partitioned tables +-- +-- Enable partitionwise join, which by default is disabled. +SET enable_partitionwise_join to true; +-- +-- partitioned by a single column +-- +CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a); +CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250); +CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600); +CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500); +INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 2 = 0; +CREATE INDEX iprt1_p1_a on prt1_p1(a); +CREATE INDEX iprt1_p2_a on prt1_p2(a); +CREATE INDEX iprt1_p3_a on prt1_p3(a); +ANALYZE prt1; +CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b); +CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250); +CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500); +CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600); +INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 3 = 0; +CREATE INDEX iprt2_p1_b on prt2_p1(b); +CREATE INDEX iprt2_p2_b on prt2_p2(b); +CREATE INDEX iprt2_p3_b on prt2_p3(b); +ANALYZE prt2; +-- inner join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Seq Scan on prt2_p1 t2 + -> Hash + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Hash Join + Hash Cond: (t2_1.b = t1_1.a) + -> Seq Scan on prt2_p2 t2_1 + -> Hash + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Hash Join + Hash Cond: (t2_2.b = t1_2.a) + -> Seq Scan on prt2_p3 t2_2 + -> Hash + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) +(21 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | 0 | 0000 + 150 | 0150 | 150 | 0150 + 300 | 0300 | 300 | 0300 + 450 | 0450 | 450 | 0450 +(4 rows) + +-- left outer join, with whole-row reference; partitionwise join does not apply +EXPLAIN (COSTS OFF) +SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: t1.a, t2.b + -> Hash Right Join + Hash Cond: (t2.b = t1.a) + -> Append + -> Seq Scan on prt2_p1 t2 + -> Seq Scan on prt2_p2 t2_1 + -> Seq Scan on prt2_p3 t2_2 + -> Hash + -> Append + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) +(16 rows) + +SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; + t1 | t2 +--------------+-------------- + (0,0,0000) | (0,0,0000) + (50,0,0050) | + (100,0,0100) | + (150,0,0150) | (0,150,0150) + (200,0,0200) | + (250,0,0250) | + (300,0,0300) | (0,300,0300) + (350,0,0350) | + (400,0,0400) | + (450,0,0450) | (0,450,0450) + (500,0,0500) | + (550,0,0550) | +(12 rows) + +-- right outer join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +--------------------------------------------------------------- + Sort + Sort Key: t1.a, t2.b + -> Append + -> Hash Right Join + Hash Cond: (t1.a = t2.b) + -> Seq Scan on prt1_p1 t1 + -> Hash + -> Seq Scan on prt2_p1 t2 + Filter: (a = 0) + -> Hash Right Join + Hash Cond: (t1_1.a = t2_1.b) + -> Seq Scan on prt1_p2 t1_1 + -> Hash + -> Seq Scan on prt2_p2 t2_1 + Filter: (a = 0) + -> Nested Loop Left Join + -> Seq Scan on prt2_p3 t2_2 + Filter: (a = 0) + -> Index Scan using iprt1_p3_a on prt1_p3 t1_2 + Index Cond: (a = t2_2.b) +(20 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | 0 | 0000 + 150 | 0150 | 150 | 0150 + 300 | 0300 | 300 | 0300 + 450 | 0450 | 450 | 0450 + | | 75 | 0075 + | | 225 | 0225 + | | 375 | 0375 + | | 525 | 0525 +(8 rows) + +-- full outer join, with placeholder vars +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------------ + Sort + Sort Key: prt1_p1.a, prt2_p1.b + -> Append + -> Hash Full Join + Hash Cond: (prt1_p1.a = prt2_p1.b) + Filter: (((50) = prt1_p1.a) OR ((75) = prt2_p1.b)) + -> Seq Scan on prt1_p1 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p1 + Filter: (a = 0) + -> Hash Full Join + Hash Cond: (prt1_p2.a = prt2_p2.b) + Filter: (((50) = prt1_p2.a) OR ((75) = prt2_p2.b)) + -> Seq Scan on prt1_p2 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p2 + Filter: (a = 0) + -> Hash Full Join + Hash Cond: (prt1_p3.a = prt2_p3.b) + Filter: (((50) = prt1_p3.a) OR ((75) = prt2_p3.b)) + -> Seq Scan on prt1_p3 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p3 + Filter: (a = 0) +(27 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b; + a | c | b | c +----+------+----+------ + 50 | 0050 | | + | | 75 | 0075 +(2 rows) + +-- Join with pruned partitions from joining relations +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +----------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Seq Scan on prt2_p2 t2 + Filter: (b > 250) + -> Hash + -> Seq Scan on prt1_p2 t1 + Filter: ((a < 450) AND (b = 0)) +(10 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 300 | 0300 | 300 | 0300 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +----------------------------------------------------------- + Sort + Sort Key: prt1_p1.a, b + -> Append + -> Hash Left Join + Hash Cond: (prt1_p1.a = b) + -> Seq Scan on prt1_p1 + Filter: ((a < 450) AND (b = 0)) + -> Hash + -> Result + One-Time Filter: false + -> Hash Right Join + Hash Cond: (prt2_p2.b = prt1_p2.a) + -> Seq Scan on prt2_p2 + Filter: (b > 250) + -> Hash + -> Seq Scan on prt1_p2 + Filter: ((a < 450) AND (b = 0)) +(17 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | | + 50 | 0050 | | + 100 | 0100 | | + 150 | 0150 | | + 200 | 0200 | | + 250 | 0250 | | + 300 | 0300 | 300 | 0300 + 350 | 0350 | | + 400 | 0400 | | +(9 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------ + Sort + Sort Key: prt1_p1.a, b + -> Append + -> Hash Full Join + Hash Cond: (prt1_p1.a = b) + Filter: ((prt1_p1.b = 0) OR (a = 0)) + -> Seq Scan on prt1_p1 + Filter: (a < 450) + -> Hash + -> Result + One-Time Filter: false + -> Hash Full Join + Hash Cond: (prt1_p2.a = prt2_p2.b) + Filter: ((prt1_p2.b = 0) OR (prt2_p2.a = 0)) + -> Seq Scan on prt1_p2 + Filter: (a < 450) + -> Hash + -> Seq Scan on prt2_p2 + Filter: (b > 250) + -> Hash Full Join + Hash Cond: (prt2_p3.b = a) + Filter: ((b = 0) OR (prt2_p3.a = 0)) + -> Seq Scan on prt2_p3 + Filter: (b > 250) + -> Hash + -> Result + One-Time Filter: false +(27 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | | + 50 | 0050 | | + 100 | 0100 | | + 150 | 0150 | | + 200 | 0200 | | + 250 | 0250 | | + 300 | 0300 | 300 | 0300 + 350 | 0350 | | + 400 | 0400 | | + | | 375 | 0375 + | | 450 | 0450 + | | 525 | 0525 +(12 rows) + +-- Semi-join +EXPLAIN (COSTS OFF) +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) AND t1.b = 0 ORDER BY t1.a; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Hash Semi Join + Hash Cond: (t1.a = t2.b) + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p1 t2 + Filter: (a = 0) + -> Hash Semi Join + Hash Cond: (t1_1.a = t2_1.b) + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p2 t2_1 + Filter: (a = 0) + -> Nested Loop Semi Join + Join Filter: (t1_2.a = t2_2.b) + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) + -> Materialize + -> Seq Scan on prt2_p3 t2_2 + Filter: (a = 0) +(24 rows) + +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) AND t1.b = 0 ORDER BY t1.a; + a | b | c +-----+---+------ + 0 | 0 | 0000 + 150 | 0 | 0150 + 300 | 0 | 0300 + 450 | 0 | 0450 +(4 rows) + +-- Anti-join with aggregates +EXPLAIN (COSTS OFF) +SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS (SELECT 1 FROM prt2 t2 WHERE t1.a = t2.b); + QUERY PLAN +-------------------------------------------------- + Aggregate + -> Append + -> Hash Anti Join + Hash Cond: (t1.a = t2.b) + -> Seq Scan on prt1_p1 t1 + -> Hash + -> Seq Scan on prt2_p1 t2 + -> Hash Anti Join + Hash Cond: (t1_1.a = t2_1.b) + -> Seq Scan on prt1_p2 t1_1 + -> Hash + -> Seq Scan on prt2_p2 t2_1 + -> Hash Anti Join + Hash Cond: (t1_2.a = t2_2.b) + -> Seq Scan on prt1_p3 t1_2 + -> Hash + -> Seq Scan on prt2_p3 t2_2 +(17 rows) + +SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS (SELECT 1 FROM prt2 t2 WHERE t1.a = t2.b); + sum | avg | sum | avg +-------+----------------------+------+--------------------- + 60000 | 300.0000000000000000 | 2400 | 12.0000000000000000 +(1 row) + +-- lateral reference +EXPLAIN (COSTS OFF) +SELECT * FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss + ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a; + QUERY PLAN +-------------------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Nested Loop Left Join + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Nested Loop + -> Index Only Scan using iprt1_p1_a on prt1_p1 t2 + Index Cond: (a = t1.a) + -> Index Scan using iprt2_p1_b on prt2_p1 t3 + Index Cond: (b = t2.a) + -> Nested Loop Left Join + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Nested Loop + -> Index Only Scan using iprt1_p2_a on prt1_p2 t2_1 + Index Cond: (a = t1_1.a) + -> Index Scan using iprt2_p2_b on prt2_p2 t3_1 + Index Cond: (b = t2_1.a) + -> Nested Loop Left Join + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) + -> Nested Loop + -> Index Only Scan using iprt1_p3_a on prt1_p3 t2_2 + Index Cond: (a = t1_2.a) + -> Index Scan using iprt2_p3_b on prt2_p3 t3_2 + Index Cond: (b = t2_2.a) +(27 rows) + +SELECT * FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss + ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a; + a | b | c | t2a | t3a | least +-----+---+------+-----+-----+------- + 0 | 0 | 0000 | 0 | 0 | 0 + 50 | 0 | 0050 | | | + 100 | 0 | 0100 | | | + 150 | 0 | 0150 | 150 | 0 | 150 + 200 | 0 | 0200 | | | + 250 | 0 | 0250 | | | + 300 | 0 | 0300 | 300 | 0 | 300 + 350 | 0 | 0350 | | | + 400 | 0 | 0400 | | | + 450 | 0 | 0450 | 450 | 0 | 450 + 500 | 0 | 0500 | | | + 550 | 0 | 0550 | | | +(12 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t2.a AS t2a, t3.a AS t3a, t2.b t2b, t2.c t2c, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss + ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a; + QUERY PLAN +-------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Hash Left Join + Hash Cond: ((t1.c)::text = (t2.c)::text) + Filter: ((t1.b + COALESCE(t2.b, 0)) = 0) + -> Append + -> Seq Scan on prt1_p1 t1 + -> Seq Scan on prt1_p2 t1_1 + -> Seq Scan on prt1_p3 t1_2 + -> Hash + -> Append + -> Hash Join + Hash Cond: (t2.a = t3.b) + -> Seq Scan on prt1_p1 t2 + -> Hash + -> Seq Scan on prt2_p1 t3 + -> Hash Join + Hash Cond: (t2_1.a = t3_1.b) + -> Seq Scan on prt1_p2 t2_1 + -> Hash + -> Seq Scan on prt2_p2 t3_1 + -> Hash Join + Hash Cond: (t2_2.a = t3_2.b) + -> Seq Scan on prt1_p3 t2_2 + -> Hash + -> Seq Scan on prt2_p3 t3_2 +(26 rows) + +SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL + (SELECT t2.a AS t2a, t3.a AS t3a, t2.b t2b, t2.c t2c, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss + ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a; + a | t2a | t2c +-----+-----+------ + 0 | 0 | 0000 + 50 | | + 100 | | + 150 | 150 | 0150 + 200 | | + 250 | | + 300 | 300 | 0300 + 350 | | + 400 | | + 450 | 450 | 0450 + 500 | | + 550 | | +(12 rows) + +-- +-- partitioned by expression +-- +CREATE TABLE prt1_e (a int, b int, c int) PARTITION BY RANGE(((a + b)/2)); +CREATE TABLE prt1_e_p1 PARTITION OF prt1_e FOR VALUES FROM (0) TO (250); +CREATE TABLE prt1_e_p2 PARTITION OF prt1_e FOR VALUES FROM (250) TO (500); +CREATE TABLE prt1_e_p3 PARTITION OF prt1_e FOR VALUES FROM (500) TO (600); +INSERT INTO prt1_e SELECT i, i, i % 25 FROM generate_series(0, 599, 2) i; +CREATE INDEX iprt1_e_p1_ab2 on prt1_e_p1(((a+b)/2)); +CREATE INDEX iprt1_e_p2_ab2 on prt1_e_p2(((a+b)/2)); +CREATE INDEX iprt1_e_p3_ab2 on prt1_e_p3(((a+b)/2)); +ANALYZE prt1_e; +CREATE TABLE prt2_e (a int, b int, c int) PARTITION BY RANGE(((b + a)/2)); +CREATE TABLE prt2_e_p1 PARTITION OF prt2_e FOR VALUES FROM (0) TO (250); +CREATE TABLE prt2_e_p2 PARTITION OF prt2_e FOR VALUES FROM (250) TO (500); +CREATE TABLE prt2_e_p3 PARTITION OF prt2_e FOR VALUES FROM (500) TO (600); +INSERT INTO prt2_e SELECT i, i, i % 25 FROM generate_series(0, 599, 3) i; +ANALYZE prt2_e; +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 = (t2.b + t2.a)/2 AND t1.c = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------------------------ + Sort + Sort Key: t1.a, t2.b + -> Append + -> Hash Join + Hash Cond: (((t2.b + t2.a) / 2) = ((t1.a + t1.b) / 2)) + -> Seq Scan on prt2_e_p1 t2 + -> Hash + -> Seq Scan on prt1_e_p1 t1 + Filter: (c = 0) + -> Hash Join + Hash Cond: (((t2_1.b + t2_1.a) / 2) = ((t1_1.a + t1_1.b) / 2)) + -> Seq Scan on prt2_e_p2 t2_1 + -> Hash + -> Seq Scan on prt1_e_p2 t1_1 + Filter: (c = 0) + -> Hash Join + Hash Cond: (((t2_2.b + t2_2.a) / 2) = ((t1_2.a + t1_2.b) / 2)) + -> Seq Scan on prt2_e_p3 t2_2 + -> Hash + -> Seq Scan on prt1_e_p3 t1_2 + Filter: (c = 0) +(21 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 = (t2.b + t2.a)/2 AND t1.c = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+---+-----+--- + 0 | 0 | 0 | 0 + 150 | 0 | 150 | 0 + 300 | 0 | 300 | 0 + 450 | 0 | 450 | 0 +(4 rows) + +-- +-- N-way join +-- +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +--------------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Nested Loop + Join Filter: (t1.a = ((t3.a + t3.b) / 2)) + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Seq Scan on prt2_p1 t2 + -> Hash + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3 + Index Cond: (((a + b) / 2) = t2.b) + -> Nested Loop + Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2)) + -> Hash Join + Hash Cond: (t2_1.b = t1_1.a) + -> Seq Scan on prt2_p2 t2_1 + -> Hash + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_1 + Index Cond: (((a + b) / 2) = t2_1.b) + -> Nested Loop + Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2)) + -> Hash Join + Hash Cond: (t2_2.b = t1_2.a) + -> Seq Scan on prt2_p3 t2_2 + -> Hash + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) + -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_2 + Index Cond: (((a + b) / 2) = t2_2.b) +(33 rows) + +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b; + a | c | b | c | ?column? | c +-----+------+-----+------+----------+--- + 0 | 0000 | 0 | 0000 | 0 | 0 + 150 | 0150 | 150 | 0150 | 300 | 0 + 300 | 0300 | 300 | 0300 | 600 | 0 + 450 | 0450 | 450 | 0450 | 900 | 0 +(4 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; + QUERY PLAN +-------------------------------------------------------------- + Sort + Sort Key: t1.a, t2.b, ((t3.a + t3.b)) + -> Append + -> Hash Right Join + Hash Cond: (((t3.a + t3.b) / 2) = t1.a) + -> Seq Scan on prt1_e_p1 t3 + -> Hash + -> Hash Right Join + Hash Cond: (t2.b = t1.a) + -> Seq Scan on prt2_p1 t2 + -> Hash + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Hash Right Join + Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a) + -> Seq Scan on prt1_e_p2 t3_1 + -> Hash + -> Hash Right Join + Hash Cond: (t2_1.b = t1_1.a) + -> Seq Scan on prt2_p2 t2_1 + -> Hash + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Hash Right Join + Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a) + -> Seq Scan on prt1_e_p3 t3_2 + -> Hash + -> Hash Right Join + Hash Cond: (t2_2.b = t1_2.a) + -> Seq Scan on prt2_p3 t2_2 + -> Hash + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) +(33 rows) + +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; + a | c | b | c | ?column? | c +-----+------+-----+------+----------+--- + 0 | 0000 | 0 | 0000 | 0 | 0 + 50 | 0050 | | | 100 | 0 + 100 | 0100 | | | 200 | 0 + 150 | 0150 | 150 | 0150 | 300 | 0 + 200 | 0200 | | | 400 | 0 + 250 | 0250 | | | 500 | 0 + 300 | 0300 | 300 | 0300 | 600 | 0 + 350 | 0350 | | | 700 | 0 + 400 | 0400 | | | 800 | 0 + 450 | 0450 | 450 | 0450 | 900 | 0 + 500 | 0500 | | | 1000 | 0 + 550 | 0550 | | | 1100 | 0 +(12 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; + QUERY PLAN +------------------------------------------------------------------- + Sort + Sort Key: t1.a, t2.b, ((t3.a + t3.b)) + -> Append + -> Nested Loop Left Join + -> Hash Right Join + Hash Cond: (t1.a = ((t3.a + t3.b) / 2)) + -> Seq Scan on prt1_p1 t1 + -> Hash + -> Seq Scan on prt1_e_p1 t3 + Filter: (c = 0) + -> Index Scan using iprt2_p1_b on prt2_p1 t2 + Index Cond: (t1.a = b) + -> Nested Loop Left Join + -> Hash Right Join + Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2)) + -> Seq Scan on prt1_p2 t1_1 + -> Hash + -> Seq Scan on prt1_e_p2 t3_1 + Filter: (c = 0) + -> Index Scan using iprt2_p2_b on prt2_p2 t2_1 + Index Cond: (t1_1.a = b) + -> Nested Loop Left Join + -> Hash Right Join + Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2)) + -> Seq Scan on prt1_p3 t1_2 + -> Hash + -> Seq Scan on prt1_e_p3 t3_2 + Filter: (c = 0) + -> Index Scan using iprt2_p3_b on prt2_p3 t2_2 + Index Cond: (t1_2.a = b) +(30 rows) + +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; + a | c | b | c | ?column? | c +-----+------+-----+------+----------+--- + 0 | 0000 | 0 | 0000 | 0 | 0 + 50 | 0050 | | | 100 | 0 + 100 | 0100 | | | 200 | 0 + 150 | 0150 | 150 | 0150 | 300 | 0 + 200 | 0200 | | | 400 | 0 + 250 | 0250 | | | 500 | 0 + 300 | 0300 | 300 | 0300 | 600 | 0 + 350 | 0350 | | | 700 | 0 + 400 | 0400 | | | 800 | 0 + 450 | 0450 | 450 | 0450 | 900 | 0 + 500 | 0500 | | | 1000 | 0 + 550 | 0550 | | | 1100 | 0 +(12 rows) + +-- Cases with non-nullable expressions in subquery results; +-- make sure these go to null as expected +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------- + Sort + Sort Key: prt1_p1.a, prt2_p1.b, ((prt1_e_p1.a + prt1_e_p1.b)) + -> Append + -> Hash Full Join + Hash Cond: (prt1_p1.a = ((prt1_e_p1.a + prt1_e_p1.b) / 2)) + Filter: ((prt1_p1.a = (50)) OR (prt2_p1.b = (75)) OR (((prt1_e_p1.a + prt1_e_p1.b) / 2) = (50))) + -> Hash Full Join + Hash Cond: (prt1_p1.a = prt2_p1.b) + -> Seq Scan on prt1_p1 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p1 + Filter: (a = 0) + -> Hash + -> Seq Scan on prt1_e_p1 + Filter: (c = 0) + -> Hash Full Join + Hash Cond: (prt1_p2.a = ((prt1_e_p2.a + prt1_e_p2.b) / 2)) + Filter: ((prt1_p2.a = (50)) OR (prt2_p2.b = (75)) OR (((prt1_e_p2.a + prt1_e_p2.b) / 2) = (50))) + -> Hash Full Join + Hash Cond: (prt1_p2.a = prt2_p2.b) + -> Seq Scan on prt1_p2 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p2 + Filter: (a = 0) + -> Hash + -> Seq Scan on prt1_e_p2 + Filter: (c = 0) + -> Hash Full Join + Hash Cond: (prt1_p3.a = ((prt1_e_p3.a + prt1_e_p3.b) / 2)) + Filter: ((prt1_p3.a = (50)) OR (prt2_p3.b = (75)) OR (((prt1_e_p3.a + prt1_e_p3.b) / 2) = (50))) + -> Hash Full Join + Hash Cond: (prt1_p3.a = prt2_p3.b) + -> Seq Scan on prt1_p3 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_p3 + Filter: (a = 0) + -> Hash + -> Seq Scan on prt1_e_p3 + Filter: (c = 0) +(42 rows) + +SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b; + a | phv | b | phv | ?column? | phv +----+-----+----+-----+----------+----- + 50 | 50 | | | 100 | 50 + | | 75 | 75 | | +(2 rows) + +-- Semi-join +EXPLAIN (COSTS OFF) +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a; + QUERY PLAN +--------------------------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Nested Loop + Join Filter: (t1.a = t1_3.b) + -> HashAggregate + Group Key: t1_3.b + -> Hash Join + Hash Cond: (((t2.a + t2.b) / 2) = t1_3.b) + -> Seq Scan on prt1_e_p1 t2 + -> Hash + -> Seq Scan on prt2_p1 t1_3 + Filter: (a = 0) + -> Index Scan using iprt1_p1_a on prt1_p1 t1 + Index Cond: (a = ((t2.a + t2.b) / 2)) + Filter: (b = 0) + -> Nested Loop + Join Filter: (t1_1.a = t1_4.b) + -> HashAggregate + Group Key: t1_4.b + -> Hash Join + Hash Cond: (((t2_1.a + t2_1.b) / 2) = t1_4.b) + -> Seq Scan on prt1_e_p2 t2_1 + -> Hash + -> Seq Scan on prt2_p2 t1_4 + Filter: (a = 0) + -> Index Scan using iprt1_p2_a on prt1_p2 t1_1 + Index Cond: (a = ((t2_1.a + t2_1.b) / 2)) + Filter: (b = 0) + -> Nested Loop + Join Filter: (t1_2.a = t1_5.b) + -> HashAggregate + Group Key: t1_5.b + -> Nested Loop + -> Seq Scan on prt2_p3 t1_5 + Filter: (a = 0) + -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t2_2 + Index Cond: (((a + b) / 2) = t1_5.b) + -> Index Scan using iprt1_p3_a on prt1_p3 t1_2 + Index Cond: (a = ((t2_2.a + t2_2.b) / 2)) + Filter: (b = 0) +(41 rows) + +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a; + a | b | c +-----+---+------ + 0 | 0 | 0000 + 150 | 0 | 0150 + 300 | 0 | 0300 + 450 | 0 | 0450 +(4 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a; + QUERY PLAN +------------------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Nested Loop + -> HashAggregate + Group Key: t1_3.b + -> Hash Semi Join + Hash Cond: (t1_3.b = ((t1_6.a + t1_6.b) / 2)) + -> Seq Scan on prt2_p1 t1_3 + -> Hash + -> Seq Scan on prt1_e_p1 t1_6 + Filter: (c = 0) + -> Index Scan using iprt1_p1_a on prt1_p1 t1 + Index Cond: (a = t1_3.b) + Filter: (b = 0) + -> Nested Loop + -> HashAggregate + Group Key: t1_4.b + -> Hash Semi Join + Hash Cond: (t1_4.b = ((t1_7.a + t1_7.b) / 2)) + -> Seq Scan on prt2_p2 t1_4 + -> Hash + -> Seq Scan on prt1_e_p2 t1_7 + Filter: (c = 0) + -> Index Scan using iprt1_p2_a on prt1_p2 t1_1 + Index Cond: (a = t1_4.b) + Filter: (b = 0) + -> Nested Loop + -> HashAggregate + Group Key: t1_5.b + -> Hash Semi Join + Hash Cond: (t1_5.b = ((t1_8.a + t1_8.b) / 2)) + -> Seq Scan on prt2_p3 t1_5 + -> Hash + -> Seq Scan on prt1_e_p3 t1_8 + Filter: (c = 0) + -> Index Scan using iprt1_p3_a on prt1_p3 t1_2 + Index Cond: (a = t1_5.b) + Filter: (b = 0) +(39 rows) + +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a; + a | b | c +-----+---+------ + 0 | 0 | 0000 + 150 | 0 | 0150 + 300 | 0 | 0300 + 450 | 0 | 0450 +(4 rows) + +-- test merge joins +SET enable_hashjoin TO off; +SET enable_nestloop TO off; +EXPLAIN (COSTS OFF) +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a; + QUERY PLAN +---------------------------------------------------------------- + Merge Append + Sort Key: t1.a + -> Merge Semi Join + Merge Cond: (t1.a = t1_3.b) + -> Sort + Sort Key: t1.a + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Merge Semi Join + Merge Cond: (t1_3.b = (((t1_6.a + t1_6.b) / 2))) + -> Sort + Sort Key: t1_3.b + -> Seq Scan on prt2_p1 t1_3 + -> Sort + Sort Key: (((t1_6.a + t1_6.b) / 2)) + -> Seq Scan on prt1_e_p1 t1_6 + Filter: (c = 0) + -> Merge Semi Join + Merge Cond: (t1_1.a = t1_4.b) + -> Sort + Sort Key: t1_1.a + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Merge Semi Join + Merge Cond: (t1_4.b = (((t1_7.a + t1_7.b) / 2))) + -> Sort + Sort Key: t1_4.b + -> Seq Scan on prt2_p2 t1_4 + -> Sort + Sort Key: (((t1_7.a + t1_7.b) / 2)) + -> Seq Scan on prt1_e_p2 t1_7 + Filter: (c = 0) + -> Merge Semi Join + Merge Cond: (t1_2.a = t1_5.b) + -> Sort + Sort Key: t1_2.a + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) + -> Merge Semi Join + Merge Cond: (t1_5.b = (((t1_8.a + t1_8.b) / 2))) + -> Sort + Sort Key: t1_5.b + -> Seq Scan on prt2_p3 t1_5 + -> Sort + Sort Key: (((t1_8.a + t1_8.b) / 2)) + -> Seq Scan on prt1_e_p3 t1_8 + Filter: (c = 0) +(47 rows) + +SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a; + a | b | c +-----+---+------ + 0 | 0 | 0000 + 150 | 0 | 0150 + 300 | 0 | 0300 + 450 | 0 | 0450 +(4 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; + QUERY PLAN +---------------------------------------------------------------------------- + Sort + Sort Key: t1.a, t2.b, ((t3.a + t3.b)) + -> Append + -> Merge Left Join + Merge Cond: (t1.a = t2.b) + -> Sort + Sort Key: t1.a + -> Merge Left Join + Merge Cond: ((((t3.a + t3.b) / 2)) = t1.a) + -> Sort + Sort Key: (((t3.a + t3.b) / 2)) + -> Seq Scan on prt1_e_p1 t3 + Filter: (c = 0) + -> Sort + Sort Key: t1.a + -> Seq Scan on prt1_p1 t1 + -> Sort + Sort Key: t2.b + -> Seq Scan on prt2_p1 t2 + -> Merge Left Join + Merge Cond: (t1_1.a = t2_1.b) + -> Sort + Sort Key: t1_1.a + -> Merge Left Join + Merge Cond: ((((t3_1.a + t3_1.b) / 2)) = t1_1.a) + -> Sort + Sort Key: (((t3_1.a + t3_1.b) / 2)) + -> Seq Scan on prt1_e_p2 t3_1 + Filter: (c = 0) + -> Sort + Sort Key: t1_1.a + -> Seq Scan on prt1_p2 t1_1 + -> Sort + Sort Key: t2_1.b + -> Seq Scan on prt2_p2 t2_1 + -> Merge Left Join + Merge Cond: (t1_2.a = t2_2.b) + -> Sort + Sort Key: t1_2.a + -> Merge Left Join + Merge Cond: ((((t3_2.a + t3_2.b) / 2)) = t1_2.a) + -> Sort + Sort Key: (((t3_2.a + t3_2.b) / 2)) + -> Seq Scan on prt1_e_p3 t3_2 + Filter: (c = 0) + -> Sort + Sort Key: t1_2.a + -> Seq Scan on prt1_p3 t1_2 + -> Sort + Sort Key: t2_2.b + -> Seq Scan on prt2_p3 t2_2 +(51 rows) + +SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b; + a | c | b | c | ?column? | c +-----+------+-----+------+----------+--- + 0 | 0000 | 0 | 0000 | 0 | 0 + 50 | 0050 | | | 100 | 0 + 100 | 0100 | | | 200 | 0 + 150 | 0150 | 150 | 0150 | 300 | 0 + 200 | 0200 | | | 400 | 0 + 250 | 0250 | | | 500 | 0 + 300 | 0300 | 300 | 0300 | 600 | 0 + 350 | 0350 | | | 700 | 0 + 400 | 0400 | | | 800 | 0 + 450 | 0450 | 450 | 0450 | 900 | 0 + 500 | 0500 | | | 1000 | 0 + 550 | 0550 | | | 1100 | 0 +(12 rows) + +-- MergeAppend on nullable column +EXPLAIN (COSTS OFF) +SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +----------------------------------------------------------- + Sort + Sort Key: prt1_p1.a, b + -> Append + -> Merge Left Join + Merge Cond: (prt1_p1.a = b) + -> Sort + Sort Key: prt1_p1.a + -> Seq Scan on prt1_p1 + Filter: ((a < 450) AND (b = 0)) + -> Sort + Sort Key: b + -> Result + One-Time Filter: false + -> Merge Left Join + Merge Cond: (prt1_p2.a = prt2_p2.b) + -> Sort + Sort Key: prt1_p2.a + -> Seq Scan on prt1_p2 + Filter: ((a < 450) AND (b = 0)) + -> Sort + Sort Key: prt2_p2.b + -> Seq Scan on prt2_p2 + Filter: (b > 250) +(23 rows) + +SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b; + a | b +-----+----- + 0 | + 50 | + 100 | + 150 | + 200 | + 250 | + 300 | 300 + 350 | + 400 | +(9 rows) + +-- merge join when expression with whole-row reference needs to be sorted; +-- partitionwise join does not apply +EXPLAIN (COSTS OFF) +SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a; + QUERY PLAN +----------------------------------------------------------------------------------------- + Merge Join + Merge Cond: ((t1.a = t2.b) AND (((((t1.*)::prt1))::text) = ((((t2.*)::prt2))::text))) + -> Sort + Sort Key: t1.a, ((((t1.*)::prt1))::text) + -> Result + -> Append + -> Seq Scan on prt1_p1 t1 + -> Seq Scan on prt1_p2 t1_1 + -> Seq Scan on prt1_p3 t1_2 + -> Sort + Sort Key: t2.b, ((((t2.*)::prt2))::text) + -> Result + -> Append + -> Seq Scan on prt2_p1 t2 + -> Seq Scan on prt2_p2 t2_1 + -> Seq Scan on prt2_p3 t2_2 +(16 rows) + +SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a; + a | b +----+---- + 0 | 0 + 6 | 6 + 12 | 12 + 18 | 18 + 24 | 24 +(5 rows) + +RESET enable_hashjoin; +RESET enable_nestloop; +-- +-- partitioned by multiple columns +-- +CREATE TABLE prt1_m (a int, b int, c int) PARTITION BY RANGE(a, ((a + b)/2)); +CREATE TABLE prt1_m_p1 PARTITION OF prt1_m FOR VALUES FROM (0, 0) TO (250, 250); +CREATE TABLE prt1_m_p2 PARTITION OF prt1_m FOR VALUES FROM (250, 250) TO (500, 500); +CREATE TABLE prt1_m_p3 PARTITION OF prt1_m FOR VALUES FROM (500, 500) TO (600, 600); +INSERT INTO prt1_m SELECT i, i, i % 25 FROM generate_series(0, 599, 2) i; +ANALYZE prt1_m; +CREATE TABLE prt2_m (a int, b int, c int) PARTITION BY RANGE(((b + a)/2), b); +CREATE TABLE prt2_m_p1 PARTITION OF prt2_m FOR VALUES FROM (0, 0) TO (250, 250); +CREATE TABLE prt2_m_p2 PARTITION OF prt2_m FOR VALUES FROM (250, 250) TO (500, 500); +CREATE TABLE prt2_m_p3 PARTITION OF prt2_m FOR VALUES FROM (500, 500) TO (600, 600); +INSERT INTO prt2_m SELECT i, i, i % 25 FROM generate_series(0, 599, 3) i; +ANALYZE prt2_m; +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_m WHERE prt1_m.c = 0) t1 FULL JOIN (SELECT * FROM prt2_m WHERE prt2_m.c = 0) t2 ON (t1.a = (t2.b + t2.a)/2 AND t2.b = (t1.a + t1.b)/2) ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------ + Sort + Sort Key: prt1_m_p1.a, prt2_m_p1.b + -> Append + -> Hash Full Join + Hash Cond: ((prt1_m_p1.a = ((prt2_m_p1.b + prt2_m_p1.a) / 2)) AND (((prt1_m_p1.a + prt1_m_p1.b) / 2) = prt2_m_p1.b)) + -> Seq Scan on prt1_m_p1 + Filter: (c = 0) + -> Hash + -> Seq Scan on prt2_m_p1 + Filter: (c = 0) + -> Hash Full Join + Hash Cond: ((prt1_m_p2.a = ((prt2_m_p2.b + prt2_m_p2.a) / 2)) AND (((prt1_m_p2.a + prt1_m_p2.b) / 2) = prt2_m_p2.b)) + -> Seq Scan on prt1_m_p2 + Filter: (c = 0) + -> Hash + -> Seq Scan on prt2_m_p2 + Filter: (c = 0) + -> Hash Full Join + Hash Cond: ((prt1_m_p3.a = ((prt2_m_p3.b + prt2_m_p3.a) / 2)) AND (((prt1_m_p3.a + prt1_m_p3.b) / 2) = prt2_m_p3.b)) + -> Seq Scan on prt1_m_p3 + Filter: (c = 0) + -> Hash + -> Seq Scan on prt2_m_p3 + Filter: (c = 0) +(24 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_m WHERE prt1_m.c = 0) t1 FULL JOIN (SELECT * FROM prt2_m WHERE prt2_m.c = 0) t2 ON (t1.a = (t2.b + t2.a)/2 AND t2.b = (t1.a + t1.b)/2) ORDER BY t1.a, t2.b; + a | c | b | c +-----+---+-----+--- + 0 | 0 | 0 | 0 + 50 | 0 | | + 100 | 0 | | + 150 | 0 | 150 | 0 + 200 | 0 | | + 250 | 0 | | + 300 | 0 | 300 | 0 + 350 | 0 | | + 400 | 0 | | + 450 | 0 | 450 | 0 + 500 | 0 | | + 550 | 0 | | + | | 75 | 0 + | | 225 | 0 + | | 375 | 0 + | | 525 | 0 +(16 rows) + +-- +-- tests for list partitioned tables. +-- +CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c); +CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0003', '0004', '0010'); +CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0001', '0005', '0002', '0009'); +CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007', '0008', '0011'); +INSERT INTO plt1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE plt1; +CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c); +CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0000', '0003', '0004', '0010'); +CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0001', '0005', '0002', '0009'); +CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0011'); +INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i; +ANALYZE plt2; +-- +-- list partitioned by expression +-- +CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY LIST(ltrim(c, 'A')); +CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0000', '0003', '0004', '0010'); +CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0001', '0005', '0002', '0009'); +CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0006', '0007', '0008', '0011'); +INSERT INTO plt1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE plt1_e; +-- test partition matching with N-way join +EXPLAIN (COSTS OFF) +SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; + QUERY PLAN +-------------------------------------------------------------------------------- + GroupAggregate + Group Key: t1.c, t2.c, t3.c + -> Sort + Sort Key: t1.c, t3.c + -> Append + -> Hash Join + Hash Cond: (t1.c = ltrim(t3.c, 'A'::text)) + -> Hash Join + Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c)) + -> Seq Scan on plt1_p1 t1 + -> Hash + -> Seq Scan on plt2_p1 t2 + -> Hash + -> Seq Scan on plt1_e_p1 t3 + -> Hash Join + Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text)) + -> Hash Join + Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c)) + -> Seq Scan on plt1_p2 t1_1 + -> Hash + -> Seq Scan on plt2_p2 t2_1 + -> Hash + -> Seq Scan on plt1_e_p2 t3_1 + -> Hash Join + Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text)) + -> Hash Join + Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c)) + -> Seq Scan on plt1_p3 t1_2 + -> Hash + -> Seq Scan on plt2_p3 t2_2 + -> Hash + -> Seq Scan on plt1_e_p3 t3_2 +(32 rows) + +SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; + avg | avg | avg | c | c | c +----------------------+----------------------+-----------------------+------+------+------- + 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000 + 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001 + 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002 + 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003 + 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004 + 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005 + 324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006 + 375.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007 + 423.0000000000000000 | 423.0000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008 + 474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009 + 525.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010 + 573.0000000000000000 | 573.0000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011 +(12 rows) + +-- joins where one of the relations is proven empty +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a = 1 AND t1.a = 2; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 LEFT JOIN prt2 t2 ON t1.a = t2.b; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b = t3.a; + QUERY PLAN +-------------------------------------------------- + Hash Left Join + Hash Cond: (t2.b = a) + -> Append + -> Hash Join + Hash Cond: (t3.a = t2.b) + -> Seq Scan on prt1_p1 t3 + -> Hash + -> Seq Scan on prt2_p1 t2 + -> Hash Join + Hash Cond: (t3_1.a = t2_1.b) + -> Seq Scan on prt1_p2 t3_1 + -> Hash + -> Seq Scan on prt2_p2 t2_1 + -> Hash Join + Hash Cond: (t3_2.a = t2_2.b) + -> Seq Scan on prt1_p3 t3_2 + -> Hash + -> Seq Scan on prt2_p3 t2_2 + -> Hash + -> Result + One-Time Filter: false +(21 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +-------------------------------------------- + Sort + Sort Key: a, t2.b + -> Hash Left Join + Hash Cond: (t2.b = a) + -> Append + -> Seq Scan on prt2_p1 t2 + Filter: (a = 0) + -> Seq Scan on prt2_p2 t2_1 + Filter: (a = 0) + -> Seq Scan on prt2_p3 t2_2 + Filter: (a = 0) + -> Hash + -> Result + One-Time Filter: false +(14 rows) + +-- +-- tests for hash partitioned tables. +-- +CREATE TABLE pht1 (a int, b int, c text) PARTITION BY HASH(c); +CREATE TABLE pht1_p1 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 0); +CREATE TABLE pht1_p2 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 1); +CREATE TABLE pht1_p3 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 2); +INSERT INTO pht1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE pht1; +CREATE TABLE pht2 (a int, b int, c text) PARTITION BY HASH(c); +CREATE TABLE pht2_p1 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 0); +CREATE TABLE pht2_p2 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 1); +CREATE TABLE pht2_p3 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 2); +INSERT INTO pht2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i; +ANALYZE pht2; +-- +-- hash partitioned by expression +-- +CREATE TABLE pht1_e (a int, b int, c text) PARTITION BY HASH(ltrim(c, 'A')); +CREATE TABLE pht1_e_p1 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 0); +CREATE TABLE pht1_e_p2 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 1); +CREATE TABLE pht1_e_p3 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 2); +INSERT INTO pht1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 299, 2) i; +ANALYZE pht1_e; +-- test partition matching with N-way join +EXPLAIN (COSTS OFF) +SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; + QUERY PLAN +-------------------------------------------------------------------------------- + GroupAggregate + Group Key: t1.c, t2.c, t3.c + -> Sort + Sort Key: t1.c, t3.c + -> Append + -> Hash Join + Hash Cond: (t1.c = ltrim(t3.c, 'A'::text)) + -> Hash Join + Hash Cond: ((t1.b = t2.b) AND (t1.c = t2.c)) + -> Seq Scan on pht1_p1 t1 + -> Hash + -> Seq Scan on pht2_p1 t2 + -> Hash + -> Seq Scan on pht1_e_p1 t3 + -> Hash Join + Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text)) + -> Hash Join + Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c)) + -> Seq Scan on pht1_p2 t1_1 + -> Hash + -> Seq Scan on pht2_p2 t2_1 + -> Hash + -> Seq Scan on pht1_e_p2 t3_1 + -> Hash Join + Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text)) + -> Hash Join + Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c)) + -> Seq Scan on pht1_p3 t1_2 + -> Hash + -> Seq Scan on pht2_p3 t2_2 + -> Hash + -> Seq Scan on pht1_e_p3 t3_2 +(32 rows) + +SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c; + avg | avg | avg | c | c | c +----------------------+----------------------+----------------------+------+------+------- + 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000 + 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001 + 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002 + 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003 + 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004 + 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005 +(6 rows) + +-- test default partition behavior for range +ALTER TABLE prt1 DETACH PARTITION prt1_p3; +ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT; +ANALYZE prt1; +ALTER TABLE prt2 DETACH PARTITION prt2_p3; +ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT; +ANALYZE prt2; +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Seq Scan on prt2_p1 t2 + -> Hash + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Hash Join + Hash Cond: (t2_1.b = t1_1.a) + -> Seq Scan on prt2_p2 t2_1 + -> Hash + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Hash Join + Hash Cond: (t2_2.b = t1_2.a) + -> Seq Scan on prt2_p3 t2_2 + -> Hash + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) +(21 rows) + +-- test default partition behavior for list +ALTER TABLE plt1 DETACH PARTITION plt1_p3; +ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT; +ANALYZE plt1; +ALTER TABLE plt2 DETACH PARTITION plt2_p3; +ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT; +ANALYZE plt2; +EXPLAIN (COSTS OFF) +SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.a % 25 = 0 GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c; + QUERY PLAN +-------------------------------------------------------- + Sort + Sort Key: t1.c + -> HashAggregate + Group Key: t1.c, t2.c + -> Append + -> Hash Join + Hash Cond: (t2.c = t1.c) + -> Seq Scan on plt2_p1 t2 + -> Hash + -> Seq Scan on plt1_p1 t1 + Filter: ((a % 25) = 0) + -> Hash Join + Hash Cond: (t2_1.c = t1_1.c) + -> Seq Scan on plt2_p2 t2_1 + -> Hash + -> Seq Scan on plt1_p2 t1_1 + Filter: ((a % 25) = 0) + -> Hash Join + Hash Cond: (t2_2.c = t1_2.c) + -> Seq Scan on plt2_p3 t2_2 + -> Hash + -> Seq Scan on plt1_p3 t1_2 + Filter: ((a % 25) = 0) +(23 rows) + +-- +-- multiple levels of partitioning +-- +CREATE TABLE prt1_l (a int, b int, c varchar) PARTITION BY RANGE(a); +CREATE TABLE prt1_l_p1 PARTITION OF prt1_l FOR VALUES FROM (0) TO (250); +CREATE TABLE prt1_l_p2 PARTITION OF prt1_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c); +CREATE TABLE prt1_l_p2_p1 PARTITION OF prt1_l_p2 FOR VALUES IN ('0000', '0001'); +CREATE TABLE prt1_l_p2_p2 PARTITION OF prt1_l_p2 FOR VALUES IN ('0002', '0003'); +CREATE TABLE prt1_l_p3 PARTITION OF prt1_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (b); +CREATE TABLE prt1_l_p3_p1 PARTITION OF prt1_l_p3 FOR VALUES FROM (0) TO (13); +CREATE TABLE prt1_l_p3_p2 PARTITION OF prt1_l_p3 FOR VALUES FROM (13) TO (25); +INSERT INTO prt1_l SELECT i, i % 25, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE prt1_l; +CREATE TABLE prt2_l (a int, b int, c varchar) PARTITION BY RANGE(b); +CREATE TABLE prt2_l_p1 PARTITION OF prt2_l FOR VALUES FROM (0) TO (250); +CREATE TABLE prt2_l_p2 PARTITION OF prt2_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c); +CREATE TABLE prt2_l_p2_p1 PARTITION OF prt2_l_p2 FOR VALUES IN ('0000', '0001'); +CREATE TABLE prt2_l_p2_p2 PARTITION OF prt2_l_p2 FOR VALUES IN ('0002', '0003'); +CREATE TABLE prt2_l_p3 PARTITION OF prt2_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (a); +CREATE TABLE prt2_l_p3_p1 PARTITION OF prt2_l_p3 FOR VALUES FROM (0) TO (13); +CREATE TABLE prt2_l_p3_p2 PARTITION OF prt2_l_p3 FOR VALUES FROM (13) TO (25); +INSERT INTO prt2_l SELECT i % 25, i, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 3) i; +ANALYZE prt2_l; +-- inner join, qual covering only top-level partitions +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Seq Scan on prt2_l_p1 t2 + -> Hash + -> Seq Scan on prt1_l_p1 t1 + Filter: (b = 0) + -> Hash Join + Hash Cond: (t2_1.b = t1_1.a) + -> Append + -> Seq Scan on prt2_l_p2_p1 t2_1 + -> Seq Scan on prt2_l_p2_p2 t2_2 + -> Hash + -> Append + -> Seq Scan on prt1_l_p2_p1 t1_1 + Filter: (b = 0) + -> Seq Scan on prt1_l_p2_p2 t1_2 + Filter: (b = 0) + -> Hash Join + Hash Cond: (t2_3.b = t1_3.a) + -> Append + -> Seq Scan on prt2_l_p3_p1 t2_3 + -> Seq Scan on prt2_l_p3_p2 t2_4 + -> Hash + -> Append + -> Seq Scan on prt1_l_p3_p1 t1_3 + Filter: (b = 0) +(29 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | 0 | 0000 + 150 | 0002 | 150 | 0002 + 300 | 0000 | 300 | 0000 + 450 | 0002 | 450 | 0002 +(4 rows) + +-- left join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------------------------------ + Sort + Sort Key: t1.a, t2.b + -> Append + -> Hash Right Join + Hash Cond: ((t2.b = t1.a) AND ((t2.c)::text = (t1.c)::text)) + -> Seq Scan on prt2_l_p1 t2 + -> Hash + -> Seq Scan on prt1_l_p1 t1 + Filter: (b = 0) + -> Hash Right Join + Hash Cond: ((t2_1.b = t1_1.a) AND ((t2_1.c)::text = (t1_1.c)::text)) + -> Seq Scan on prt2_l_p2_p1 t2_1 + -> Hash + -> Seq Scan on prt1_l_p2_p1 t1_1 + Filter: (b = 0) + -> Hash Right Join + Hash Cond: ((t2_2.b = t1_2.a) AND ((t2_2.c)::text = (t1_2.c)::text)) + -> Seq Scan on prt2_l_p2_p2 t2_2 + -> Hash + -> Seq Scan on prt1_l_p2_p2 t1_2 + Filter: (b = 0) + -> Hash Right Join + Hash Cond: ((t2_3.b = t1_3.a) AND ((t2_3.c)::text = (t1_3.c)::text)) + -> Append + -> Seq Scan on prt2_l_p3_p1 t2_3 + -> Seq Scan on prt2_l_p3_p2 t2_4 + -> Hash + -> Append + -> Seq Scan on prt1_l_p3_p1 t1_3 + Filter: (b = 0) +(30 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | 0 | 0000 + 50 | 0002 | | + 100 | 0000 | | + 150 | 0002 | 150 | 0002 + 200 | 0000 | | + 250 | 0002 | | + 300 | 0000 | 300 | 0000 + 350 | 0002 | | + 400 | 0000 | | + 450 | 0002 | 450 | 0002 + 500 | 0000 | | + 550 | 0002 | | +(12 rows) + +-- right join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +------------------------------------------------------------------------------------ + Sort + Sort Key: t1.a, t2.b + -> Append + -> Hash Right Join + Hash Cond: ((t1.a = t2.b) AND ((t1.c)::text = (t2.c)::text)) + -> Seq Scan on prt1_l_p1 t1 + -> Hash + -> Seq Scan on prt2_l_p1 t2 + Filter: (a = 0) + -> Hash Right Join + Hash Cond: ((t1_1.a = t2_1.b) AND ((t1_1.c)::text = (t2_1.c)::text)) + -> Seq Scan on prt1_l_p2_p1 t1_1 + -> Hash + -> Seq Scan on prt2_l_p2_p1 t2_1 + Filter: (a = 0) + -> Hash Right Join + Hash Cond: ((t1_2.a = t2_2.b) AND ((t1_2.c)::text = (t2_2.c)::text)) + -> Seq Scan on prt1_l_p2_p2 t1_2 + -> Hash + -> Seq Scan on prt2_l_p2_p2 t2_2 + Filter: (a = 0) + -> Hash Right Join + Hash Cond: ((t1_3.a = t2_3.b) AND ((t1_3.c)::text = (t2_3.c)::text)) + -> Append + -> Seq Scan on prt1_l_p3_p1 t1_3 + -> Seq Scan on prt1_l_p3_p2 t1_4 + -> Hash + -> Append + -> Seq Scan on prt2_l_p3_p1 t2_3 + Filter: (a = 0) +(30 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | 0 | 0000 + 150 | 0002 | 150 | 0002 + 300 | 0000 | 300 | 0000 + 450 | 0002 | 450 | 0002 + | | 75 | 0003 + | | 225 | 0001 + | | 375 | 0003 + | | 525 | 0001 +(8 rows) + +-- full join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------- + Sort + Sort Key: prt1_l_p1.a, prt2_l_p1.b + -> Append + -> Hash Full Join + Hash Cond: ((prt1_l_p1.a = prt2_l_p1.b) AND ((prt1_l_p1.c)::text = (prt2_l_p1.c)::text)) + -> Seq Scan on prt1_l_p1 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_l_p1 + Filter: (a = 0) + -> Hash Full Join + Hash Cond: ((prt1_l_p2_p1.a = prt2_l_p2_p1.b) AND ((prt1_l_p2_p1.c)::text = (prt2_l_p2_p1.c)::text)) + -> Seq Scan on prt1_l_p2_p1 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_l_p2_p1 + Filter: (a = 0) + -> Hash Full Join + Hash Cond: ((prt1_l_p2_p2.a = prt2_l_p2_p2.b) AND ((prt1_l_p2_p2.c)::text = (prt2_l_p2_p2.c)::text)) + -> Seq Scan on prt1_l_p2_p2 + Filter: (b = 0) + -> Hash + -> Seq Scan on prt2_l_p2_p2 + Filter: (a = 0) + -> Hash Full Join + Hash Cond: ((prt1_l_p3_p1.a = prt2_l_p3_p1.b) AND ((prt1_l_p3_p1.c)::text = (prt2_l_p3_p1.c)::text)) + -> Append + -> Seq Scan on prt1_l_p3_p1 + Filter: (b = 0) + -> Hash + -> Append + -> Seq Scan on prt2_l_p3_p1 + Filter: (a = 0) +(33 rows) + +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b; + a | c | b | c +-----+------+-----+------ + 0 | 0000 | 0 | 0000 + 50 | 0002 | | + 100 | 0000 | | + 150 | 0002 | 150 | 0002 + 200 | 0000 | | + 250 | 0002 | | + 300 | 0000 | 300 | 0000 + 350 | 0002 | | + 400 | 0000 | | + 450 | 0002 | 450 | 0002 + 500 | 0000 | | + 550 | 0002 | | + | | 75 | 0003 + | | 225 | 0001 + | | 375 | 0003 + | | 525 | 0001 +(16 rows) + +-- lateral partitionwise join +EXPLAIN (COSTS OFF) +SELECT * FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss + ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Sort + Sort Key: t1.a + -> Append + -> Nested Loop Left Join + -> Seq Scan on prt1_l_p1 t1 + Filter: (b = 0) + -> Hash Join + Hash Cond: ((t3.b = t2.a) AND ((t3.c)::text = (t2.c)::text)) + -> Seq Scan on prt2_l_p1 t3 + -> Hash + -> Seq Scan on prt1_l_p1 t2 + Filter: ((t1.a = a) AND ((t1.c)::text = (c)::text)) + -> Nested Loop Left Join + -> Seq Scan on prt1_l_p2_p1 t1_1 + Filter: (b = 0) + -> Hash Join + Hash Cond: ((t3_1.b = t2_1.a) AND ((t3_1.c)::text = (t2_1.c)::text)) + -> Seq Scan on prt2_l_p2_p1 t3_1 + -> Hash + -> Seq Scan on prt1_l_p2_p1 t2_1 + Filter: ((t1_1.a = a) AND ((t1_1.c)::text = (c)::text)) + -> Nested Loop Left Join + -> Seq Scan on prt1_l_p2_p2 t1_2 + Filter: (b = 0) + -> Hash Join + Hash Cond: ((t3_2.b = t2_2.a) AND ((t3_2.c)::text = (t2_2.c)::text)) + -> Seq Scan on prt2_l_p2_p2 t3_2 + -> Hash + -> Seq Scan on prt1_l_p2_p2 t2_2 + Filter: ((t1_2.a = a) AND ((t1_2.c)::text = (c)::text)) + -> Nested Loop Left Join + -> Append + -> Seq Scan on prt1_l_p3_p1 t1_3 + Filter: (b = 0) + -> Hash Join + Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text = (t2_3.c)::text)) + -> Append + -> Seq Scan on prt2_l_p3_p1 t3_3 + -> Seq Scan on prt2_l_p3_p2 t3_4 + -> Hash + -> Append + -> Seq Scan on prt1_l_p3_p1 t2_3 + Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text)) + -> Seq Scan on prt1_l_p3_p2 t2_4 + Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text)) +(45 rows) + +SELECT * FROM prt1_l t1 LEFT JOIN LATERAL + (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss + ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a; + a | b | c | t2a | t2c | t2b | t3b | least +-----+---+------+-----+------+-----+-----+------- + 0 | 0 | 0000 | 0 | 0000 | 0 | 0 | 0 + 50 | 0 | 0002 | | | | | + 100 | 0 | 0000 | | | | | + 150 | 0 | 0002 | 150 | 0002 | 0 | 150 | 150 + 200 | 0 | 0000 | | | | | + 250 | 0 | 0002 | | | | | + 300 | 0 | 0000 | 300 | 0000 | 0 | 300 | 300 + 350 | 0 | 0002 | | | | | + 400 | 0 | 0000 | | | | | + 450 | 0 | 0002 | 450 | 0002 | 0 | 450 | 450 + 500 | 0 | 0000 | | | | | + 550 | 0 | 0002 | | | | | +(12 rows) + +-- join with one side empty +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c; + QUERY PLAN +------------------------------------------------------------------------- + Hash Left Join + Hash Cond: ((t2.b = a) AND (t2.a = b) AND ((t2.c)::text = (c)::text)) + -> Append + -> Seq Scan on prt2_l_p1 t2 + -> Seq Scan on prt2_l_p2_p1 t2_1 + -> Seq Scan on prt2_l_p2_p2 t2_2 + -> Seq Scan on prt2_l_p3_p1 t2_3 + -> Seq Scan on prt2_l_p3_p2 t2_4 + -> Hash + -> Result + One-Time Filter: false +(11 rows) + +-- Test case to verify proper handling of subqueries in a partitioned delete. +-- The weird-looking lateral join is just there to force creation of a +-- nestloop parameter within the subquery, which exposes the problem if the +-- planner fails to make multiple copies of the subquery as appropriate. +EXPLAIN (COSTS OFF) +DELETE FROM prt1_l +WHERE EXISTS ( + SELECT 1 + FROM int4_tbl, + LATERAL (SELECT int4_tbl.f1 FROM int8_tbl LIMIT 2) ss + WHERE prt1_l.c IS NULL); + QUERY PLAN +--------------------------------------------------------------- + Delete on prt1_l + Delete on prt1_l_p1 + Delete on prt1_l_p3_p1 + Delete on prt1_l_p3_p2 + -> Nested Loop Semi Join + -> Seq Scan on prt1_l_p1 + Filter: (c IS NULL) + -> Nested Loop + -> Seq Scan on int4_tbl + -> Subquery Scan on ss + -> Limit + -> Seq Scan on int8_tbl + -> Nested Loop Semi Join + -> Seq Scan on prt1_l_p3_p1 + Filter: (c IS NULL) + -> Nested Loop + -> Seq Scan on int4_tbl + -> Subquery Scan on ss_1 + -> Limit + -> Seq Scan on int8_tbl int8_tbl_1 + -> Nested Loop Semi Join + -> Seq Scan on prt1_l_p3_p2 + Filter: (c IS NULL) + -> Nested Loop + -> Seq Scan on int4_tbl + -> Subquery Scan on ss_2 + -> Limit + -> Seq Scan on int8_tbl int8_tbl_2 +(28 rows) + +-- +-- negative testcases +-- +CREATE TABLE prt1_n (a int, b int, c varchar) PARTITION BY RANGE(c); +CREATE TABLE prt1_n_p1 PARTITION OF prt1_n FOR VALUES FROM ('0000') TO ('0250'); +CREATE TABLE prt1_n_p2 PARTITION OF prt1_n FOR VALUES FROM ('0250') TO ('0500'); +INSERT INTO prt1_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 499, 2) i; +ANALYZE prt1_n; +CREATE TABLE prt2_n (a int, b int, c text) PARTITION BY LIST(c); +CREATE TABLE prt2_n_p1 PARTITION OF prt2_n FOR VALUES IN ('0000', '0003', '0004', '0010', '0006', '0007'); +CREATE TABLE prt2_n_p2 PARTITION OF prt2_n FOR VALUES IN ('0001', '0005', '0002', '0009', '0008', '0011'); +INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE prt2_n; +CREATE TABLE prt3_n (a int, b int, c text) PARTITION BY LIST(c); +CREATE TABLE prt3_n_p1 PARTITION OF prt3_n FOR VALUES IN ('0000', '0004', '0006', '0007'); +CREATE TABLE prt3_n_p2 PARTITION OF prt3_n FOR VALUES IN ('0001', '0002', '0008', '0010'); +CREATE TABLE prt3_n_p3 PARTITION OF prt3_n FOR VALUES IN ('0003', '0005', '0009', '0011'); +INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE prt3_n; +CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a); +CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300); +CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500); +CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600); +INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i; +ANALYZE prt4_n; +-- partitionwise join can not be applied if the partition ranges differ +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a; + QUERY PLAN +---------------------------------------------- + Hash Join + Hash Cond: (t1.a = t2.a) + -> Append + -> Seq Scan on prt1_p1 t1 + -> Seq Scan on prt1_p2 t1_1 + -> Seq Scan on prt1_p3 t1_2 + -> Hash + -> Append + -> Seq Scan on prt4_n_p1 t2 + -> Seq Scan on prt4_n_p2 t2_1 + -> Seq Scan on prt4_n_p3 t2_2 +(11 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt2 t3 WHERE t1.a = t2.a and t1.a = t3.b; + QUERY PLAN +-------------------------------------------------------- + Hash Join + Hash Cond: (t2.a = t1.a) + -> Append + -> Seq Scan on prt4_n_p1 t2 + -> Seq Scan on prt4_n_p2 t2_1 + -> Seq Scan on prt4_n_p3 t2_2 + -> Hash + -> Append + -> Hash Join + Hash Cond: (t1.a = t3.b) + -> Seq Scan on prt1_p1 t1 + -> Hash + -> Seq Scan on prt2_p1 t3 + -> Hash Join + Hash Cond: (t1_1.a = t3_1.b) + -> Seq Scan on prt1_p2 t1_1 + -> Hash + -> Seq Scan on prt2_p2 t3_1 + -> Hash Join + Hash Cond: (t1_2.a = t3_2.b) + -> Seq Scan on prt1_p3 t1_2 + -> Hash + -> Seq Scan on prt2_p3 t3_2 +(23 rows) + +-- partitionwise join can not be applied if there are no equi-join conditions +-- between partition keys +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON (t1.a < t2.b); + QUERY PLAN +--------------------------------------------------------- + Nested Loop Left Join + -> Append + -> Seq Scan on prt1_p1 t1 + -> Seq Scan on prt1_p2 t1_1 + -> Seq Scan on prt1_p3 t1_2 + -> Append + -> Index Scan using iprt2_p1_b on prt2_p1 t2 + Index Cond: (t1.a < b) + -> Index Scan using iprt2_p2_b on prt2_p2 t2_1 + Index Cond: (t1.a < b) + -> Index Scan using iprt2_p3_b on prt2_p3 t2_2 + Index Cond: (t1.a < b) +(12 rows) + +-- equi-join with join condition on partial keys does not qualify for +-- partitionwise join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1, prt2_m t2 WHERE t1.a = (t2.b + t2.a)/2; + QUERY PLAN +---------------------------------------------- + Hash Join + Hash Cond: (((t2.b + t2.a) / 2) = t1.a) + -> Append + -> Seq Scan on prt2_m_p1 t2 + -> Seq Scan on prt2_m_p2 t2_1 + -> Seq Scan on prt2_m_p3 t2_2 + -> Hash + -> Append + -> Seq Scan on prt1_m_p1 t1 + -> Seq Scan on prt1_m_p2 t1_1 + -> Seq Scan on prt1_m_p3 t1_2 +(11 rows) + +-- equi-join between out-of-order partition key columns does not qualify for +-- partitionwise join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.a = t2.b; + QUERY PLAN +---------------------------------------------- + Hash Left Join + Hash Cond: (t1.a = t2.b) + -> Append + -> Seq Scan on prt1_m_p1 t1 + -> Seq Scan on prt1_m_p2 t1_1 + -> Seq Scan on prt1_m_p3 t1_2 + -> Hash + -> Append + -> Seq Scan on prt2_m_p1 t2 + -> Seq Scan on prt2_m_p2 t2_1 + -> Seq Scan on prt2_m_p3 t2_2 +(11 rows) + +-- equi-join between non-key columns does not qualify for partitionwise join +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.c = t2.c; + QUERY PLAN +---------------------------------------------- + Hash Left Join + Hash Cond: (t1.c = t2.c) + -> Append + -> Seq Scan on prt1_m_p1 t1 + -> Seq Scan on prt1_m_p2 t1_1 + -> Seq Scan on prt1_m_p3 t1_2 + -> Hash + -> Append + -> Seq Scan on prt2_m_p1 t2 + -> Seq Scan on prt2_m_p2 t2_1 + -> Seq Scan on prt2_m_p3 t2_2 +(11 rows) + +-- partitionwise join can not be applied between tables with different +-- partition lists +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c); + QUERY PLAN +---------------------------------------------- + Hash Right Join + Hash Cond: (t2.c = (t1.c)::text) + -> Append + -> Seq Scan on prt2_n_p1 t2 + -> Seq Scan on prt2_n_p2 t2_1 + -> Hash + -> Append + -> Seq Scan on prt1_n_p1 t1 + -> Seq Scan on prt1_n_p2 t1_1 +(9 rows) + +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOIN plt1 t3 ON (t1.c = t3.c); + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: (t3.c = (t1.c)::text) + -> Append + -> Seq Scan on plt1_p1 t3 + -> Seq Scan on plt1_p2 t3_1 + -> Seq Scan on plt1_p3 t3_2 + -> Hash + -> Hash Join + Hash Cond: (t2.c = (t1.c)::text) + -> Append + -> Seq Scan on prt2_n_p1 t2 + -> Seq Scan on prt2_n_p2 t2_1 + -> Hash + -> Append + -> Seq Scan on prt1_n_p1 t1 + -> Seq Scan on prt1_n_p2 t1_1 +(16 rows) + +-- partitionwise join can not be applied for a join between list and range +-- partitioned table +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c); + QUERY PLAN +---------------------------------------------- + Hash Full Join + Hash Cond: ((t2.c)::text = (t1.c)::text) + -> Append + -> Seq Scan on prt1_p1 t2 + -> Seq Scan on prt1_p2 t2_1 + -> Seq Scan on prt1_p3 t2_2 + -> Hash + -> Append + -> Seq Scan on prt1_n_p1 t1 + -> Seq Scan on prt1_n_p2 t1_1 +(10 rows) + +-- partitionwise join can not be applied if only one of joining table has +-- default partition +ALTER TABLE prt2 DETACH PARTITION prt2_p3; +ALTER TABLE prt2 ATTACH PARTITION prt2_p3 FOR VALUES FROM (500) TO (600); +ANALYZE prt2; +EXPLAIN (COSTS OFF) +SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b; + QUERY PLAN +-------------------------------------------------- + Sort + Sort Key: t1.a + -> Hash Join + Hash Cond: (t2.b = t1.a) + -> Append + -> Seq Scan on prt2_p1 t2 + -> Seq Scan on prt2_p2 t2_1 + -> Seq Scan on prt2_p3 t2_2 + -> Hash + -> Append + -> Seq Scan on prt1_p1 t1 + Filter: (b = 0) + -> Seq Scan on prt1_p2 t1_1 + Filter: (b = 0) + -> Seq Scan on prt1_p3 t1_2 + Filter: (b = 0) +(16 rows) + diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index b9a987dbcf..03997c7a8c 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -1615,15 +1615,15 @@ select test_found(); t (1 row) -select * from found_test_tbl; +select * from found_test_tbl order by a; a ----- 2 - 100 3 4 5 6 + 100 (6 rows) -- @@ -1638,15 +1638,15 @@ BEGIN END LOOP; RETURN; END;' language plpgsql; -select * from test_table_func_rec(); +select * from test_table_func_rec() order by a; a ----- 2 - 100 3 4 5 6 + 100 (6 rows) create function test_table_func_row() returns setof found_test_tbl as ' @@ -1658,15 +1658,15 @@ BEGIN END LOOP; RETURN; END;' language plpgsql; -select * from test_table_func_row(); +select * from test_table_func_row() order by a; a ----- 2 - 100 3 4 5 6 + 100 (6 rows) create function test_ret_set_scalar(int,int) returns setof int as ' diff --git a/src/test/regress/expected/triggers_1.out b/src/test/regress/expected/triggers_1.out index 569b8c181e..6c9a57072e 100644 --- a/src/test/regress/expected/triggers_1.out +++ b/src/test/regress/expected/triggers_1.out @@ -2557,7 +2557,7 @@ create trigger child_row_trig -- but now we're not allowed to make it inherit anymore alter table child inherit parent; ERROR: trigger "child_row_trig" prevents table "child" from becoming an inheritance child -DETAIL: ROW triggers with transition tables are not supported in inheritance hierarchies +DETAIL: ROW triggers with transition tables are not supported in inheritance hierarchies. -- drop the trigger, and now we're allowed to make it inherit again drop trigger child_row_trig on child; alter table child inherit parent; diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 01239e26be..307af6f07c 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -1481,7 +1481,7 @@ create function test_found() end;' language plpgsql; select test_found(); -select * from found_test_tbl; +select * from found_test_tbl order by a; -- -- Test set-returning functions for PL/pgSQL @@ -1497,7 +1497,7 @@ BEGIN RETURN; END;' language plpgsql; -select * from test_table_func_rec(); +select * from test_table_func_rec() order by a; create function test_table_func_row() returns setof found_test_tbl as ' DECLARE @@ -1509,7 +1509,7 @@ BEGIN RETURN; END;' language plpgsql; -select * from test_table_func_row(); +select * from test_table_func_row() order by a; create function test_ret_set_scalar(int,int) returns setof int as ' DECLARE |