Added a regtest for partitioned table.

[pghintplan/pg_hint_plan.git] / expected / ut-init.out

diff --git a/expected/ut-init.out b/expected/ut-init.out
index a19ef0c..ba6b2c9 100644 (file)
--- a/expected/ut-init.out
+++ b/expected/ut-init.out
@@ -21,97 +21,79 @@ CREATE ROLE normal_user
 CREATE SCHEMA s1;
 CREATE SCHEMA s2;
 CREATE TABLE s1.t1 (c1 int, c2 int, c3 int, c4 text, PRIMARY KEY (c1));
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
 CREATE TABLE s1.t2 (LIKE s1.t1 INCLUDING ALL);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
 CREATE TABLE s1.t3 (LIKE s1.t1 INCLUDING ALL);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for table "t3"
 CREATE TABLE s1.t4 (LIKE s1.t1 INCLUDING ALL);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t4_pkey" for table "t4"
 CREATE TABLE s2.t1 (LIKE s1.t1 INCLUDING ALL);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
 CREATE TABLE s1.p1 (LIKE s1.t1 INCLUDING ALL);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p1_pkey" for table "p1"
+CREATE UNIQUE INDEX p1_parent ON s1.p1 USING btree (c4 COLLATE "ja_JP" varchar_ops ASC NULLS LAST, (c1 * 2 < 100)) WHERE c1 < 10;
 CREATE TABLE s1.p2 (LIKE s1.t1 INCLUDING ALL);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2_pkey" for table "p2"
 CREATE TABLE s1.p1c1 (LIKE s1.p1 INCLUDING ALL, CHECK (c1 <= 100)) INHERITS(s1.p1);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p1c1_pkey" for table "p1c1"
 CREATE TABLE s1.p1c2 (LIKE s1.p1 INCLUDING ALL, CHECK (c1 > 100 AND c1 <= 200)) INHERITS(s1.p1);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p1c2_pkey" for table "p1c2"
 CREATE TABLE s1.p1c3 (LIKE s1.p1 INCLUDING ALL, CHECK (c1 > 200)) INHERITS(s1.p1);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p1c3_pkey" for table "p1c3"
 CREATE TABLE s1.p2c1 (LIKE s1.p2 INCLUDING ALL, CHECK (c1 <= 100)) INHERITS(s1.p2);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c1_pkey" for table "p2c1"
 CREATE TABLE s1.p2c2 (LIKE s1.p2 INCLUDING ALL, CHECK (c1 > 100 AND c1 <= 200)) INHERITS(s1.p2);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c2_pkey" for table "p2c2"
 CREATE TABLE s1.p2c3 (LIKE s1.p2 INCLUDING ALL, CHECK (c1 > 200)) INHERITS(s1.p2);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c3_pkey" for table "p2c3"
 CREATE TABLE s1.p2c1c1 (LIKE s1.p2c1 INCLUDING ALL, CHECK (c1 <= 50)) INHERITS(s1.p2c1);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
 NOTICE:  merging constraint "p2c1_c1_check" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c1c1_pkey" for table "p2c1c1"
 CREATE TABLE s1.p2c1c2 (LIKE s1.p2c1 INCLUDING ALL, CHECK (c1 > 50 AND c1 <= 100)) INHERITS(s1.p2c1);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
 NOTICE:  merging constraint "p2c1_c1_check" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c1c2_pkey" for table "p2c1c2"
 CREATE TABLE s1.p2c2c1 (LIKE s1.p2c2 INCLUDING ALL, CHECK (c1 > 100 AND c1 <= 150)) INHERITS(s1.p2c2);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
 NOTICE:  merging constraint "p2c2_c1_check" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c2c1_pkey" for table "p2c2c1"
 CREATE TABLE s1.p2c2c2 (LIKE s1.p2c2 INCLUDING ALL, CHECK (c1 > 150 AND c1 <= 200)) INHERITS(s1.p2c2);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
 NOTICE:  merging constraint "p2c2_c1_check" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c2c2_pkey" for table "p2c2c2"
 CREATE TABLE s1.p2c3c1 (LIKE s1.p2c3 INCLUDING ALL, CHECK (c1 > 200 AND c1 <= 250)) INHERITS(s1.p2c3);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
 NOTICE:  merging constraint "p2c3_c1_check" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c3c1_pkey" for table "p2c3c1"
 CREATE TABLE s1.p2c3c2 (LIKE s1.p2c3 INCLUDING ALL, CHECK (c1 > 250)) INHERITS(s1.p2c3);
 NOTICE:  merging column "c1" with inherited definition
 NOTICE:  merging column "c2" with inherited definition
 NOTICE:  merging column "c3" with inherited definition
 NOTICE:  merging column "c4" with inherited definition
 NOTICE:  merging constraint "p2c3_c1_check" with inherited definition
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "p2c3c2_pkey" for table "p2c3c2"
 CREATE TABLE s1.r1 (LIKE s1.t1);
 CREATE TABLE s1.r2 (LIKE s1.t1);
 CREATE TABLE s1.r3 (LIKE s1.t1);
@@ -121,10 +103,11 @@ 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));
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ti1_pkey" for table "ti1"
-NOTICE:  CREATE TABLE / UNIQUE will create implicit index "ti1_c2_key" for table "ti1"
+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);
-NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ul1_pkey" for table "ul1"
 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;
 INSERT INTO s2.t1 SELECT i, i, i % 10, i FROM (SELECT generate_series(1, 100) i) t;
@@ -138,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);
@@ -145,6 +129,7 @@ CREATE INDEX t3_i1 ON s1.t3 (c1);
 CREATE INDEX t4_i1 ON s1.t4 (c1);
 CREATE INDEX p1_i ON s1.p1 (c1);
 CREATE INDEX p2_i ON s1.p2 (c1);
+CREATE INDEX p1_i2 ON s1.p1 (c2);
 CREATE INDEX p1c1_i ON s1.p1c1 (c1);
 CREATE INDEX p1c2_i ON s1.p1c2 (c1);
 CREATE INDEX p1c3_i ON s1.p1c3 (c1);
@@ -170,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);
@@ -189,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;