| blob | 7555764c7794816a3680e3a99d75ec3e7f3d9ba6 |
1 --
2 -- Test partitioning planner code
3 --
4 -- Force generic plans to be used for all prepared statements in this file.
5 set plan_cache_mode = force_generic_plan;
6 create table lp (a char) partition by list (a);
7 create table lp_default partition of lp default;
8 create table lp_ef partition of lp for values in ('e', 'f');
9 create table lp_ad partition of lp for values in ('a', 'd');
10 create table lp_bc partition of lp for values in ('b', 'c');
11 create table lp_g partition of lp for values in ('g');
12 create table lp_null partition of lp for values in (null);
13 explain (costs off) select * from lp;
14 QUERY PLAN
15 -----------------------------------
16 Append
17 -> Seq Scan on lp_ad lp_1
18 -> Seq Scan on lp_bc lp_2
19 -> Seq Scan on lp_ef lp_3
20 -> Seq Scan on lp_g lp_4
21 -> Seq Scan on lp_null lp_5
22 -> Seq Scan on lp_default lp_6
23 (7 rows)
25 explain (costs off) select * from lp where a > 'a' and a < 'd';
26 QUERY PLAN
27 -----------------------------------------------------------
28 Append
29 -> Seq Scan on lp_bc lp_1
30 Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
31 -> Seq Scan on lp_default lp_2
32 Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
33 (5 rows)
35 explain (costs off) select * from lp where a > 'a' and a <= 'd';
36 QUERY PLAN
37 ------------------------------------------------------------
38 Append
39 -> Seq Scan on lp_ad lp_1
40 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
41 -> Seq Scan on lp_bc lp_2
42 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
43 -> Seq Scan on lp_default lp_3
44 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
45 (7 rows)
47 explain (costs off) select * from lp where a = 'a';
48 QUERY PLAN
49 -----------------------------
50 Seq Scan on lp_ad lp
51 Filter: (a = 'a'::bpchar)
52 (2 rows)
54 explain (costs off) select * from lp where 'a' = a; /* commuted */
55 QUERY PLAN
56 -----------------------------
57 Seq Scan on lp_ad lp
58 Filter: ('a'::bpchar = a)
59 (2 rows)
61 explain (costs off) select * from lp where a is not null;
62 QUERY PLAN
63 -----------------------------------
64 Append
65 -> Seq Scan on lp_ad lp_1
66 Filter: (a IS NOT NULL)
67 -> Seq Scan on lp_bc lp_2
68 Filter: (a IS NOT NULL)
69 -> Seq Scan on lp_ef lp_3
70 Filter: (a IS NOT NULL)
71 -> Seq Scan on lp_g lp_4
72 Filter: (a IS NOT NULL)
73 -> Seq Scan on lp_default lp_5
74 Filter: (a IS NOT NULL)
75 (11 rows)
77 explain (costs off) select * from lp where a is null;
78 QUERY PLAN
79 ------------------------
80 Seq Scan on lp_null lp
81 Filter: (a IS NULL)
82 (2 rows)
84 explain (costs off) select * from lp where a = 'a' or a = 'c';
85 QUERY PLAN
86 ----------------------------------------------------------
87 Append
88 -> Seq Scan on lp_ad lp_1
89 Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
90 -> Seq Scan on lp_bc lp_2
91 Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
92 (5 rows)
94 explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
95 QUERY PLAN
96 --------------------------------------------------------------------------------
97 Append
98 -> Seq Scan on lp_ad lp_1
99 Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
100 -> Seq Scan on lp_bc lp_2
101 Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
102 (5 rows)
104 explain (costs off) select * from lp where a <> 'g';
105 QUERY PLAN
106 ------------------------------------
107 Append
108 -> Seq Scan on lp_ad lp_1
109 Filter: (a <> 'g'::bpchar)
110 -> Seq Scan on lp_bc lp_2
111 Filter: (a <> 'g'::bpchar)
112 -> Seq Scan on lp_ef lp_3
113 Filter: (a <> 'g'::bpchar)
114 -> Seq Scan on lp_default lp_4
115 Filter: (a <> 'g'::bpchar)
116 (9 rows)
118 explain (costs off) select * from lp where a <> 'a' and a <> 'd';
119 QUERY PLAN
120 -------------------------------------------------------------
121 Append
122 -> Seq Scan on lp_bc lp_1
123 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
124 -> Seq Scan on lp_ef lp_2
125 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
126 -> Seq Scan on lp_g lp_3
127 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
128 -> Seq Scan on lp_default lp_4
129 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
130 (9 rows)
132 explain (costs off) select * from lp where a not in ('a', 'd');
133 QUERY PLAN
134 ------------------------------------------------
135 Append
136 -> Seq Scan on lp_bc lp_1
137 Filter: (a <> ALL ('{a,d}'::bpchar[]))
138 -> Seq Scan on lp_ef lp_2
139 Filter: (a <> ALL ('{a,d}'::bpchar[]))
140 -> Seq Scan on lp_g lp_3
141 Filter: (a <> ALL ('{a,d}'::bpchar[]))
142 -> Seq Scan on lp_default lp_4
143 Filter: (a <> ALL ('{a,d}'::bpchar[]))
144 (9 rows)
146 -- collation matches the partitioning collation, pruning works
147 create table coll_pruning (a text collate "C") partition by list (a);
148 create table coll_pruning_a partition of coll_pruning for values in ('a');
149 create table coll_pruning_b partition of coll_pruning for values in ('b');
150 create table coll_pruning_def partition of coll_pruning default;
151 explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
152 QUERY PLAN
153 -----------------------------------------
154 Seq Scan on coll_pruning_a coll_pruning
155 Filter: (a = 'a'::text COLLATE "C")
156 (2 rows)
158 -- collation doesn't match the partitioning collation, no pruning occurs
159 explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
160 QUERY PLAN
161 ---------------------------------------------------------
162 Append
163 -> Seq Scan on coll_pruning_a coll_pruning_1
164 Filter: ((a)::text = 'a'::text COLLATE "POSIX")
165 -> Seq Scan on coll_pruning_b coll_pruning_2
166 Filter: ((a)::text = 'a'::text COLLATE "POSIX")
167 -> Seq Scan on coll_pruning_def coll_pruning_3
168 Filter: ((a)::text = 'a'::text COLLATE "POSIX")
169 (7 rows)
171 create table rlp (a int, b varchar) partition by range (a);
172 create table rlp_default partition of rlp default partition by list (a);
173 create table rlp_default_default partition of rlp_default default;
174 create table rlp_default_10 partition of rlp_default for values in (10);
175 create table rlp_default_30 partition of rlp_default for values in (30);
176 create table rlp_default_null partition of rlp_default for values in (null);
177 create table rlp1 partition of rlp for values from (minvalue) to (1);
178 create table rlp2 partition of rlp for values from (1) to (10);
179 create table rlp3 (b varchar, a int) partition by list (b varchar_ops);
180 create table rlp3_default partition of rlp3 default;
181 create table rlp3abcd partition of rlp3 for values in ('ab', 'cd');
182 create table rlp3efgh partition of rlp3 for values in ('ef', 'gh');
183 create table rlp3nullxy partition of rlp3 for values in (null, 'xy');
184 alter table rlp attach partition rlp3 for values from (15) to (20);
185 create table rlp4 partition of rlp for values from (20) to (30) partition by range (a);
186 create table rlp4_default partition of rlp4 default;
187 create table rlp4_1 partition of rlp4 for values from (20) to (25);
188 create table rlp4_2 partition of rlp4 for values from (25) to (29);
189 create table rlp5 partition of rlp for values from (31) to (maxvalue) partition by range (a);
190 create table rlp5_default partition of rlp5 default;
191 create table rlp5_1 partition of rlp5 for values from (31) to (40);
192 explain (costs off) select * from rlp where a < 1;
193 QUERY PLAN
194 ----------------------
195 Seq Scan on rlp1 rlp
196 Filter: (a < 1)
197 (2 rows)
199 explain (costs off) select * from rlp where 1 > a; /* commuted */
200 QUERY PLAN
201 ----------------------
202 Seq Scan on rlp1 rlp
203 Filter: (1 > a)
204 (2 rows)
206 explain (costs off) select * from rlp where a <= 1;
207 QUERY PLAN
208 ------------------------------
209 Append
210 -> Seq Scan on rlp1 rlp_1
211 Filter: (a <= 1)
212 -> Seq Scan on rlp2 rlp_2
213 Filter: (a <= 1)
214 (5 rows)
216 explain (costs off) select * from rlp where a = 1;
217 QUERY PLAN
218 ----------------------
219 Seq Scan on rlp2 rlp
220 Filter: (a = 1)
221 (2 rows)
223 explain (costs off) select * from rlp where a = 1::bigint; /* same as above */
224 QUERY PLAN
225 -----------------------------
226 Seq Scan on rlp2 rlp
227 Filter: (a = '1'::bigint)
228 (2 rows)
230 explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */
231 QUERY PLAN
232 -----------------------------------------------
233 Append
234 -> Seq Scan on rlp1 rlp_1
235 Filter: ((a)::numeric = '1'::numeric)
236 -> Seq Scan on rlp2 rlp_2
237 Filter: ((a)::numeric = '1'::numeric)
238 -> Seq Scan on rlp3abcd rlp_3
239 Filter: ((a)::numeric = '1'::numeric)
240 -> Seq Scan on rlp3efgh rlp_4
241 Filter: ((a)::numeric = '1'::numeric)
242 -> Seq Scan on rlp3nullxy rlp_5
243 Filter: ((a)::numeric = '1'::numeric)
244 -> Seq Scan on rlp3_default rlp_6
245 Filter: ((a)::numeric = '1'::numeric)
246 -> Seq Scan on rlp4_1 rlp_7
247 Filter: ((a)::numeric = '1'::numeric)
248 -> Seq Scan on rlp4_2 rlp_8
249 Filter: ((a)::numeric = '1'::numeric)
250 -> Seq Scan on rlp4_default rlp_9
251 Filter: ((a)::numeric = '1'::numeric)
252 -> Seq Scan on rlp5_1 rlp_10
253 Filter: ((a)::numeric = '1'::numeric)
254 -> Seq Scan on rlp5_default rlp_11
255 Filter: ((a)::numeric = '1'::numeric)
256 -> Seq Scan on rlp_default_10 rlp_12
257 Filter: ((a)::numeric = '1'::numeric)
258 -> Seq Scan on rlp_default_30 rlp_13
259 Filter: ((a)::numeric = '1'::numeric)
260 -> Seq Scan on rlp_default_null rlp_14
261 Filter: ((a)::numeric = '1'::numeric)
262 -> Seq Scan on rlp_default_default rlp_15
263 Filter: ((a)::numeric = '1'::numeric)
264 (31 rows)
266 explain (costs off) select * from rlp where a <= 10;
267 QUERY PLAN
268 ---------------------------------------------
269 Append
270 -> Seq Scan on rlp1 rlp_1
271 Filter: (a <= 10)
272 -> Seq Scan on rlp2 rlp_2
273 Filter: (a <= 10)
274 -> Seq Scan on rlp_default_10 rlp_3
275 Filter: (a <= 10)
276 -> Seq Scan on rlp_default_default rlp_4
277 Filter: (a <= 10)
278 (9 rows)
280 explain (costs off) select * from rlp where a > 10;
281 QUERY PLAN
282 ----------------------------------------------
283 Append
284 -> Seq Scan on rlp3abcd rlp_1
285 Filter: (a > 10)
286 -> Seq Scan on rlp3efgh rlp_2
287 Filter: (a > 10)
288 -> Seq Scan on rlp3nullxy rlp_3
289 Filter: (a > 10)
290 -> Seq Scan on rlp3_default rlp_4
291 Filter: (a > 10)
292 -> Seq Scan on rlp4_1 rlp_5
293 Filter: (a > 10)
294 -> Seq Scan on rlp4_2 rlp_6
295 Filter: (a > 10)
296 -> Seq Scan on rlp4_default rlp_7
297 Filter: (a > 10)
298 -> Seq Scan on rlp5_1 rlp_8
299 Filter: (a > 10)
300 -> Seq Scan on rlp5_default rlp_9
301 Filter: (a > 10)
302 -> Seq Scan on rlp_default_30 rlp_10
303 Filter: (a > 10)
304 -> Seq Scan on rlp_default_default rlp_11
305 Filter: (a > 10)
306 (23 rows)
308 explain (costs off) select * from rlp where a < 15;
309 QUERY PLAN
310 ---------------------------------------------
311 Append
312 -> Seq Scan on rlp1 rlp_1
313 Filter: (a < 15)
314 -> Seq Scan on rlp2 rlp_2
315 Filter: (a < 15)
316 -> Seq Scan on rlp_default_10 rlp_3
317 Filter: (a < 15)
318 -> Seq Scan on rlp_default_default rlp_4
319 Filter: (a < 15)
320 (9 rows)
322 explain (costs off) select * from rlp where a <= 15;
323 QUERY PLAN
324 ---------------------------------------------
325 Append
326 -> Seq Scan on rlp1 rlp_1
327 Filter: (a <= 15)
328 -> Seq Scan on rlp2 rlp_2
329 Filter: (a <= 15)
330 -> Seq Scan on rlp3abcd rlp_3
331 Filter: (a <= 15)
332 -> Seq Scan on rlp3efgh rlp_4
333 Filter: (a <= 15)
334 -> Seq Scan on rlp3nullxy rlp_5
335 Filter: (a <= 15)
336 -> Seq Scan on rlp3_default rlp_6
337 Filter: (a <= 15)
338 -> Seq Scan on rlp_default_10 rlp_7
339 Filter: (a <= 15)
340 -> Seq Scan on rlp_default_default rlp_8
341 Filter: (a <= 15)
342 (17 rows)
344 explain (costs off) select * from rlp where a > 15 and b = 'ab';
345 QUERY PLAN
346 ---------------------------------------------------------
347 Append
348 -> Seq Scan on rlp3abcd rlp_1
349 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
350 -> Seq Scan on rlp4_1 rlp_2
351 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
352 -> Seq Scan on rlp4_2 rlp_3
353 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
354 -> Seq Scan on rlp4_default rlp_4
355 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
356 -> Seq Scan on rlp5_1 rlp_5
357 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
358 -> Seq Scan on rlp5_default rlp_6
359 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
360 -> Seq Scan on rlp_default_30 rlp_7
361 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
362 -> Seq Scan on rlp_default_default rlp_8
363 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
364 (17 rows)
366 explain (costs off) select * from rlp where a = 16;
367 QUERY PLAN
368 --------------------------------------
369 Append
370 -> Seq Scan on rlp3abcd rlp_1
371 Filter: (a = 16)
372 -> Seq Scan on rlp3efgh rlp_2
373 Filter: (a = 16)
374 -> Seq Scan on rlp3nullxy rlp_3
375 Filter: (a = 16)
376 -> Seq Scan on rlp3_default rlp_4
377 Filter: (a = 16)
378 (9 rows)
380 explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here');
381 QUERY PLAN
382 ----------------------------------------------------------------------
383 Seq Scan on rlp3_default rlp
384 Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[])))
385 (2 rows)
387 explain (costs off) select * from rlp where a = 16 and b < 'ab';
388 QUERY PLAN
389 ---------------------------------------------------
390 Seq Scan on rlp3_default rlp
391 Filter: (((b)::text < 'ab'::text) AND (a = 16))
392 (2 rows)
394 explain (costs off) select * from rlp where a = 16 and b <= 'ab';
395 QUERY PLAN
396 ----------------------------------------------------------
397 Append
398 -> Seq Scan on rlp3abcd rlp_1
399 Filter: (((b)::text <= 'ab'::text) AND (a = 16))
400 -> Seq Scan on rlp3_default rlp_2
401 Filter: (((b)::text <= 'ab'::text) AND (a = 16))
402 (5 rows)
404 explain (costs off) select * from rlp where a = 16 and b is null;
405 QUERY PLAN
406 --------------------------------------
407 Seq Scan on rlp3nullxy rlp
408 Filter: ((b IS NULL) AND (a = 16))
409 (2 rows)
411 explain (costs off) select * from rlp where a = 16 and b is not null;
412 QUERY PLAN
413 ------------------------------------------------
414 Append
415 -> Seq Scan on rlp3abcd rlp_1
416 Filter: ((b IS NOT NULL) AND (a = 16))
417 -> Seq Scan on rlp3efgh rlp_2
418 Filter: ((b IS NOT NULL) AND (a = 16))
419 -> Seq Scan on rlp3nullxy rlp_3
420 Filter: ((b IS NOT NULL) AND (a = 16))
421 -> Seq Scan on rlp3_default rlp_4
422 Filter: ((b IS NOT NULL) AND (a = 16))
423 (9 rows)
425 explain (costs off) select * from rlp where a is null;
426 QUERY PLAN
427 ----------------------------------
428 Seq Scan on rlp_default_null rlp
429 Filter: (a IS NULL)
430 (2 rows)
432 explain (costs off) select * from rlp where a is not null;
433 QUERY PLAN
434 ----------------------------------------------
435 Append
436 -> Seq Scan on rlp1 rlp_1
437 Filter: (a IS NOT NULL)
438 -> Seq Scan on rlp2 rlp_2
439 Filter: (a IS NOT NULL)
440 -> Seq Scan on rlp3abcd rlp_3
441 Filter: (a IS NOT NULL)
442 -> Seq Scan on rlp3efgh rlp_4
443 Filter: (a IS NOT NULL)
444 -> Seq Scan on rlp3nullxy rlp_5
445 Filter: (a IS NOT NULL)
446 -> Seq Scan on rlp3_default rlp_6
447 Filter: (a IS NOT NULL)
448 -> Seq Scan on rlp4_1 rlp_7
449 Filter: (a IS NOT NULL)
450 -> Seq Scan on rlp4_2 rlp_8
451 Filter: (a IS NOT NULL)
452 -> Seq Scan on rlp4_default rlp_9
453 Filter: (a IS NOT NULL)
454 -> Seq Scan on rlp5_1 rlp_10
455 Filter: (a IS NOT NULL)
456 -> Seq Scan on rlp5_default rlp_11
457 Filter: (a IS NOT NULL)
458 -> Seq Scan on rlp_default_10 rlp_12
459 Filter: (a IS NOT NULL)
460 -> Seq Scan on rlp_default_30 rlp_13
461 Filter: (a IS NOT NULL)
462 -> Seq Scan on rlp_default_default rlp_14
463 Filter: (a IS NOT NULL)
464 (29 rows)
466 explain (costs off) select * from rlp where a > 30;
467 QUERY PLAN
468 ---------------------------------------------
469 Append
470 -> Seq Scan on rlp5_1 rlp_1
471 Filter: (a > 30)
472 -> Seq Scan on rlp5_default rlp_2
473 Filter: (a > 30)
474 -> Seq Scan on rlp_default_default rlp_3
475 Filter: (a > 30)
476 (7 rows)
478 explain (costs off) select * from rlp where a = 30; /* only default is scanned */
479 QUERY PLAN
480 --------------------------------
481 Seq Scan on rlp_default_30 rlp
482 Filter: (a = 30)
483 (2 rows)
485 explain (costs off) select * from rlp where a <= 31;
486 QUERY PLAN
487 ----------------------------------------------
488 Append
489 -> Seq Scan on rlp1 rlp_1
490 Filter: (a <= 31)
491 -> Seq Scan on rlp2 rlp_2
492 Filter: (a <= 31)
493 -> Seq Scan on rlp3abcd rlp_3
494 Filter: (a <= 31)
495 -> Seq Scan on rlp3efgh rlp_4
496 Filter: (a <= 31)
497 -> Seq Scan on rlp3nullxy rlp_5
498 Filter: (a <= 31)
499 -> Seq Scan on rlp3_default rlp_6
500 Filter: (a <= 31)
501 -> Seq Scan on rlp4_1 rlp_7
502 Filter: (a <= 31)
503 -> Seq Scan on rlp4_2 rlp_8
504 Filter: (a <= 31)
505 -> Seq Scan on rlp4_default rlp_9
506 Filter: (a <= 31)
507 -> Seq Scan on rlp5_1 rlp_10
508 Filter: (a <= 31)
509 -> Seq Scan on rlp_default_10 rlp_11
510 Filter: (a <= 31)
511 -> Seq Scan on rlp_default_30 rlp_12
512 Filter: (a <= 31)
513 -> Seq Scan on rlp_default_default rlp_13
514 Filter: (a <= 31)
515 (27 rows)
517 explain (costs off) select * from rlp where a = 1 or a = 7;
518 QUERY PLAN
519 --------------------------------
520 Seq Scan on rlp2 rlp
521 Filter: ((a = 1) OR (a = 7))
522 (2 rows)
524 explain (costs off) select * from rlp where a = 1 or b = 'ab';
525 QUERY PLAN
526 -------------------------------------------------------
527 Append
528 -> Seq Scan on rlp1 rlp_1
529 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
530 -> Seq Scan on rlp2 rlp_2
531 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
532 -> Seq Scan on rlp3abcd rlp_3
533 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
534 -> Seq Scan on rlp4_1 rlp_4
535 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
536 -> Seq Scan on rlp4_2 rlp_5
537 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
538 -> Seq Scan on rlp4_default rlp_6
539 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
540 -> Seq Scan on rlp5_1 rlp_7
541 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
542 -> Seq Scan on rlp5_default rlp_8
543 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
544 -> Seq Scan on rlp_default_10 rlp_9
545 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
546 -> Seq Scan on rlp_default_30 rlp_10
547 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
548 -> Seq Scan on rlp_default_null rlp_11
549 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
550 -> Seq Scan on rlp_default_default rlp_12
551 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
552 (25 rows)
554 explain (costs off) select * from rlp where a > 20 and a < 27;
555 QUERY PLAN
556 -----------------------------------------
557 Append
558 -> Seq Scan on rlp4_1 rlp_1
559 Filter: ((a > 20) AND (a < 27))
560 -> Seq Scan on rlp4_2 rlp_2
561 Filter: ((a > 20) AND (a < 27))
562 (5 rows)
564 explain (costs off) select * from rlp where a = 29;
565 QUERY PLAN
566 ------------------------------
567 Seq Scan on rlp4_default rlp
568 Filter: (a = 29)
569 (2 rows)
571 explain (costs off) select * from rlp where a >= 29;
572 QUERY PLAN
573 ---------------------------------------------
574 Append
575 -> Seq Scan on rlp4_default rlp_1
576 Filter: (a >= 29)
577 -> Seq Scan on rlp5_1 rlp_2
578 Filter: (a >= 29)
579 -> Seq Scan on rlp5_default rlp_3
580 Filter: (a >= 29)
581 -> Seq Scan on rlp_default_30 rlp_4
582 Filter: (a >= 29)
583 -> Seq Scan on rlp_default_default rlp_5
584 Filter: (a >= 29)
585 (11 rows)
587 explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
588 QUERY PLAN
589 ------------------------------------------------------
590 Append
591 -> Seq Scan on rlp1 rlp_1
592 Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
593 -> Seq Scan on rlp4_1 rlp_2
594 Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
595 (5 rows)
597 -- where clause contradicts sub-partition's constraint
598 explain (costs off) select * from rlp where a = 20 or a = 40;
599 QUERY PLAN
600 ----------------------------------------
601 Append
602 -> Seq Scan on rlp4_1 rlp_1
603 Filter: ((a = 20) OR (a = 40))
604 -> Seq Scan on rlp5_default rlp_2
605 Filter: ((a = 20) OR (a = 40))
606 (5 rows)
608 explain (costs off) select * from rlp3 where a = 20; /* empty */
609 QUERY PLAN
610 --------------------------
611 Result
612 One-Time Filter: false
613 (2 rows)
615 -- redundant clauses are eliminated
616 explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
617 QUERY PLAN
618 ----------------------------------
619 Seq Scan on rlp_default_10 rlp
620 Filter: ((a > 1) AND (a = 10))
621 (2 rows)
623 explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
624 QUERY PLAN
625 ----------------------------------------------
626 Append
627 -> Seq Scan on rlp3abcd rlp_1
628 Filter: ((a > 1) AND (a >= 15))
629 -> Seq Scan on rlp3efgh rlp_2
630 Filter: ((a > 1) AND (a >= 15))
631 -> Seq Scan on rlp3nullxy rlp_3
632 Filter: ((a > 1) AND (a >= 15))
633 -> Seq Scan on rlp3_default rlp_4
634 Filter: ((a > 1) AND (a >= 15))
635 -> Seq Scan on rlp4_1 rlp_5
636 Filter: ((a > 1) AND (a >= 15))
637 -> Seq Scan on rlp4_2 rlp_6
638 Filter: ((a > 1) AND (a >= 15))
639 -> Seq Scan on rlp4_default rlp_7
640 Filter: ((a > 1) AND (a >= 15))
641 -> Seq Scan on rlp5_1 rlp_8
642 Filter: ((a > 1) AND (a >= 15))
643 -> Seq Scan on rlp5_default rlp_9
644 Filter: ((a > 1) AND (a >= 15))
645 -> Seq Scan on rlp_default_30 rlp_10
646 Filter: ((a > 1) AND (a >= 15))
647 -> Seq Scan on rlp_default_default rlp_11
648 Filter: ((a > 1) AND (a >= 15))
649 (23 rows)
651 explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
652 QUERY PLAN
653 --------------------------
654 Result
655 One-Time Filter: false
656 (2 rows)
658 explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
659 QUERY PLAN
660 -------------------------------------------------------------------
661 Append
662 -> Seq Scan on rlp2 rlp_1
663 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
664 -> Seq Scan on rlp3abcd rlp_2
665 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
666 -> Seq Scan on rlp3efgh rlp_3
667 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
668 -> Seq Scan on rlp3nullxy rlp_4
669 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
670 -> Seq Scan on rlp3_default rlp_5
671 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
672 (11 rows)
674 -- multi-column keys
675 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
676 create table mc3p_default partition of mc3p default;
677 create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
678 create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10);
679 create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10);
680 create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20);
681 create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue);
682 create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10);
683 create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20);
684 create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
685 explain (costs off) select * from mc3p where a = 1;
686 QUERY PLAN
687 ---------------------------------------
688 Append
689 -> Seq Scan on mc3p0 mc3p_1
690 Filter: (a = 1)
691 -> Seq Scan on mc3p1 mc3p_2
692 Filter: (a = 1)
693 -> Seq Scan on mc3p_default mc3p_3
694 Filter: (a = 1)
695 (7 rows)
697 explain (costs off) select * from mc3p where a = 1 and abs(b) < 1;
698 QUERY PLAN
699 --------------------------------------------
700 Append
701 -> Seq Scan on mc3p0 mc3p_1
702 Filter: ((a = 1) AND (abs(b) < 1))
703 -> Seq Scan on mc3p_default mc3p_2
704 Filter: ((a = 1) AND (abs(b) < 1))
705 (5 rows)
707 explain (costs off) select * from mc3p where a = 1 and abs(b) = 1;
708 QUERY PLAN
709 --------------------------------------------
710 Append
711 -> Seq Scan on mc3p0 mc3p_1
712 Filter: ((a = 1) AND (abs(b) = 1))
713 -> Seq Scan on mc3p1 mc3p_2
714 Filter: ((a = 1) AND (abs(b) = 1))
715 -> Seq Scan on mc3p_default mc3p_3
716 Filter: ((a = 1) AND (abs(b) = 1))
717 (7 rows)
719 explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8;
720 QUERY PLAN
721 --------------------------------------------------------
722 Append
723 -> Seq Scan on mc3p0 mc3p_1
724 Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
725 -> Seq Scan on mc3p1 mc3p_2
726 Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
727 (5 rows)
729 explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
730 QUERY PLAN
731 -----------------------------------------------------------------
732 Append
733 -> Seq Scan on mc3p1 mc3p_1
734 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
735 -> Seq Scan on mc3p2 mc3p_2
736 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
737 -> Seq Scan on mc3p3 mc3p_3
738 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
739 -> Seq Scan on mc3p4 mc3p_4
740 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
741 -> Seq Scan on mc3p_default mc3p_5
742 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
743 (11 rows)
745 explain (costs off) select * from mc3p where a > 10;
746 QUERY PLAN
747 ---------------------------------------
748 Append
749 -> Seq Scan on mc3p5 mc3p_1
750 Filter: (a > 10)
751 -> Seq Scan on mc3p6 mc3p_2
752 Filter: (a > 10)
753 -> Seq Scan on mc3p7 mc3p_3
754 Filter: (a > 10)
755 -> Seq Scan on mc3p_default mc3p_4
756 Filter: (a > 10)
757 (9 rows)
759 explain (costs off) select * from mc3p where a >= 10;
760 QUERY PLAN
761 ---------------------------------------
762 Append
763 -> Seq Scan on mc3p1 mc3p_1
764 Filter: (a >= 10)
765 -> Seq Scan on mc3p2 mc3p_2
766 Filter: (a >= 10)
767 -> Seq Scan on mc3p3 mc3p_3
768 Filter: (a >= 10)
769 -> Seq Scan on mc3p4 mc3p_4
770 Filter: (a >= 10)
771 -> Seq Scan on mc3p5 mc3p_5
772 Filter: (a >= 10)
773 -> Seq Scan on mc3p6 mc3p_6
774 Filter: (a >= 10)
775 -> Seq Scan on mc3p7 mc3p_7
776 Filter: (a >= 10)
777 -> Seq Scan on mc3p_default mc3p_8
778 Filter: (a >= 10)
779 (17 rows)
781 explain (costs off) select * from mc3p where a < 10;
782 QUERY PLAN
783 ---------------------------------------
784 Append
785 -> Seq Scan on mc3p0 mc3p_1
786 Filter: (a < 10)
787 -> Seq Scan on mc3p1 mc3p_2
788 Filter: (a < 10)
789 -> Seq Scan on mc3p_default mc3p_3
790 Filter: (a < 10)
791 (7 rows)
793 explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
794 QUERY PLAN
795 -----------------------------------------------
796 Append
797 -> Seq Scan on mc3p0 mc3p_1
798 Filter: ((a <= 10) AND (abs(b) < 10))
799 -> Seq Scan on mc3p1 mc3p_2
800 Filter: ((a <= 10) AND (abs(b) < 10))
801 -> Seq Scan on mc3p2 mc3p_3
802 Filter: ((a <= 10) AND (abs(b) < 10))
803 -> Seq Scan on mc3p_default mc3p_4
804 Filter: ((a <= 10) AND (abs(b) < 10))
805 (9 rows)
807 explain (costs off) select * from mc3p where a = 11 and abs(b) = 0;
808 QUERY PLAN
809 ---------------------------------------
810 Seq Scan on mc3p_default mc3p
811 Filter: ((a = 11) AND (abs(b) = 0))
812 (2 rows)
814 explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
815 QUERY PLAN
816 ------------------------------------------------------
817 Seq Scan on mc3p6 mc3p
818 Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))
819 (2 rows)
821 explain (costs off) select * from mc3p where a > 20;
822 QUERY PLAN
823 ---------------------------------------
824 Append
825 -> Seq Scan on mc3p7 mc3p_1
826 Filter: (a > 20)
827 -> Seq Scan on mc3p_default mc3p_2
828 Filter: (a > 20)
829 (5 rows)
831 explain (costs off) select * from mc3p where a >= 20;
832 QUERY PLAN
833 ---------------------------------------
834 Append
835 -> Seq Scan on mc3p5 mc3p_1
836 Filter: (a >= 20)
837 -> Seq Scan on mc3p6 mc3p_2
838 Filter: (a >= 20)
839 -> Seq Scan on mc3p7 mc3p_3
840 Filter: (a >= 20)
841 -> Seq Scan on mc3p_default mc3p_4
842 Filter: (a >= 20)
843 (9 rows)
845 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20);
846 QUERY PLAN
847 ---------------------------------------------------------------------------------------------------------------------------------
848 Append
849 -> Seq Scan on mc3p1 mc3p_1
850 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
851 -> Seq Scan on mc3p2 mc3p_2
852 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
853 -> Seq Scan on mc3p5 mc3p_3
854 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
855 -> Seq Scan on mc3p_default mc3p_4
856 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
857 (9 rows)
859 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1;
860 QUERY PLAN
861 --------------------------------------------------------------------------------------------------------------------------------------------
862 Append
863 -> Seq Scan on mc3p0 mc3p_1
864 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
865 -> Seq Scan on mc3p1 mc3p_2
866 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
867 -> Seq Scan on mc3p2 mc3p_3
868 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
869 -> Seq Scan on mc3p5 mc3p_4
870 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
871 -> Seq Scan on mc3p_default mc3p_5
872 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
873 (11 rows)
875 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1 or a = 1;
876 QUERY PLAN
877 -------------------------------------------------------------------------------------------------------------------------------------------------------
878 Append
879 -> Seq Scan on mc3p0 mc3p_1
880 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
881 -> Seq Scan on mc3p1 mc3p_2
882 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
883 -> Seq Scan on mc3p2 mc3p_3
884 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
885 -> Seq Scan on mc3p5 mc3p_4
886 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
887 -> Seq Scan on mc3p_default mc3p_5
888 Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
889 (11 rows)
891 explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
892 QUERY PLAN
893 ------------------------------------------------------
894 Append
895 -> Seq Scan on mc3p0 mc3p_1
896 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
897 -> Seq Scan on mc3p1 mc3p_2
898 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
899 -> Seq Scan on mc3p2 mc3p_3
900 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
901 -> Seq Scan on mc3p3 mc3p_4
902 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
903 -> Seq Scan on mc3p4 mc3p_5
904 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
905 -> Seq Scan on mc3p5 mc3p_6
906 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
907 -> Seq Scan on mc3p6 mc3p_7
908 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
909 -> Seq Scan on mc3p7 mc3p_8
910 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
911 -> Seq Scan on mc3p_default mc3p_9
912 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
913 (19 rows)
915 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10);
916 QUERY PLAN
917 ------------------------------------------------------------------------------
918 Append
919 -> Seq Scan on mc3p0 mc3p_1
920 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
921 -> Seq Scan on mc3p1 mc3p_2
922 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
923 -> Seq Scan on mc3p2 mc3p_3
924 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
925 -> Seq Scan on mc3p3 mc3p_4
926 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
927 -> Seq Scan on mc3p4 mc3p_5
928 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
929 -> Seq Scan on mc3p_default mc3p_6
930 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
931 (13 rows)
933 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
934 QUERY PLAN
935 -----------------------------------------------------------------------------
936 Append
937 -> Seq Scan on mc3p0 mc3p_1
938 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
939 -> Seq Scan on mc3p1 mc3p_2
940 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
941 -> Seq Scan on mc3p2 mc3p_3
942 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
943 -> Seq Scan on mc3p_default mc3p_4
944 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
945 (9 rows)
947 -- a simpler multi-column keys case
948 create table mc2p (a int, b int) partition by range (a, b);
949 create table mc2p_default partition of mc2p default;
950 create table mc2p0 partition of mc2p for values from (minvalue, minvalue) to (1, minvalue);
951 create table mc2p1 partition of mc2p for values from (1, minvalue) to (1, 1);
952 create table mc2p2 partition of mc2p for values from (1, 1) to (2, minvalue);
953 create table mc2p3 partition of mc2p for values from (2, minvalue) to (2, 1);
954 create table mc2p4 partition of mc2p for values from (2, 1) to (2, maxvalue);
955 create table mc2p5 partition of mc2p for values from (2, maxvalue) to (maxvalue, maxvalue);
956 explain (costs off) select * from mc2p where a < 2;
957 QUERY PLAN
958 ---------------------------------------
959 Append
960 -> Seq Scan on mc2p0 mc2p_1
961 Filter: (a < 2)
962 -> Seq Scan on mc2p1 mc2p_2
963 Filter: (a < 2)
964 -> Seq Scan on mc2p2 mc2p_3
965 Filter: (a < 2)
966 -> Seq Scan on mc2p_default mc2p_4
967 Filter: (a < 2)
968 (9 rows)
970 explain (costs off) select * from mc2p where a = 2 and b < 1;
971 QUERY PLAN
972 ---------------------------------
973 Seq Scan on mc2p3 mc2p
974 Filter: ((b < 1) AND (a = 2))
975 (2 rows)
977 explain (costs off) select * from mc2p where a > 1;
978 QUERY PLAN
979 ---------------------------------------
980 Append
981 -> Seq Scan on mc2p2 mc2p_1
982 Filter: (a > 1)
983 -> Seq Scan on mc2p3 mc2p_2
984 Filter: (a > 1)
985 -> Seq Scan on mc2p4 mc2p_3
986 Filter: (a > 1)
987 -> Seq Scan on mc2p5 mc2p_4
988 Filter: (a > 1)
989 -> Seq Scan on mc2p_default mc2p_5
990 Filter: (a > 1)
991 (11 rows)
993 explain (costs off) select * from mc2p where a = 1 and b > 1;
994 QUERY PLAN
995 ---------------------------------
996 Seq Scan on mc2p2 mc2p
997 Filter: ((b > 1) AND (a = 1))
998 (2 rows)
1000 -- all partitions but the default one should be pruned
1001 explain (costs off) select * from mc2p where a = 1 and b is null;
1002 QUERY PLAN
1003 -------------------------------------
1004 Seq Scan on mc2p_default mc2p
1005 Filter: ((b IS NULL) AND (a = 1))
1006 (2 rows)
1008 explain (costs off) select * from mc2p where a is null and b is null;
1009 QUERY PLAN
1010 -----------------------------------------
1011 Seq Scan on mc2p_default mc2p
1012 Filter: ((a IS NULL) AND (b IS NULL))
1013 (2 rows)
1015 explain (costs off) select * from mc2p where a is null and b = 1;
1016 QUERY PLAN
1017 -------------------------------------
1018 Seq Scan on mc2p_default mc2p
1019 Filter: ((a IS NULL) AND (b = 1))
1020 (2 rows)
1022 explain (costs off) select * from mc2p where a is null;
1023 QUERY PLAN
1024 -------------------------------
1025 Seq Scan on mc2p_default mc2p
1026 Filter: (a IS NULL)
1027 (2 rows)
1029 explain (costs off) select * from mc2p where b is null;
1030 QUERY PLAN
1031 -------------------------------
1032 Seq Scan on mc2p_default mc2p
1033 Filter: (b IS NULL)
1034 (2 rows)
1036 -- boolean partitioning
1037 create table boolpart (a bool) partition by list (a);
1038 create table boolpart_default partition of boolpart default;
1039 create table boolpart_t partition of boolpart for values in ('true');
1040 create table boolpart_f partition of boolpart for values in ('false');
1041 explain (costs off) select * from boolpart where a in (true, false);
1042 QUERY PLAN
1043 ------------------------------------------------
1044 Append
1045 -> Seq Scan on boolpart_f boolpart_1
1046 Filter: (a = ANY ('{t,f}'::boolean[]))
1047 -> Seq Scan on boolpart_t boolpart_2
1048 Filter: (a = ANY ('{t,f}'::boolean[]))
1049 (5 rows)
1051 explain (costs off) select * from boolpart where a = false;
1052 QUERY PLAN
1053 ---------------------------------
1054 Seq Scan on boolpart_f boolpart
1055 Filter: (NOT a)
1056 (2 rows)
1058 explain (costs off) select * from boolpart where not a = false;
1059 QUERY PLAN
1060 ---------------------------------
1061 Seq Scan on boolpart_t boolpart
1062 Filter: a
1063 (2 rows)
1065 explain (costs off) select * from boolpart where a is true or a is not true;
1066 QUERY PLAN
1067 --------------------------------------------------
1068 Append
1069 -> Seq Scan on boolpart_f boolpart_1
1070 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1071 -> Seq Scan on boolpart_t boolpart_2
1072 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1073 (5 rows)
1075 explain (costs off) select * from boolpart where a is not true;
1076 QUERY PLAN
1077 ---------------------------------
1078 Seq Scan on boolpart_f boolpart
1079 Filter: (a IS NOT TRUE)
1080 (2 rows)
1082 explain (costs off) select * from boolpart where a is not true and a is not false;
1083 QUERY PLAN
1084 --------------------------
1085 Result
1086 One-Time Filter: false
1087 (2 rows)
1089 explain (costs off) select * from boolpart where a is unknown;
1090 QUERY PLAN
1091 -----------------------------------------------
1092 Append
1093 -> Seq Scan on boolpart_f boolpart_1
1094 Filter: (a IS UNKNOWN)
1095 -> Seq Scan on boolpart_t boolpart_2
1096 Filter: (a IS UNKNOWN)
1097 -> Seq Scan on boolpart_default boolpart_3
1098 Filter: (a IS UNKNOWN)
1099 (7 rows)
1101 explain (costs off) select * from boolpart where a is not unknown;
1102 QUERY PLAN
1103 -----------------------------------------------
1104 Append
1105 -> Seq Scan on boolpart_f boolpart_1
1106 Filter: (a IS NOT UNKNOWN)
1107 -> Seq Scan on boolpart_t boolpart_2
1108 Filter: (a IS NOT UNKNOWN)
1109 -> Seq Scan on boolpart_default boolpart_3
1110 Filter: (a IS NOT UNKNOWN)
1111 (7 rows)
1113 create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
1114 create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
1115 create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
1116 create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
1117 create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
1118 -- try a more complex case that's been known to trip up pruning in the past
1119 explain (costs off) select * from boolrangep where not a and not b and c = 25;
1120 QUERY PLAN
1121 ----------------------------------------------
1122 Seq Scan on boolrangep_ff1 boolrangep
1123 Filter: ((NOT a) AND (NOT b) AND (c = 25))
1124 (2 rows)
1126 -- test scalar-to-array operators
1127 create table coercepart (a varchar) partition by list (a);
1128 create table coercepart_ab partition of coercepart for values in ('ab');
1129 create table coercepart_bc partition of coercepart for values in ('bc');
1130 create table coercepart_cd partition of coercepart for values in ('cd');
1131 explain (costs off) select * from coercepart where a in ('ab', to_char(125, '999'));
1132 QUERY PLAN
1133 ------------------------------------------------------------------------------------------------------------------------------
1134 Append
1135 -> Seq Scan on coercepart_ab coercepart_1
1136 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[]))
1137 -> Seq Scan on coercepart_bc coercepart_2
1138 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[]))
1139 -> Seq Scan on coercepart_cd coercepart_3
1140 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[]))
1141 (7 rows)
1143 explain (costs off) select * from coercepart where a ~ any ('{ab}');
1144 QUERY PLAN
1145 ----------------------------------------------------
1146 Append
1147 -> Seq Scan on coercepart_ab coercepart_1
1148 Filter: ((a)::text ~ ANY ('{ab}'::text[]))
1149 -> Seq Scan on coercepart_bc coercepart_2
1150 Filter: ((a)::text ~ ANY ('{ab}'::text[]))
1151 -> Seq Scan on coercepart_cd coercepart_3
1152 Filter: ((a)::text ~ ANY ('{ab}'::text[]))
1153 (7 rows)
1155 explain (costs off) select * from coercepart where a !~ all ('{ab}');
1156 QUERY PLAN
1157 -----------------------------------------------------
1158 Append
1159 -> Seq Scan on coercepart_ab coercepart_1
1160 Filter: ((a)::text !~ ALL ('{ab}'::text[]))
1161 -> Seq Scan on coercepart_bc coercepart_2
1162 Filter: ((a)::text !~ ALL ('{ab}'::text[]))
1163 -> Seq Scan on coercepart_cd coercepart_3
1164 Filter: ((a)::text !~ ALL ('{ab}'::text[]))
1165 (7 rows)
1167 explain (costs off) select * from coercepart where a ~ any ('{ab,bc}');
1168 QUERY PLAN
1169 -------------------------------------------------------
1170 Append
1171 -> Seq Scan on coercepart_ab coercepart_1
1172 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
1173 -> Seq Scan on coercepart_bc coercepart_2
1174 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
1175 -> Seq Scan on coercepart_cd coercepart_3
1176 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
1177 (7 rows)
1179 explain (costs off) select * from coercepart where a !~ all ('{ab,bc}');
1180 QUERY PLAN
1181 --------------------------------------------------------
1182 Append
1183 -> Seq Scan on coercepart_ab coercepart_1
1184 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
1185 -> Seq Scan on coercepart_bc coercepart_2
1186 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
1187 -> Seq Scan on coercepart_cd coercepart_3
1188 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
1189 (7 rows)
1191 explain (costs off) select * from coercepart where a = any ('{ab,bc}');
1192 QUERY PLAN
1193 -------------------------------------------------------
1194 Append
1195 -> Seq Scan on coercepart_ab coercepart_1
1196 Filter: ((a)::text = ANY ('{ab,bc}'::text[]))
1197 -> Seq Scan on coercepart_bc coercepart_2
1198 Filter: ((a)::text = ANY ('{ab,bc}'::text[]))
1199 (5 rows)
1201 explain (costs off) select * from coercepart where a = any ('{ab,null}');
1202 QUERY PLAN
1203 ---------------------------------------------------
1204 Seq Scan on coercepart_ab coercepart
1205 Filter: ((a)::text = ANY ('{ab,NULL}'::text[]))
1206 (2 rows)
1208 explain (costs off) select * from coercepart where a = any (null::text[]);
1209 QUERY PLAN
1210 --------------------------
1211 Result
1212 One-Time Filter: false
1213 (2 rows)
1215 explain (costs off) select * from coercepart where a = all ('{ab}');
1216 QUERY PLAN
1217 ----------------------------------------------
1218 Seq Scan on coercepart_ab coercepart
1219 Filter: ((a)::text = ALL ('{ab}'::text[]))
1220 (2 rows)
1222 explain (costs off) select * from coercepart where a = all ('{ab,bc}');
1223 QUERY PLAN
1224 --------------------------
1225 Result
1226 One-Time Filter: false
1227 (2 rows)
1229 explain (costs off) select * from coercepart where a = all ('{ab,null}');
1230 QUERY PLAN
1231 --------------------------
1232 Result
1233 One-Time Filter: false
1234 (2 rows)
1236 explain (costs off) select * from coercepart where a = all (null::text[]);
1237 QUERY PLAN
1238 --------------------------
1239 Result
1240 One-Time Filter: false
1241 (2 rows)
1243 drop table coercepart;
1244 CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a);
1245 CREATE TABLE part_p1 PARTITION OF part FOR VALUES IN (-2,-1,0,1,2);
1246 CREATE TABLE part_p2 PARTITION OF part DEFAULT PARTITION BY RANGE(a);
1247 CREATE TABLE part_p2_p1 PARTITION OF part_p2 DEFAULT;
1248 CREATE TABLE part_rev (b INT, c INT, a INT);
1249 ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- fail
1250 ERROR: table "part_rev" contains column "c" not found in parent "part"
1251 DETAIL: The new partition may contain only the columns present in parent.
1252 ALTER TABLE part_rev DROP COLUMN c;
1253 ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- now it's ok
1254 INSERT INTO part VALUES (-1,-1), (1,1), (2,NULL), (NULL,-2),(NULL,NULL);
1255 EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS NULL ORDER BY 1, 2, 3;
1256 QUERY PLAN
1257 ---------------------------------------------------------
1258 Sort
1259 Sort Key: ((part.tableoid)::regclass), part.a, part.b
1260 -> Seq Scan on part_p2_p1 part
1261 Filter: (a IS NULL)
1262 (4 rows)
1264 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x;
1265 QUERY PLAN
1266 -----------------------------------------------
1267 Sort
1268 Output: p.x, p.b
1269 Sort Key: p.x
1270 -> Append
1271 -> Seq Scan on public.part_p1 p_1
1272 Output: p_1.x, p_1.b
1273 -> Seq Scan on public.part_rev p_2
1274 Output: p_2.x, p_2.b
1275 -> Seq Scan on public.part_p2_p1 p_3
1276 Output: p_3.x, p_3.b
1277 (10 rows)
1279 --
1280 -- some more cases
1281 --
1282 --
1283 -- pruning for partitioned table appearing inside a sub-query
1284 --
1285 -- pruning won't work for mc3p, because some keys are Params
1286 explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
1287 QUERY PLAN
1288 -----------------------------------------------------------------------
1289 Nested Loop
1290 -> Append
1291 -> Seq Scan on mc2p1 t1_1
1292 Filter: (a = 1)
1293 -> Seq Scan on mc2p2 t1_2
1294 Filter: (a = 1)
1295 -> Seq Scan on mc2p_default t1_3
1296 Filter: (a = 1)
1297 -> Aggregate
1298 -> Append
1299 -> Seq Scan on mc3p0 t2_1
1300 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1301 -> Seq Scan on mc3p1 t2_2
1302 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1303 -> Seq Scan on mc3p2 t2_3
1304 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1305 -> Seq Scan on mc3p3 t2_4
1306 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1307 -> Seq Scan on mc3p4 t2_5
1308 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1309 -> Seq Scan on mc3p5 t2_6
1310 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1311 -> Seq Scan on mc3p6 t2_7
1312 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1313 -> Seq Scan on mc3p7 t2_8
1314 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1315 -> Seq Scan on mc3p_default t2_9
1316 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1317 (28 rows)
1319 -- pruning should work fine, because values for a prefix of keys (a, b) are
1320 -- available
1321 explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1;
1322 QUERY PLAN
1323 -----------------------------------------------------------------------
1324 Nested Loop
1325 -> Append
1326 -> Seq Scan on mc2p1 t1_1
1327 Filter: (a = 1)
1328 -> Seq Scan on mc2p2 t1_2
1329 Filter: (a = 1)
1330 -> Seq Scan on mc2p_default t1_3
1331 Filter: (a = 1)
1332 -> Aggregate
1333 -> Append
1334 -> Seq Scan on mc3p0 t2_1
1335 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
1336 -> Seq Scan on mc3p1 t2_2
1337 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
1338 -> Seq Scan on mc3p_default t2_3
1339 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
1340 (16 rows)
1342 -- also here, because values for all keys are provided
1343 explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
1344 QUERY PLAN
1345 --------------------------------------------------------------
1346 Nested Loop
1347 -> Aggregate
1348 -> Seq Scan on mc3p1 t2
1349 Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1))
1350 -> Append
1351 -> Seq Scan on mc2p1 t1_1
1352 Filter: (a = 1)
1353 -> Seq Scan on mc2p2 t1_2
1354 Filter: (a = 1)
1355 -> Seq Scan on mc2p_default t1_3
1356 Filter: (a = 1)
1357 (11 rows)
1359 --
1360 -- pruning with clauses containing <> operator
1361 --
1362 -- doesn't prune range partitions
1363 create table rp (a int) partition by range (a);
1364 create table rp0 partition of rp for values from (minvalue) to (1);
1365 create table rp1 partition of rp for values from (1) to (2);
1366 create table rp2 partition of rp for values from (2) to (maxvalue);
1367 explain (costs off) select * from rp where a <> 1;
1368 QUERY PLAN
1369 ----------------------------
1370 Append
1371 -> Seq Scan on rp0 rp_1
1372 Filter: (a <> 1)
1373 -> Seq Scan on rp1 rp_2
1374 Filter: (a <> 1)
1375 -> Seq Scan on rp2 rp_3
1376 Filter: (a <> 1)
1377 (7 rows)
1379 explain (costs off) select * from rp where a <> 1 and a <> 2;
1380 QUERY PLAN
1381 -----------------------------------------
1382 Append
1383 -> Seq Scan on rp0 rp_1
1384 Filter: ((a <> 1) AND (a <> 2))
1385 -> Seq Scan on rp1 rp_2
1386 Filter: ((a <> 1) AND (a <> 2))
1387 -> Seq Scan on rp2 rp_3
1388 Filter: ((a <> 1) AND (a <> 2))
1389 (7 rows)
1391 -- null partition should be eliminated due to strict <> clause.
1392 explain (costs off) select * from lp where a <> 'a';
1393 QUERY PLAN
1394 ------------------------------------
1395 Append
1396 -> Seq Scan on lp_ad lp_1
1397 Filter: (a <> 'a'::bpchar)
1398 -> Seq Scan on lp_bc lp_2
1399 Filter: (a <> 'a'::bpchar)
1400 -> Seq Scan on lp_ef lp_3
1401 Filter: (a <> 'a'::bpchar)
1402 -> Seq Scan on lp_g lp_4
1403 Filter: (a <> 'a'::bpchar)
1404 -> Seq Scan on lp_default lp_5
1405 Filter: (a <> 'a'::bpchar)
1406 (11 rows)
1408 -- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL.
1409 explain (costs off) select * from lp where a <> 'a' and a is null;
1410 QUERY PLAN
1411 --------------------------
1412 Result
1413 One-Time Filter: false
1414 (2 rows)
1416 explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
1417 QUERY PLAN
1418 ------------------------------------------------------------------------------
1419 Append
1420 -> Seq Scan on lp_bc lp_1
1421 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1422 -> Seq Scan on lp_ef lp_2
1423 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1424 -> Seq Scan on lp_g lp_3
1425 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1426 -> Seq Scan on lp_null lp_4
1427 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1428 -> Seq Scan on lp_default lp_5
1429 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1430 (11 rows)
1432 -- check that it also works for a partitioned table that's not root,
1433 -- which in this case are partitions of rlp that are themselves
1434 -- list-partitioned on b
1435 explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null;
1436 QUERY PLAN
1437 ------------------------------------------------------------------------------------------------------------------------------------------
1438 Append
1439 -> Seq Scan on rlp3efgh rlp_1
1440 Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
1441 -> Seq Scan on rlp3_default rlp_2
1442 Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
1443 (5 rows)
1445 --
1446 -- different collations for different keys with same expression
1447 --
1448 create table coll_pruning_multi (a text) partition by range (substr(a, 1) collate "POSIX", substr(a, 1) collate "C");
1449 create table coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a') to ('a', 'e');
1450 create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e') to ('a', 'z');
1451 create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a') to ('b', 'e');
1452 -- no pruning, because no value for the leading key
1453 explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C";
1454 QUERY PLAN
1455 ------------------------------------------------------------
1456 Append
1457 -> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1
1458 Filter: (substr(a, 1) = 'e'::text COLLATE "C")
1459 -> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2
1460 Filter: (substr(a, 1) = 'e'::text COLLATE "C")
1461 -> Seq Scan on coll_pruning_multi3 coll_pruning_multi_3
1462 Filter: (substr(a, 1) = 'e'::text COLLATE "C")
1463 (7 rows)
1465 -- pruning, with a value provided for the leading key
1466 explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' collate "POSIX";
1467 QUERY PLAN
1468 ------------------------------------------------------------
1469 Append
1470 -> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1
1471 Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
1472 -> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2
1473 Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
1474 (5 rows)
1476 -- pruning, with values provided for both keys
1477 explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX";
1478 QUERY PLAN
1479 ---------------------------------------------------------------------------------------------------
1480 Seq Scan on coll_pruning_multi2 coll_pruning_multi
1481 Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX"))
1482 (2 rows)
1484 --
1485 -- LIKE operators don't prune
1486 --
1487 create table like_op_noprune (a text) partition by list (a);
1488 create table like_op_noprune1 partition of like_op_noprune for values in ('ABC');
1489 create table like_op_noprune2 partition of like_op_noprune for values in ('BCD');
1490 explain (costs off) select * from like_op_noprune where a like '%BC';
1491 QUERY PLAN
1492 ------------------------------------------------------
1493 Append
1494 -> Seq Scan on like_op_noprune1 like_op_noprune_1
1495 Filter: (a ~~ '%BC'::text)
1496 -> Seq Scan on like_op_noprune2 like_op_noprune_2
1497 Filter: (a ~~ '%BC'::text)
1498 (5 rows)
1500 --
1501 -- tests wherein clause value requires a cross-type comparison function
1502 --
1503 create table lparted_by_int2 (a smallint) partition by list (a);
1504 create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1);
1505 create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384);
1506 explain (costs off) select * from lparted_by_int2 where a = 100000000000000;
1507 QUERY PLAN
1508 --------------------------
1509 Result
1510 One-Time Filter: false
1511 (2 rows)
1513 create table rparted_by_int2 (a smallint) partition by range (a);
1514 create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10);
1515 create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384);
1516 -- all partitions pruned
1517 explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
1518 QUERY PLAN
1519 --------------------------
1520 Result
1521 One-Time Filter: false
1522 (2 rows)
1524 create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue);
1525 -- all partitions but rparted_by_int2_maxvalue pruned
1526 explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
1527 QUERY PLAN
1528 ------------------------------------------------------
1529 Seq Scan on rparted_by_int2_maxvalue rparted_by_int2
1530 Filter: (a > '100000000000000'::bigint)
1531 (2 rows)
1533 drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
1534 --
1535 -- Test Partition pruning for HASH partitioning
1536 --
1537 -- Use hand-rolled hash functions and operator classes to get predictable
1538 -- result on different machines. See the definitions of
1539 -- part_part_test_int4_ops and part_test_text_ops in insert.sql.
1540 --
1541 create table hp (a int, b text, c int)
1542 partition by hash (a part_test_int4_ops, b part_test_text_ops);
1543 create table hp0 partition of hp for values with (modulus 4, remainder 0);
1544 create table hp3 partition of hp for values with (modulus 4, remainder 3);
1545 create table hp1 partition of hp for values with (modulus 4, remainder 1);
1546 create table hp2 partition of hp for values with (modulus 4, remainder 2);
1547 insert into hp values (null, null, 0);
1548 insert into hp values (1, null, 1);
1549 insert into hp values (1, 'xxx', 2);
1550 insert into hp values (null, 'xxx', 3);
1551 insert into hp values (2, 'xxx', 4);
1552 insert into hp values (1, 'abcde', 5);
1553 select tableoid::regclass, * from hp order by c;
1554 tableoid | a | b | c
1555 ----------+---+-------+---
1556 hp0 | | | 0
1557 hp1 | 1 | | 1
1558 hp0 | 1 | xxx | 2
1559 hp2 | | xxx | 3
1560 hp3 | 2 | xxx | 4
1561 hp2 | 1 | abcde | 5
1562 (6 rows)
1564 -- partial keys won't prune, nor would non-equality conditions
1565 explain (costs off) select * from hp where a = 1;
1566 QUERY PLAN
1567 ----------------------------
1568 Append
1569 -> Seq Scan on hp0 hp_1
1570 Filter: (a = 1)
1571 -> Seq Scan on hp1 hp_2
1572 Filter: (a = 1)
1573 -> Seq Scan on hp2 hp_3
1574 Filter: (a = 1)
1575 -> Seq Scan on hp3 hp_4
1576 Filter: (a = 1)
1577 (9 rows)
1579 explain (costs off) select * from hp where b = 'xxx';
1580 QUERY PLAN
1581 -----------------------------------
1582 Append
1583 -> Seq Scan on hp0 hp_1
1584 Filter: (b = 'xxx'::text)
1585 -> Seq Scan on hp1 hp_2
1586 Filter: (b = 'xxx'::text)
1587 -> Seq Scan on hp2 hp_3
1588 Filter: (b = 'xxx'::text)
1589 -> Seq Scan on hp3 hp_4
1590 Filter: (b = 'xxx'::text)
1591 (9 rows)
1593 explain (costs off) select * from hp where a is null;
1594 QUERY PLAN
1595 -----------------------------
1596 Append
1597 -> Seq Scan on hp0 hp_1
1598 Filter: (a IS NULL)
1599 -> Seq Scan on hp1 hp_2
1600 Filter: (a IS NULL)
1601 -> Seq Scan on hp2 hp_3
1602 Filter: (a IS NULL)
1603 -> Seq Scan on hp3 hp_4
1604 Filter: (a IS NULL)
1605 (9 rows)
1607 explain (costs off) select * from hp where b is null;
1608 QUERY PLAN
1609 -----------------------------
1610 Append
1611 -> Seq Scan on hp0 hp_1
1612 Filter: (b IS NULL)
1613 -> Seq Scan on hp1 hp_2
1614 Filter: (b IS NULL)
1615 -> Seq Scan on hp2 hp_3
1616 Filter: (b IS NULL)
1617 -> Seq Scan on hp3 hp_4
1618 Filter: (b IS NULL)
1619 (9 rows)
1621 explain (costs off) select * from hp where a < 1 and b = 'xxx';
1622 QUERY PLAN
1623 -------------------------------------------------
1624 Append
1625 -> Seq Scan on hp0 hp_1
1626 Filter: ((a < 1) AND (b = 'xxx'::text))
1627 -> Seq Scan on hp1 hp_2
1628 Filter: ((a < 1) AND (b = 'xxx'::text))
1629 -> Seq Scan on hp2 hp_3
1630 Filter: ((a < 1) AND (b = 'xxx'::text))
1631 -> Seq Scan on hp3 hp_4
1632 Filter: ((a < 1) AND (b = 'xxx'::text))
1633 (9 rows)
1635 explain (costs off) select * from hp where a <> 1 and b = 'yyy';
1636 QUERY PLAN
1637 --------------------------------------------------
1638 Append
1639 -> Seq Scan on hp0 hp_1
1640 Filter: ((a <> 1) AND (b = 'yyy'::text))
1641 -> Seq Scan on hp1 hp_2
1642 Filter: ((a <> 1) AND (b = 'yyy'::text))
1643 -> Seq Scan on hp2 hp_3
1644 Filter: ((a <> 1) AND (b = 'yyy'::text))
1645 -> Seq Scan on hp3 hp_4
1646 Filter: ((a <> 1) AND (b = 'yyy'::text))
1647 (9 rows)
1649 explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
1650 QUERY PLAN
1651 ---------------------------------------------------
1652 Append
1653 -> Seq Scan on hp0 hp_1
1654 Filter: ((a <> 1) AND (b <> 'xxx'::text))
1655 -> Seq Scan on hp1 hp_2
1656 Filter: ((a <> 1) AND (b <> 'xxx'::text))
1657 -> Seq Scan on hp2 hp_3
1658 Filter: ((a <> 1) AND (b <> 'xxx'::text))
1659 -> Seq Scan on hp3 hp_4
1660 Filter: ((a <> 1) AND (b <> 'xxx'::text))
1661 (9 rows)
1663 -- pruning should work if either a value or a IS NULL clause is provided for
1664 -- each of the keys
1665 explain (costs off) select * from hp where a is null and b is null;
1666 QUERY PLAN
1667 -----------------------------------------
1668 Seq Scan on hp0 hp
1669 Filter: ((a IS NULL) AND (b IS NULL))
1670 (2 rows)
1672 explain (costs off) select * from hp where a = 1 and b is null;
1673 QUERY PLAN
1674 -------------------------------------
1675 Seq Scan on hp1 hp
1676 Filter: ((b IS NULL) AND (a = 1))
1677 (2 rows)
1679 explain (costs off) select * from hp where a = 1 and b = 'xxx';
1680 QUERY PLAN
1681 -------------------------------------------
1682 Seq Scan on hp0 hp
1683 Filter: ((a = 1) AND (b = 'xxx'::text))
1684 (2 rows)
1686 explain (costs off) select * from hp where a is null and b = 'xxx';
1687 QUERY PLAN
1688 -----------------------------------------------
1689 Seq Scan on hp2 hp
1690 Filter: ((a IS NULL) AND (b = 'xxx'::text))
1691 (2 rows)
1693 explain (costs off) select * from hp where a = 2 and b = 'xxx';
1694 QUERY PLAN
1695 -------------------------------------------
1696 Seq Scan on hp3 hp
1697 Filter: ((a = 2) AND (b = 'xxx'::text))
1698 (2 rows)
1700 explain (costs off) select * from hp where a = 1 and b = 'abcde';
1701 QUERY PLAN
1702 ---------------------------------------------
1703 Seq Scan on hp2 hp
1704 Filter: ((a = 1) AND (b = 'abcde'::text))
1705 (2 rows)
1707 explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null);
1708 QUERY PLAN
1709 -------------------------------------------------------------------------------------------------------------------------
1710 Append
1711 -> Seq Scan on hp0 hp_1
1712 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1713 -> Seq Scan on hp2 hp_2
1714 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1715 -> Seq Scan on hp3 hp_3
1716 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
1717 (7 rows)
1719 -- test pruning when not all the partitions exist
1720 drop table hp1;
1721 drop table hp3;
1722 explain (costs off) select * from hp where a = 1 and b = 'abcde';
1723 QUERY PLAN
1724 ---------------------------------------------
1725 Seq Scan on hp2 hp
1726 Filter: ((a = 1) AND (b = 'abcde'::text))
1727 (2 rows)
1729 explain (costs off) select * from hp where a = 1 and b = 'abcde' and
1730 (c = 2 or c = 3);
1731 QUERY PLAN
1732 ----------------------------------------------------------------------
1733 Seq Scan on hp2 hp
1734 Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
1735 (2 rows)
1737 drop table hp2;
1738 explain (costs off) select * from hp where a = 1 and b = 'abcde' and
1739 (c = 2 or c = 3);
1740 QUERY PLAN
1741 --------------------------
1742 Result
1743 One-Time Filter: false
1744 (2 rows)
1746 drop table hp;
1747 --
1748 -- Test runtime partition pruning
1749 --
1750 create table ab (a int not null, b int not null) partition by list (a);
1751 create table ab_a2 partition of ab for values in(2) partition by list (b);
1752 create table ab_a2_b1 partition of ab_a2 for values in (1);
1753 create table ab_a2_b2 partition of ab_a2 for values in (2);
1754 create table ab_a2_b3 partition of ab_a2 for values in (3);
1755 create table ab_a1 partition of ab for values in(1) partition by list (b);
1756 create table ab_a1_b1 partition of ab_a1 for values in (1);
1757 create table ab_a1_b2 partition of ab_a1 for values in (2);
1758 create table ab_a1_b3 partition of ab_a1 for values in (3);
1759 create table ab_a3 partition of ab for values in(3) partition by list (b);
1760 create table ab_a3_b1 partition of ab_a3 for values in (1);
1761 create table ab_a3_b2 partition of ab_a3 for values in (2);
1762 create table ab_a3_b3 partition of ab_a3 for values in (3);
1763 -- Disallow index only scans as concurrent transactions may stop visibility
1764 -- bits being set causing "Heap Fetches" to be unstable in the EXPLAIN ANALYZE
1765 -- output.
1766 set enable_indexonlyscan = off;
1767 prepare ab_q1 (int, int, int) as
1768 select * from ab where a between $1 and $2 and b <= $3;
1769 explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
1770 QUERY PLAN
1771 ---------------------------------------------------------
1772 Append (actual rows=0 loops=1)
1773 Subplans Removed: 6
1774 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
1775 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1776 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
1777 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1778 -> Seq Scan on ab_a2_b3 ab_3 (actual rows=0 loops=1)
1779 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1780 (8 rows)
1782 explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
1783 QUERY PLAN
1784 ---------------------------------------------------------
1785 Append (actual rows=0 loops=1)
1786 Subplans Removed: 3
1787 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
1788 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1789 -> Seq Scan on ab_a1_b2 ab_2 (actual rows=0 loops=1)
1790 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1791 -> Seq Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
1792 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1793 -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
1794 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1795 -> Seq Scan on ab_a2_b2 ab_5 (actual rows=0 loops=1)
1796 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1797 -> Seq Scan on ab_a2_b3 ab_6 (actual rows=0 loops=1)
1798 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
1799 (14 rows)
1801 deallocate ab_q1;
1802 -- Runtime pruning after optimizer pruning
1803 prepare ab_q1 (int, int) as
1804 select a from ab where a between $1 and $2 and b < 3;
1805 explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
1806 QUERY PLAN
1807 ---------------------------------------------------------
1808 Append (actual rows=0 loops=1)
1809 Subplans Removed: 4
1810 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
1811 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
1812 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
1813 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
1814 (6 rows)
1816 explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
1817 QUERY PLAN
1818 ---------------------------------------------------------
1819 Append (actual rows=0 loops=1)
1820 Subplans Removed: 2
1821 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
1822 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
1823 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
1824 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
1825 -> Seq Scan on ab_a3_b1 ab_3 (actual rows=0 loops=1)
1826 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
1827 -> Seq Scan on ab_a3_b2 ab_4 (actual rows=0 loops=1)
1828 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
1829 (10 rows)
1831 -- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at
1832 -- different levels of partitioning.
1833 prepare ab_q2 (int, int) as
1834 select a from ab where a between $1 and $2 and b < (select 3);
1835 explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
1836 QUERY PLAN
1837 ---------------------------------------------------------
1838 Append (actual rows=0 loops=1)
1839 Subplans Removed: 6
1840 InitPlan 1 (returns $0)
1841 -> Result (actual rows=1 loops=1)
1842 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
1843 Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
1844 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
1845 Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
1846 -> Seq Scan on ab_a2_b3 ab_3 (never executed)
1847 Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
1848 (10 rows)
1850 -- As above, but swap the PARAM_EXEC Param to the first partition level
1851 prepare ab_q3 (int, int) as
1852 select a from ab where b between $1 and $2 and a < (select 3);
1853 explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
1854 QUERY PLAN
1855 ---------------------------------------------------------
1856 Append (actual rows=0 loops=1)
1857 Subplans Removed: 6
1858 InitPlan 1 (returns $0)
1859 -> Result (actual rows=1 loops=1)
1860 -> Seq Scan on ab_a1_b2 ab_1 (actual rows=0 loops=1)
1861 Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
1862 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
1863 Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
1864 -> Seq Scan on ab_a3_b2 ab_3 (never executed)
1865 Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
1866 (10 rows)
1868 -- Test a backwards Append scan
1869 create table list_part (a int) partition by list (a);
1870 create table list_part1 partition of list_part for values in (1);
1871 create table list_part2 partition of list_part for values in (2);
1872 create table list_part3 partition of list_part for values in (3);
1873 create table list_part4 partition of list_part for values in (4);
1874 insert into list_part select generate_series(1,4);
1875 begin;
1876 -- Don't select an actual value out of the table as the order of the Append's
1877 -- subnodes may not be stable.
1878 declare cur SCROLL CURSOR for select 1 from list_part where a > (select 1) and a < (select 4);
1879 -- move beyond the final row
1880 move 3 from cur;
1881 -- Ensure we get two rows.
1882 fetch backward all from cur;
1883 ?column?
1884 ----------
1885 1
1886 1
1887 (2 rows)
1889 commit;
1890 begin;
1891 -- Test run-time pruning using stable functions
1892 create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
1893 -- Ensure pruning works using a stable function containing no Vars
1894 explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
1895 QUERY PLAN
1896 ------------------------------------------------------------------
1897 Append (actual rows=1 loops=1)
1898 Subplans Removed: 3
1899 -> Seq Scan on list_part1 list_part_1 (actual rows=1 loops=1)
1900 Filter: (a = list_part_fn(1))
1901 (4 rows)
1903 -- Ensure pruning does not take place when the function has a Var parameter
1904 explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
1905 QUERY PLAN
1906 ------------------------------------------------------------------
1907 Append (actual rows=4 loops=1)
1908 -> Seq Scan on list_part1 list_part_1 (actual rows=1 loops=1)
1909 Filter: (a = list_part_fn(a))
1910 -> Seq Scan on list_part2 list_part_2 (actual rows=1 loops=1)
1911 Filter: (a = list_part_fn(a))
1912 -> Seq Scan on list_part3 list_part_3 (actual rows=1 loops=1)
1913 Filter: (a = list_part_fn(a))
1914 -> Seq Scan on list_part4 list_part_4 (actual rows=1 loops=1)
1915 Filter: (a = list_part_fn(a))
1916 (9 rows)
1918 -- Ensure pruning does not take place when the expression contains a Var.
1919 explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
1920 QUERY PLAN
1921 ------------------------------------------------------------------
1922 Append (actual rows=0 loops=1)
1923 -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1)
1924 Filter: (a = (list_part_fn(1) + a))
1925 Rows Removed by Filter: 1
1926 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1)
1927 Filter: (a = (list_part_fn(1) + a))
1928 Rows Removed by Filter: 1
1929 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1)
1930 Filter: (a = (list_part_fn(1) + a))
1931 Rows Removed by Filter: 1
1932 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1)
1933 Filter: (a = (list_part_fn(1) + a))
1934 Rows Removed by Filter: 1
1935 (13 rows)
1937 rollback;
1938 drop table list_part;
1939 -- Parallel append
1940 -- Parallel queries won't necessarily get as many workers as the planner
1941 -- asked for. This affects not only the "Workers Launched:" field of EXPLAIN
1942 -- results, but also row counts and loop counts for parallel scans, Gathers,
1943 -- and everything in between. This function filters out the values we can't
1944 -- rely on to be stable.
1945 -- This removes enough info that you might wonder why bother with EXPLAIN
1946 -- ANALYZE at all. The answer is that we need to see '(never executed)'
1947 -- notations because that's the only way to verify runtime pruning.
1948 create function explain_parallel_append(text) returns setof text
1949 language plpgsql as
1950 $$
1951 declare
1952 ln text;
1953 begin
1954 for ln in
1955 execute format('explain (analyze, costs off, summary off, timing off) %s',
1956 $1)
1957 loop
1958 ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
1959 ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
1960 ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
1961 return next ln;
1962 end loop;
1963 end;
1964 $$;
1965 prepare ab_q4 (int, int) as
1966 select avg(a) from ab where a between $1 and $2 and b < 4;
1967 -- Encourage use of parallel plans
1968 set parallel_setup_cost = 0;
1969 set parallel_tuple_cost = 0;
1970 set min_parallel_table_scan_size = 0;
1971 set max_parallel_workers_per_gather = 2;
1972 select explain_parallel_append('execute ab_q4 (2, 2)');
1973 explain_parallel_append
1974 ------------------------------------------------------------------------------------
1975 Finalize Aggregate (actual rows=N loops=N)
1976 -> Gather (actual rows=N loops=N)
1977 Workers Planned: 2
1978 Workers Launched: N
1979 -> Partial Aggregate (actual rows=N loops=N)
1980 -> Parallel Append (actual rows=N loops=N)
1981 Subplans Removed: 6
1982 -> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N)
1983 Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
1984 -> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N)
1985 Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
1986 -> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N)
1987 Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
1988 (13 rows)
1990 -- Test run-time pruning with IN lists.
1991 prepare ab_q5 (int, int, int) as
1992 select avg(a) from ab where a in($1,$2,$3) and b < 4;
1993 select explain_parallel_append('execute ab_q5 (1, 1, 1)');
1994 explain_parallel_append
1995 ------------------------------------------------------------------------------------
1996 Finalize Aggregate (actual rows=N loops=N)
1997 -> Gather (actual rows=N loops=N)
1998 Workers Planned: 2
1999 Workers Launched: N
2000 -> Partial Aggregate (actual rows=N loops=N)
2001 -> Parallel Append (actual rows=N loops=N)
2002 Subplans Removed: 6
2003 -> Parallel Seq Scan on ab_a1_b1 ab_1 (actual rows=N loops=N)
2004 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2005 -> Parallel Seq Scan on ab_a1_b2 ab_2 (actual rows=N loops=N)
2006 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2007 -> Parallel Seq Scan on ab_a1_b3 ab_3 (actual rows=N loops=N)
2008 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2009 (13 rows)
2011 select explain_parallel_append('execute ab_q5 (2, 3, 3)');
2012 explain_parallel_append
2013 ------------------------------------------------------------------------------------
2014 Finalize Aggregate (actual rows=N loops=N)
2015 -> Gather (actual rows=N loops=N)
2016 Workers Planned: 2
2017 Workers Launched: N
2018 -> Partial Aggregate (actual rows=N loops=N)
2019 -> Parallel Append (actual rows=N loops=N)
2020 Subplans Removed: 3
2021 -> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N)
2022 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2023 -> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N)
2024 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2025 -> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N)
2026 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2027 -> Parallel Seq Scan on ab_a3_b1 ab_4 (actual rows=N loops=N)
2028 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2029 -> Parallel Seq Scan on ab_a3_b2 ab_5 (actual rows=N loops=N)
2030 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2031 -> Parallel Seq Scan on ab_a3_b3 ab_6 (actual rows=N loops=N)
2032 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2033 (19 rows)
2035 -- Try some params whose values do not belong to any partition.
2036 select explain_parallel_append('execute ab_q5 (33, 44, 55)');
2037 explain_parallel_append
2038 -----------------------------------------------------------
2039 Finalize Aggregate (actual rows=N loops=N)
2040 -> Gather (actual rows=N loops=N)
2041 Workers Planned: 2
2042 Workers Launched: N
2043 -> Partial Aggregate (actual rows=N loops=N)
2044 -> Parallel Append (actual rows=N loops=N)
2045 Subplans Removed: 9
2046 (7 rows)
2048 -- Test Parallel Append with PARAM_EXEC Params
2049 select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
2050 explain_parallel_append
2051 ------------------------------------------------------------------------------
2052 Aggregate (actual rows=N loops=N)
2053 InitPlan 1 (returns $0)
2054 -> Result (actual rows=N loops=N)
2055 InitPlan 2 (returns $1)
2056 -> Result (actual rows=N loops=N)
2057 -> Gather (actual rows=N loops=N)
2058 Workers Planned: 2
2059 Params Evaluated: $0, $1
2060 Workers Launched: N
2061 -> Parallel Append (actual rows=N loops=N)
2062 -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
2063 Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
2064 -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
2065 Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
2066 -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
2067 Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
2068 (16 rows)
2070 -- Test pruning during parallel nested loop query
2071 create table lprt_a (a int not null);
2072 -- Insert some values we won't find in ab
2073 insert into lprt_a select 0 from generate_series(1,100);
2074 -- and insert some values that we should find.
2075 insert into lprt_a values(1),(1);
2076 analyze lprt_a;
2077 create index ab_a2_b1_a_idx on ab_a2_b1 (a);
2078 create index ab_a2_b2_a_idx on ab_a2_b2 (a);
2079 create index ab_a2_b3_a_idx on ab_a2_b3 (a);
2080 create index ab_a1_b1_a_idx on ab_a1_b1 (a);
2081 create index ab_a1_b2_a_idx on ab_a1_b2 (a);
2082 create index ab_a1_b3_a_idx on ab_a1_b3 (a);
2083 create index ab_a3_b1_a_idx on ab_a3_b1 (a);
2084 create index ab_a3_b2_a_idx on ab_a3_b2 (a);
2085 create index ab_a3_b3_a_idx on ab_a3_b3 (a);
2086 set enable_hashjoin = 0;
2087 set enable_mergejoin = 0;
2088 set enable_memoize = 0;
2089 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)');
2090 explain_parallel_append
2091 --------------------------------------------------------------------------------------------------------
2092 Finalize Aggregate (actual rows=N loops=N)
2093 -> Gather (actual rows=N loops=N)
2094 Workers Planned: 1
2095 Workers Launched: N
2096 -> Partial Aggregate (actual rows=N loops=N)
2097 -> Nested Loop (actual rows=N loops=N)
2098 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2099 Filter: (a = ANY ('{0,0,1}'::integer[]))
2100 -> Append (actual rows=N loops=N)
2101 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2102 Index Cond: (a = a.a)
2103 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2104 Index Cond: (a = a.a)
2105 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2106 Index Cond: (a = a.a)
2107 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2108 Index Cond: (a = a.a)
2109 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2110 Index Cond: (a = a.a)
2111 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2112 Index Cond: (a = a.a)
2113 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2114 Index Cond: (a = a.a)
2115 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2116 Index Cond: (a = a.a)
2117 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2118 Index Cond: (a = a.a)
2119 (27 rows)
2121 -- Ensure the same partitions are pruned when we make the nested loop
2122 -- parameter an Expr rather than a plain Param.
2123 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1)');
2124 explain_parallel_append
2125 --------------------------------------------------------------------------------------------------------
2126 Finalize Aggregate (actual rows=N loops=N)
2127 -> Gather (actual rows=N loops=N)
2128 Workers Planned: 1
2129 Workers Launched: N
2130 -> Partial Aggregate (actual rows=N loops=N)
2131 -> Nested Loop (actual rows=N loops=N)
2132 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2133 Filter: (a = ANY ('{0,0,1}'::integer[]))
2134 -> Append (actual rows=N loops=N)
2135 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2136 Index Cond: (a = (a.a + 0))
2137 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2138 Index Cond: (a = (a.a + 0))
2139 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2140 Index Cond: (a = (a.a + 0))
2141 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2142 Index Cond: (a = (a.a + 0))
2143 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2144 Index Cond: (a = (a.a + 0))
2145 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2146 Index Cond: (a = (a.a + 0))
2147 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2148 Index Cond: (a = (a.a + 0))
2149 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2150 Index Cond: (a = (a.a + 0))
2151 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2152 Index Cond: (a = (a.a + 0))
2153 (27 rows)
2155 insert into lprt_a values(3),(3);
2156 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)');
2157 explain_parallel_append
2158 --------------------------------------------------------------------------------------------------------
2159 Finalize Aggregate (actual rows=N loops=N)
2160 -> Gather (actual rows=N loops=N)
2161 Workers Planned: 1
2162 Workers Launched: N
2163 -> Partial Aggregate (actual rows=N loops=N)
2164 -> Nested Loop (actual rows=N loops=N)
2165 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2166 Filter: (a = ANY ('{1,0,3}'::integer[]))
2167 -> Append (actual rows=N loops=N)
2168 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2169 Index Cond: (a = a.a)
2170 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2171 Index Cond: (a = a.a)
2172 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2173 Index Cond: (a = a.a)
2174 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2175 Index Cond: (a = a.a)
2176 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2177 Index Cond: (a = a.a)
2178 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2179 Index Cond: (a = a.a)
2180 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (actual rows=N loops=N)
2181 Index Cond: (a = a.a)
2182 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (actual rows=N loops=N)
2183 Index Cond: (a = a.a)
2184 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N)
2185 Index Cond: (a = a.a)
2186 (27 rows)
2188 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
2189 explain_parallel_append
2190 --------------------------------------------------------------------------------------------------------
2191 Finalize Aggregate (actual rows=N loops=N)
2192 -> Gather (actual rows=N loops=N)
2193 Workers Planned: 1
2194 Workers Launched: N
2195 -> Partial Aggregate (actual rows=N loops=N)
2196 -> Nested Loop (actual rows=N loops=N)
2197 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2198 Filter: (a = ANY ('{1,0,0}'::integer[]))
2199 Rows Removed by Filter: N
2200 -> Append (actual rows=N loops=N)
2201 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2202 Index Cond: (a = a.a)
2203 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2204 Index Cond: (a = a.a)
2205 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2206 Index Cond: (a = a.a)
2207 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2208 Index Cond: (a = a.a)
2209 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2210 Index Cond: (a = a.a)
2211 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2212 Index Cond: (a = a.a)
2213 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2214 Index Cond: (a = a.a)
2215 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2216 Index Cond: (a = a.a)
2217 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2218 Index Cond: (a = a.a)
2219 (28 rows)
2221 delete from lprt_a where a = 1;
2222 select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
2223 explain_parallel_append
2224 -------------------------------------------------------------------------------------------------
2225 Finalize Aggregate (actual rows=N loops=N)
2226 -> Gather (actual rows=N loops=N)
2227 Workers Planned: 1
2228 Workers Launched: N
2229 -> Partial Aggregate (actual rows=N loops=N)
2230 -> Nested Loop (actual rows=N loops=N)
2231 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2232 Filter: (a = ANY ('{1,0,0}'::integer[]))
2233 Rows Removed by Filter: N
2234 -> Append (actual rows=N loops=N)
2235 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed)
2236 Index Cond: (a = a.a)
2237 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (never executed)
2238 Index Cond: (a = a.a)
2239 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (never executed)
2240 Index Cond: (a = a.a)
2241 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2242 Index Cond: (a = a.a)
2243 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2244 Index Cond: (a = a.a)
2245 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2246 Index Cond: (a = a.a)
2247 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2248 Index Cond: (a = a.a)
2249 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2250 Index Cond: (a = a.a)
2251 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2252 Index Cond: (a = a.a)
2253 (28 rows)
2255 reset enable_hashjoin;
2256 reset enable_mergejoin;
2257 reset enable_memoize;
2258 reset parallel_setup_cost;
2259 reset parallel_tuple_cost;
2260 reset min_parallel_table_scan_size;
2261 reset max_parallel_workers_per_gather;
2262 -- Test run-time partition pruning with an initplan
2263 explain (analyze, costs off, summary off, timing off)
2264 select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
2265 QUERY PLAN
2266 -------------------------------------------------------------------------
2267 Append (actual rows=0 loops=1)
2268 InitPlan 1 (returns $0)
2269 -> Aggregate (actual rows=1 loops=1)
2270 -> Seq Scan on lprt_a (actual rows=102 loops=1)
2271 InitPlan 2 (returns $1)
2272 -> Aggregate (actual rows=1 loops=1)
2273 -> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1)
2274 -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed)
2275 Recheck Cond: (a = $0)
2276 Filter: (b = $1)
2277 -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
2278 Index Cond: (a = $0)
2279 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed)
2280 Recheck Cond: (a = $0)
2281 Filter: (b = $1)
2282 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
2283 Index Cond: (a = $0)
2284 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed)
2285 Recheck Cond: (a = $0)
2286 Filter: (b = $1)
2287 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
2288 Index Cond: (a = $0)
2289 -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed)
2290 Recheck Cond: (a = $0)
2291 Filter: (b = $1)
2292 -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
2293 Index Cond: (a = $0)
2294 -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed)
2295 Recheck Cond: (a = $0)
2296 Filter: (b = $1)
2297 -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
2298 Index Cond: (a = $0)
2299 -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed)
2300 Recheck Cond: (a = $0)
2301 Filter: (b = $1)
2302 -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
2303 Index Cond: (a = $0)
2304 -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed)
2305 Recheck Cond: (a = $0)
2306 Filter: (b = $1)
2307 -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
2308 Index Cond: (a = $0)
2309 -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0 loops=1)
2310 Recheck Cond: (a = $0)
2311 Filter: (b = $1)
2312 -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
2313 Index Cond: (a = $0)
2314 -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed)
2315 Recheck Cond: (a = $0)
2316 Filter: (b = $1)
2317 -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
2318 Index Cond: (a = $0)
2319 (52 rows)
2321 -- Test run-time partition pruning with UNION ALL parents
2322 explain (analyze, costs off, summary off, timing off)
2323 select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
2324 QUERY PLAN
2325 -------------------------------------------------------------------------------
2326 Append (actual rows=0 loops=1)
2327 InitPlan 1 (returns $0)
2328 -> Result (actual rows=1 loops=1)
2329 -> Append (actual rows=0 loops=1)
2330 -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
2331 Recheck Cond: (a = 1)
2332 Filter: (b = $0)
2333 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2334 Index Cond: (a = 1)
2335 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
2336 Recheck Cond: (a = 1)
2337 Filter: (b = $0)
2338 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
2339 Index Cond: (a = 1)
2340 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
2341 Recheck Cond: (a = 1)
2342 Filter: (b = $0)
2343 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
2344 Index Cond: (a = 1)
2345 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
2346 Filter: (b = $0)
2347 -> Seq Scan on ab_a1_b2 ab_2 (never executed)
2348 Filter: (b = $0)
2349 -> Seq Scan on ab_a1_b3 ab_3 (never executed)
2350 Filter: (b = $0)
2351 -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
2352 Filter: (b = $0)
2353 -> Seq Scan on ab_a2_b2 ab_5 (never executed)
2354 Filter: (b = $0)
2355 -> Seq Scan on ab_a2_b3 ab_6 (never executed)
2356 Filter: (b = $0)
2357 -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1)
2358 Filter: (b = $0)
2359 -> Seq Scan on ab_a3_b2 ab_8 (never executed)
2360 Filter: (b = $0)
2361 -> Seq Scan on ab_a3_b3 ab_9 (never executed)
2362 Filter: (b = $0)
2363 (37 rows)
2365 -- A case containing a UNION ALL with a non-partitioned child.
2366 explain (analyze, costs off, summary off, timing off)
2367 select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
2368 QUERY PLAN
2369 -------------------------------------------------------------------------------
2370 Append (actual rows=0 loops=1)
2371 InitPlan 1 (returns $0)
2372 -> Result (actual rows=1 loops=1)
2373 -> Append (actual rows=0 loops=1)
2374 -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
2375 Recheck Cond: (a = 1)
2376 Filter: (b = $0)
2377 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2378 Index Cond: (a = 1)
2379 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
2380 Recheck Cond: (a = 1)
2381 Filter: (b = $0)
2382 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
2383 Index Cond: (a = 1)
2384 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
2385 Recheck Cond: (a = 1)
2386 Filter: (b = $0)
2387 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
2388 Index Cond: (a = 1)
2389 -> Result (actual rows=0 loops=1)
2390 One-Time Filter: (5 = $0)
2391 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
2392 Filter: (b = $0)
2393 -> Seq Scan on ab_a1_b2 ab_2 (never executed)
2394 Filter: (b = $0)
2395 -> Seq Scan on ab_a1_b3 ab_3 (never executed)
2396 Filter: (b = $0)
2397 -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
2398 Filter: (b = $0)
2399 -> Seq Scan on ab_a2_b2 ab_5 (never executed)
2400 Filter: (b = $0)
2401 -> Seq Scan on ab_a2_b3 ab_6 (never executed)
2402 Filter: (b = $0)
2403 -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1)
2404 Filter: (b = $0)
2405 -> Seq Scan on ab_a3_b2 ab_8 (never executed)
2406 Filter: (b = $0)
2407 -> Seq Scan on ab_a3_b3 ab_9 (never executed)
2408 Filter: (b = $0)
2409 (39 rows)
2411 -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
2412 create table xy_1 (x int, y int);
2413 insert into xy_1 values(100,-10);
2414 set enable_bitmapscan = 0;
2415 set enable_indexscan = 0;
2416 prepare ab_q6 as
2417 select * from (
2418 select tableoid::regclass,a,b from ab
2419 union all
2420 select tableoid::regclass,x,y from xy_1
2421 union all
2422 select tableoid::regclass,a,b from ab
2423 ) ab where a = $1 and b = (select -10);
2424 -- Ensure the xy_1 subplan is not pruned.
2425 explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
2426 QUERY PLAN
2427 --------------------------------------------------
2428 Append (actual rows=0 loops=1)
2429 Subplans Removed: 12
2430 InitPlan 1 (returns $0)
2431 -> Result (actual rows=1 loops=1)
2432 -> Seq Scan on ab_a1_b1 ab_1 (never executed)
2433 Filter: ((a = $1) AND (b = $0))
2434 -> Seq Scan on ab_a1_b2 ab_2 (never executed)
2435 Filter: ((a = $1) AND (b = $0))
2436 -> Seq Scan on ab_a1_b3 ab_3 (never executed)
2437 Filter: ((a = $1) AND (b = $0))
2438 -> Seq Scan on xy_1 (actual rows=0 loops=1)
2439 Filter: ((x = $1) AND (y = $0))
2440 Rows Removed by Filter: 1
2441 -> Seq Scan on ab_a1_b1 ab_4 (never executed)
2442 Filter: ((a = $1) AND (b = $0))
2443 -> Seq Scan on ab_a1_b2 ab_5 (never executed)
2444 Filter: ((a = $1) AND (b = $0))
2445 -> Seq Scan on ab_a1_b3 ab_6 (never executed)
2446 Filter: ((a = $1) AND (b = $0))
2447 (19 rows)
2449 -- Ensure we see just the xy_1 row.
2450 execute ab_q6(100);
2451 tableoid | a | b
2452 ----------+-----+-----
2453 xy_1 | 100 | -10
2454 (1 row)
2456 reset enable_bitmapscan;
2457 reset enable_indexscan;
2458 deallocate ab_q1;
2459 deallocate ab_q2;
2460 deallocate ab_q3;
2461 deallocate ab_q4;
2462 deallocate ab_q5;
2463 deallocate ab_q6;
2464 -- UPDATE on a partition subtree has been seen to have problems.
2465 insert into ab values (1,2);
2466 explain (analyze, costs off, summary off, timing off)
2467 update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
2468 QUERY PLAN
2469 -------------------------------------------------------------------------------------------
2470 Update on ab_a1 (actual rows=0 loops=1)
2471 Update on ab_a1_b1 ab_a1_1
2472 Update on ab_a1_b2 ab_a1_2
2473 Update on ab_a1_b3 ab_a1_3
2474 -> Nested Loop (actual rows=1 loops=1)
2475 -> Append (actual rows=1 loops=1)
2476 -> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1)
2477 Recheck Cond: (a = 1)
2478 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2479 Index Cond: (a = 1)
2480 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
2481 Recheck Cond: (a = 1)
2482 Heap Blocks: exact=1
2483 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
2484 Index Cond: (a = 1)
2485 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
2486 Recheck Cond: (a = 1)
2487 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
2488 Index Cond: (a = 1)
2489 -> Materialize (actual rows=1 loops=1)
2490 -> Append (actual rows=1 loops=1)
2491 -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
2492 Recheck Cond: (a = 1)
2493 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2494 Index Cond: (a = 1)
2495 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
2496 Recheck Cond: (a = 1)
2497 Heap Blocks: exact=1
2498 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
2499 Index Cond: (a = 1)
2500 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
2501 Recheck Cond: (a = 1)
2502 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
2503 Index Cond: (a = 1)
2504 (34 rows)
2506 table ab;
2507 a | b
2508 ---+---
2509 1 | 3
2510 (1 row)
2512 -- Test UPDATE where source relation has run-time pruning enabled
2513 truncate ab;
2514 insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
2515 explain (analyze, costs off, summary off, timing off)
2516 update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
2517 QUERY PLAN
2518 ------------------------------------------------------------------------------
2519 Update on ab_a1 (actual rows=0 loops=1)
2520 Update on ab_a1_b1 ab_a1_1
2521 Update on ab_a1_b2 ab_a1_2
2522 Update on ab_a1_b3 ab_a1_3
2523 InitPlan 1 (returns $0)
2524 -> Result (actual rows=1 loops=1)
2525 -> Nested Loop (actual rows=3 loops=1)
2526 -> Append (actual rows=3 loops=1)
2527 -> Seq Scan on ab_a1_b1 ab_a1_1 (actual rows=1 loops=1)
2528 -> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
2529 -> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1 loops=1)
2530 -> Materialize (actual rows=1 loops=3)
2531 -> Append (actual rows=1 loops=1)
2532 -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1 loops=1)
2533 Filter: (b = $0)
2534 -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed)
2535 Filter: (b = $0)
2536 -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed)
2537 Filter: (b = $0)
2538 (19 rows)
2540 select tableoid::regclass, * from ab;
2541 tableoid | a | b
2542 ----------+---+---
2543 ab_a1_b3 | 1 | 3
2544 ab_a1_b3 | 1 | 3
2545 ab_a1_b3 | 1 | 3
2546 ab_a2_b1 | 2 | 1
2547 (4 rows)
2549 drop table ab, lprt_a;
2550 -- Join
2551 create table tbl1(col1 int);
2552 insert into tbl1 values (501), (505);
2553 -- Basic table
2554 create table tprt (col1 int) partition by range (col1);
2555 create table tprt_1 partition of tprt for values from (1) to (501);
2556 create table tprt_2 partition of tprt for values from (501) to (1001);
2557 create table tprt_3 partition of tprt for values from (1001) to (2001);
2558 create table tprt_4 partition of tprt for values from (2001) to (3001);
2559 create table tprt_5 partition of tprt for values from (3001) to (4001);
2560 create table tprt_6 partition of tprt for values from (4001) to (5001);
2561 create index tprt1_idx on tprt_1 (col1);
2562 create index tprt2_idx on tprt_2 (col1);
2563 create index tprt3_idx on tprt_3 (col1);
2564 create index tprt4_idx on tprt_4 (col1);
2565 create index tprt5_idx on tprt_5 (col1);
2566 create index tprt6_idx on tprt_6 (col1);
2567 insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
2568 set enable_hashjoin = off;
2569 set enable_mergejoin = off;
2570 explain (analyze, costs off, summary off, timing off)
2571 select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
2572 QUERY PLAN
2573 --------------------------------------------------------------------------
2574 Nested Loop (actual rows=6 loops=1)
2575 -> Seq Scan on tbl1 (actual rows=2 loops=1)
2576 -> Append (actual rows=3 loops=2)
2577 -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
2578 Index Cond: (col1 < tbl1.col1)
2579 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
2580 Index Cond: (col1 < tbl1.col1)
2581 -> Index Scan using tprt3_idx on tprt_3 (never executed)
2582 Index Cond: (col1 < tbl1.col1)
2583 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2584 Index Cond: (col1 < tbl1.col1)
2585 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2586 Index Cond: (col1 < tbl1.col1)
2587 -> Index Scan using tprt6_idx on tprt_6 (never executed)
2588 Index Cond: (col1 < tbl1.col1)
2589 (15 rows)
2591 explain (analyze, costs off, summary off, timing off)
2592 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
2593 QUERY PLAN
2594 --------------------------------------------------------------------------
2595 Nested Loop (actual rows=2 loops=1)
2596 -> Seq Scan on tbl1 (actual rows=2 loops=1)
2597 -> Append (actual rows=1 loops=2)
2598 -> Index Scan using tprt1_idx on tprt_1 (never executed)
2599 Index Cond: (col1 = tbl1.col1)
2600 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
2601 Index Cond: (col1 = tbl1.col1)
2602 -> Index Scan using tprt3_idx on tprt_3 (never executed)
2603 Index Cond: (col1 = tbl1.col1)
2604 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2605 Index Cond: (col1 = tbl1.col1)
2606 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2607 Index Cond: (col1 = tbl1.col1)
2608 -> Index Scan using tprt6_idx on tprt_6 (never executed)
2609 Index Cond: (col1 = tbl1.col1)
2610 (15 rows)
2612 select tbl1.col1, tprt.col1 from tbl1
2613 inner join tprt on tbl1.col1 > tprt.col1
2614 order by tbl1.col1, tprt.col1;
2615 col1 | col1
2616 ------+------
2617 501 | 10
2618 501 | 20
2619 505 | 10
2620 505 | 20
2621 505 | 501
2622 505 | 502
2623 (6 rows)
2625 select tbl1.col1, tprt.col1 from tbl1
2626 inner join tprt on tbl1.col1 = tprt.col1
2627 order by tbl1.col1, tprt.col1;
2628 col1 | col1
2629 ------+------
2630 501 | 501
2631 505 | 505
2632 (2 rows)
2634 -- Multiple partitions
2635 insert into tbl1 values (1001), (1010), (1011);
2636 explain (analyze, costs off, summary off, timing off)
2637 select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
2638 QUERY PLAN
2639 --------------------------------------------------------------------------
2640 Nested Loop (actual rows=23 loops=1)
2641 -> Seq Scan on tbl1 (actual rows=5 loops=1)
2642 -> Append (actual rows=5 loops=5)
2643 -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
2644 Index Cond: (col1 < tbl1.col1)
2645 -> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
2646 Index Cond: (col1 < tbl1.col1)
2647 -> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
2648 Index Cond: (col1 < tbl1.col1)
2649 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2650 Index Cond: (col1 < tbl1.col1)
2651 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2652 Index Cond: (col1 < tbl1.col1)
2653 -> Index Scan using tprt6_idx on tprt_6 (never executed)
2654 Index Cond: (col1 < tbl1.col1)
2655 (15 rows)
2657 explain (analyze, costs off, summary off, timing off)
2658 select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
2659 QUERY PLAN
2660 --------------------------------------------------------------------------
2661 Nested Loop (actual rows=3 loops=1)
2662 -> Seq Scan on tbl1 (actual rows=5 loops=1)
2663 -> Append (actual rows=1 loops=5)
2664 -> Index Scan using tprt1_idx on tprt_1 (never executed)
2665 Index Cond: (col1 = tbl1.col1)
2666 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
2667 Index Cond: (col1 = tbl1.col1)
2668 -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
2669 Index Cond: (col1 = tbl1.col1)
2670 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2671 Index Cond: (col1 = tbl1.col1)
2672 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2673 Index Cond: (col1 = tbl1.col1)
2674 -> Index Scan using tprt6_idx on tprt_6 (never executed)
2675 Index Cond: (col1 = tbl1.col1)
2676 (15 rows)
2678 select tbl1.col1, tprt.col1 from tbl1
2679 inner join tprt on tbl1.col1 > tprt.col1
2680 order by tbl1.col1, tprt.col1;
2681 col1 | col1
2682 ------+------
2683 501 | 10
2684 501 | 20
2685 505 | 10
2686 505 | 20
2687 505 | 501
2688 505 | 502
2689 1001 | 10
2690 1001 | 20
2691 1001 | 501
2692 1001 | 502
2693 1001 | 505
2694 1010 | 10
2695 1010 | 20
2696 1010 | 501
2697 1010 | 502
2698 1010 | 505
2699 1010 | 1001
2700 1011 | 10
2701 1011 | 20
2702 1011 | 501
2703 1011 | 502
2704 1011 | 505
2705 1011 | 1001
2706 (23 rows)
2708 select tbl1.col1, tprt.col1 from tbl1
2709 inner join tprt on tbl1.col1 = tprt.col1
2710 order by tbl1.col1, tprt.col1;
2711 col1 | col1
2712 ------+------
2713 501 | 501
2714 505 | 505
2715 1001 | 1001
2716 (3 rows)
2718 -- Last partition
2719 delete from tbl1;
2720 insert into tbl1 values (4400);
2721 explain (analyze, costs off, summary off, timing off)
2722 select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
2723 QUERY PLAN
2724 --------------------------------------------------------------------------
2725 Nested Loop (actual rows=1 loops=1)
2726 -> Seq Scan on tbl1 (actual rows=1 loops=1)
2727 -> Append (actual rows=1 loops=1)
2728 -> Index Scan using tprt1_idx on tprt_1 (never executed)
2729 Index Cond: (col1 > tbl1.col1)
2730 -> Index Scan using tprt2_idx on tprt_2 (never executed)
2731 Index Cond: (col1 > tbl1.col1)
2732 -> Index Scan using tprt3_idx on tprt_3 (never executed)
2733 Index Cond: (col1 > tbl1.col1)
2734 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2735 Index Cond: (col1 > tbl1.col1)
2736 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2737 Index Cond: (col1 > tbl1.col1)
2738 -> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
2739 Index Cond: (col1 > tbl1.col1)
2740 (15 rows)
2742 select tbl1.col1, tprt.col1 from tbl1
2743 inner join tprt on tbl1.col1 < tprt.col1
2744 order by tbl1.col1, tprt.col1;
2745 col1 | col1
2746 ------+------
2747 4400 | 4500
2748 (1 row)
2750 -- No matching partition
2751 delete from tbl1;
2752 insert into tbl1 values (10000);
2753 explain (analyze, costs off, summary off, timing off)
2754 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
2755 QUERY PLAN
2756 -------------------------------------------------------------------
2757 Nested Loop (actual rows=0 loops=1)
2758 -> Seq Scan on tbl1 (actual rows=1 loops=1)
2759 -> Append (actual rows=0 loops=1)
2760 -> Index Scan using tprt1_idx on tprt_1 (never executed)
2761 Index Cond: (col1 = tbl1.col1)
2762 -> Index Scan using tprt2_idx on tprt_2 (never executed)
2763 Index Cond: (col1 = tbl1.col1)
2764 -> Index Scan using tprt3_idx on tprt_3 (never executed)
2765 Index Cond: (col1 = tbl1.col1)
2766 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2767 Index Cond: (col1 = tbl1.col1)
2768 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2769 Index Cond: (col1 = tbl1.col1)
2770 -> Index Scan using tprt6_idx on tprt_6 (never executed)
2771 Index Cond: (col1 = tbl1.col1)
2772 (15 rows)
2774 select tbl1.col1, tprt.col1 from tbl1
2775 inner join tprt on tbl1.col1 = tprt.col1
2776 order by tbl1.col1, tprt.col1;
2777 col1 | col1
2778 ------+------
2779 (0 rows)
2781 drop table tbl1, tprt;
2782 -- Test with columns defined in varying orders between each level
2783 create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
2784 create table part_bac (b int not null, a int not null, c int not null) partition by list (b);
2785 create table part_cab (c int not null, a int not null, b int not null) partition by list (c);
2786 create table part_abc_p1 (a int not null, b int not null, c int not null);
2787 alter table part_abc attach partition part_bac for values in(1);
2788 alter table part_bac attach partition part_cab for values in(2);
2789 alter table part_cab attach partition part_abc_p1 for values in(3);
2790 prepare part_abc_q1 (int, int, int) as
2791 select * from part_abc where a = $1 and b = $2 and c = $3;
2792 -- Single partition should be scanned.
2793 explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
2794 QUERY PLAN
2795 ----------------------------------------------------------
2796 Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1)
2797 Filter: ((a = $1) AND (b = $2) AND (c = $3))
2798 (2 rows)
2800 deallocate part_abc_q1;
2801 drop table part_abc;
2802 -- Ensure that an Append node properly handles a sub-partitioned table
2803 -- matching without any of its leaf partitions matching the clause.
2804 create table listp (a int, b int) partition by list (a);
2805 create table listp_1 partition of listp for values in(1) partition by list (b);
2806 create table listp_1_1 partition of listp_1 for values in(1);
2807 create table listp_2 partition of listp for values in(2) partition by list (b);
2808 create table listp_2_1 partition of listp_2 for values in(2);
2809 select * from listp where b = 1;
2810 a | b
2811 ---+---
2812 (0 rows)
2814 -- Ensure that an Append node properly can handle selection of all first level
2815 -- partitions before finally detecting the correct set of 2nd level partitions
2816 -- which match the given parameter.
2817 prepare q1 (int,int) as select * from listp where b in ($1,$2);
2818 explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
2819 QUERY PLAN
2820 -------------------------------------------------------------
2821 Append (actual rows=0 loops=1)
2822 Subplans Removed: 1
2823 -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1)
2824 Filter: (b = ANY (ARRAY[$1, $2]))
2825 (4 rows)
2827 explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
2828 QUERY PLAN
2829 -------------------------------------------------------------
2830 Append (actual rows=0 loops=1)
2831 Subplans Removed: 1
2832 -> Seq Scan on listp_2_1 listp_1 (actual rows=0 loops=1)
2833 Filter: (b = ANY (ARRAY[$1, $2]))
2834 (4 rows)
2836 -- Try with no matching partitions.
2837 explain (analyze, costs off, summary off, timing off) execute q1 (0,0);
2838 QUERY PLAN
2839 --------------------------------
2840 Append (actual rows=0 loops=1)
2841 Subplans Removed: 2
2842 (2 rows)
2844 deallocate q1;
2845 -- Test more complex cases where a not-equal condition further eliminates partitions.
2846 prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
2847 -- Both partitions allowed by IN clause, but one disallowed by <> clause
2848 explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
2849 QUERY PLAN
2850 -------------------------------------------------------------------------
2851 Append (actual rows=0 loops=1)
2852 Subplans Removed: 1
2853 -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1)
2854 Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
2855 (4 rows)
2857 -- Both partitions allowed by IN clause, then both excluded again by <> clauses.
2858 explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
2859 QUERY PLAN
2860 --------------------------------
2861 Append (actual rows=0 loops=1)
2862 Subplans Removed: 2
2863 (2 rows)
2865 -- Ensure Params that evaluate to NULL properly prune away all partitions
2866 explain (analyze, costs off, summary off, timing off)
2867 select * from listp where a = (select null::int);
2868 QUERY PLAN
2869 ------------------------------------------------------
2870 Append (actual rows=0 loops=1)
2871 InitPlan 1 (returns $0)
2872 -> Result (actual rows=1 loops=1)
2873 -> Seq Scan on listp_1_1 listp_1 (never executed)
2874 Filter: (a = $0)
2875 -> Seq Scan on listp_2_1 listp_2 (never executed)
2876 Filter: (a = $0)
2877 (7 rows)
2879 drop table listp;
2880 --
2881 -- check that stable query clauses are only used in run-time pruning
2882 --
2883 create table stable_qual_pruning (a timestamp) partition by range (a);
2884 create table stable_qual_pruning1 partition of stable_qual_pruning
2885 for values from ('2000-01-01') to ('2000-02-01');
2886 create table stable_qual_pruning2 partition of stable_qual_pruning
2887 for values from ('2000-02-01') to ('2000-03-01');
2888 create table stable_qual_pruning3 partition of stable_qual_pruning
2889 for values from ('3000-02-01') to ('3000-03-01');
2890 -- comparison against a stable value requires run-time pruning
2891 explain (analyze, costs off, summary off, timing off)
2892 select * from stable_qual_pruning where a < localtimestamp;
2893 QUERY PLAN
2894 --------------------------------------------------------------------------------------
2895 Append (actual rows=0 loops=1)
2896 Subplans Removed: 1
2897 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
2898 Filter: (a < LOCALTIMESTAMP)
2899 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1)
2900 Filter: (a < LOCALTIMESTAMP)
2901 (6 rows)
2903 -- timestamp < timestamptz comparison is only stable, not immutable
2904 explain (analyze, costs off, summary off, timing off)
2905 select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
2906 QUERY PLAN
2907 --------------------------------------------------------------------------------------
2908 Append (actual rows=0 loops=1)
2909 Subplans Removed: 2
2910 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
2911 Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone)
2912 (4 rows)
2914 -- check ScalarArrayOp cases
2915 explain (analyze, costs off, summary off, timing off)
2916 select * from stable_qual_pruning
2917 where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
2918 QUERY PLAN
2919 --------------------------------
2920 Result (actual rows=0 loops=1)
2921 One-Time Filter: false
2922 (2 rows)
2924 explain (analyze, costs off, summary off, timing off)
2925 select * from stable_qual_pruning
2926 where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
2927 QUERY PLAN
2928 ----------------------------------------------------------------------------------------------------------------
2929 Seq Scan on stable_qual_pruning2 stable_qual_pruning (actual rows=0 loops=1)
2930 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[]))
2931 (2 rows)
2933 explain (analyze, costs off, summary off, timing off)
2934 select * from stable_qual_pruning
2935 where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
2936 QUERY PLAN
2937 ------------------------------------------------------------------------------------------------------------
2938 Append (actual rows=0 loops=1)
2939 Subplans Removed: 2
2940 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1)
2941 Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP]))
2942 (4 rows)
2944 explain (analyze, costs off, summary off, timing off)
2945 select * from stable_qual_pruning
2946 where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
2947 QUERY PLAN
2948 --------------------------------
2949 Append (actual rows=0 loops=1)
2950 Subplans Removed: 3
2951 (2 rows)
2953 explain (analyze, costs off, summary off, timing off)
2954 select * from stable_qual_pruning
2955 where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
2956 QUERY PLAN
2957 ---------------------------------------------------------------------------------------------------------------------------
2958 Append (actual rows=0 loops=1)
2959 Subplans Removed: 2
2960 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1)
2961 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[]))
2962 (4 rows)
2964 explain (analyze, costs off, summary off, timing off)
2965 select * from stable_qual_pruning
2966 where a = any(null::timestamptz[]);
2967 QUERY PLAN
2968 --------------------------------------------------------------------------------------
2969 Append (actual rows=0 loops=1)
2970 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
2971 Filter: (a = ANY (NULL::timestamp with time zone[]))
2972 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1)
2973 Filter: (a = ANY (NULL::timestamp with time zone[]))
2974 -> Seq Scan on stable_qual_pruning3 stable_qual_pruning_3 (actual rows=0 loops=1)
2975 Filter: (a = ANY (NULL::timestamp with time zone[]))
2976 (7 rows)
2978 drop table stable_qual_pruning;
2979 --
2980 -- Check that pruning with composite range partitioning works correctly when
2981 -- it must ignore clauses for trailing keys once it has seen a clause with
2982 -- non-inclusive operator for an earlier key
2983 --
2984 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
2985 create table mc3p0 partition of mc3p
2986 for values from (0, 0, 0) to (0, maxvalue, maxvalue);
2987 create table mc3p1 partition of mc3p
2988 for values from (1, 1, 1) to (2, minvalue, minvalue);
2989 create table mc3p2 partition of mc3p
2990 for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
2991 insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
2992 explain (analyze, costs off, summary off, timing off)
2993 select * from mc3p where a < 3 and abs(b) = 1;
2994 QUERY PLAN
2995 --------------------------------------------------------
2996 Append (actual rows=3 loops=1)
2997 -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
2998 Filter: ((a < 3) AND (abs(b) = 1))
2999 -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1)
3000 Filter: ((a < 3) AND (abs(b) = 1))
3001 -> Seq Scan on mc3p2 mc3p_3 (actual rows=1 loops=1)
3002 Filter: ((a < 3) AND (abs(b) = 1))
3003 (7 rows)
3005 --
3006 -- Check that pruning with composite range partitioning works correctly when
3007 -- a combination of runtime parameters is specified, not all of whose values
3008 -- are available at the same time
3009 --
3010 prepare ps1 as
3011 select * from mc3p where a = $1 and abs(b) < (select 3);
3012 explain (analyze, costs off, summary off, timing off)
3013 execute ps1(1);
3014 QUERY PLAN
3015 --------------------------------------------------------
3016 Append (actual rows=1 loops=1)
3017 Subplans Removed: 2
3018 InitPlan 1 (returns $0)
3019 -> Result (actual rows=1 loops=1)
3020 -> Seq Scan on mc3p1 mc3p_1 (actual rows=1 loops=1)
3021 Filter: ((a = $1) AND (abs(b) < $0))
3022 (6 rows)
3024 deallocate ps1;
3025 prepare ps2 as
3026 select * from mc3p where a <= $1 and abs(b) < (select 3);
3027 explain (analyze, costs off, summary off, timing off)
3028 execute ps2(1);
3029 QUERY PLAN
3030 --------------------------------------------------------
3031 Append (actual rows=2 loops=1)
3032 Subplans Removed: 1
3033 InitPlan 1 (returns $0)
3034 -> Result (actual rows=1 loops=1)
3035 -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
3036 Filter: ((a <= $1) AND (abs(b) < $0))
3037 -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1)
3038 Filter: ((a <= $1) AND (abs(b) < $0))
3039 (8 rows)
3041 deallocate ps2;
3042 drop table mc3p;
3043 -- Ensure runtime pruning works with initplans params with boolean types
3044 create table boolvalues (value bool not null);
3045 insert into boolvalues values('t'),('f');
3046 create table boolp (a bool) partition by list (a);
3047 create table boolp_t partition of boolp for values in('t');
3048 create table boolp_f partition of boolp for values in('f');
3049 explain (analyze, costs off, summary off, timing off)
3050 select * from boolp where a = (select value from boolvalues where value);
3051 QUERY PLAN
3052 -----------------------------------------------------------
3053 Append (actual rows=0 loops=1)
3054 InitPlan 1 (returns $0)
3055 -> Seq Scan on boolvalues (actual rows=1 loops=1)
3056 Filter: value
3057 Rows Removed by Filter: 1
3058 -> Seq Scan on boolp_f boolp_1 (never executed)
3059 Filter: (a = $0)
3060 -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1)
3061 Filter: (a = $0)
3062 (9 rows)
3064 explain (analyze, costs off, summary off, timing off)
3065 select * from boolp where a = (select value from boolvalues where not value);
3066 QUERY PLAN
3067 -----------------------------------------------------------
3068 Append (actual rows=0 loops=1)
3069 InitPlan 1 (returns $0)
3070 -> Seq Scan on boolvalues (actual rows=1 loops=1)
3071 Filter: (NOT value)
3072 Rows Removed by Filter: 1
3073 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1)
3074 Filter: (a = $0)
3075 -> Seq Scan on boolp_t boolp_2 (never executed)
3076 Filter: (a = $0)
3077 (9 rows)
3079 drop table boolp;
3080 --
3081 -- Test run-time pruning of MergeAppend subnodes
3082 --
3083 set enable_seqscan = off;
3084 set enable_sort = off;
3085 create table ma_test (a int, b int) partition by range (a);
3086 create table ma_test_p1 partition of ma_test for values from (0) to (10);
3087 create table ma_test_p2 partition of ma_test for values from (10) to (20);
3088 create table ma_test_p3 partition of ma_test for values from (20) to (30);
3089 insert into ma_test select x,x from generate_series(0,29) t(x);
3090 create index on ma_test (b);
3091 analyze ma_test;
3092 prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
3093 explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
3094 QUERY PLAN
3095 -----------------------------------------------------------------------------------------
3096 Merge Append (actual rows=2 loops=1)
3097 Sort Key: ma_test.b
3098 Subplans Removed: 1
3099 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1)
3100 Filter: ((a >= $1) AND ((a % 10) = 5))
3101 Rows Removed by Filter: 9
3102 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1)
3103 Filter: ((a >= $1) AND ((a % 10) = 5))
3104 Rows Removed by Filter: 9
3105 (9 rows)
3107 execute mt_q1(15);
3108 a
3109 ----
3110 15
3111 25
3112 (2 rows)
3114 explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
3115 QUERY PLAN
3116 -----------------------------------------------------------------------------------------
3117 Merge Append (actual rows=1 loops=1)
3118 Sort Key: ma_test.b
3119 Subplans Removed: 2
3120 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1)
3121 Filter: ((a >= $1) AND ((a % 10) = 5))
3122 Rows Removed by Filter: 9
3123 (6 rows)
3125 execute mt_q1(25);
3126 a
3127 ----
3128 25
3129 (1 row)
3131 -- Ensure MergeAppend behaves correctly when no subplans match
3132 explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
3133 QUERY PLAN
3134 --------------------------------------
3135 Merge Append (actual rows=0 loops=1)
3136 Sort Key: ma_test.b
3137 Subplans Removed: 3
3138 (3 rows)
3140 execute mt_q1(35);
3141 a
3142 ---
3143 (0 rows)
3145 deallocate mt_q1;
3146 prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
3147 -- Ensure output list looks sane when the MergeAppend has no subplans.
3148 explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
3149 QUERY PLAN
3150 --------------------------------------------
3151 Limit (actual rows=0 loops=1)
3152 Output: ma_test.a, ma_test.b
3153 -> Merge Append (actual rows=0 loops=1)
3154 Sort Key: ma_test.b
3155 Subplans Removed: 3
3156 (5 rows)
3158 deallocate mt_q2;
3159 -- ensure initplan params properly prune partitions
3160 explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
3161 QUERY PLAN
3162 -----------------------------------------------------------------------------------------------
3163 Merge Append (actual rows=20 loops=1)
3164 Sort Key: ma_test.b
3165 InitPlan 2 (returns $1)
3166 -> Result (actual rows=1 loops=1)
3167 InitPlan 1 (returns $0)
3168 -> Limit (actual rows=1 loops=1)
3169 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1)
3170 Index Cond: (b IS NOT NULL)
3171 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed)
3172 Filter: (a >= $1)
3173 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10 loops=1)
3174 Filter: (a >= $1)
3175 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10 loops=1)
3176 Filter: (a >= $1)
3177 (14 rows)
3179 reset enable_seqscan;
3180 reset enable_sort;
3181 drop table ma_test;
3182 reset enable_indexonlyscan;
3183 --
3184 -- check that pruning works properly when the partition key is of a
3185 -- pseudotype
3186 --
3187 -- array type list partition key
3188 create table pp_arrpart (a int[]) partition by list (a);
3189 create table pp_arrpart1 partition of pp_arrpart for values in ('{1}');
3190 create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}');
3191 explain (costs off) select * from pp_arrpart where a = '{1}';
3192 QUERY PLAN
3193 ------------------------------------
3194 Seq Scan on pp_arrpart1 pp_arrpart
3195 Filter: (a = '{1}'::integer[])
3196 (2 rows)
3198 explain (costs off) select * from pp_arrpart where a = '{1, 2}';
3199 QUERY PLAN
3200 --------------------------
3201 Result
3202 One-Time Filter: false
3203 (2 rows)
3205 explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
3206 QUERY PLAN
3207 ----------------------------------------------------------------------
3208 Append
3209 -> Seq Scan on pp_arrpart1 pp_arrpart_1
3210 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3211 -> Seq Scan on pp_arrpart2 pp_arrpart_2
3212 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3213 (5 rows)
3215 explain (costs off) update pp_arrpart set a = a where a = '{1}';
3216 QUERY PLAN
3217 --------------------------------------------
3218 Update on pp_arrpart
3219 Update on pp_arrpart1 pp_arrpart_1
3220 -> Seq Scan on pp_arrpart1 pp_arrpart_1
3221 Filter: (a = '{1}'::integer[])
3222 (4 rows)
3224 explain (costs off) delete from pp_arrpart where a = '{1}';
3225 QUERY PLAN
3226 --------------------------------------------
3227 Delete on pp_arrpart
3228 Delete on pp_arrpart1 pp_arrpart_1
3229 -> Seq Scan on pp_arrpart1 pp_arrpart_1
3230 Filter: (a = '{1}'::integer[])
3231 (4 rows)
3233 drop table pp_arrpart;
3234 -- array type hash partition key
3235 create table pph_arrpart (a int[]) partition by hash (a);
3236 create table pph_arrpart1 partition of pph_arrpart for values with (modulus 2, remainder 0);
3237 create table pph_arrpart2 partition of pph_arrpart for values with (modulus 2, remainder 1);
3238 insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}');
3239 select tableoid::regclass, * from pph_arrpart order by 1;
3240 tableoid | a
3241 --------------+-------
3242 pph_arrpart1 | {1,2}
3243 pph_arrpart1 | {4,5}
3244 pph_arrpart2 | {1}
3245 (3 rows)
3247 explain (costs off) select * from pph_arrpart where a = '{1}';
3248 QUERY PLAN
3249 --------------------------------------
3250 Seq Scan on pph_arrpart2 pph_arrpart
3251 Filter: (a = '{1}'::integer[])
3252 (2 rows)
3254 explain (costs off) select * from pph_arrpart where a = '{1, 2}';
3255 QUERY PLAN
3256 --------------------------------------
3257 Seq Scan on pph_arrpart1 pph_arrpart
3258 Filter: (a = '{1,2}'::integer[])
3259 (2 rows)
3261 explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}');
3262 QUERY PLAN
3263 ----------------------------------------------------------------------
3264 Append
3265 -> Seq Scan on pph_arrpart1 pph_arrpart_1
3266 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3267 -> Seq Scan on pph_arrpart2 pph_arrpart_2
3268 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3269 (5 rows)
3271 drop table pph_arrpart;
3272 -- enum type list partition key
3273 create type pp_colors as enum ('green', 'blue', 'black');
3274 create table pp_enumpart (a pp_colors) partition by list (a);
3275 create table pp_enumpart_green partition of pp_enumpart for values in ('green');
3276 create table pp_enumpart_blue partition of pp_enumpart for values in ('blue');
3277 explain (costs off) select * from pp_enumpart where a = 'blue';
3278 QUERY PLAN
3279 ------------------------------------------
3280 Seq Scan on pp_enumpart_blue pp_enumpart
3281 Filter: (a = 'blue'::pp_colors)
3282 (2 rows)
3284 explain (costs off) select * from pp_enumpart where a = 'black';
3285 QUERY PLAN
3286 --------------------------
3287 Result
3288 One-Time Filter: false
3289 (2 rows)
3291 drop table pp_enumpart;
3292 drop type pp_colors;
3293 -- record type as partition key
3294 create type pp_rectype as (a int, b int);
3295 create table pp_recpart (a pp_rectype) partition by list (a);
3296 create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)');
3297 create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)');
3298 explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
3299 QUERY PLAN
3300 --------------------------------------
3301 Seq Scan on pp_recpart_11 pp_recpart
3302 Filter: (a = '(1,1)'::pp_rectype)
3303 (2 rows)
3305 explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
3306 QUERY PLAN
3307 --------------------------
3308 Result
3309 One-Time Filter: false
3310 (2 rows)
3312 drop table pp_recpart;
3313 drop type pp_rectype;
3314 -- range type partition key
3315 create table pp_intrangepart (a int4range) partition by list (a);
3316 create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]');
3317 create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)');
3318 explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
3319 QUERY PLAN
3320 -----------------------------------------------
3321 Seq Scan on pp_intrangepart12 pp_intrangepart
3322 Filter: (a = '[1,3)'::int4range)
3323 (2 rows)
3325 explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
3326 QUERY PLAN
3327 --------------------------
3328 Result
3329 One-Time Filter: false
3330 (2 rows)
3332 drop table pp_intrangepart;
3333 --
3334 -- Ensure the enable_partition_prune GUC properly disables partition pruning.
3335 --
3336 create table pp_lp (a int, value int) partition by list (a);
3337 create table pp_lp1 partition of pp_lp for values in(1);
3338 create table pp_lp2 partition of pp_lp for values in(2);
3339 explain (costs off) select * from pp_lp where a = 1;
3340 QUERY PLAN
3341 --------------------------
3342 Seq Scan on pp_lp1 pp_lp
3343 Filter: (a = 1)
3344 (2 rows)
3346 explain (costs off) update pp_lp set value = 10 where a = 1;
3347 QUERY PLAN
3348 ----------------------------------
3349 Update on pp_lp
3350 Update on pp_lp1 pp_lp_1
3351 -> Seq Scan on pp_lp1 pp_lp_1
3352 Filter: (a = 1)
3353 (4 rows)
3355 explain (costs off) delete from pp_lp where a = 1;
3356 QUERY PLAN
3357 ----------------------------------
3358 Delete on pp_lp
3359 Delete on pp_lp1 pp_lp_1
3360 -> Seq Scan on pp_lp1 pp_lp_1
3361 Filter: (a = 1)
3362 (4 rows)
3364 set enable_partition_pruning = off;
3365 set constraint_exclusion = 'partition'; -- this should not affect the result.
3366 explain (costs off) select * from pp_lp where a = 1;
3367 QUERY PLAN
3368 ----------------------------------
3369 Append
3370 -> Seq Scan on pp_lp1 pp_lp_1
3371 Filter: (a = 1)
3372 -> Seq Scan on pp_lp2 pp_lp_2
3373 Filter: (a = 1)
3374 (5 rows)
3376 explain (costs off) update pp_lp set value = 10 where a = 1;
3377 QUERY PLAN
3378 ----------------------------------------
3379 Update on pp_lp
3380 Update on pp_lp1 pp_lp_1
3381 Update on pp_lp2 pp_lp_2
3382 -> Append
3383 -> Seq Scan on pp_lp1 pp_lp_1
3384 Filter: (a = 1)
3385 -> Seq Scan on pp_lp2 pp_lp_2
3386 Filter: (a = 1)
3387 (8 rows)
3389 explain (costs off) delete from pp_lp where a = 1;
3390 QUERY PLAN
3391 ----------------------------------------
3392 Delete on pp_lp
3393 Delete on pp_lp1 pp_lp_1
3394 Delete on pp_lp2 pp_lp_2
3395 -> Append
3396 -> Seq Scan on pp_lp1 pp_lp_1
3397 Filter: (a = 1)
3398 -> Seq Scan on pp_lp2 pp_lp_2
3399 Filter: (a = 1)
3400 (8 rows)
3402 set constraint_exclusion = 'off'; -- this should not affect the result.
3403 explain (costs off) select * from pp_lp where a = 1;
3404 QUERY PLAN
3405 ----------------------------------
3406 Append
3407 -> Seq Scan on pp_lp1 pp_lp_1
3408 Filter: (a = 1)
3409 -> Seq Scan on pp_lp2 pp_lp_2
3410 Filter: (a = 1)
3411 (5 rows)
3413 explain (costs off) update pp_lp set value = 10 where a = 1;
3414 QUERY PLAN
3415 ----------------------------------------
3416 Update on pp_lp
3417 Update on pp_lp1 pp_lp_1
3418 Update on pp_lp2 pp_lp_2
3419 -> Append
3420 -> Seq Scan on pp_lp1 pp_lp_1
3421 Filter: (a = 1)
3422 -> Seq Scan on pp_lp2 pp_lp_2
3423 Filter: (a = 1)
3424 (8 rows)
3426 explain (costs off) delete from pp_lp where a = 1;
3427 QUERY PLAN
3428 ----------------------------------------
3429 Delete on pp_lp
3430 Delete on pp_lp1 pp_lp_1
3431 Delete on pp_lp2 pp_lp_2
3432 -> Append
3433 -> Seq Scan on pp_lp1 pp_lp_1
3434 Filter: (a = 1)
3435 -> Seq Scan on pp_lp2 pp_lp_2
3436 Filter: (a = 1)
3437 (8 rows)
3439 drop table pp_lp;
3440 -- Ensure enable_partition_prune does not affect non-partitioned tables.
3441 create table inh_lp (a int, value int);
3442 create table inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp);
3443 NOTICE: merging column "a" with inherited definition
3444 NOTICE: merging column "value" with inherited definition
3445 create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp);
3446 NOTICE: merging column "a" with inherited definition
3447 NOTICE: merging column "value" with inherited definition
3448 set constraint_exclusion = 'partition';
3449 -- inh_lp2 should be removed in the following 3 cases.
3450 explain (costs off) select * from inh_lp where a = 1;
3451 QUERY PLAN
3452 ------------------------------------
3453 Append
3454 -> Seq Scan on inh_lp inh_lp_1
3455 Filter: (a = 1)
3456 -> Seq Scan on inh_lp1 inh_lp_2
3457 Filter: (a = 1)
3458 (5 rows)
3460 explain (costs off) update inh_lp set value = 10 where a = 1;
3461 QUERY PLAN
3462 ------------------------------------------------
3463 Update on inh_lp
3464 Update on inh_lp inh_lp_1
3465 Update on inh_lp1 inh_lp_2
3466 -> Result
3467 -> Append
3468 -> Seq Scan on inh_lp inh_lp_1
3469 Filter: (a = 1)
3470 -> Seq Scan on inh_lp1 inh_lp_2
3471 Filter: (a = 1)
3472 (9 rows)
3474 explain (costs off) delete from inh_lp where a = 1;
3475 QUERY PLAN
3476 ------------------------------------------
3477 Delete on inh_lp
3478 Delete on inh_lp inh_lp_1
3479 Delete on inh_lp1 inh_lp_2
3480 -> Append
3481 -> Seq Scan on inh_lp inh_lp_1
3482 Filter: (a = 1)
3483 -> Seq Scan on inh_lp1 inh_lp_2
3484 Filter: (a = 1)
3485 (8 rows)
3487 -- Ensure we don't exclude normal relations when we only expect to exclude
3488 -- inheritance children
3489 explain (costs off) update inh_lp1 set value = 10 where a = 2;
3490 QUERY PLAN
3491 ---------------------------
3492 Update on inh_lp1
3493 -> Seq Scan on inh_lp1
3494 Filter: (a = 2)
3495 (3 rows)
3497 drop table inh_lp cascade;
3498 NOTICE: drop cascades to 2 other objects
3499 DETAIL: drop cascades to table inh_lp1
3500 drop cascades to table inh_lp2
3501 reset enable_partition_pruning;
3502 reset constraint_exclusion;
3503 -- Check pruning for a partition tree containing only temporary relations
3504 create temp table pp_temp_parent (a int) partition by list (a);
3505 create temp table pp_temp_part_1 partition of pp_temp_parent for values in (1);
3506 create temp table pp_temp_part_def partition of pp_temp_parent default;
3507 explain (costs off) select * from pp_temp_parent where true;
3508 QUERY PLAN
3509 -----------------------------------------------------
3510 Append
3511 -> Seq Scan on pp_temp_part_1 pp_temp_parent_1
3512 -> Seq Scan on pp_temp_part_def pp_temp_parent_2
3513 (3 rows)
3515 explain (costs off) select * from pp_temp_parent where a = 2;
3516 QUERY PLAN
3517 ---------------------------------------------
3518 Seq Scan on pp_temp_part_def pp_temp_parent
3519 Filter: (a = 2)
3520 (2 rows)
3522 drop table pp_temp_parent;
3523 -- Stress run-time partition pruning a bit more, per bug reports
3524 create temp table p (a int, b int, c int) partition by list (a);
3525 create temp table p1 partition of p for values in (1);
3526 create temp table p2 partition of p for values in (2);
3527 create temp table q (a int, b int, c int) partition by list (a);
3528 create temp table q1 partition of q for values in (1) partition by list (b);
3529 create temp table q11 partition of q1 for values in (1) partition by list (c);
3530 create temp table q111 partition of q11 for values in (1);
3531 create temp table q2 partition of q for values in (2) partition by list (b);
3532 create temp table q21 partition of q2 for values in (1);
3533 create temp table q22 partition of q2 for values in (2);
3534 insert into q22 values (2, 2, 3);
3535 explain (costs off)
3536 select *
3537 from (
3538 select * from p
3539 union all
3540 select * from q1
3541 union all
3542 select 1, 1, 1
3543 ) s(a, b, c)
3544 where s.a = 1 and s.b = 1 and s.c = (select 1);
3545 QUERY PLAN
3546 ----------------------------------------------------
3547 Append
3548 InitPlan 1 (returns $0)
3549 -> Result
3550 -> Seq Scan on p1 p
3551 Filter: ((a = 1) AND (b = 1) AND (c = $0))
3552 -> Seq Scan on q111 q1
3553 Filter: ((a = 1) AND (b = 1) AND (c = $0))
3554 -> Result
3555 One-Time Filter: (1 = $0)
3556 (9 rows)
3558 select *
3559 from (
3560 select * from p
3561 union all
3562 select * from q1
3563 union all
3564 select 1, 1, 1
3565 ) s(a, b, c)
3566 where s.a = 1 and s.b = 1 and s.c = (select 1);
3567 a | b | c
3568 ---+---+---
3569 1 | 1 | 1
3570 (1 row)
3572 prepare q (int, int) as
3573 select *
3574 from (
3575 select * from p
3576 union all
3577 select * from q1
3578 union all
3579 select 1, 1, 1
3580 ) s(a, b, c)
3581 where s.a = $1 and s.b = $2 and s.c = (select 1);
3582 explain (costs off) execute q (1, 1);
3583 QUERY PLAN
3584 ---------------------------------------------------------------
3585 Append
3586 Subplans Removed: 1
3587 InitPlan 1 (returns $0)
3588 -> Result
3589 -> Seq Scan on p1 p
3590 Filter: ((a = $1) AND (b = $2) AND (c = $0))
3591 -> Seq Scan on q111 q1
3592 Filter: ((a = $1) AND (b = $2) AND (c = $0))
3593 -> Result
3594 One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = $0))
3595 (10 rows)
3597 execute q (1, 1);
3598 a | b | c
3599 ---+---+---
3600 1 | 1 | 1
3601 (1 row)
3603 drop table p, q;
3604 -- Ensure run-time pruning works correctly when we match a partitioned table
3605 -- on the first level but find no matching partitions on the second level.
3606 create table listp (a int, b int) partition by list (a);
3607 create table listp1 partition of listp for values in(1);
3608 create table listp2 partition of listp for values in(2) partition by list(b);
3609 create table listp2_10 partition of listp2 for values in (10);
3610 explain (analyze, costs off, summary off, timing off)
3611 select * from listp where a = (select 2) and b <> 10;
3612 QUERY PLAN
3613 --------------------------------------------------
3614 Seq Scan on listp1 listp (actual rows=0 loops=1)
3615 Filter: ((b <> 10) AND (a = $0))
3616 InitPlan 1 (returns $0)
3617 -> Result (never executed)
3618 (4 rows)
3620 --
3621 -- check that a partition directly accessed in a query is excluded with
3622 -- constraint_exclusion = on
3623 --
3624 -- turn off partition pruning, so that it doesn't interfere
3625 set enable_partition_pruning to off;
3626 -- setting constraint_exclusion to 'partition' disables exclusion
3627 set constraint_exclusion to 'partition';
3628 explain (costs off) select * from listp1 where a = 2;
3629 QUERY PLAN
3630 --------------------
3631 Seq Scan on listp1
3632 Filter: (a = 2)
3633 (2 rows)
3635 explain (costs off) update listp1 set a = 1 where a = 2;
3636 QUERY PLAN
3637 --------------------------
3638 Update on listp1
3639 -> Seq Scan on listp1
3640 Filter: (a = 2)
3641 (3 rows)
3643 -- constraint exclusion enabled
3644 set constraint_exclusion to 'on';
3645 explain (costs off) select * from listp1 where a = 2;
3646 QUERY PLAN
3647 --------------------------
3648 Result
3649 One-Time Filter: false
3650 (2 rows)
3652 explain (costs off) update listp1 set a = 1 where a = 2;
3653 QUERY PLAN
3654 --------------------------------
3655 Update on listp1
3656 -> Result
3657 One-Time Filter: false
3658 (3 rows)
3660 reset constraint_exclusion;
3661 reset enable_partition_pruning;
3662 drop table listp;
3663 -- Ensure run-time pruning works correctly for nested Append nodes
3664 set parallel_setup_cost to 0;
3665 set parallel_tuple_cost to 0;
3666 create table listp (a int) partition by list(a);
3667 create table listp_12 partition of listp for values in(1,2) partition by list(a);
3668 create table listp_12_1 partition of listp_12 for values in(1);
3669 create table listp_12_2 partition of listp_12 for values in(2);
3670 -- Force the 2nd subnode of the Append to be non-parallel. This results in
3671 -- a nested Append node because the mixed parallel / non-parallel paths cannot
3672 -- be pulled into the top-level Append.
3673 alter table listp_12_1 set (parallel_workers = 0);
3674 -- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in
3675 -- the plan as it's pulled in setref.c due to having just a single subnode).
3676 select explain_parallel_append('select * from listp where a = (select 1);');
3677 explain_parallel_append
3678 ----------------------------------------------------------------------
3679 Gather (actual rows=N loops=N)
3680 Workers Planned: 2
3681 Params Evaluated: $0
3682 Workers Launched: N
3683 InitPlan 1 (returns $0)
3684 -> Result (actual rows=N loops=N)
3685 -> Parallel Append (actual rows=N loops=N)
3686 -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N)
3687 Filter: (a = $0)
3688 -> Parallel Seq Scan on listp_12_2 listp_2 (never executed)
3689 Filter: (a = $0)
3690 (11 rows)
3692 -- Like the above but throw some more complexity at the planner by adding
3693 -- a UNION ALL. We expect both sides of the union not to scan the
3694 -- non-required partitions.
3695 select explain_parallel_append(
3696 'select * from listp where a = (select 1)
3697 union all
3698 select * from listp where a = (select 2);');
3699 explain_parallel_append
3700 -----------------------------------------------------------------------------------
3701 Append (actual rows=N loops=N)
3702 -> Gather (actual rows=N loops=N)
3703 Workers Planned: 2
3704 Params Evaluated: $0
3705 Workers Launched: N
3706 InitPlan 1 (returns $0)
3707 -> Result (actual rows=N loops=N)
3708 -> Parallel Append (actual rows=N loops=N)
3709 -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N)
3710 Filter: (a = $0)
3711 -> Parallel Seq Scan on listp_12_2 listp_2 (never executed)
3712 Filter: (a = $0)
3713 -> Gather (actual rows=N loops=N)
3714 Workers Planned: 2
3715 Params Evaluated: $1
3716 Workers Launched: N
3717 InitPlan 2 (returns $1)
3718 -> Result (actual rows=N loops=N)
3719 -> Parallel Append (actual rows=N loops=N)
3720 -> Seq Scan on listp_12_1 listp_4 (never executed)
3721 Filter: (a = $1)
3722 -> Parallel Seq Scan on listp_12_2 listp_5 (actual rows=N loops=N)
3723 Filter: (a = $1)
3724 (23 rows)
3726 drop table listp;
3727 reset parallel_tuple_cost;
3728 reset parallel_setup_cost;
3729 -- Test case for run-time pruning with a nested Merge Append
3730 set enable_sort to 0;
3731 create table rangep (a int, b int) partition by range (a);
3732 create table rangep_0_to_100 partition of rangep for values from (0) to (100) partition by list (b);
3733 -- We need 3 sub-partitions. 1 to validate pruning worked and another two
3734 -- because a single remaining partition would be pulled up to the main Append.
3735 create table rangep_0_to_100_1 partition of rangep_0_to_100 for values in(1);
3736 create table rangep_0_to_100_2 partition of rangep_0_to_100 for values in(2);
3737 create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3);
3738 create table rangep_100_to_200 partition of rangep for values from (100) to (200);
3739 create index on rangep (a);
3740 -- Ensure run-time pruning works on the nested Merge Append
3741 explain (analyze on, costs off, timing off, summary off)
3742 select * from rangep where b IN((select 1),(select 2)) order by a;
3743 QUERY PLAN
3744 ------------------------------------------------------------------------------------------------------------
3745 Append (actual rows=0 loops=1)
3746 InitPlan 1 (returns $0)
3747 -> Result (actual rows=1 loops=1)
3748 InitPlan 2 (returns $1)
3749 -> Result (actual rows=1 loops=1)
3750 -> Merge Append (actual rows=0 loops=1)
3751 Sort Key: rangep_2.a
3752 -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0 loops=1)
3753 Filter: (b = ANY (ARRAY[$0, $1]))
3754 -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1)
3755 Filter: (b = ANY (ARRAY[$0, $1]))
3756 -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed)
3757 Filter: (b = ANY (ARRAY[$0, $1]))
3758 -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0 loops=1)
3759 Filter: (b = ANY (ARRAY[$0, $1]))
3760 (15 rows)
3762 reset enable_sort;
3763 drop table rangep;
3764 --
3765 -- Check that gen_prune_steps_from_opexps() works well for various cases of
3766 -- clauses for different partition keys
3767 --
3768 create table rp_prefix_test1 (a int, b varchar) partition by range(a, b);
3769 create table rp_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b');
3770 create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b');
3771 -- Don't call get_steps_using_prefix() with the last partition key b plus
3772 -- an empty prefix
3773 explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a';
3774 QUERY PLAN
3775 --------------------------------------------------
3776 Seq Scan on rp_prefix_test1_p1 rp_prefix_test1
3777 Filter: ((a <= 1) AND ((b)::text = 'a'::text))
3778 (2 rows)
3780 create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c);
3781 create table rp_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10);
3782 create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10);
3783 -- Don't call get_steps_using_prefix() with the last partition key c plus
3784 -- an invalid prefix (ie, b = 1)
3785 explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0;
3786 QUERY PLAN
3787 ------------------------------------------------
3788 Seq Scan on rp_prefix_test2_p1 rp_prefix_test2
3789 Filter: ((a <= 1) AND (c >= 0) AND (b = 1))
3790 (2 rows)
3792 create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d);
3793 create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10);
3794 create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10);
3795 -- Test that get_steps_using_prefix() handles a prefix that contains multiple
3796 -- clauses for the partition key b (ie, b >= 1 and b >= 2)
3797 explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0;
3798 QUERY PLAN
3799 --------------------------------------------------------------------------
3800 Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
3801 Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0))
3802 (2 rows)
3804 -- Test that get_steps_using_prefix() handles a prefix that contains multiple
3805 -- clauses for the partition key b (ie, b >= 1 and b = 2) (This also tests
3806 -- that the caller arranges clauses in that prefix in the required order)
3807 explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b = 2 and c = 2 and d >= 0;
3808 QUERY PLAN
3809 ------------------------------------------------------------------------
3810 Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
3811 Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2))
3812 (2 rows)
3814 create table hp_prefix_test (a int, b int, c int, d int) partition by hash (a part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d part_test_int4_ops);
3815 create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 2, remainder 0);
3816 create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 2, remainder 1);
3817 -- Test that get_steps_using_prefix() handles non-NULL step_nullkeys
3818 explain (costs off) select * from hp_prefix_test where a = 1 and b is null and c = 1 and d = 1;
3819 QUERY PLAN
3820 -------------------------------------------------------------
3821 Seq Scan on hp_prefix_test_p1 hp_prefix_test
3822 Filter: ((b IS NULL) AND (a = 1) AND (c = 1) AND (d = 1))
3823 (2 rows)
3825 drop table rp_prefix_test1;
3826 drop table rp_prefix_test2;
3827 drop table rp_prefix_test3;
3828 drop table hp_prefix_test;
3829 --
3830 -- Check that gen_partprune_steps() detects self-contradiction from clauses
3831 -- regardless of the order of the clauses (Here we use a custom operator to
3832 -- prevent the equivclass.c machinery from reordering the clauses)
3833 --
3834 create operator === (
3835 leftarg = int4,
3836 rightarg = int4,
3837 procedure = int4eq,
3838 commutator = ===,
3839 hashes
3840 );
3841 create operator class part_test_int4_ops2
3842 for type int4
3843 using hash as
3844 operator 1 ===,
3845 function 2 part_hashint4_noop(int4, int8);
3846 create table hp_contradict_test (a int, b int) partition by hash (a part_test_int4_ops2, b part_test_int4_ops2);
3847 create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0);
3848 create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1);
3849 explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1;
3850 QUERY PLAN
3851 --------------------------
3852 Result
3853 One-Time Filter: false
3854 (2 rows)
3856 explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null;
3857 QUERY PLAN
3858 --------------------------
3859 Result
3860 One-Time Filter: false
3861 (2 rows)
3863 drop table hp_contradict_test;
3864 drop operator class part_test_int4_ops2 using hash;
3865 drop operator ===(int4, int4);