forked from ossc-db/pg_hint_plan
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinit.out
232 lines (231 loc) · 13.1 KB
/
init.out
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
SET search_path TO public;
CREATE EXTENSION pg_hint_plan;
CREATE SCHEMA s0;
CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE t2 (id int PRIMARY KEY, val int);
CREATE TABLE t3 (id int PRIMARY KEY, val int);
CREATE TABLE t4 (id int PRIMARY KEY, val int);
CREATE TABLE t5 (id int PRIMARY KEY, val int);
CREATE TABLE p1 (id int PRIMARY KEY, val int);
CREATE TABLE p1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 100)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 100 AND id <= 200)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p1_c3 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 300)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p1_c4 (LIKE p1 INCLUDING ALL, CHECK (id > 300)) INHERITS(p1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p1_c1_c1 (LIKE p1 INCLUDING ALL, CHECK (id <= 50)) INHERITS(p1_c1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p1_c1_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 50 AND id <= 100)) INHERITS(p1_c1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p1_c3_c1 (LIKE p1 INCLUDING ALL, CHECK (id > 200 AND id <= 250)) INHERITS(p1_c3);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p1_c3_c2 (LIKE p1 INCLUDING ALL, CHECK (id > 250 AND id <= 300)) INHERITS(p1_c3);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2 (id int PRIMARY KEY, val text);
CREATE INDEX p2_id_val_idx ON p2 (id, val);
CREATE UNIQUE INDEX p2_val_idx ON p2 (val);
CREATE INDEX p2_ununi_id_val_idx ON p2 (val);
CREATE INDEX p2_val_idx_1 ON p2 USING hash (val);
CREATE INDEX p2_val_id_idx ON p2 (val, id);
CREATE INDEX p2_val_idx2 ON p2 (val COLLATE "C");
CREATE INDEX p2_val_idx3 ON p2 (val varchar_ops);
CREATE INDEX p2_val_idx4 ON p2 (val DESC NULLS LAST);
CREATE INDEX p2_val_idx5 ON p2 (val NULLS FIRST);
CREATE INDEX p2_expr ON p2 ((val < '120'));
CREATE INDEX p2_expr2 ON p2 ((id * 2 < 120));
CREATE INDEX p2_val_idx6 ON p2 (val) WHERE val >= '50' AND val < '51';
CREATE INDEX p2_val_idx7 ON p2 (val) WHERE id < 120;
CREATE TABLE p2_c1 (LIKE p2 INCLUDING ALL, CHECK (id <= 100)) INHERITS(p2);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 100 AND id <= 200)) INHERITS(p2);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2_c3 (LIKE p2 INCLUDING ALL, CHECK (id > 200 AND id <= 300)) INHERITS(p2);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2_c4 (LIKE p2 INCLUDING ALL, CHECK (id > 300)) INHERITS(p2);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2_c1_c1 (LIKE p2 INCLUDING ALL, CHECK (id <= 50)) INHERITS(p2_c1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2_c1_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 50 AND id <= 100)) INHERITS(p2_c1);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2_c3_c1 (LIKE p2 INCLUDING ALL, CHECK (id > 200 AND id <= 250)) INHERITS(p2_c3);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE p2_c3_c2 (LIKE p2 INCLUDING ALL, CHECK (id > 250 AND id <= 300)) INHERITS(p2_c3);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "val" with inherited definition
CREATE TABLE s0.t1 (id int PRIMARY KEY, val int);
INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;
INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;
INSERT INTO t4 SELECT i, i FROM (SELECT generate_series(1, 10) i) t;
INSERT INTO t5 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO p1_c1_c1 SELECT i, i % 100 FROM (SELECT generate_series(1, 50) i) t;
INSERT INTO p1_c1_c2 SELECT i, i % 100 FROM (SELECT generate_series(51, 100) i) t;
INSERT INTO p1_c2 SELECT i, i % 100 FROM (SELECT generate_series(101, 200) i) t;
INSERT INTO p1_c3_c1 SELECT i, i % 100 FROM (SELECT generate_series(201, 250) i) t;
INSERT INTO p1_c3_c2 SELECT i, i % 100 FROM (SELECT generate_series(251, 300) i) t;
INSERT INTO p1_c4 SELECT i, i % 100 FROM (SELECT generate_series(301, 400) i) t;
INSERT INTO p2_c1_c1 SELECT i, i % 100 FROM (SELECT generate_series(1, 50) i) t;
INSERT INTO p2_c1_c2 SELECT i, i % 100 FROM (SELECT generate_series(51, 100) i) t;
INSERT INTO p2_c2 SELECT i, i % 100 FROM (SELECT generate_series(101, 200) i) t;
INSERT INTO p2_c3_c1 SELECT i, i % 100 FROM (SELECT generate_series(201, 250) i) t;
INSERT INTO p2_c3_c2 SELECT i, i % 100 FROM (SELECT generate_series(251, 300) i) t;
INSERT INTO p2_c4 SELECT i, i % 100 FROM (SELECT generate_series(301, 400) i) t;
CREATE INDEX t1_val ON t1 (val);
CREATE INDEX t2_val ON t2 (val);
CREATE INDEX t5_id1 ON t5 (id);
CREATE INDEX t5_id2 ON t5 (id);
CREATE INDEX t5_id3 ON t5 (id);
CREATE INDEX t5_val ON t5 (val);
DROP INDEX p2_c4_val_id_idx;
CREATE INDEX p2_id2_val ON p2 (id, id, val);
CREATE INDEX p2_c1_id2_val ON p2_c1 (id, id, val);
CREATE INDEX p2_c2_id2_val ON p2_c2 (id, id, val);
CREATE INDEX p2_val2_id ON p2 (val, id, val);
CREATE INDEX t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ON t5 (id);
CREATE INDEX p1_val1 ON p1 (val);
CREATE INDEX p1_val2 ON p1 (val);
CREATE INDEX p1_val3 ON p1 (val);
CREATE INDEX p1_c1_val1 ON p1_c1 (val);
CREATE INDEX p1_c1_val2 ON p1_c1 (val);
CREATE INDEX p1_c1_val3 ON p1_c1 (val);
CREATE INDEX p1_c1_c1_val1 ON p1_c1_c1 (val);
CREATE INDEX p1_c1_c1_val2 ON p1_c1_c1 (val);
CREATE INDEX p1_c1_c1_val3 ON p1_c1_c1 (val);
CREATE INDEX p1_c1_c2_val1 ON p1_c1_c2 (val);
CREATE INDEX p1_c1_c2_val2 ON p1_c1_c2 (val);
CREATE INDEX p1_c1_c2_val3 ON p1_c1_c2 (val);
CREATE INDEX p1_c2_val1 ON p1_c2 (val);
CREATE INDEX p1_c2_val2 ON p1_c2 (val);
CREATE INDEX p1_c2_val3 ON p1_c2 (val);
CREATE INDEX p1_c3_val1 ON p1_c3 (val);
CREATE INDEX p1_c3_val2 ON p1_c3 (val);
CREATE INDEX p1_c3_val3 ON p1_c3 (val);
CREATE INDEX p1_c3_c1_val1 ON p1_c3_c1 (val);
CREATE INDEX p1_c3_c1_val2 ON p1_c3_c1 (val);
CREATE INDEX p1_c3_c1_val3 ON p1_c3_c1 (val);
CREATE INDEX p1_c3_c2_val1 ON p1_c3_c2 (val);
CREATE INDEX p1_c3_c2_val2 ON p1_c3_c2 (val);
CREATE INDEX p1_c3_c2_val3 ON p1_c3_c2 (val);
CREATE INDEX p1_c4_val1 ON p1_c4 (val);
CREATE INDEX p1_c4_val2 ON p1_c4 (val);
CREATE INDEX p1_c4_val3 ON p1_c4 (val);
ANALYZE t1;
ANALYZE t2;
ANALYZE t3;
ANALYZE t4;
ANALYZE t5;
ANALYZE p1;
ANALYZE p1_c1;
ANALYZE p1_c2;
ANALYZE p2;
CREATE VIEW v1 AS SELECT id, val FROM t1;
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;
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;
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;
/*
* Utility function to retrieve a query ID from a query.
*
* This wraps the input query within an EXPLAIN (VERBOSE, FORMAT json) and
* returns its query ID.
*/
CREATE FUNCTION get_query_id(text) RETURNS bigint
LANGUAGE plpgsql AS
$$
DECLARE
query text;
explain_output text;
query_id bigint;
BEGIN
query = 'EXPLAIN (VERBOSE, FORMAT json) ' || $1;
EXECUTE query INTO explain_output;
SELECT INTO query_id ((explain_output::jsonb)->0->'Query Identifier')::bigint;
return query_id;
END;
$$;
/*
* The following GUC parameters need the setting of the default value to
* succeed in regression test.
*/
SELECT current_database() AS datname \gset
/* Fix auto-tunable parameters */
ALTER DATABASE :"datname" SET effective_cache_size TO 16384;
SET effective_cache_size TO 16384;
CREATE VIEW settings AS
SELECT name, setting, category
FROM pg_settings
WHERE category LIKE 'Query Tuning%'
OR name = 'client_min_messages'
ORDER BY category, name;
SELECT * FROM settings;
name | setting | category
--------------------------------+-----------+-------------------------------------------------
client_min_messages | notice | Client Connection Defaults / Statement Behavior
geqo | on | Query Tuning / Genetic Query Optimizer
geqo_effort | 5 | Query Tuning / Genetic Query Optimizer
geqo_generations | 0 | Query Tuning / Genetic Query Optimizer
geqo_pool_size | 0 | Query Tuning / Genetic Query Optimizer
geqo_seed | 0 | Query Tuning / Genetic Query Optimizer
geqo_selection_bias | 2 | Query Tuning / Genetic Query Optimizer
geqo_threshold | 12 | Query Tuning / Genetic Query Optimizer
constraint_exclusion | partition | Query Tuning / Other Planner Options
cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options
default_statistics_target | 100 | Query Tuning / Other Planner Options
from_collapse_limit | 8 | Query Tuning / Other Planner Options
jit | on | Query Tuning / Other Planner Options
join_collapse_limit | 8 | Query Tuning / Other Planner Options
plan_cache_mode | auto | Query Tuning / Other Planner Options
recursive_worktable_factor | 10 | Query Tuning / Other Planner Options
cpu_index_tuple_cost | 0.005 | Query Tuning / Planner Cost Constants
cpu_operator_cost | 0.0025 | Query Tuning / Planner Cost Constants
cpu_tuple_cost | 0.01 | Query Tuning / Planner Cost Constants
effective_cache_size | 16384 | Query Tuning / Planner Cost Constants
jit_above_cost | 100000 | Query Tuning / Planner Cost Constants
jit_inline_above_cost | 500000 | Query Tuning / Planner Cost Constants
jit_optimize_above_cost | 500000 | Query Tuning / Planner Cost Constants
min_parallel_index_scan_size | 64 | Query Tuning / Planner Cost Constants
min_parallel_table_scan_size | 1024 | Query Tuning / Planner Cost Constants
parallel_setup_cost | 1000 | Query Tuning / Planner Cost Constants
parallel_tuple_cost | 0.1 | Query Tuning / Planner Cost Constants
random_page_cost | 4 | Query Tuning / Planner Cost Constants
seq_page_cost | 1 | Query Tuning / Planner Cost Constants
enable_async_append | on | Query Tuning / Planner Method Configuration
enable_bitmapscan | on | Query Tuning / Planner Method Configuration
enable_gathermerge | on | Query Tuning / Planner Method Configuration
enable_group_by_reordering | on | Query Tuning / Planner Method Configuration
enable_hashagg | on | Query Tuning / Planner Method Configuration
enable_hashjoin | on | Query Tuning / Planner Method Configuration
enable_incremental_sort | on | Query Tuning / Planner Method Configuration
enable_indexonlyscan | on | Query Tuning / Planner Method Configuration
enable_indexscan | on | Query Tuning / Planner Method Configuration
enable_material | on | Query Tuning / Planner Method Configuration
enable_memoize | on | Query Tuning / Planner Method Configuration
enable_mergejoin | on | Query Tuning / Planner Method Configuration
enable_nestloop | on | Query Tuning / Planner Method Configuration
enable_parallel_append | on | Query Tuning / Planner Method Configuration
enable_parallel_hash | on | Query Tuning / Planner Method Configuration
enable_partition_pruning | on | Query Tuning / Planner Method Configuration
enable_partitionwise_aggregate | off | Query Tuning / Planner Method Configuration
enable_partitionwise_join | off | Query Tuning / Planner Method Configuration
enable_presorted_aggregate | on | Query Tuning / Planner Method Configuration
enable_seqscan | on | Query Tuning / Planner Method Configuration
enable_sort | on | Query Tuning / Planner Method Configuration
enable_tidscan | on | Query Tuning / Planner Method Configuration
(51 rows)
ANALYZE;