1 SET search_path TO public;
2 CREATE EXTENSION pg_hint_plan;
4 CREATE TABLE t1 (id int PRIMARY KEY, val int);
5 CREATE TABLE t2 (id int PRIMARY KEY, val int);
6 CREATE TABLE t3 (id int PRIMARY KEY, val int);
7 CREATE TABLE t4 (id int PRIMARY KEY, val int);
8 CREATE TABLE t5 (id int PRIMARY KEY, val int);
9 CREATE TABLE p1 (id int PRIMARY KEY, val int);
10 CREATE TABLE p1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 100)) INHERITS(p1);
11 NOTICE: merging column "id" with inherited definition
12 NOTICE: merging column "val" with inherited definition
13 CREATE TABLE p1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 100 AND id <= 200)) INHERITS(p1);
14 NOTICE: merging column "id" with inherited definition
15 NOTICE: merging column "val" with inherited definition
16 CREATE TABLE p1_c3 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 300)) INHERITS(p1);
17 NOTICE: merging column "id" with inherited definition
18 NOTICE: merging column "val" with inherited definition
19 CREATE TABLE p1_c4 (LIKE p1 INCLUDING ALL, CHECK (id > 300)) INHERITS(p1);
20 NOTICE: merging column "id" with inherited definition
21 NOTICE: merging column "val" with inherited definition
22 CREATE TABLE p1_c1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 50)) INHERITS(p1_c1);
23 NOTICE: merging column "id" with inherited definition
24 NOTICE: merging column "val" with inherited definition
25 CREATE TABLE p1_c1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 50 AND id <= 100)) INHERITS(p1_c1);
26 NOTICE: merging column "id" with inherited definition
27 NOTICE: merging column "val" with inherited definition
28 CREATE TABLE p1_c3_c1 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 250)) INHERITS(p1_c3);
29 NOTICE: merging column "id" with inherited definition
30 NOTICE: merging column "val" with inherited definition
31 CREATE TABLE p1_c3_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 250 AND id <= 300)) INHERITS(p1_c3);
32 NOTICE: merging column "id" with inherited definition
33 NOTICE: merging column "val" with inherited definition
34 CREATE TABLE p2 (id int PRIMARY KEY, val text);
35 CREATE INDEX p2_id_val_idx ON p2 (id, val);
36 CREATE UNIQUE INDEX p2_val_idx ON p2 (val);
37 CREATE INDEX p2_ununi_id_val_idx ON p2 (val);
38 CREATE INDEX p2_val_idx_1 ON p2 USING hash (val);
39 WARNING: hash indexes are not WAL-logged and their use is discouraged
40 CREATE INDEX p2_val_id_idx ON p2 (val, id);
41 CREATE INDEX p2_val_idx2 ON p2 (val COLLATE "ja_JP");
42 CREATE INDEX p2_val_idx3 ON p2 (val varchar_ops);
43 CREATE INDEX p2_val_idx4 ON p2 (val DESC NULLS LAST);
44 CREATE INDEX p2_val_idx5 ON p2 (val NULLS FIRST);
45 CREATE INDEX p2_expr ON p2 ((val < '120'));
46 CREATE INDEX p2_expr2 ON p2 ((id * 2 < 120));
47 CREATE INDEX p2_val_idx6 ON p2 (val) WHERE val >= '50' AND val < '51';
48 CREATE INDEX p2_val_idx7 ON p2 (val) WHERE id < 120;
49 CREATE TABLE p2_c1 (LIKE p2 INCLUDING ALL, CHECK (id <= 100)) INHERITS(p2);
50 NOTICE: merging column "id" with inherited definition
51 NOTICE: merging column "val" with inherited definition
52 WARNING: hash indexes are not WAL-logged and their use is discouraged
53 CREATE TABLE p2_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 100 AND id <= 200)) INHERITS(p2);
54 NOTICE: merging column "id" with inherited definition
55 NOTICE: merging column "val" with inherited definition
56 WARNING: hash indexes are not WAL-logged and their use is discouraged
57 CREATE TABLE p2_c3 (LIKE p2 INCLUDING ALL, CHECK (id > 200 AND id <= 300)) INHERITS(p2);
58 NOTICE: merging column "id" with inherited definition
59 NOTICE: merging column "val" with inherited definition
60 WARNING: hash indexes are not WAL-logged and their use is discouraged
61 CREATE TABLE p2_c4 (LIKE p2 INCLUDING ALL, CHECK (id > 300)) INHERITS(p2);
62 NOTICE: merging column "id" with inherited definition
63 NOTICE: merging column "val" with inherited definition
64 WARNING: hash indexes are not WAL-logged and their use is discouraged
65 CREATE TABLE p2_c1_c1 (LIKE p2 INCLUDING ALL, CHECK (id <= 50)) INHERITS(p2_c1);
66 NOTICE: merging column "id" with inherited definition
67 NOTICE: merging column "val" with inherited definition
68 WARNING: hash indexes are not WAL-logged and their use is discouraged
69 CREATE TABLE p2_c1_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 50 AND id <= 100)) INHERITS(p2_c1);
70 NOTICE: merging column "id" with inherited definition
71 NOTICE: merging column "val" with inherited definition
72 WARNING: hash indexes are not WAL-logged and their use is discouraged
73 CREATE TABLE p2_c3_c1 (LIKE p2 INCLUDING ALL, CHECK (id > 200 AND id <= 250)) INHERITS(p2_c3);
74 NOTICE: merging column "id" with inherited definition
75 NOTICE: merging column "val" with inherited definition
76 WARNING: hash indexes are not WAL-logged and their use is discouraged
77 CREATE TABLE p2_c3_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 250 AND id <= 300)) INHERITS(p2_c3);
78 NOTICE: merging column "id" with inherited definition
79 NOTICE: merging column "val" with inherited definition
80 WARNING: hash indexes are not WAL-logged and their use is discouraged
81 CREATE TABLE s0.t1 (id int PRIMARY KEY, val int);
82 INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
83 INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;
84 INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;
85 INSERT INTO t4 SELECT i, i FROM (SELECT generate_series(1, 10) i) t;
86 INSERT INTO t5 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
87 INSERT INTO p1_c1_c1 SELECT i, i % 100 FROM (SELECT generate_series(1, 50) i) t;
88 INSERT INTO p1_c1_c2 SELECT i, i % 100 FROM (SELECT generate_series(51, 100) i) t;
89 INSERT INTO p1_c2 SELECT i, i % 100 FROM (SELECT generate_series(101, 200) i) t;
90 INSERT INTO p1_c3_c1 SELECT i, i % 100 FROM (SELECT generate_series(201, 250) i) t;
91 INSERT INTO p1_c3_c2 SELECT i, i % 100 FROM (SELECT generate_series(251, 300) i) t;
92 INSERT INTO p1_c4 SELECT i, i % 100 FROM (SELECT generate_series(301, 400) i) t;
93 INSERT INTO p2_c1_c1 SELECT i, i % 100 FROM (SELECT generate_series(1, 50) i) t;
94 INSERT INTO p2_c1_c2 SELECT i, i % 100 FROM (SELECT generate_series(51, 100) i) t;
95 INSERT INTO p2_c2 SELECT i, i % 100 FROM (SELECT generate_series(101, 200) i) t;
96 INSERT INTO p2_c3_c1 SELECT i, i % 100 FROM (SELECT generate_series(201, 250) i) t;
97 INSERT INTO p2_c3_c2 SELECT i, i % 100 FROM (SELECT generate_series(251, 300) i) t;
98 INSERT INTO p2_c4 SELECT i, i % 100 FROM (SELECT generate_series(301, 400) i) t;
99 CREATE INDEX t1_val ON t1 (val);
100 CREATE INDEX t2_val ON t2 (val);
101 CREATE INDEX t5_id1 ON t5 (id);
102 CREATE INDEX t5_id2 ON t5 (id);
103 CREATE INDEX t5_id3 ON t5 (id);
104 CREATE INDEX t5_val ON t5 (val);
105 DROP INDEX p2_c4_val_id_idx;
106 CREATE INDEX p2_id2_val ON p2 (id, id, val);
107 CREATE INDEX p2_c1_id2_val ON p2_c1 (id, id, val);
108 CREATE INDEX p2_c2_id2_val ON p2_c2 (id, id, val);
109 CREATE INDEX p2_val2_id ON p2 (val, id, val);
110 CREATE INDEX t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ON t5 (id);
111 CREATE INDEX p1_val1 ON p1 (val);
112 CREATE INDEX p1_val2 ON p1 (val);
113 CREATE INDEX p1_val3 ON p1 (val);
114 CREATE INDEX p1_c1_val1 ON p1_c1 (val);
115 CREATE INDEX p1_c1_val2 ON p1_c1 (val);
116 CREATE INDEX p1_c1_val3 ON p1_c1 (val);
117 CREATE INDEX p1_c1_c1_val1 ON p1_c1_c1 (val);
118 CREATE INDEX p1_c1_c1_val2 ON p1_c1_c1 (val);
119 CREATE INDEX p1_c1_c1_val3 ON p1_c1_c1 (val);
120 CREATE INDEX p1_c1_c2_val1 ON p1_c1_c2 (val);
121 CREATE INDEX p1_c1_c2_val2 ON p1_c1_c2 (val);
122 CREATE INDEX p1_c1_c2_val3 ON p1_c1_c2 (val);
123 CREATE INDEX p1_c2_val1 ON p1_c2 (val);
124 CREATE INDEX p1_c2_val2 ON p1_c2 (val);
125 CREATE INDEX p1_c2_val3 ON p1_c2 (val);
126 CREATE INDEX p1_c3_val1 ON p1_c3 (val);
127 CREATE INDEX p1_c3_val2 ON p1_c3 (val);
128 CREATE INDEX p1_c3_val3 ON p1_c3 (val);
129 CREATE INDEX p1_c3_c1_val1 ON p1_c3_c1 (val);
130 CREATE INDEX p1_c3_c1_val2 ON p1_c3_c1 (val);
131 CREATE INDEX p1_c3_c1_val3 ON p1_c3_c1 (val);
132 CREATE INDEX p1_c3_c2_val1 ON p1_c3_c2 (val);
133 CREATE INDEX p1_c3_c2_val2 ON p1_c3_c2 (val);
134 CREATE INDEX p1_c3_c2_val3 ON p1_c3_c2 (val);
135 CREATE INDEX p1_c4_val1 ON p1_c4 (val);
136 CREATE INDEX p1_c4_val2 ON p1_c4 (val);
137 CREATE INDEX p1_c4_val3 ON p1_c4 (val);
147 CREATE VIEW v1 AS SELECT id, val FROM t1;
148 CREATE VIEW v2 AS SELECT t1.id t1_id, t1.val t1_val, t2.id t2_id, t2.val t2_val FROM t1, t2 WHERE t1.id = t2.id;
149 CREATE VIEW v3 AS SELECT t_1.id t1_id, t_1.val t1_val, t_2.id t2_id, t_2.val t2_val FROM t1 t_1, t2 t_2 WHERE t_1.id = t_2.id;
150 CREATE VIEW v4 AS SELECT v_2.t1_id, t_3.id FROM v2 v_2, t3 t_3 WHERE v_2.t1_id = t_3.id;
152 * The following GUC parameters need the setting of the default value to
153 * succeed in regression test.
155 /* Fix auto-tunable parameters */
156 ALTER SYSTEM SET effective_cache_size TO 16384;
157 SELECT pg_reload_conf();
163 SET effective_cache_size TO 16384;
164 CREATE VIEW settings AS
165 SELECT name, setting, category
167 WHERE category LIKE 'Query Tuning%'
168 OR name = 'client_min_messages'
169 ORDER BY category, name;
170 SELECT * FROM settings;
171 name | setting | category
172 ----------------------------+-----------+---------------------------------------------
173 geqo | on | Query Tuning / Genetic Query Optimizer
174 geqo_effort | 5 | Query Tuning / Genetic Query Optimizer
175 geqo_generations | 0 | Query Tuning / Genetic Query Optimizer
176 geqo_pool_size | 0 | Query Tuning / Genetic Query Optimizer
177 geqo_seed | 0 | Query Tuning / Genetic Query Optimizer
178 geqo_selection_bias | 2 | Query Tuning / Genetic Query Optimizer
179 geqo_threshold | 12 | Query Tuning / Genetic Query Optimizer
180 constraint_exclusion | partition | Query Tuning / Other Planner Options
181 cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options
182 default_statistics_target | 100 | Query Tuning / Other Planner Options
183 force_parallel_mode | off | Query Tuning / Other Planner Options
184 from_collapse_limit | 8 | Query Tuning / Other Planner Options
185 join_collapse_limit | 8 | Query Tuning / Other Planner Options
186 cpu_index_tuple_cost | 0.005 | Query Tuning / Planner Cost Constants
187 cpu_operator_cost | 0.0025 | Query Tuning / Planner Cost Constants
188 cpu_tuple_cost | 0.01 | Query Tuning / Planner Cost Constants
189 effective_cache_size | 16384 | Query Tuning / Planner Cost Constants
190 min_parallel_relation_size | 1024 | Query Tuning / Planner Cost Constants
191 parallel_setup_cost | 1000 | Query Tuning / Planner Cost Constants
192 parallel_tuple_cost | 0.1 | Query Tuning / Planner Cost Constants
193 random_page_cost | 4 | Query Tuning / Planner Cost Constants
194 seq_page_cost | 1 | Query Tuning / Planner Cost Constants
195 enable_bitmapscan | on | Query Tuning / Planner Method Configuration
196 enable_hashagg | on | Query Tuning / Planner Method Configuration
197 enable_hashjoin | on | Query Tuning / Planner Method Configuration
198 enable_indexonlyscan | on | Query Tuning / Planner Method Configuration
199 enable_indexscan | on | Query Tuning / Planner Method Configuration
200 enable_material | on | Query Tuning / Planner Method Configuration
201 enable_mergejoin | on | Query Tuning / Planner Method Configuration
202 enable_nestloop | on | Query Tuning / Planner Method Configuration
203 enable_seqscan | on | Query Tuning / Planner Method Configuration
204 enable_sort | on | Query Tuning / Planner Method Configuration
205 enable_tidscan | on | Query Tuning / Planner Method Configuration
206 client_min_messages | notice | Reporting and Logging / When to Log