Elgato_dark/hypopg
1
0
Fork 0
mirror of https://github.com/HypoPG/hypopg synced 2026-05-23 17:18:44 +00:00
Code Issues Projects Releases Packages Wiki Activity Actions
hypopg/expected/hypo_table.out
Yuzuko Hosoya 4aefb2de9b Disable runtime partition pruning on hypothetical partitioned table 
Unfortunately, the runtime partition pruning code in v11 is written in such a
way that it can't be made compatible with hypothetical partitioning.
2019-07-13 09:13:58 +02:00

1929 lines
82 KiB
Text
Raw Permalink Blame History

-- Creating real and hypothetical tables"
-- ====================================="
-- Real tables
-- -----------
-- 1.1. Range partition
DROP TABLE IF EXISTS part_range;
NOTICE: table "part_range" does not exist, skipping
CREATE TABLE part_range (id integer, val text) PARTITION BY RANGE (id);
CREATE TABLE part_range_1_10000 PARTITION OF part_range FOR VALUES FROM (1) TO (10000);
CREATE TABLE part_range_10000_20000 PARTITION OF part_range FOR VALUES FROM (10000) TO (20000);
CREATE TABLE part_range_20000_30000 PARTITION OF part_range FOR VALUES FROM (20000) TO (30000);
INSERT INTO part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
-- 1.2. List partitioning
DROP TABLE IF EXISTS part_list;
NOTICE: table "part_list" does not exist, skipping
CREATE TABLE part_list (id integer, id_key integer, val text) PARTITION BY LIST (id_key);
CREATE TABLE part_list_4_5_6_8_10 PARTITION OF part_list FOR VALUES IN (4, 5, 6, 8, 10);
CREATE TABLE part_list_7_9 PARTITION OF part_list FOR VALUES IN (7, 9);
CREATE TABLE part_list_1_2_3 PARTITION OF part_list FOR VALUES IN (1, 2, 3);
INSERT INTO part_list SELECT i, (i % 9) + 1, 'line ' || i FROM generate_series(1, 50000) i;
-- 1.3. Hash partitioning
DROP TABLE IF EXISTS part_hash;
NOTICE: table "part_hash" does not exist, skipping
CREATE TABLE part_hash (id integer, val text) PARTITION BY HASH (id);
CREATE TABLE part_hash_9 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 9);
CREATE TABLE part_hash_8 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 8);
CREATE TABLE part_hash_7 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 7);
CREATE TABLE part_hash_6 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 6);
CREATE TABLE part_hash_5 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 5);
CREATE TABLE part_hash_4 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 4);
CREATE TABLE part_hash_3 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 3);
CREATE TABLE part_hash_2 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 2);
CREATE TABLE part_hash_1 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 1);
CREATE TABLE part_hash_0 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0);
INSERT INTO part_hash SELECT i, 'line ' || i FROM generate_series(1, 90000) i;
-- 1.4. Multi level range
DROP TABLE IF EXISTS part_multi;
NOTICE: table "part_multi" does not exist, skipping
CREATE TABLE part_multi(dpt smallint, dt date, val text) PARTITION BY LIST (dpt);
CREATE TABLE part_multi_2 PARTITION OF part_multi FOR VALUES IN (2) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_2_q1 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_2_q2 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_2_q3 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_2_q4 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_2_def PARTITION OF part_multi_2 DEFAULT;
CREATE TABLE part_multi_1 PARTITION OF part_multi FOR VALUES IN (1) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_1_q2 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_1_q3 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_1_q4 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_1_q1 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$) PARTITION BY RANGE (dt);
CREATE TABLE part_multi_1_q1_b PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_1_q1_a PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$);
CREATE TABLE part_multi_1_def PARTITION OF part_multi_1 DEFAULT;
CREATE TABLE part_multi_3 PARTITION OF part_multi FOR VALUES IN (3) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_3_q1 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_3_q2 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_3_q3 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_3_q4 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_3_def PARTITION OF part_multi_3 DEFAULT;
CREATE TABLE part_multi_def PARTITION OF part_multi DEFAULT PARTITION BY RANGE(dt);
CREATE TABLE part_multi_def_q1 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_def_q2 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_def_q3 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_def_q4 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_def_def PARTITION OF part_multi_def DEFAULT;
INSERT INTO part_multi select (i%4)+1, '2015-01-01'::date + interval '1 day' * (i%500), 'val ' || i FROM generate_series(1,50000) i;
-- Hypothetical tables
-- -------------------
-- 2.0. Dropping any hypothetical object
SELECT * FROM hypopg_reset();
hypopg_reset
--------------
(1 row)
-- 2.1. Range partition
DROP TABLE IF EXISTS hypo_part_range;
NOTICE: table "hypo_part_range" does not exist, skipping
CREATE TABLE hypo_part_range (id integer, val text);
INSERT INTO hypo_part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
SELECT * FROM hypopg_partition_table('hypo_part_range', 'PARTITION BY RANGE (id)');
hypopg_partition_table
------------------------
t
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
tablename
-------------------------
hypo_part_range_1_10000
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_range_10000_20000', 'PARTITION OF hypo_part_range FOR VALUES FROM (10000) TO (20000)');
tablename
-----------------------------
hypo_part_range_10000_20000
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_range_20000_30000', 'PARTITION OF hypo_part_range FOR VALUES FROM (20000) TO (30000)');
tablename
-----------------------------
hypo_part_range_20000_30000
(1 row)
-- 2.2. List partitioning
DROP TABLE IF EXISTS hypo_part_list;
NOTICE: table "hypo_part_list" does not exist, skipping
CREATE TABLE hypo_part_list (id integer, id_key integer, val text);
INSERT INTO hypo_part_list SELECT i, (i % 9) + 1, 'line ' || i FROM generate_series(1, 50000) i;
SELECT * FROM hypopg_partition_table('hypo_part_list', 'PARTITION BY LIST (id_key)');
hypopg_partition_table
------------------------
t
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_list_4_5_6_8_10', 'PARTITION OF hypo_part_list FOR VALUES IN (4, 5, 6, 8, 10)');
tablename
---------------------------
hypo_part_list_4_5_6_8_10
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_list_7_9', 'PARTITION OF hypo_part_list FOR VALUES IN (7, 9)');
tablename
--------------------
hypo_part_list_7_9
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_list_1_2_3', 'PARTITION OF hypo_part_list FOR VALUES IN (1, 2, 3)');
tablename
----------------------
hypo_part_list_1_2_3
(1 row)
-- 2.3. Hash partitioning
DROP TABLE IF EXISTS hypo_part_hash;
NOTICE: table "hypo_part_hash" does not exist, skipping
CREATE TABLE hypo_part_hash (id integer, val text);
INSERT INTO hypo_part_hash SELECT i, 'line ' || i FROM generate_series(1, 90000) i;
SELECT * FROM hypopg_partition_table('hypo_part_hash', 'PARTITION BY HASH (id)');
hypopg_partition_table
------------------------
t
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_9', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 9)');
tablename
------------------
hypo_part_hash_9
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_8', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 8)');
tablename
------------------
hypo_part_hash_8
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_7', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 7)');
tablename
------------------
hypo_part_hash_7
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_6', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 6)');
tablename
------------------
hypo_part_hash_6
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_5', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 5)');
tablename
------------------
hypo_part_hash_5
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_4', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 4)');
tablename
------------------
hypo_part_hash_4
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_3', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 3)');
tablename
------------------
hypo_part_hash_3
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_2', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 2)');
tablename
------------------
hypo_part_hash_2
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_1', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 1)');
tablename
------------------
hypo_part_hash_1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_0', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0)');
tablename
------------------
hypo_part_hash_0
(1 row)
-- 2.4. Multi level range
DROP TABLE IF EXISTS hypo_part_multi;
NOTICE: table "hypo_part_multi" does not exist, skipping
CREATE TABLE hypo_part_multi(dpt smallint, dt date, val text);
INSERT INTO hypo_part_multi select (i%4)+1, '2015-01-01'::date + interval '1 day' * (i%500), 'val ' || i FROM generate_series(1,50000) i;
SELECT * FROM hypopg_partition_table('hypo_part_multi', 'PARTITION BY LIST (dpt)');
hypopg_partition_table
------------------------
t
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2', 'PARTITION OF hypo_part_multi FOR VALUES IN (2)', 'PARTITION BY RANGE(dt)');
tablename
-------------------
hypo_part_multi_2
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q1', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
tablename
----------------------
hypo_part_multi_2_q1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q2', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
tablename
----------------------
hypo_part_multi_2_q2
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q3', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
tablename
----------------------
hypo_part_multi_2_q3
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q4', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
tablename
----------------------
hypo_part_multi_2_q4
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_def', 'PARTITION OF hypo_part_multi_2 DEFAULT');
tablename
-----------------------
hypo_part_multi_2_def
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1', 'PARTITION OF hypo_part_multi FOR VALUES IN (1)', 'PARTITION BY RANGE(dt)');
tablename
-------------------
hypo_part_multi_1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q2', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
tablename
----------------------
hypo_part_multi_1_q2
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q3', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
tablename
----------------------
hypo_part_multi_1_q3
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q4', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
tablename
----------------------
hypo_part_multi_1_q4
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)','PARTITION BY RANGE (dt)');
tablename
----------------------
hypo_part_multi_1_q1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_b', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$)');
tablename
------------------------
hypo_part_multi_1_q1_b
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_a', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$)');
tablename
------------------------
hypo_part_multi_1_q1_a
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_def', 'PARTITION OF hypo_part_multi_1 DEFAULT');
tablename
-----------------------
hypo_part_multi_1_def
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3', 'PARTITION OF hypo_part_multi FOR VALUES IN (3)', 'PARTITION BY RANGE(dt)');
tablename
-------------------
hypo_part_multi_3
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q1', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
tablename
----------------------
hypo_part_multi_3_q1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q2', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
tablename
----------------------
hypo_part_multi_3_q2
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q3', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
tablename
----------------------
hypo_part_multi_3_q3
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q4', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
tablename
----------------------
hypo_part_multi_3_q4
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_def', 'PARTITION OF hypo_part_multi_3 DEFAULT');
tablename
-----------------------
hypo_part_multi_3_def
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def', 'PARTITION OF hypo_part_multi DEFAULT' ,'PARTITION BY RANGE(dt)');
tablename
---------------------
hypo_part_multi_def
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q1', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
tablename
------------------------
hypo_part_multi_def_q1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q2', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
tablename
------------------------
hypo_part_multi_def_q2
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q3', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
tablename
------------------------
hypo_part_multi_def_q3
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q4', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
tablename
------------------------
hypo_part_multi_def_q4
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_def', 'PARTITION OF hypo_part_multi_def DEFAULT');
tablename
-------------------------
hypo_part_multi_def_def
(1 row)
-- Maintenance
-- -----------
VACUUM ANALYZE;
SELECT * FROM hypopg_analyze('hypo_part_range',100);
hypopg_analyze
----------------
(1 row)
SELECT * FROM hypopg_analyze('hypo_part_list',100);
hypopg_analyze
----------------
(1 row)
SELECT * FROM hypopg_analyze('hypo_part_hash',100);
NOTICE: hypothetical partition "hypo_part_hash_9" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_8" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_7" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_6" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_5" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_4" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_3" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_2" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_1" is a hash partition, skipping
NOTICE: hypothetical partition "hypo_part_hash_0" is a hash partition, skipping
hypopg_analyze
----------------
(1 row)
SELECT * FROM hypopg_analyze('hypo_part_multi',100);
hypopg_analyze
----------------
(1 row)
-- Test deparsing
-- ==============
SELECT relid = rootid AS is_root, tablename, parentid IS NULL parentid_is_null,
parentid IS NOT NULL AS parentid_is_not_null,
partition_by_clause, partition_bounds
FROM hypopg_table()
ORDER BY tablename COLLATE "C";
is_root | tablename | parentid_is_null | parentid_is_not_null | partition_by_clause | partition_bounds
---------+-----------------------------+------------------+----------------------+----------------------------+--------------------------------------------------
t | hypo_part_hash | t | f | PARTITION BY HASH (id) |
f | hypo_part_hash_0 | f | t | | FOR VALUES WITH (modulus 10, remainder 0)
f | hypo_part_hash_1 | f | t | | FOR VALUES WITH (modulus 10, remainder 1)
f | hypo_part_hash_2 | f | t | | FOR VALUES WITH (modulus 10, remainder 2)
f | hypo_part_hash_3 | f | t | | FOR VALUES WITH (modulus 10, remainder 3)
f | hypo_part_hash_4 | f | t | | FOR VALUES WITH (modulus 10, remainder 4)
f | hypo_part_hash_5 | f | t | | FOR VALUES WITH (modulus 10, remainder 5)
f | hypo_part_hash_6 | f | t | | FOR VALUES WITH (modulus 10, remainder 6)
f | hypo_part_hash_7 | f | t | | FOR VALUES WITH (modulus 10, remainder 7)
f | hypo_part_hash_8 | f | t | | FOR VALUES WITH (modulus 10, remainder 8)
f | hypo_part_hash_9 | f | t | | FOR VALUES WITH (modulus 10, remainder 9)
t | hypo_part_list | t | f | PARTITION BY LIST (id_key) |
f | hypo_part_list_1_2_3 | f | t | | FOR VALUES IN (1, 2, 3)
f | hypo_part_list_4_5_6_8_10 | f | t | | FOR VALUES IN (4, 5, 6, 8, 10)
f | hypo_part_list_7_9 | f | t | | FOR VALUES IN (7, 9)
t | hypo_part_multi | t | f | PARTITION BY LIST (dpt) |
f | hypo_part_multi_1 | f | t | PARTITION BY RANGE (dt) | FOR VALUES IN ('1')
f | hypo_part_multi_1_def | f | t | | DEFAULT
f | hypo_part_multi_1_q1 | f | t | PARTITION BY RANGE (dt) | FOR VALUES FROM ('01-01-2015') TO ('04-01-2015')
f | hypo_part_multi_1_q1_a | f | t | | FOR VALUES FROM ('01-01-2015') TO ('02-01-2015')
f | hypo_part_multi_1_q1_b | f | t | | FOR VALUES FROM ('02-01-2015') TO ('04-01-2015')
f | hypo_part_multi_1_q2 | f | t | | FOR VALUES FROM ('04-01-2015') TO ('07-01-2015')
f | hypo_part_multi_1_q3 | f | t | | FOR VALUES FROM ('07-01-2015') TO ('10-01-2015')
f | hypo_part_multi_1_q4 | f | t | | FOR VALUES FROM ('10-01-2015') TO ('01-01-2016')
f | hypo_part_multi_2 | f | t | PARTITION BY RANGE (dt) | FOR VALUES IN ('2')
f | hypo_part_multi_2_def | f | t | | DEFAULT
f | hypo_part_multi_2_q1 | f | t | | FOR VALUES FROM ('01-01-2015') TO ('04-01-2015')
f | hypo_part_multi_2_q2 | f | t | | FOR VALUES FROM ('04-01-2015') TO ('07-01-2015')
f | hypo_part_multi_2_q3 | f | t | | FOR VALUES FROM ('07-01-2015') TO ('10-01-2015')
f | hypo_part_multi_2_q4 | f | t | | FOR VALUES FROM ('10-01-2015') TO ('01-01-2016')
f | hypo_part_multi_3 | f | t | PARTITION BY RANGE (dt) | FOR VALUES IN ('3')
f | hypo_part_multi_3_def | f | t | | DEFAULT
f | hypo_part_multi_3_q1 | f | t | | FOR VALUES FROM ('01-01-2015') TO ('04-01-2015')
f | hypo_part_multi_3_q2 | f | t | | FOR VALUES FROM ('04-01-2015') TO ('07-01-2015')
f | hypo_part_multi_3_q3 | f | t | | FOR VALUES FROM ('07-01-2015') TO ('10-01-2015')
f | hypo_part_multi_3_q4 | f | t | | FOR VALUES FROM ('10-01-2015') TO ('01-01-2016')
f | hypo_part_multi_def | f | t | PARTITION BY RANGE (dt) | DEFAULT
f | hypo_part_multi_def_def | f | t | | DEFAULT
f | hypo_part_multi_def_q1 | f | t | | FOR VALUES FROM ('01-01-2015') TO ('04-01-2015')
f | hypo_part_multi_def_q2 | f | t | | FOR VALUES FROM ('04-01-2015') TO ('07-01-2015')
f | hypo_part_multi_def_q3 | f | t | | FOR VALUES FROM ('07-01-2015') TO ('10-01-2015')
f | hypo_part_multi_def_q4 | f | t | | FOR VALUES FROM ('10-01-2015') TO ('01-01-2016')
t | hypo_part_range | t | f | PARTITION BY RANGE (id) |
f | hypo_part_range_10000_20000 | f | t | | FOR VALUES FROM (10000) TO (20000)
f | hypo_part_range_1_10000 | f | t | | FOR VALUES FROM (1) TO (10000)
f | hypo_part_range_20000_30000 | f | t | | FOR VALUES FROM (20000) TO (30000)
(46 rows)
-- Test hypothetical partitioning behavior
-- =======================================
-- Simple queries
-- --------------
-- Real tables
-- -----------
-- 3.1. Range partitioning
EXPLAIN (COSTS OFF) SELECT * FROM part_range;
QUERY PLAN
------------------------------------------
Append
-> Seq Scan on part_range_1_10000
-> Seq Scan on part_range_10000_20000
-> Seq Scan on part_range_20000_30000
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id = 42;
QUERY PLAN
--------------------------------------
Append
-> Seq Scan on part_range_1_10000
Filter: (id = 42)
(3 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id < 15000;
QUERY PLAN
------------------------------------------
Append
-> Seq Scan on part_range_1_10000
Filter: (id < 15000)
-> Seq Scan on part_range_10000_20000
Filter: (id < 15000)
(5 rows)
-- 3.2. List partitioning
EXPLAIN (COSTS OFF) SELECT * FROM part_list;
QUERY PLAN
----------------------------------------
Append
-> Seq Scan on part_list_1_2_3
-> Seq Scan on part_list_4_5_6_8_10
-> Seq Scan on part_list_7_9
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id < 42;
QUERY PLAN
----------------------------------------
Append
-> Seq Scan on part_list_1_2_3
Filter: (id < 42)
-> Seq Scan on part_list_4_5_6_8_10
Filter: (id < 42)
-> Seq Scan on part_list_7_9
Filter: (id < 42)
(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id < 15000;
QUERY PLAN
----------------------------------------
Append
-> Seq Scan on part_list_1_2_3
Filter: (id < 15000)
-> Seq Scan on part_list_4_5_6_8_10
Filter: (id < 15000)
-> Seq Scan on part_list_7_9
Filter: (id < 15000)
(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id_key < 5;
QUERY PLAN
----------------------------------------
Append
-> Seq Scan on part_list_1_2_3
Filter: (id_key < 5)
-> Seq Scan on part_list_4_5_6_8_10
Filter: (id_key < 5)
(5 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id_key = 7;
QUERY PLAN
---------------------------------
Append
-> Seq Scan on part_list_7_9
Filter: (id_key = 7)
(3 rows)
-- 3.3. Hash partitioning
EXPLAIN (COSTS OFF) SELECT * FROM part_hash;
QUERY PLAN
-------------------------------
Append
-> Seq Scan on part_hash_0
-> Seq Scan on part_hash_1
-> Seq Scan on part_hash_2
-> Seq Scan on part_hash_3
-> Seq Scan on part_hash_4
-> Seq Scan on part_hash_5
-> Seq Scan on part_hash_6
-> Seq Scan on part_hash_7
-> Seq Scan on part_hash_8
-> Seq Scan on part_hash_9
(11 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_hash WHERE id = 42;
QUERY PLAN
-------------------------------
Append
-> Seq Scan on part_hash_4
Filter: (id = 42)
(3 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_hash WHERE id < 15000;
QUERY PLAN
-------------------------------
Append
-> Seq Scan on part_hash_0
Filter: (id < 15000)
-> Seq Scan on part_hash_1
Filter: (id < 15000)
-> Seq Scan on part_hash_2
Filter: (id < 15000)
-> Seq Scan on part_hash_3
Filter: (id < 15000)
-> Seq Scan on part_hash_4
Filter: (id < 15000)
-> Seq Scan on part_hash_5
Filter: (id < 15000)
-> Seq Scan on part_hash_6
Filter: (id < 15000)
-> Seq Scan on part_hash_7
Filter: (id < 15000)
-> Seq Scan on part_hash_8
Filter: (id < 15000)
-> Seq Scan on part_hash_9
Filter: (id < 15000)
(21 rows)
-- 3.4. Multi level range
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
QUERY PLAN
--------------------------------------
Append
-> Seq Scan on part_multi_1_q1_a
-> Seq Scan on part_multi_1_q1_b
-> Seq Scan on part_multi_1_q2
-> Seq Scan on part_multi_1_q3
-> Seq Scan on part_multi_1_q4
-> Seq Scan on part_multi_1_def
-> Seq Scan on part_multi_2_q1
-> Seq Scan on part_multi_2_q2
-> Seq Scan on part_multi_2_q3
-> Seq Scan on part_multi_2_q4
-> Seq Scan on part_multi_2_def
-> Seq Scan on part_multi_3_q1
-> Seq Scan on part_multi_3_q2
-> Seq Scan on part_multi_3_q3
-> Seq Scan on part_multi_3_q4
-> Seq Scan on part_multi_3_def
-> Seq Scan on part_multi_def_q1
-> Seq Scan on part_multi_def_q2
-> Seq Scan on part_multi_def_q3
-> Seq Scan on part_multi_def_q4
-> Seq Scan on part_multi_def_def
(22 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi WHERE dpt = 2;
QUERY PLAN
------------------------------------
Append
-> Seq Scan on part_multi_2_q1
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q2
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q3
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q4
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_def
Filter: (dpt = 2)
(11 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi WHERE dt >= '2015-01-05' AND dt < '2015-01-10';
QUERY PLAN
----------------------------------------------------------------------------
Append
-> Seq Scan on part_multi_1_q1_a
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on part_multi_1_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on part_multi_2_q1
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on part_multi_2_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on part_multi_3_q1
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on part_multi_3_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on part_multi_def_q1
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on part_multi_def_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
(17 rows)
-- Hypothetical tables
-- -------------------
-- 4.1. Range partitioning
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range;
QUERY PLAN
---------------------------------------------------------------
Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = 42;
QUERY PLAN
-----------------------------------------------------------
Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = 42)
(3 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id < 15000;
QUERY PLAN
---------------------------------------------------------------
Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id < 15000)
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id < 15000)
(5 rows)
-- 4.2. List partitioning
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list;
QUERY PLAN
------------------------------------------------------------
Append
-> Seq Scan on hypo_part_list hypo_part_list_1_2_3
-> Seq Scan on hypo_part_list hypo_part_list_4_5_6_8_10
-> Seq Scan on hypo_part_list hypo_part_list_7_9
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id < 42;
QUERY PLAN
------------------------------------------------------------
Append
-> Seq Scan on hypo_part_list hypo_part_list_1_2_3
Filter: (id < 42)
-> Seq Scan on hypo_part_list hypo_part_list_4_5_6_8_10
Filter: (id < 42)
-> Seq Scan on hypo_part_list hypo_part_list_7_9
Filter: (id < 42)
(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id < 15000;
QUERY PLAN
------------------------------------------------------------
Append
-> Seq Scan on hypo_part_list hypo_part_list_1_2_3
Filter: (id < 15000)
-> Seq Scan on hypo_part_list hypo_part_list_4_5_6_8_10
Filter: (id < 15000)
-> Seq Scan on hypo_part_list hypo_part_list_7_9
Filter: (id < 15000)
(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id_key < 5;
QUERY PLAN
------------------------------------------------------------
Append
-> Seq Scan on hypo_part_list hypo_part_list_1_2_3
Filter: (id_key < 5)
-> Seq Scan on hypo_part_list hypo_part_list_4_5_6_8_10
Filter: (id_key < 5)
(5 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id_key = 7;
QUERY PLAN
-----------------------------------------------------
Append
-> Seq Scan on hypo_part_list hypo_part_list_7_9
Filter: (id_key = 7)
(3 rows)
-- 4.3. Hash partitioning
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash;
QUERY PLAN
---------------------------------------------------
Append
-> Seq Scan on hypo_part_hash hypo_part_hash_0
-> Seq Scan on hypo_part_hash hypo_part_hash_1
-> Seq Scan on hypo_part_hash hypo_part_hash_2
-> Seq Scan on hypo_part_hash hypo_part_hash_3
-> Seq Scan on hypo_part_hash hypo_part_hash_4
-> Seq Scan on hypo_part_hash hypo_part_hash_5
-> Seq Scan on hypo_part_hash hypo_part_hash_6
-> Seq Scan on hypo_part_hash hypo_part_hash_7
-> Seq Scan on hypo_part_hash hypo_part_hash_8
-> Seq Scan on hypo_part_hash hypo_part_hash_9
(11 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash WHERE id = 42;
QUERY PLAN
---------------------------------------------------
Append
-> Seq Scan on hypo_part_hash hypo_part_hash_4
Filter: (id = 42)
(3 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash WHERE id < 15000;
QUERY PLAN
---------------------------------------------------
Append
-> Seq Scan on hypo_part_hash hypo_part_hash_0
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_1
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_2
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_3
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_4
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_5
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_6
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_7
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_8
Filter: (id < 15000)
-> Seq Scan on hypo_part_hash hypo_part_hash_9
Filter: (id < 15000)
(21 rows)
-- 4.4. Multi level range
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
QUERY PLAN
-----------------------------------------------------------
Append
-> Seq Scan on hypo_part_multi hypo_part_multi_1_q1_a
-> Seq Scan on hypo_part_multi hypo_part_multi_1_q1_b
-> Seq Scan on hypo_part_multi hypo_part_multi_1_q2
-> Seq Scan on hypo_part_multi hypo_part_multi_1_q3
-> Seq Scan on hypo_part_multi hypo_part_multi_1_q4
-> Seq Scan on hypo_part_multi hypo_part_multi_1_def
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q2
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q3
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q4
-> Seq Scan on hypo_part_multi hypo_part_multi_2_def
-> Seq Scan on hypo_part_multi hypo_part_multi_3_q1
-> Seq Scan on hypo_part_multi hypo_part_multi_3_q2
-> Seq Scan on hypo_part_multi hypo_part_multi_3_q3
-> Seq Scan on hypo_part_multi hypo_part_multi_3_q4
-> Seq Scan on hypo_part_multi hypo_part_multi_3_def
-> Seq Scan on hypo_part_multi hypo_part_multi_def_q1
-> Seq Scan on hypo_part_multi hypo_part_multi_def_q2
-> Seq Scan on hypo_part_multi hypo_part_multi_def_q3
-> Seq Scan on hypo_part_multi hypo_part_multi_def_q4
-> Seq Scan on hypo_part_multi hypo_part_multi_def_def
(22 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi WHERE dpt = 2;
QUERY PLAN
---------------------------------------------------------
Append
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q2
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q3
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q4
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_def
Filter: (dpt = 2)
(11 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi WHERE dt >= '2015-01-05' AND dt < '2015-01-10';
QUERY PLAN
----------------------------------------------------------------------------
Append
-> Seq Scan on hypo_part_multi hypo_part_multi_1_q1_a
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on hypo_part_multi hypo_part_multi_1_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on hypo_part_multi hypo_part_multi_2_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on hypo_part_multi hypo_part_multi_3_q1
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on hypo_part_multi hypo_part_multi_3_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on hypo_part_multi hypo_part_multi_def_q1
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
-> Seq Scan on hypo_part_multi hypo_part_multi_def_def
Filter: ((dt >= '01-05-2015'::date) AND (dt < '01-10-2015'::date))
(17 rows)
-- Join queries
-- ------------
-- Simple joins
-- ------------
-- 5.1. Real tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
QUERY PLAN
-----------------------------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
-> Append
-> Seq Scan on part_range_1_10000 t2
-> Seq Scan on part_range_10000_20000 t2_1
-> Seq Scan on part_range_20000_30000 t2_2
-> Hash
-> Append
-> Seq Scan on part_range_1_10000 t1
Filter: (id < 15000)
-> Seq Scan on part_range_10000_20000 t1_1
Filter: (id < 15000)
(12 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_list t1, part_list t2 WHERE t1.id_key = t2.id_key and t1.id_key < 5;
QUERY PLAN
---------------------------------------------------------
Hash Join
Hash Cond: (t2.id_key = t1.id_key)
-> Append
-> Seq Scan on part_list_1_2_3 t2
-> Seq Scan on part_list_4_5_6_8_10 t2_1
-> Seq Scan on part_list_7_9 t2_2
-> Hash
-> Append
-> Seq Scan on part_list_1_2_3 t1
Filter: (id_key < 5)
-> Seq Scan on part_list_4_5_6_8_10 t1_1
Filter: (id_key < 5)
(12 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, part_hash t2 WHERE t1.id = t2.id;
QUERY PLAN
------------------------------------------------
Hash Join
Hash Cond: (t1.id = t2.id)
-> Append
-> Seq Scan on part_hash_0 t1
-> Seq Scan on part_hash_1 t1_1
-> Seq Scan on part_hash_2 t1_2
-> Seq Scan on part_hash_3 t1_3
-> Seq Scan on part_hash_4 t1_4
-> Seq Scan on part_hash_5 t1_5
-> Seq Scan on part_hash_6 t1_6
-> Seq Scan on part_hash_7 t1_7
-> Seq Scan on part_hash_8 t1_8
-> Seq Scan on part_hash_9 t1_9
-> Hash
-> Append
-> Seq Scan on part_hash_0 t2
-> Seq Scan on part_hash_1 t2_1
-> Seq Scan on part_hash_2 t2_2
-> Seq Scan on part_hash_3 t2_3
-> Seq Scan on part_hash_4 t2_4
-> Seq Scan on part_hash_5 t2_5
-> Seq Scan on part_hash_6 t2_6
-> Seq Scan on part_hash_7 t2_7
-> Seq Scan on part_hash_8 t2_8
-> Seq Scan on part_hash_9 t2_9
(25 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi t1, part_multi t2 WHERE t1.dpt = t2.dpt and t1.dpt = 2;
QUERY PLAN
-----------------------------------------------------
Nested Loop
-> Append
-> Seq Scan on part_multi_2_q1 t1
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q2 t1_1
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q3 t1_2
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q4 t1_3
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_def t1_4
Filter: (dpt = 2)
-> Materialize
-> Append
-> Seq Scan on part_multi_2_q1 t2
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q2 t2_1
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q3 t2_2
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q4 t2_3
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_def t2_4
Filter: (dpt = 2)
(24 rows)
-- 5.2. Hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
QUERY PLAN
---------------------------------------------------------------------------
Hash Join
Hash Cond: (hypo_part_range_1_10000_1.id = hypo_part_range_1_10000.id)
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000_1
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000_1
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
-> Hash
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id < 15000)
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id < 15000)
(12 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list t1, hypo_part_list t2 WHERE t1.id_key = t2.id_key and t1.id_key < 5;
QUERY PLAN
----------------------------------------------------------------------------
Hash Join
Hash Cond: (hypo_part_list_1_2_3_1.id_key = hypo_part_list_1_2_3.id_key)
-> Append
-> Seq Scan on hypo_part_list hypo_part_list_1_2_3_1
-> Seq Scan on hypo_part_list hypo_part_list_4_5_6_8_10_1
-> Seq Scan on hypo_part_list hypo_part_list_7_9
-> Hash
-> Append
-> Seq Scan on hypo_part_list hypo_part_list_1_2_3
Filter: (id_key < 5)
-> Seq Scan on hypo_part_list hypo_part_list_4_5_6_8_10
Filter: (id_key < 5)
(12 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
QUERY PLAN
-----------------------------------------------------------------
Hash Join
Hash Cond: (hypo_part_hash_0.id = hypo_part_hash_0_1.id)
-> Append
-> Seq Scan on hypo_part_hash hypo_part_hash_0
-> Seq Scan on hypo_part_hash hypo_part_hash_1
-> Seq Scan on hypo_part_hash hypo_part_hash_2
-> Seq Scan on hypo_part_hash hypo_part_hash_3
-> Seq Scan on hypo_part_hash hypo_part_hash_4
-> Seq Scan on hypo_part_hash hypo_part_hash_5
-> Seq Scan on hypo_part_hash hypo_part_hash_6
-> Seq Scan on hypo_part_hash hypo_part_hash_7
-> Seq Scan on hypo_part_hash hypo_part_hash_8
-> Seq Scan on hypo_part_hash hypo_part_hash_9
-> Hash
-> Append
-> Seq Scan on hypo_part_hash hypo_part_hash_0_1
-> Seq Scan on hypo_part_hash hypo_part_hash_1_1
-> Seq Scan on hypo_part_hash hypo_part_hash_2_1
-> Seq Scan on hypo_part_hash hypo_part_hash_3_1
-> Seq Scan on hypo_part_hash hypo_part_hash_4_1
-> Seq Scan on hypo_part_hash hypo_part_hash_5_1
-> Seq Scan on hypo_part_hash hypo_part_hash_6_1
-> Seq Scan on hypo_part_hash hypo_part_hash_7_1
-> Seq Scan on hypo_part_hash hypo_part_hash_8_1
-> Seq Scan on hypo_part_hash hypo_part_hash_9_1
(25 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi t1, hypo_part_multi t2 WHERE t1.dpt = t2.dpt and t1.dpt = 2;
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop
-> Append
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q2
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q3
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q4
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_def
Filter: (dpt = 2)
-> Materialize
-> Append
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1_1
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q2_1
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q3_1
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q4_1
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_def_1
Filter: (dpt = 2)
(24 rows)
-- 5.3. Real tables and hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
QUERY PLAN
---------------------------------------------------------------------
Hash Join
Hash Cond: (hypo_part_range_1_10000.id = t1.id)
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
-> Hash
-> Append
-> Seq Scan on part_range_1_10000 t1
Filter: (id < 15000)
-> Seq Scan on part_range_10000_20000 t1_1
Filter: (id < 15000)
(12 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_list t1, hypo_part_list t2 WHERE t1.id_key = t2.id_key and t1.id_key < 5;
QUERY PLAN
------------------------------------------------------------------
Hash Join
Hash Cond: (hypo_part_list_1_2_3.id_key = t1.id_key)
-> Append
-> Seq Scan on hypo_part_list hypo_part_list_1_2_3
-> Seq Scan on hypo_part_list hypo_part_list_4_5_6_8_10
-> Seq Scan on hypo_part_list hypo_part_list_7_9
-> Hash
-> Append
-> Seq Scan on part_list_1_2_3 t1
Filter: (id_key < 5)
-> Seq Scan on part_list_4_5_6_8_10 t1_1
Filter: (id_key < 5)
(12 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
QUERY PLAN
---------------------------------------------------------------
Hash Join
Hash Cond: (t1.id = hypo_part_hash_0.id)
-> Append
-> Seq Scan on part_hash_0 t1
-> Seq Scan on part_hash_1 t1_1
-> Seq Scan on part_hash_2 t1_2
-> Seq Scan on part_hash_3 t1_3
-> Seq Scan on part_hash_4 t1_4
-> Seq Scan on part_hash_5 t1_5
-> Seq Scan on part_hash_6 t1_6
-> Seq Scan on part_hash_7 t1_7
-> Seq Scan on part_hash_8 t1_8
-> Seq Scan on part_hash_9 t1_9
-> Hash
-> Append
-> Seq Scan on hypo_part_hash hypo_part_hash_0
-> Seq Scan on hypo_part_hash hypo_part_hash_1
-> Seq Scan on hypo_part_hash hypo_part_hash_2
-> Seq Scan on hypo_part_hash hypo_part_hash_3
-> Seq Scan on hypo_part_hash hypo_part_hash_4
-> Seq Scan on hypo_part_hash hypo_part_hash_5
-> Seq Scan on hypo_part_hash hypo_part_hash_6
-> Seq Scan on hypo_part_hash hypo_part_hash_7
-> Seq Scan on hypo_part_hash hypo_part_hash_8
-> Seq Scan on hypo_part_hash hypo_part_hash_9
(25 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi t1, hypo_part_multi t2 WHERE t1.dpt = t2.dpt and t1.dpt = 2;
QUERY PLAN
---------------------------------------------------------------------
Nested Loop
-> Append
-> Seq Scan on part_multi_2_q1 t1
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q2 t1_1
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q3 t1_2
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_q4 t1_3
Filter: (dpt = 2)
-> Seq Scan on part_multi_2_def t1_4
Filter: (dpt = 2)
-> Materialize
-> Append
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q2
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q3
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q4
Filter: (dpt = 2)
-> Seq Scan on hypo_part_multi hypo_part_multi_2_def
Filter: (dpt = 2)
(24 rows)
-- Partitionwise joins
-- -------------------
-- enable partitionwise join
-- -------------------------
SET enable_partitionwise_join to true;
-- 6.1. Real tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
QUERY PLAN
-----------------------------------------------------------
Append
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on part_range_1_10000 t1
Filter: (id < 15000)
-> Hash
-> Seq Scan on part_range_1_10000 t2
-> Hash Join
Hash Cond: (t2_1.id = t1_1.id)
-> Seq Scan on part_range_10000_20000 t2_1
-> Hash
-> Seq Scan on part_range_10000_20000 t1_1
Filter: (id < 15000)
(13 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, part_hash t2 WHERE t1.id = t2.id;
QUERY PLAN
------------------------------------------------
Append
-> Hash Join
Hash Cond: (t1.id = t2.id)
-> Seq Scan on part_hash_0 t1
-> Hash
-> Seq Scan on part_hash_0 t2
-> Hash Join
Hash Cond: (t1_1.id = t2_1.id)
-> Seq Scan on part_hash_1 t1_1
-> Hash
-> Seq Scan on part_hash_1 t2_1
-> Hash Join
Hash Cond: (t1_2.id = t2_2.id)
-> Seq Scan on part_hash_2 t1_2
-> Hash
-> Seq Scan on part_hash_2 t2_2
-> Hash Join
Hash Cond: (t1_3.id = t2_3.id)
-> Seq Scan on part_hash_3 t1_3
-> Hash
-> Seq Scan on part_hash_3 t2_3
-> Hash Join
Hash Cond: (t1_4.id = t2_4.id)
-> Seq Scan on part_hash_4 t1_4
-> Hash
-> Seq Scan on part_hash_4 t2_4
-> Hash Join
Hash Cond: (t1_5.id = t2_5.id)
-> Seq Scan on part_hash_5 t1_5
-> Hash
-> Seq Scan on part_hash_5 t2_5
-> Hash Join
Hash Cond: (t1_6.id = t2_6.id)
-> Seq Scan on part_hash_6 t1_6
-> Hash
-> Seq Scan on part_hash_6 t2_6
-> Hash Join
Hash Cond: (t1_7.id = t2_7.id)
-> Seq Scan on part_hash_7 t1_7
-> Hash
-> Seq Scan on part_hash_7 t2_7
-> Hash Join
Hash Cond: (t1_8.id = t2_8.id)
-> Seq Scan on part_hash_8 t1_8
-> Hash
-> Seq Scan on part_hash_8 t2_8
-> Hash Join
Hash Cond: (t1_9.id = t2_9.id)
-> Seq Scan on part_hash_9 t1_9
-> Hash
-> Seq Scan on part_hash_9 t2_9
(51 rows)
-- 6.2. Hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
QUERY PLAN
----------------------------------------------------------------------------------------
Append
-> Hash Join
Hash Cond: (hypo_part_range_1_10000.id = hypo_part_range_1_10000_1.id)
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id < 15000)
-> Hash
-> Seq Scan on hypo_part_range hypo_part_range_1_10000_1
-> Hash Join
Hash Cond: (hypo_part_range_10000_20000_1.id = hypo_part_range_10000_20000.id)
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000_1
-> Hash
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id < 15000)
(13 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
QUERY PLAN
------------------------------------------------------------------
Append
-> Hash Join
Hash Cond: (hypo_part_hash_0.id = hypo_part_hash_0_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_0
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_0_1
-> Hash Join
Hash Cond: (hypo_part_hash_1.id = hypo_part_hash_1_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_1
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_1_1
-> Hash Join
Hash Cond: (hypo_part_hash_2.id = hypo_part_hash_2_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_2
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_2_1
-> Hash Join
Hash Cond: (hypo_part_hash_3.id = hypo_part_hash_3_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_3
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_3_1
-> Hash Join
Hash Cond: (hypo_part_hash_4.id = hypo_part_hash_4_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_4
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_4_1
-> Hash Join
Hash Cond: (hypo_part_hash_5.id = hypo_part_hash_5_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_5
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_5_1
-> Hash Join
Hash Cond: (hypo_part_hash_6.id = hypo_part_hash_6_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_6
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_6_1
-> Hash Join
Hash Cond: (hypo_part_hash_7.id = hypo_part_hash_7_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_7
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_7_1
-> Hash Join
Hash Cond: (hypo_part_hash_8.id = hypo_part_hash_8_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_8
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_8_1
-> Hash Join
Hash Cond: (hypo_part_hash_9.id = hypo_part_hash_9_1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_9
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_9_1
(51 rows)
-- 6.3. Real tables and hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
QUERY PLAN
-----------------------------------------------------------------------
Append
-> Hash Join
Hash Cond: (t1.id = hypo_part_range_1_10000.id)
-> Seq Scan on part_range_1_10000 t1
Filter: (id < 15000)
-> Hash
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
-> Hash Join
Hash Cond: (hypo_part_range_10000_20000.id = t1_1.id)
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
-> Hash
-> Seq Scan on part_range_10000_20000 t1_1
Filter: (id < 15000)
(13 rows)
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
QUERY PLAN
---------------------------------------------------------------
Append
-> Hash Join
Hash Cond: (hypo_part_hash_0.id = t1.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_0
-> Hash
-> Seq Scan on part_hash_0 t1
-> Hash Join
Hash Cond: (t1_1.id = hypo_part_hash_1.id)
-> Seq Scan on part_hash_1 t1_1
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_1
-> Hash Join
Hash Cond: (t1_2.id = hypo_part_hash_2.id)
-> Seq Scan on part_hash_2 t1_2
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_2
-> Hash Join
Hash Cond: (hypo_part_hash_3.id = t1_3.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_3
-> Hash
-> Seq Scan on part_hash_3 t1_3
-> Hash Join
Hash Cond: (t1_4.id = hypo_part_hash_4.id)
-> Seq Scan on part_hash_4 t1_4
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_4
-> Hash Join
Hash Cond: (hypo_part_hash_5.id = t1_5.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_5
-> Hash
-> Seq Scan on part_hash_5 t1_5
-> Hash Join
Hash Cond: (t1_6.id = hypo_part_hash_6.id)
-> Seq Scan on part_hash_6 t1_6
-> Hash
-> Seq Scan on hypo_part_hash hypo_part_hash_6
-> Hash Join
Hash Cond: (hypo_part_hash_7.id = t1_7.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_7
-> Hash
-> Seq Scan on part_hash_7 t1_7
-> Hash Join
Hash Cond: (hypo_part_hash_8.id = t1_8.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_8
-> Hash
-> Seq Scan on part_hash_8 t1_8
-> Hash Join
Hash Cond: (hypo_part_hash_9.id = t1_9.id)
-> Seq Scan on hypo_part_hash hypo_part_hash_9
-> Hash
-> Seq Scan on part_hash_9 t1_9
(51 rows)
-- Runtime partition pruning
-- -------------------------
-- 6B.0 Add function to easily simulate runtime partitioning
CREATE FUNCTION hypo_number_one() RETURNS integer AS
$_$
BEGIN
RETURN 1;
END;
$_$ LANGUAGE plpgsql STABLE;
-- 6B.1 disable runtime partition pruning
SET enable_partition_pruning to false;
-- 6B.2 simple case
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id = hypo_number_one();
QUERY PLAN
------------------------------------------
Append
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_20000_30000
Filter: (id = hypo_number_one())
(7 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = hypo_number_one();
QUERY PLAN
---------------------------------------------------------------
Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(7 rows)
-- 6B.3 CTE
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
--------------------------------------------------
CTE Scan on s
CTE s
-> Append
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_20000_30000
Filter: (id = hypo_number_one())
(9 rows)
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
-----------------------------------------------------------------------
CTE Scan on s
CTE s
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(9 rows)
-- 6B.4 CTE with partitioning underneath an union
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
--------------------------------------------------------
CTE Scan on s
CTE s
-> Append
-> Result
-> Append
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_20000_30000
Filter: (id = hypo_number_one())
(11 rows)
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
-----------------------------------------------------------------------------
CTE Scan on s
CTE s
-> Append
-> Result
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(11 rows)
-- 6B.5 InitPlan
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s);
QUERY PLAN
----------------------------------------------------------
Result
InitPlan 2 (returns $1)
-> CTE Scan on s
CTE s
-> Append
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on part_range_20000_30000
Filter: (id = hypo_number_one())
(11 rows)
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s);
QUERY PLAN
-------------------------------------------------------------------------------
Result
InitPlan 2 (returns $1)
-> CTE Scan on s
CTE s
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(11 rows)
-- 6B.6 enable runtime partition pruning
SET enable_partition_pruning to true;
-- 6B.7 simple case
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id = hypo_number_one();
QUERY PLAN
------------------------------------------
Append
Subplans Removed: 2
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
(4 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = hypo_number_one();
QUERY PLAN
---------------------------------------------------------------
Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(7 rows)
-- 6B.8 CTE
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
--------------------------------------------------
CTE Scan on s
CTE s
-> Append
Subplans Removed: 2
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
(6 rows)
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
-----------------------------------------------------------------------
CTE Scan on s
CTE s
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(9 rows)
-- 6B.9 CTE with partitioning underneath an union
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
--------------------------------------------------------
CTE Scan on s
CTE s
-> Append
-> Result
-> Append
Subplans Removed: 2
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
(8 rows)
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
QUERY PLAN
-----------------------------------------------------------------------------
CTE Scan on s
CTE s
-> Append
-> Result
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(11 rows)
-- 6B.10 InitPlan
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s);
QUERY PLAN
----------------------------------------------------------
Result
InitPlan 2 (returns $1)
-> CTE Scan on s
CTE s
-> Append
Subplans Removed: 2
-> Seq Scan on part_range_1_10000
Filter: (id = hypo_number_one())
(8 rows)
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s);
QUERY PLAN
-------------------------------------------------------------------------------
Result
InitPlan 2 (returns $1)
-> CTE Scan on s
CTE s
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_10000_20000
Filter: (id = hypo_number_one())
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id = hypo_number_one())
(11 rows)
-- Tests for sanity checks
-- =======================
-- 7.1 Duplicate name
CREATE TABLE part_range_1_10000 PARTITION OF part_range FOR VALUES FROM (1) TO (10000);
ERROR: relation "part_range_1_10000" already exists
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
ERROR: hypopg: hypothetical table hypo_part_range_1_10000 already exists
-- 7.2 Overlapping range bounds
CREATE TABLE part_range_1_10000_dup PARTITION OF part_range FOR VALUES FROM (1) TO (10000);
ERROR: partition "part_range_1_10000_dup" would overlap partition "part_range_1_10000"
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000_dup', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
ERROR: hypopg: partition "hypo_part_range_1_10000_dup" would overlap partition "hypo_part_range_1_10000"
-- 7.3 Overlapping list bounds
CREATE TABLE part_list_1_2_3_dup PARTITION OF part_list FOR VALUES IN (1, 2, 3);
ERROR: partition "part_list_1_2_3_dup" would overlap partition "part_list_1_2_3"
SELECT tablename FROM hypopg_add_partition('hypo_part_list_1_2_3_dup', 'PARTITION OF hypo_part_list FOR VALUES IN (1, 2, 3)');
ERROR: hypopg: partition "hypo_part_list_1_2_3_dup" would overlap partition "hypo_part_list_1_2_3"
-- 7.4 Overlapping hash bounds
CREATE TABLE part_hash_0 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0);
ERROR: relation "part_hash_0" already exists
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_0_dup', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0)');
ERROR: hypopg: partition "hypo_part_hash_0_dup" would overlap partition "hypo_part_hash_0"
-- 7.5 Overlapping range bounds, subpartition
CREATE TABLE part_multi_1_q1_a PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$);
ERROR: relation "part_multi_1_q1_a" already exists
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_a_dup', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$)');
ERROR: hypopg: partition "hypo_part_multi_1_q1_a_dup" would overlap partition "hypo_part_multi_1_q1_a"
-- relcache callback test
-- ======================
SELECT tablename FROM hypopg_table() WHERE tablename LIKE 'hypo_part_range%' ORDER BY tablename COLLATE "C";
tablename
-----------------------------
hypo_part_range
hypo_part_range_10000_20000
hypo_part_range_1_10000
hypo_part_range_20000_30000
(4 rows)
DROP TABLE hypo_part_range;
SELECT tablename FROM hypopg_table() WHERE tablename LIKE 'hypo_part_range%' ORDER BY tablename COLLATE "C";
tablename
-----------
(0 rows)
CREATE TABLE hypo_part_range (id integer, val text);
INSERT INTO hypo_part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
SELECT * FROM hypopg_partition_table('hypo_part_range', 'PARTITION BY RANGE (id)');
hypopg_partition_table
------------------------
t
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_range_10000_20000', 'PARTITION OF hypo_part_range FOR VALUES FROM (10000) TO (20000)');
tablename
-----------------------------
hypo_part_range_10000_20000
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_range_20000_30000', 'PARTITION OF hypo_part_range FOR VALUES FROM (20000) TO (30000)');
tablename
-----------------------------
hypo_part_range_20000_30000
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
tablename
-------------------------
hypo_part_range_1_10000
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = 42;
QUERY PLAN
-----------------------------------------------------------
Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = 42)
(3 rows)
-- no UPDATE/DELETE test
-- =====================
-- 8.1 simple UPDATE and DELETE on hypothetically partitioned table
EXPLAIN (COSTS OFF) UPDATE hypo_part_range set id = id;
ERROR: hypopg: UPDATE and DELETE on hypothetically partitioned tables are not supported
EXPLAIN DELETE FROM hypo_part_range WHERE id = 42;
ERROR: hypopg: UPDATE and DELETE on hypothetically partitioned tables are not supported
-- 8.2 UPDATE and DELETE on hypothetically partitioned table inside CTE
EXPLAIN (COSTS OFF) WITH s AS (UPDATE hypo_part_range set id = id returning *) SELECT 1;
ERROR: hypopg: UPDATE and DELETE on hypothetically partitioned tables are not supported
EXPLAIN (COSTS OFF) WITH s AS (DELETE FROM hypo_part_range WHERE id = 42 returning *) SELECT 1;
ERROR: hypopg: UPDATE and DELETE on hypothetically partitioned tables are not supported
-- 8.3 UPDATE and DELETE involving hypothetically partitioned table, but on
-- regular tables
CREATE TABLE foo(id integer);
-- 8.4 UPDATE on non hypothetically partitioned table but having a hypothetically
-- partitioned table joined
EXPLAIN (COSTS OFF) WITH s AS (UPDATE foo SET id = 0 from hypo_part_range WHERE foo.id = hypo_part_range.id AND hypo_part_range.id > 25000 RETURNING *) SELECT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------
Result
CTE s
-> Update on foo
-> Hash Join
Hash Cond: (foo.id = hypo_part_range_20000_30000.id)
-> Seq Scan on foo
-> Hash
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_20000_30000
Filter: (id > 25000)
(10 rows)
-- 8.5 same but with real table
EXPLAIN (COSTS OFF) WITH s AS (UPDATE foo SET id = 0 from part_range WHERE foo.id = part_range.id AND part_range.id > 25000 RETURNING *) SELECT 1;
QUERY PLAN
--------------------------------------------------------------------
Result
CTE s
-> Update on foo
-> Hash Join
Hash Cond: (foo.id = part_range_20000_30000.id)
-> Seq Scan on foo
-> Hash
-> Append
-> Seq Scan on part_range_20000_30000
Filter: (id > 25000)
(10 rows)
-- 8.6 DELETE on non hypothetically partitioned table but having a
-- hypothetically partitioned table joined
EXPLAIN (COSTS OFF) WITH s AS (DELETE FROM foo USING hypo_part_range WHERE foo.id = hypo_part_range.id AND hypo_part_range.id = 42 RETURNING *) SELECT 1;
QUERY PLAN
-------------------------------------------------------------------------------
Result
CTE s
-> Delete on foo
-> Nested Loop
-> Append
-> Seq Scan on hypo_part_range hypo_part_range_1_10000
Filter: (id = 42)
-> Seq Scan on foo
Filter: (id = 42)
(9 rows)
-- 8.7 same but with real table
EXPLAIN (COSTS OFF) WITH s AS (DELETE FROM foo USING part_range WHERE foo.id = part_range.id AND part_range.id = 42 RETURNING *) SELECT 1;
QUERY PLAN
----------------------------------------------------------
Result
CTE s
-> Delete on foo
-> Nested Loop
-> Append
-> Seq Scan on part_range_1_10000
Filter: (id = 42)
-> Seq Scan on foo
Filter: (id = 42)
(9 rows)
-- childless partitioning
-- ======================
SELECT * FROM hypopg_reset();
hypopg_reset
--------------
(1 row)
DROP TABLE part_multi;
CREATE TABLE part_multi(dpt smallint, dt date, val text) PARTITION BY LIST (dpt);
SELECT * FROM hypopg_partition_table('hypo_part_multi', 'PARTITION BY LIST (dpt)');
hypopg_partition_table
------------------------
t
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
QUERY PLAN
--------------------------
Result
One-Time Filter: false
(2 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
QUERY PLAN
--------------------------
Result
One-Time Filter: false
(2 rows)
CREATE TABLE part_multi_2 PARTITION OF part_multi FOR VALUES IN (2) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_2_q1 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_1 PARTITION OF part_multi FOR VALUES IN (1) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_1_q1 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$) PARTITION BY RANGE (dt);
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2', 'PARTITION OF hypo_part_multi FOR VALUES IN (2)', 'PARTITION BY RANGE(dt)');
tablename
-------------------
hypo_part_multi_2
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q1', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
tablename
----------------------
hypo_part_multi_2_q1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1', 'PARTITION OF hypo_part_multi FOR VALUES IN (1)', 'PARTITION BY RANGE(dt)');
tablename
-------------------
hypo_part_multi_1
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)','PARTITION BY RANGE (dt)');
tablename
----------------------
hypo_part_multi_1_q1
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
QUERY PLAN
-----------------------------------
Append
-> Seq Scan on part_multi_2_q1
(2 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
QUERY PLAN
--------------------------------------------------------
Append
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
(2 rows)
-- removing hypothetical partitions
-- ================================
CREATE TABLE part_multi_1_q1_b PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_1_q1_a PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$);
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_b', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$)');
tablename
------------------------
hypo_part_multi_1_q1_b
(1 row)
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_a', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$)');
tablename
------------------------
hypo_part_multi_1_q1_a
(1 row)
DROP TABLE part_multi_1_q1_a;
SELECT hypopg_drop_table(relid) FROM hypopg_table() WHERE tablename = 'hypo_part_multi_1_q1_a';
hypopg_drop_table
-------------------
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
QUERY PLAN
-------------------------------------
Append
-> Seq Scan on part_multi_1_q1_b
-> Seq Scan on part_multi_2_q1
(3 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
QUERY PLAN
----------------------------------------------------------
Append
-> Seq Scan on hypo_part_multi hypo_part_multi_1_q1_b
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
(3 rows)
DROP TABLE part_multi_1;
SELECT hypopg_drop_table(relid) FROM hypopg_table() WHERE tablename = 'hypo_part_multi_1';
hypopg_drop_table
-------------------
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
QUERY PLAN
-----------------------------------
Append
-> Seq Scan on part_multi_2_q1
(2 rows)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
QUERY PLAN
--------------------------------------------------------
Append
-> Seq Scan on hypo_part_multi hypo_part_multi_2_q1
(2 rows)
SELECT hypopg_drop_table(relid) FROM hypopg_table() WHERE tablename = 'hypo_part_multi';
hypopg_drop_table
-------------------
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
QUERY PLAN
-----------------------------
Seq Scan on hypo_part_multi
(1 row)
SELECT hypopg_drop_table(oid) FROM pg_class WHERE relname = 'pg_class';
ERROR: hypopg: Oid 1259 is not a hypothetically partitioned table
SELECT hypopg_drop_table(1);
ERROR: hypopg: Oid 1 is not a hypothetically partitioned table
Reference in a new issue View git blame Copy permalink