-
Notifications
You must be signed in to change notification settings - Fork 57
/
hypo_table.sql
376 lines (342 loc) · 25.7 KB
/
hypo_table.sql
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
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
-- Creating real and hypothetical tables"
-- ====================================="
-- Real tables
-- -----------
-- 1.1. Range partition
DROP TABLE IF EXISTS part_range;
CREATE TABLE part_range (id integer, val text) PARTITION BY RANGE (id);
CREATE TABLE part_range_1_10000 PARTITION OF part_range FOR VALUES FROM (1) TO (10000);
CREATE TABLE part_range_10000_20000 PARTITION OF part_range FOR VALUES FROM (10000) TO (20000);
CREATE TABLE part_range_20000_30000 PARTITION OF part_range FOR VALUES FROM (20000) TO (30000);
INSERT INTO part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
-- 1.2. List partitioning
DROP TABLE IF EXISTS part_list;
CREATE TABLE part_list (id integer, id_key integer, val text) PARTITION BY LIST (id_key);
CREATE TABLE part_list_4_5_6_8_10 PARTITION OF part_list FOR VALUES IN (4, 5, 6, 8, 10);
CREATE TABLE part_list_7_9 PARTITION OF part_list FOR VALUES IN (7, 9);
CREATE TABLE part_list_1_2_3 PARTITION OF part_list FOR VALUES IN (1, 2, 3);
INSERT INTO part_list SELECT i, (i % 9) + 1, 'line ' || i FROM generate_series(1, 50000) i;
-- 1.3. Hash partitioning
DROP TABLE IF EXISTS part_hash;
CREATE TABLE part_hash (id integer, val text) PARTITION BY HASH (id);
CREATE TABLE part_hash_9 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 9);
CREATE TABLE part_hash_8 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 8);
CREATE TABLE part_hash_7 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 7);
CREATE TABLE part_hash_6 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 6);
CREATE TABLE part_hash_5 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 5);
CREATE TABLE part_hash_4 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 4);
CREATE TABLE part_hash_3 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 3);
CREATE TABLE part_hash_2 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 2);
CREATE TABLE part_hash_1 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 1);
CREATE TABLE part_hash_0 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0);
INSERT INTO part_hash SELECT i, 'line ' || i FROM generate_series(1, 90000) i;
-- 1.4. Multi level range
DROP TABLE IF EXISTS part_multi;
CREATE TABLE part_multi(dpt smallint, dt date, val text) PARTITION BY LIST (dpt);
CREATE TABLE part_multi_2 PARTITION OF part_multi FOR VALUES IN (2) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_2_q1 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_2_q2 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_2_q3 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_2_q4 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_2_def PARTITION OF part_multi_2 DEFAULT;
CREATE TABLE part_multi_1 PARTITION OF part_multi FOR VALUES IN (1) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_1_q2 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_1_q3 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_1_q4 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_1_q1 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$) PARTITION BY RANGE (dt);
CREATE TABLE part_multi_1_q1_b PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_1_q1_a PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$);
CREATE TABLE part_multi_1_def PARTITION OF part_multi_1 DEFAULT;
CREATE TABLE part_multi_3 PARTITION OF part_multi FOR VALUES IN (3) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_3_q1 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_3_q2 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_3_q3 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_3_q4 PARTITION OF part_multi_3 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_3_def PARTITION OF part_multi_3 DEFAULT;
CREATE TABLE part_multi_def PARTITION OF part_multi DEFAULT PARTITION BY RANGE(dt);
CREATE TABLE part_multi_def_q1 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_def_q2 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$);
CREATE TABLE part_multi_def_q3 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$);
CREATE TABLE part_multi_def_q4 PARTITION OF part_multi_def FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$);
CREATE TABLE part_multi_def_def PARTITION OF part_multi_def DEFAULT;
INSERT INTO part_multi select (i%4)+1, '2015-01-01'::date + interval '1 day' * (i%500), 'val ' || i FROM generate_series(1,50000) i;
-- Hypothetical tables
-- -------------------
-- 2.0. Dropping any hypothetical object
SELECT * FROM hypopg_reset();
-- 2.1. Range partition
DROP TABLE IF EXISTS hypo_part_range;
CREATE TABLE hypo_part_range (id integer, val text);
INSERT INTO hypo_part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
SELECT * FROM hypopg_partition_table('hypo_part_range', 'PARTITION BY RANGE (id)');
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
SELECT tablename FROM hypopg_add_partition('hypo_part_range_10000_20000', 'PARTITION OF hypo_part_range FOR VALUES FROM (10000) TO (20000)');
SELECT tablename FROM hypopg_add_partition('hypo_part_range_20000_30000', 'PARTITION OF hypo_part_range FOR VALUES FROM (20000) TO (30000)');
-- 2.2. List partitioning
DROP TABLE IF EXISTS hypo_part_list;
CREATE TABLE hypo_part_list (id integer, id_key integer, val text);
INSERT INTO hypo_part_list SELECT i, (i % 9) + 1, 'line ' || i FROM generate_series(1, 50000) i;
SELECT * FROM hypopg_partition_table('hypo_part_list', 'PARTITION BY LIST (id_key)');
SELECT tablename FROM hypopg_add_partition('hypo_part_list_4_5_6_8_10', 'PARTITION OF hypo_part_list FOR VALUES IN (4, 5, 6, 8, 10)');
SELECT tablename FROM hypopg_add_partition('hypo_part_list_7_9', 'PARTITION OF hypo_part_list FOR VALUES IN (7, 9)');
SELECT tablename FROM hypopg_add_partition('hypo_part_list_1_2_3', 'PARTITION OF hypo_part_list FOR VALUES IN (1, 2, 3)');
-- 2.3. Hash partitioning
DROP TABLE IF EXISTS hypo_part_hash;
CREATE TABLE hypo_part_hash (id integer, val text);
INSERT INTO hypo_part_hash SELECT i, 'line ' || i FROM generate_series(1, 90000) i;
SELECT * FROM hypopg_partition_table('hypo_part_hash', 'PARTITION BY HASH (id)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_9', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 9)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_8', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 8)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_7', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 7)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_6', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 6)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_5', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 5)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_4', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 4)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_3', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 3)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_2', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 2)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_1', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 1)');
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_0', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0)');
-- 2.4. Multi level range
DROP TABLE IF EXISTS hypo_part_multi;
CREATE TABLE hypo_part_multi(dpt smallint, dt date, val text);
INSERT INTO hypo_part_multi select (i%4)+1, '2015-01-01'::date + interval '1 day' * (i%500), 'val ' || i FROM generate_series(1,50000) i;
SELECT * FROM hypopg_partition_table('hypo_part_multi', 'PARTITION BY LIST (dpt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2', 'PARTITION OF hypo_part_multi FOR VALUES IN (2)', 'PARTITION BY RANGE(dt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q1', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q2', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q3', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q4', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_def', 'PARTITION OF hypo_part_multi_2 DEFAULT');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1', 'PARTITION OF hypo_part_multi FOR VALUES IN (1)', 'PARTITION BY RANGE(dt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q2', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q3', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q4', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)','PARTITION BY RANGE (dt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_b', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_a', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_def', 'PARTITION OF hypo_part_multi_1 DEFAULT');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3', 'PARTITION OF hypo_part_multi FOR VALUES IN (3)', 'PARTITION BY RANGE(dt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q1', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q2', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q3', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_q4', 'PARTITION OF hypo_part_multi_3 FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_3_def', 'PARTITION OF hypo_part_multi_3 DEFAULT');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def', 'PARTITION OF hypo_part_multi DEFAULT' ,'PARTITION BY RANGE(dt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q1', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q2', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-04-01$$) TO ($$2015-07-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q3', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-07-01$$) TO ($$2015-10-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_q4', 'PARTITION OF hypo_part_multi_def FOR VALUES FROM ($$2015-10-01$$) TO ($$2016-01-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_def_def', 'PARTITION OF hypo_part_multi_def DEFAULT');
-- Maintenance
-- -----------
VACUUM ANALYZE;
SELECT * FROM hypopg_analyze('hypo_part_range',100);
SELECT * FROM hypopg_analyze('hypo_part_list',100);
SELECT * FROM hypopg_analyze('hypo_part_hash',100);
SELECT * FROM hypopg_analyze('hypo_part_multi',100);
-- Test deparsing
-- ==============
SELECT relid = rootid AS is_root, tablename, parentid IS NULL parentid_is_null,
parentid IS NOT NULL AS parentid_is_not_null,
partition_by_clause, partition_bounds
FROM hypopg_table()
ORDER BY tablename COLLATE "C";
-- Test hypothetical partitioning behavior
-- =======================================
-- Simple queries
-- --------------
-- Real tables
-- -----------
-- 3.1. Range partitioning
EXPLAIN (COSTS OFF) SELECT * FROM part_range;
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id < 15000;
-- 3.2. List partitioning
EXPLAIN (COSTS OFF) SELECT * FROM part_list;
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id < 42;
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id_key < 5;
EXPLAIN (COSTS OFF) SELECT * FROM part_list WHERE id_key = 7;
-- 3.3. Hash partitioning
EXPLAIN (COSTS OFF) SELECT * FROM part_hash;
EXPLAIN (COSTS OFF) SELECT * FROM part_hash WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM part_hash WHERE id < 15000;
-- 3.4. Multi level range
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
EXPLAIN (COSTS OFF) SELECT * FROM part_multi WHERE dpt = 2;
EXPLAIN (COSTS OFF) SELECT * FROM part_multi WHERE dt >= '2015-01-05' AND dt < '2015-01-10';
-- Hypothetical tables
-- -------------------
-- 4.1. Range partitioning
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id < 15000;
-- 4.2. List partitioning
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id < 42;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id_key < 5;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list WHERE id_key = 7;
-- 4.3. Hash partitioning
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash WHERE id = 42;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash WHERE id < 15000;
-- 4.4. Multi level range
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi WHERE dpt = 2;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi WHERE dt >= '2015-01-05' AND dt < '2015-01-10';
-- Join queries
-- ------------
-- Simple joins
-- ------------
-- 5.1. Real tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM part_list t1, part_list t2 WHERE t1.id_key = t2.id_key and t1.id_key < 5;
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, part_hash t2 WHERE t1.id = t2.id;
EXPLAIN (COSTS OFF) SELECT * FROM part_multi t1, part_multi t2 WHERE t1.dpt = t2.dpt and t1.dpt = 2;
-- 5.2. Hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_list t1, hypo_part_list t2 WHERE t1.id_key = t2.id_key and t1.id_key < 5;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi t1, hypo_part_multi t2 WHERE t1.dpt = t2.dpt and t1.dpt = 2;
-- 5.3. Real tables and hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM part_list t1, hypo_part_list t2 WHERE t1.id_key = t2.id_key and t1.id_key < 5;
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
EXPLAIN (COSTS OFF) SELECT * FROM part_multi t1, hypo_part_multi t2 WHERE t1.dpt = t2.dpt and t1.dpt = 2;
-- Partitionwise joins
-- -------------------
-- enable partitionwise join
-- -------------------------
SET enable_partitionwise_join to true;
-- 6.1. Real tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, part_hash t2 WHERE t1.id = t2.id;
-- 6.2. Hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
-- 6.3. Real tables and hypothetical tables
EXPLAIN (COSTS OFF) SELECT * FROM part_range t1, hypo_part_range t2 WHERE t1.id = t2.id and t1.id < 15000;
EXPLAIN (COSTS OFF) SELECT * FROM part_hash t1, hypo_part_hash t2 WHERE t1.id = t2.id;
-- Runtime partition pruning
-- -------------------------
-- 6B.0 Add function to easily simulate runtime partitioning
CREATE FUNCTION hypo_number_one() RETURNS integer AS
$_$
BEGIN
RETURN 1;
END;
$_$ LANGUAGE plpgsql STABLE;
-- 6B.1 disable runtime partition pruning
SET enable_partition_pruning to false;
-- 6B.2 simple case
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id = hypo_number_one();
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = hypo_number_one();
-- 6B.3 CTE
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
-- 6B.4 CTE with partitioning underneath an union
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
-- 6B.5 InitPlan
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s);
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s);
-- 6B.6 enable runtime partition pruning
SET enable_partition_pruning to true;
-- 6B.7 simple case
EXPLAIN (COSTS OFF) SELECT * FROM part_range WHERE id = hypo_number_one();
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = hypo_number_one();
-- 6B.8 CTE
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
EXPLAIN (COSTS OFF) WITH s AS (SELECT * FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
-- 6B.9 CTE with partitioning underneath an union
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s;
EXPLAIN (COSTS OFF) WITH s AS (SELECT 1 UNION ALL SELECT 2 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s;
-- 6B.10 InitPlan
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM part_range WHERE id = hypo_number_one()) SELECT * FROM s);
EXPLAIN (COSTS OFF) SELECT (WITH s AS (SELECT 1 FROM hypo_part_range WHERE id = hypo_number_one()) SELECT * FROM s);
-- Tests for sanity checks
-- =======================
-- 7.1 Duplicate name
CREATE TABLE part_range_1_10000 PARTITION OF part_range FOR VALUES FROM (1) TO (10000);
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
-- 7.2 Overlapping range bounds
CREATE TABLE part_range_1_10000_dup PARTITION OF part_range FOR VALUES FROM (1) TO (10000);
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000_dup', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
-- 7.3 Overlapping list bounds
CREATE TABLE part_list_1_2_3_dup PARTITION OF part_list FOR VALUES IN (1, 2, 3);
SELECT tablename FROM hypopg_add_partition('hypo_part_list_1_2_3_dup', 'PARTITION OF hypo_part_list FOR VALUES IN (1, 2, 3)');
-- 7.4 Overlapping hash bounds
CREATE TABLE part_hash_0 PARTITION OF part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0);
SELECT tablename FROM hypopg_add_partition('hypo_part_hash_0_dup', 'PARTITION OF hypo_part_hash FOR VALUES WITH (MODULUS 10, REMAINDER 0)');
-- 7.5 Overlapping range bounds, subpartition
CREATE TABLE part_multi_1_q1_a PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$);
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_a_dup', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$)');
-- relcache callback test
-- ======================
SELECT tablename FROM hypopg_table() WHERE tablename LIKE 'hypo_part_range%' ORDER BY tablename COLLATE "C";
DROP TABLE hypo_part_range;
SELECT tablename FROM hypopg_table() WHERE tablename LIKE 'hypo_part_range%' ORDER BY tablename COLLATE "C";
CREATE TABLE hypo_part_range (id integer, val text);
INSERT INTO hypo_part_range SELECT i, 'line ' || i FROM generate_series(1, 29999) i;
SELECT * FROM hypopg_partition_table('hypo_part_range', 'PARTITION BY RANGE (id)');
SELECT tablename FROM hypopg_add_partition('hypo_part_range_10000_20000', 'PARTITION OF hypo_part_range FOR VALUES FROM (10000) TO (20000)');
SELECT tablename FROM hypopg_add_partition('hypo_part_range_20000_30000', 'PARTITION OF hypo_part_range FOR VALUES FROM (20000) TO (30000)');
SELECT tablename FROM hypopg_add_partition('hypo_part_range_1_10000', 'PARTITION OF hypo_part_range FOR VALUES FROM (1) TO (10000)');
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_range WHERE id = 42;
-- no UPDATE/DELETE test
-- =====================
-- 8.1 simple UPDATE and DELETE on hypothetically partitioned table
EXPLAIN (COSTS OFF) UPDATE hypo_part_range set id = id;
EXPLAIN DELETE FROM hypo_part_range WHERE id = 42;
-- 8.2 UPDATE and DELETE on hypothetically partitioned table inside CTE
EXPLAIN (COSTS OFF) WITH s AS (UPDATE hypo_part_range set id = id returning *) SELECT 1;
EXPLAIN (COSTS OFF) WITH s AS (DELETE FROM hypo_part_range WHERE id = 42 returning *) SELECT 1;
-- 8.3 UPDATE and DELETE involving hypothetically partitioned table, but on
-- regular tables
CREATE TABLE foo(id integer);
-- 8.4 UPDATE on non hypothetically partitioned table but having a hypothetically
-- partitioned table joined
EXPLAIN (COSTS OFF) WITH s AS (UPDATE foo SET id = 0 from hypo_part_range WHERE foo.id = hypo_part_range.id AND hypo_part_range.id > 25000 RETURNING *) SELECT 1;
-- 8.5 same but with real table
EXPLAIN (COSTS OFF) WITH s AS (UPDATE foo SET id = 0 from part_range WHERE foo.id = part_range.id AND part_range.id > 25000 RETURNING *) SELECT 1;
-- 8.6 DELETE on non hypothetically partitioned table but having a
-- hypothetically partitioned table joined
EXPLAIN (COSTS OFF) WITH s AS (DELETE FROM foo USING hypo_part_range WHERE foo.id = hypo_part_range.id AND hypo_part_range.id = 42 RETURNING *) SELECT 1;
-- 8.7 same but with real table
EXPLAIN (COSTS OFF) WITH s AS (DELETE FROM foo USING part_range WHERE foo.id = part_range.id AND part_range.id = 42 RETURNING *) SELECT 1;
-- childless partitioning
-- ======================
SELECT * FROM hypopg_reset();
DROP TABLE part_multi;
CREATE TABLE part_multi(dpt smallint, dt date, val text) PARTITION BY LIST (dpt);
SELECT * FROM hypopg_partition_table('hypo_part_multi', 'PARTITION BY LIST (dpt)');
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
CREATE TABLE part_multi_2 PARTITION OF part_multi FOR VALUES IN (2) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_2_q1 PARTITION OF part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_1 PARTITION OF part_multi FOR VALUES IN (1) PARTITION BY RANGE(dt);
CREATE TABLE part_multi_1_q1 PARTITION OF part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$) PARTITION BY RANGE (dt);
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2', 'PARTITION OF hypo_part_multi FOR VALUES IN (2)', 'PARTITION BY RANGE(dt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_2_q1', 'PARTITION OF hypo_part_multi_2 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1', 'PARTITION OF hypo_part_multi FOR VALUES IN (1)', 'PARTITION BY RANGE(dt)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1', 'PARTITION OF hypo_part_multi_1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-04-01$$)','PARTITION BY RANGE (dt)');
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
-- removing hypothetical partitions
-- ================================
CREATE TABLE part_multi_1_q1_b PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$);
CREATE TABLE part_multi_1_q1_a PARTITION OF part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$);
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_b', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-02-01$$) TO ($$2015-04-01$$)');
SELECT tablename FROM hypopg_add_partition('hypo_part_multi_1_q1_a', 'PARTITION OF hypo_part_multi_1_q1 FOR VALUES FROM ($$2015-01-01$$) TO ($$2015-02-01$$)');
DROP TABLE part_multi_1_q1_a;
SELECT hypopg_drop_table(relid) FROM hypopg_table() WHERE tablename = 'hypo_part_multi_1_q1_a';
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
DROP TABLE part_multi_1;
SELECT hypopg_drop_table(relid) FROM hypopg_table() WHERE tablename = 'hypo_part_multi_1';
EXPLAIN (COSTS OFF) SELECT * FROM part_multi;
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
SELECT hypopg_drop_table(relid) FROM hypopg_table() WHERE tablename = 'hypo_part_multi';
EXPLAIN (COSTS OFF) SELECT * FROM hypo_part_multi;
SELECT hypopg_drop_table(oid) FROM pg_class WHERE relname = 'pg_class';
SELECT hypopg_drop_table(1);