• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: コミット

firtst release


コミットメタ情報

リビジョンbe384a43a7547e53f279562b22a5afed47d6ee50 (tree)
日時2017-10-10 11:20:13
作者Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
コミッターKyotaro Horiguchi

ログメッセージ

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.

変更サマリ

差分

--- a/expected/ut-J.out
+++ b/expected/ut-J.out
@@ -4378,6 +4378,97 @@ error hint:
43784378 (19 rows)
43794379
43804380 ----
4381+---- No. J-3-2-2 join partitioned tables
4382+----
4383+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
4384+ QUERY PLAN
4385+--------------------------------------
4386+ Hash Join
4387+ Hash Cond: (p2.c1 = pt1_c1.c1)
4388+ -> Append
4389+ -> Seq Scan on p2
4390+ -> Seq Scan on p2c1
4391+ -> Seq Scan on p2c2
4392+ -> Seq Scan on p2c3
4393+ -> Seq Scan on p2c1c1
4394+ -> Seq Scan on p2c1c2
4395+ -> Seq Scan on p2c2c1
4396+ -> Seq Scan on p2c2c2
4397+ -> Seq Scan on p2c3c1
4398+ -> Seq Scan on p2c3c2
4399+ -> Hash
4400+ -> Append
4401+ -> Seq Scan on pt1_c1
4402+ -> Seq Scan on pt1_c2
4403+ -> Seq Scan on pt1_c3
4404+(18 rows)
4405+
4406+/*+MergeJoin(pt1 p2)*/
4407+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
4408+LOG: pg_hint_plan:
4409+used hint:
4410+MergeJoin(p2 pt1)
4411+not used hint:
4412+duplication hint:
4413+error hint:
4414+
4415+ QUERY PLAN
4416+--------------------------------------
4417+ Merge Join
4418+ Merge Cond: (pt1_c1.c1 = p2.c1)
4419+ -> Sort
4420+ Sort Key: pt1_c1.c1
4421+ -> Append
4422+ -> Seq Scan on pt1_c1
4423+ -> Seq Scan on pt1_c2
4424+ -> Seq Scan on pt1_c3
4425+ -> Sort
4426+ Sort Key: p2.c1
4427+ -> Append
4428+ -> Seq Scan on p2
4429+ -> Seq Scan on p2c1
4430+ -> Seq Scan on p2c2
4431+ -> Seq Scan on p2c3
4432+ -> Seq Scan on p2c1c1
4433+ -> Seq Scan on p2c1c2
4434+ -> Seq Scan on p2c2c1
4435+ -> Seq Scan on p2c2c2
4436+ -> Seq Scan on p2c3c1
4437+ -> Seq Scan on p2c3c2
4438+(21 rows)
4439+
4440+/*+MergeJoin(pt1_c1 p2c1)*/ /* will ignored */
4441+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
4442+LOG: pg_hint_plan:
4443+used hint:
4444+not used hint:
4445+MergeJoin(p2c1 pt1_c1)
4446+duplication hint:
4447+error hint:
4448+
4449+ QUERY PLAN
4450+--------------------------------------
4451+ Hash Join
4452+ Hash Cond: (p2.c1 = pt1_c1.c1)
4453+ -> Append
4454+ -> Seq Scan on p2
4455+ -> Seq Scan on p2c1
4456+ -> Seq Scan on p2c2
4457+ -> Seq Scan on p2c3
4458+ -> Seq Scan on p2c1c1
4459+ -> Seq Scan on p2c1c2
4460+ -> Seq Scan on p2c2c1
4461+ -> Seq Scan on p2c2c2
4462+ -> Seq Scan on p2c3c1
4463+ -> Seq Scan on p2c3c2
4464+ -> Hash
4465+ -> Append
4466+ -> Seq Scan on pt1_c1
4467+ -> Seq Scan on pt1_c2
4468+ -> Seq Scan on pt1_c3
4469+(18 rows)
4470+
4471+----
43814472 ---- No. J-3-3 conflict join method hint
43824473 ----
43834474 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
--- a/expected/ut-init.out
+++ b/expected/ut-init.out
@@ -103,6 +103,10 @@ CREATE TABLE s1.r1_ (LIKE s1.t1);
103103 CREATE TABLE s1.r2_ (LIKE s1.t1);
104104 CREATE TABLE s1.r3_ (LIKE s1.t1);
105105 CREATE TABLE s1.ti1 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1), UNIQUE (c2));
106+CREATE TABLE s1.pt1 (c1 int, c2 int, c3 int, c4 int) PARTITION BY RANGE (c1);
107+CREATE TABLE s1.pt1_c1 PARTITION OF s1.pt1 FOR VALUES FROM (MINVALUE) TO (101);
108+CREATE TABLE s1.pt1_c2 PARTITION OF s1.pt1 FOR VALUES FROM (101) TO (201);
109+CREATE TABLE s1.pt1_c3 PARTITION OF s1.pt1 FOR VALUES FROM (201) TO (MAXVALUE);
106110 CREATE UNLOGGED TABLE s1.ul1 (LIKE s1.t1 INCLUDING ALL);
107111 INSERT INTO s1.t1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t;
108112 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
117121 INSERT INTO s1.p2c3c1 SELECT i, i, i % 10, i FROM (SELECT generate_series(201, 250) i) t;
118122 INSERT INTO s1.p2c3c2 SELECT i, i, i % 10, i FROM (SELECT generate_series(251, 300) i) t;
119123 INSERT INTO s1.ti1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t;
124+INSERT INTO s1.pt1 SELECT i, i, i % 10, i FROM (SELECT generate_series(0, 300) i) t;
120125 CREATE INDEX t1_i ON s1.t1 (c3);
121126 CREATE INDEX t1_i1 ON s1.t1 (c1);
122127 CREATE INDEX t2_i1 ON s1.t2 (c1);
@@ -150,6 +155,12 @@ CREATE INDEX ti1_pred ON s1.ti1 (lower(c4));
150155 CREATE UNIQUE INDEX ti1_uniq ON s1.ti1 (c1);
151156 CREATE INDEX ti1_multi ON s1.ti1 (c1, c2, c3, c4);
152157 CREATE INDEX ti1_ts ON s1.ti1 USING gin(to_tsvector('english', c4));
158+CREATE INDEX pt1_c1_c2_i ON s1.pt1_c1(c2);
159+CREATE INDEX pt1_c1_c3_i ON s1.pt1_c1(c3);
160+CREATE INDEX pt1_c2_c2_i ON s1.pt1_c2(c2);
161+CREATE INDEX pt1_c2_c3_i ON s1.pt1_c2(c3);
162+CREATE INDEX pt1_c3_c2_i ON s1.pt1_c3(c2);
163+CREATE INDEX pt1_c3_c3_i ON s1.pt1_c3(c3);
153164 CREATE VIEW s1.v1 AS SELECT v1t1.c1, v1t1.c2, v1t1.c3, v1t1.c4 FROM s1.t1 v1t1;
154165 CREATE VIEW s1.v1_ AS SELECT v1t1_.c1, v1t1_.c2, v1t1_.c3, v1t1_.c4 FROM s1.t1 v1t1_;
155166 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;
169180 ANALYZE s1.p2c3c1;
170181 ANALYZE s1.p2c3c2;
171182 ANALYZE s1.ti1;
183+ANALYZE s1.pt1;
172184 CREATE FUNCTION s1.f1 () RETURNS s1.t1 AS $$
173185 VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')
174186 $$ LANGUAGE sql;
--- 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;
743743 EXPLAIN (COSTS false) SELECT * FROM s1.p1, s1.p2 WHERE p1.c1 = p2.c1;
744744
745745 ----
746+---- No. J-3-2-2 join partitioned tables
747+----
748+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
749+
750+/*+MergeJoin(pt1 p2)*/
751+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
752+
753+/*+MergeJoin(pt1_c1 p2c1)*/ /* will ignored */
754+EXPLAIN (COSTS false) SELECT * FROM s1.pt1, s1.p2 WHERE pt1.c1 = p2.c1;
755+
756+----
746757 ---- No. J-3-3 conflict join method hint
747758 ----
748759 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
--- 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
4444 CREATE TABLE s1.p2c2c2 (LIKE s1.p2c2 INCLUDING ALL, CHECK (c1 > 150 AND c1 <= 200)) INHERITS(s1.p2c2);
4545 CREATE TABLE s1.p2c3c1 (LIKE s1.p2c3 INCLUDING ALL, CHECK (c1 > 200 AND c1 <= 250)) INHERITS(s1.p2c3);
4646 CREATE TABLE s1.p2c3c2 (LIKE s1.p2c3 INCLUDING ALL, CHECK (c1 > 250)) INHERITS(s1.p2c3);
47+
4748 CREATE TABLE s1.r1 (LIKE s1.t1);
4849 CREATE TABLE s1.r2 (LIKE s1.t1);
4950 CREATE TABLE s1.r3 (LIKE s1.t1);
@@ -53,6 +54,10 @@ CREATE TABLE s1.r1_ (LIKE s1.t1);
5354 CREATE TABLE s1.r2_ (LIKE s1.t1);
5455 CREATE TABLE s1.r3_ (LIKE s1.t1);
5556 CREATE TABLE s1.ti1 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1), UNIQUE (c2));
57+CREATE TABLE s1.pt1 (c1 int, c2 int, c3 int, c4 int) PARTITION BY RANGE (c1);
58+CREATE TABLE s1.pt1_c1 PARTITION OF s1.pt1 FOR VALUES FROM (MINVALUE) TO (101);
59+CREATE TABLE s1.pt1_c2 PARTITION OF s1.pt1 FOR VALUES FROM (101) TO (201);
60+CREATE TABLE s1.pt1_c3 PARTITION OF s1.pt1 FOR VALUES FROM (201) TO (MAXVALUE);
5661 CREATE UNLOGGED TABLE s1.ul1 (LIKE s1.t1 INCLUDING ALL);
5762
5863 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
6873 INSERT INTO s1.p2c3c1 SELECT i, i, i % 10, i FROM (SELECT generate_series(201, 250) i) t;
6974 INSERT INTO s1.p2c3c2 SELECT i, i, i % 10, i FROM (SELECT generate_series(251, 300) i) t;
7075 INSERT INTO s1.ti1 SELECT i, i, i % 100, i FROM (SELECT generate_series(1, 1000) i) t;
71-
76+INSERT INTO s1.pt1 SELECT i, i, i % 10, i FROM (SELECT generate_series(0, 300) i) t;
7277
7378 CREATE INDEX t1_i ON s1.t1 (c3);
7479 CREATE INDEX t1_i1 ON s1.t1 (c1);
@@ -103,6 +108,12 @@ CREATE INDEX ti1_pred ON s1.ti1 (lower(c4));
103108 CREATE UNIQUE INDEX ti1_uniq ON s1.ti1 (c1);
104109 CREATE INDEX ti1_multi ON s1.ti1 (c1, c2, c3, c4);
105110 CREATE INDEX ti1_ts ON s1.ti1 USING gin(to_tsvector('english', c4));
111+CREATE INDEX pt1_c1_c2_i ON s1.pt1_c1(c2);
112+CREATE INDEX pt1_c1_c3_i ON s1.pt1_c1(c3);
113+CREATE INDEX pt1_c2_c2_i ON s1.pt1_c2(c2);
114+CREATE INDEX pt1_c2_c3_i ON s1.pt1_c2(c3);
115+CREATE INDEX pt1_c3_c2_i ON s1.pt1_c3(c2);
116+CREATE INDEX pt1_c3_c3_i ON s1.pt1_c3(c3);
106117
107118 CREATE VIEW s1.v1 AS SELECT v1t1.c1, v1t1.c2, v1t1.c3, v1t1.c4 FROM s1.t1 v1t1;
108119 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;
124135 ANALYZE s1.p2c3c1;
125136 ANALYZE s1.p2c3c2;
126137 ANALYZE s1.ti1;
138+ANALYZE s1.pt1;
127139
128140 CREATE FUNCTION s1.f1 () RETURNS s1.t1 AS $$
129141 VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')
旧リポジトリブラウザで表示