From be384a43a7547e53f279562b22a5afed47d6ee50 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 10 Oct 2017 11:20:13 +0900 Subject: [PATCH] Added a regtest for partitioned table. Added a minimal regression test that checks this works also on partitioned tables. Currently partitioned tables are handled in almost the same way with inheritance tables so this would be enough. --- expected/ut-J.out | 91 ++++++++++++++++++++++++++++++++++++++++++++++++++++ expected/ut-init.out | 12 +++++++ sql/ut-J.sql | 11 +++++++ sql/ut-init.sql | 14 +++++++- 4 files changed, 127 insertions(+), 1 deletion(-) diff --git a/expected/ut-J.out b/expected/ut-J.out index fdb8246..85cf692 100644 --- a/expected/ut-J.out +++ b/expected/ut-J.out @@ -4378,6 +4378,97 @@ error hint: (19 rows) ---- +---- No. J-3-2-2 join partitioned tables +---- +EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1; + QUERY PLAN +-------------------------------------- + Hash Join + Hash Cond: (p2.c1 = pt1_c1.c1) + -> Append + -> Seq Scan on p2 + -> Seq Scan on p2c1 + -> Seq Scan on p2c2 + -> Seq Scan on p2c3 + -> Seq Scan on p2c1c1 + -> Seq Scan on p2c1c2 + -> Seq Scan on p2c2c1 + -> Seq Scan on p2c2c2 + -> Seq Scan on p2c3c1 + -> Seq Scan on p2c3c2 + -> Hash + -> Append + -> Seq Scan on pt1_c1 + -> Seq Scan on pt1_c2 + -> Seq Scan on pt1_c3 +(18 rows) + +/*+MergeJoin(pt1 p2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1; +LOG: pg_hint_plan: +used hint: +MergeJoin(p2 pt1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------- + Merge Join + Merge Cond: (pt1_c1.c1 = p2.c1) + -> Sort + Sort Key: pt1_c1.c1 + -> Append + -> Seq Scan on pt1_c1 + -> Seq Scan on pt1_c2 + -> Seq Scan on pt1_c3 + -> Sort + Sort Key: p2.c1 + -> Append + -> Seq Scan on p2 + -> Seq Scan on p2c1 + -> Seq Scan on p2c2 + -> Seq Scan on p2c3 + -> Seq Scan on p2c1c1 + -> Seq Scan on p2c1c2 + -> Seq Scan on p2c2c1 + -> Seq Scan on p2c2c2 + -> Seq Scan on p2c3c1 + -> Seq Scan on p2c3c2 +(21 rows) + +/*+MergeJoin(pt1_c1 p2c1)*/ /* will ignored */ +EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1; +LOG: pg_hint_plan: +used hint: +not used hint: +MergeJoin(p2c1 pt1_c1) +duplication hint: +error hint: + + QUERY PLAN +-------------------------------------- + Hash Join + Hash Cond: (p2.c1 = pt1_c1.c1) + -> Append + -> Seq Scan on p2 + -> Seq Scan on p2c1 + -> Seq Scan on p2c2 + -> Seq Scan on p2c3 + -> Seq Scan on p2c1c1 + -> Seq Scan on p2c1c2 + -> Seq Scan on p2c2c1 + -> Seq Scan on p2c2c2 + -> Seq Scan on p2c3c1 + -> Seq Scan on p2c3c2 + -> Hash + -> Append + -> Seq Scan on pt1_c1 + -> Seq Scan on pt1_c2 + -> Seq Scan on pt1_c3 +(18 rows) + +---- ---- No. J-3-3 conflict join method hint ---- EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; diff --git a/expected/ut-init.out b/expected/ut-init.out index b968616..ba6b2c9 100644 --- a/expected/ut-init.out +++ b/expected/ut-init.out @@ -103,6 +103,10 @@ CREATE TABLE s1.r1_ (LIKE s1.t1); CREATE TABLE s1.r2_ (LIKE s1.t1); CREATE TABLE s1.r3_ (LIKE s1.t1); CREATE TABLE s1.ti1 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1), UNIQUE (c2)); +CREATE TABLE s1.pt1 (c1 int, c2 int, c3 int, c4 int) PARTITION BY RANGE (c1); +CREATE TABLE s1.pt1_c1 PARTITION OF s1.pt1 FOR VALUES FROM (MINVALUE) TO (101); +CREATE TABLE s1.pt1_c2 PARTITION OF s1.pt1 FOR VALUES FROM (101) TO (201); +CREATE TABLE s1.pt1_c3 PARTITION OF s1.pt1 FOR VALUES FROM (201) TO (MAXVALUE); CREATE UNLOGGED TABLE s1.ul1 (LIKE s1.t1 INCLUDING ALL); INSERT INTO s1.t1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t; INSERT INTO s1.t2 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t; @@ -117,6 +121,7 @@ INSERT INTO s1.p2c2c2 SELECT i, i, i % 10, i FROM (SELECT generate_series(151, 2 INSERT INTO s1.p2c3c1 SELECT i, i, i % 10, i FROM (SELECT generate_series(201, 250) i) t; INSERT INTO s1.p2c3c2 SELECT i, i, i % 10, i FROM (SELECT generate_series(251, 300) i) t; INSERT INTO s1.ti1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t; +INSERT INTO s1.pt1 SELECT i, i, i % 10, i FROM (SELECT generate_series(0, 300) i) t; CREATE INDEX t1_i ON s1.t1 (c3); CREATE INDEX t1_i1 ON s1.t1 (c1); CREATE INDEX t2_i1 ON s1.t2 (c1); @@ -150,6 +155,12 @@ CREATE INDEX ti1_pred ON s1.ti1 (lower(c4)); CREATE UNIQUE INDEX ti1_uniq ON s1.ti1 (c1); CREATE INDEX ti1_multi ON s1.ti1 (c1, c2, c3, c4); CREATE INDEX ti1_ts ON s1.ti1 USING gin(to_tsvector('english', c4)); +CREATE INDEX pt1_c1_c2_i ON s1.pt1_c1(c2); +CREATE INDEX pt1_c1_c3_i ON s1.pt1_c1(c3); +CREATE INDEX pt1_c2_c2_i ON s1.pt1_c2(c2); +CREATE INDEX pt1_c2_c3_i ON s1.pt1_c2(c3); +CREATE INDEX pt1_c3_c2_i ON s1.pt1_c3(c2); +CREATE INDEX pt1_c3_c3_i ON s1.pt1_c3(c3); CREATE VIEW s1.v1 AS SELECT v1t1.c1, v1t1.c2, v1t1.c3, v1t1.c4 FROM s1.t1 v1t1; CREATE VIEW s1.v1_ AS SELECT v1t1_.c1, v1t1_.c2, v1t1_.c3, v1t1_.c4 FROM s1.t1 v1t1_; CREATE VIEW s1.v2 AS SELECT v2t1.c1, v2t1.c2, v2t1.c3, v2t1.c4 FROM s1.t1 v2t1 JOIN s1.t2 v2t2 ON(v2t1.c1 = v2t2.c1); @@ -169,6 +180,7 @@ ANALYZE s1.p2c2c2; ANALYZE s1.p2c3c1; ANALYZE s1.p2c3c2; ANALYZE s1.ti1; +ANALYZE s1.pt1; CREATE FUNCTION s1.f1 () RETURNS s1.t1 AS $$ VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3') $$ LANGUAGE sql; diff --git a/sql/ut-J.sql b/sql/ut-J.sql index 095b53b..a52116f 100644 --- a/sql/ut-J.sql +++ b/sql/ut-J.sql @@ -743,6 +743,17 @@ EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1; EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1; ---- +---- No. J-3-2-2 join partitioned tables +---- +EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1; + +/*+MergeJoin(pt1 p2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1; + +/*+MergeJoin(pt1_c1 p2c1)*/ /* will ignored */ +EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1; + +---- ---- No. J-3-3 conflict join method hint ---- EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; diff --git a/sql/ut-init.sql b/sql/ut-init.sql index 4f24f26..154d02b 100644 --- a/sql/ut-init.sql +++ b/sql/ut-init.sql @@ -44,6 +44,7 @@ CREATE TABLE s1.p2c2c1 (LIKE s1.p2c2 INCLUDING ALL, CHECK (c1 > 100 AND c1 <= 15 CREATE TABLE s1.p2c2c2 (LIKE s1.p2c2 INCLUDING ALL, CHECK (c1 > 150 AND c1 <= 200)) INHERITS(s1.p2c2); CREATE TABLE s1.p2c3c1 (LIKE s1.p2c3 INCLUDING ALL, CHECK (c1 > 200 AND c1 <= 250)) INHERITS(s1.p2c3); CREATE TABLE s1.p2c3c2 (LIKE s1.p2c3 INCLUDING ALL, CHECK (c1 > 250)) INHERITS(s1.p2c3); + CREATE TABLE s1.r1 (LIKE s1.t1); CREATE TABLE s1.r2 (LIKE s1.t1); CREATE TABLE s1.r3 (LIKE s1.t1); @@ -53,6 +54,10 @@ CREATE TABLE s1.r1_ (LIKE s1.t1); CREATE TABLE s1.r2_ (LIKE s1.t1); CREATE TABLE s1.r3_ (LIKE s1.t1); CREATE TABLE s1.ti1 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1), UNIQUE (c2)); +CREATE TABLE s1.pt1 (c1 int, c2 int, c3 int, c4 int) PARTITION BY RANGE (c1); +CREATE TABLE s1.pt1_c1 PARTITION OF s1.pt1 FOR VALUES FROM (MINVALUE) TO (101); +CREATE TABLE s1.pt1_c2 PARTITION OF s1.pt1 FOR VALUES FROM (101) TO (201); +CREATE TABLE s1.pt1_c3 PARTITION OF s1.pt1 FOR VALUES FROM (201) TO (MAXVALUE); CREATE UNLOGGED TABLE s1.ul1 (LIKE s1.t1 INCLUDING ALL); INSERT INTO s1.t1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t; @@ -68,7 +73,7 @@ INSERT INTO s1.p2c2c2 SELECT i, i, i % 10, i FROM (SELECT generate_series(151, 2 INSERT INTO s1.p2c3c1 SELECT i, i, i % 10, i FROM (SELECT generate_series(201, 250) i) t; INSERT INTO s1.p2c3c2 SELECT i, i, i % 10, i FROM (SELECT generate_series(251, 300) i) t; INSERT INTO s1.ti1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t; - +INSERT INTO s1.pt1 SELECT i, i, i % 10, i FROM (SELECT generate_series(0, 300) i) t; CREATE INDEX t1_i ON s1.t1 (c3); CREATE INDEX t1_i1 ON s1.t1 (c1); @@ -103,6 +108,12 @@ CREATE INDEX ti1_pred ON s1.ti1 (lower(c4)); CREATE UNIQUE INDEX ti1_uniq ON s1.ti1 (c1); CREATE INDEX ti1_multi ON s1.ti1 (c1, c2, c3, c4); CREATE INDEX ti1_ts ON s1.ti1 USING gin(to_tsvector('english', c4)); +CREATE INDEX pt1_c1_c2_i ON s1.pt1_c1(c2); +CREATE INDEX pt1_c1_c3_i ON s1.pt1_c1(c3); +CREATE INDEX pt1_c2_c2_i ON s1.pt1_c2(c2); +CREATE INDEX pt1_c2_c3_i ON s1.pt1_c2(c3); +CREATE INDEX pt1_c3_c2_i ON s1.pt1_c3(c2); +CREATE INDEX pt1_c3_c3_i ON s1.pt1_c3(c3); CREATE VIEW s1.v1 AS SELECT v1t1.c1, v1t1.c2, v1t1.c3, v1t1.c4 FROM s1.t1 v1t1; CREATE VIEW s1.v1_ AS SELECT v1t1_.c1, v1t1_.c2, v1t1_.c3, v1t1_.c4 FROM s1.t1 v1t1_; @@ -124,6 +135,7 @@ ANALYZE s1.p2c2c2; ANALYZE s1.p2c3c1; ANALYZE s1.p2c3c2; ANALYZE s1.ti1; +ANALYZE s1.pt1; CREATE FUNCTION s1.f1 () RETURNS s1.t1 AS $$ VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3') -- 2.11.0