| blob | c52bc40e8129e38cbf89a47af1a0f196de7f40aa |
1 --
2 -- Test partitioning planner code
3 --
4 -- Helper function which can be used for masking out portions of EXPLAIN
5 -- ANALYZE which could contain information that's not consistent on all
6 -- platforms.
7 create function explain_analyze(query text) returns setof text
8 language plpgsql as
9 $$
10 declare
11 ln text;
12 begin
13 for ln in
14 execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
15 query)
16 loop
17 ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N');
18 return next ln;
19 end loop;
20 end;
21 $$;
22 -- Force generic plans to be used for all prepared statements in this file.
23 set plan_cache_mode = force_generic_plan;
24 create table lp (a char) partition by list (a);
25 create table lp_default partition of lp default;
26 create table lp_ef partition of lp for values in ('e', 'f');
27 create table lp_ad partition of lp for values in ('a', 'd');
28 create table lp_bc partition of lp for values in ('b', 'c');
29 create table lp_g partition of lp for values in ('g');
30 create table lp_null partition of lp for values in (null);
31 explain (costs off) select * from lp;
32 QUERY PLAN
33 -----------------------------------
34 Append
35 -> Seq Scan on lp_ad lp_1
36 -> Seq Scan on lp_bc lp_2
37 -> Seq Scan on lp_ef lp_3
38 -> Seq Scan on lp_g lp_4
39 -> Seq Scan on lp_null lp_5
40 -> Seq Scan on lp_default lp_6
41 (7 rows)
43 explain (costs off) select * from lp where a > 'a' and a < 'd';
44 QUERY PLAN
45 -----------------------------------------------------------
46 Append
47 -> Seq Scan on lp_bc lp_1
48 Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
49 -> Seq Scan on lp_default lp_2
50 Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
51 (5 rows)
53 explain (costs off) select * from lp where a > 'a' and a <= 'd';
54 QUERY PLAN
55 ------------------------------------------------------------
56 Append
57 -> Seq Scan on lp_ad lp_1
58 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
59 -> Seq Scan on lp_bc lp_2
60 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
61 -> Seq Scan on lp_default lp_3
62 Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
63 (7 rows)
65 explain (costs off) select * from lp where a = 'a';
66 QUERY PLAN
67 -----------------------------
68 Seq Scan on lp_ad lp
69 Filter: (a = 'a'::bpchar)
70 (2 rows)
72 explain (costs off) select * from lp where 'a' = a; /* commuted */
73 QUERY PLAN
74 -----------------------------
75 Seq Scan on lp_ad lp
76 Filter: ('a'::bpchar = a)
77 (2 rows)
79 explain (costs off) select * from lp where a is not null;
80 QUERY PLAN
81 -----------------------------------
82 Append
83 -> Seq Scan on lp_ad lp_1
84 Filter: (a IS NOT NULL)
85 -> Seq Scan on lp_bc lp_2
86 Filter: (a IS NOT NULL)
87 -> Seq Scan on lp_ef lp_3
88 Filter: (a IS NOT NULL)
89 -> Seq Scan on lp_g lp_4
90 Filter: (a IS NOT NULL)
91 -> Seq Scan on lp_default lp_5
92 Filter: (a IS NOT NULL)
93 (11 rows)
95 explain (costs off) select * from lp where a is null;
96 QUERY PLAN
97 ------------------------
98 Seq Scan on lp_null lp
99 Filter: (a IS NULL)
100 (2 rows)
102 explain (costs off) select * from lp where a = 'a' or a = 'c';
103 QUERY PLAN
104 ----------------------------------------------------------
105 Append
106 -> Seq Scan on lp_ad lp_1
107 Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
108 -> Seq Scan on lp_bc lp_2
109 Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
110 (5 rows)
112 explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
113 QUERY PLAN
114 --------------------------------------------------------------------------------
115 Append
116 -> Seq Scan on lp_ad lp_1
117 Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
118 -> Seq Scan on lp_bc lp_2
119 Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
120 (5 rows)
122 explain (costs off) select * from lp where a <> 'g';
123 QUERY PLAN
124 ------------------------------------
125 Append
126 -> Seq Scan on lp_ad lp_1
127 Filter: (a <> 'g'::bpchar)
128 -> Seq Scan on lp_bc lp_2
129 Filter: (a <> 'g'::bpchar)
130 -> Seq Scan on lp_ef lp_3
131 Filter: (a <> 'g'::bpchar)
132 -> Seq Scan on lp_default lp_4
133 Filter: (a <> 'g'::bpchar)
134 (9 rows)
136 explain (costs off) select * from lp where a <> 'a' and a <> 'd';
137 QUERY PLAN
138 -------------------------------------------------------------
139 Append
140 -> Seq Scan on lp_bc lp_1
141 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
142 -> Seq Scan on lp_ef lp_2
143 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
144 -> Seq Scan on lp_g lp_3
145 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
146 -> Seq Scan on lp_default lp_4
147 Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
148 (9 rows)
150 explain (costs off) select * from lp where a not in ('a', 'd');
151 QUERY PLAN
152 ------------------------------------------------
153 Append
154 -> Seq Scan on lp_bc lp_1
155 Filter: (a <> ALL ('{a,d}'::bpchar[]))
156 -> Seq Scan on lp_ef lp_2
157 Filter: (a <> ALL ('{a,d}'::bpchar[]))
158 -> Seq Scan on lp_g lp_3
159 Filter: (a <> ALL ('{a,d}'::bpchar[]))
160 -> Seq Scan on lp_default lp_4
161 Filter: (a <> ALL ('{a,d}'::bpchar[]))
162 (9 rows)
164 -- collation matches the partitioning collation, pruning works
165 create table coll_pruning (a text collate "C") partition by list (a);
166 create table coll_pruning_a partition of coll_pruning for values in ('a');
167 create table coll_pruning_b partition of coll_pruning for values in ('b');
168 create table coll_pruning_def partition of coll_pruning default;
169 explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
170 QUERY PLAN
171 -----------------------------------------
172 Seq Scan on coll_pruning_a coll_pruning
173 Filter: (a = 'a'::text COLLATE "C")
174 (2 rows)
176 -- collation doesn't match the partitioning collation, no pruning occurs
177 explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
178 QUERY PLAN
179 ---------------------------------------------------------
180 Append
181 -> Seq Scan on coll_pruning_a coll_pruning_1
182 Filter: ((a)::text = 'a'::text COLLATE "POSIX")
183 -> Seq Scan on coll_pruning_b coll_pruning_2
184 Filter: ((a)::text = 'a'::text COLLATE "POSIX")
185 -> Seq Scan on coll_pruning_def coll_pruning_3
186 Filter: ((a)::text = 'a'::text COLLATE "POSIX")
187 (7 rows)
189 create table rlp (a int, b varchar) partition by range (a);
190 create table rlp_default partition of rlp default partition by list (a);
191 create table rlp_default_default partition of rlp_default default;
192 create table rlp_default_10 partition of rlp_default for values in (10);
193 create table rlp_default_30 partition of rlp_default for values in (30);
194 create table rlp_default_null partition of rlp_default for values in (null);
195 create table rlp1 partition of rlp for values from (minvalue) to (1);
196 create table rlp2 partition of rlp for values from (1) to (10);
197 create table rlp3 (b varchar, a int) partition by list (b varchar_ops);
198 create table rlp3_default partition of rlp3 default;
199 create table rlp3abcd partition of rlp3 for values in ('ab', 'cd');
200 create table rlp3efgh partition of rlp3 for values in ('ef', 'gh');
201 create table rlp3nullxy partition of rlp3 for values in (null, 'xy');
202 alter table rlp attach partition rlp3 for values from (15) to (20);
203 create table rlp4 partition of rlp for values from (20) to (30) partition by range (a);
204 create table rlp4_default partition of rlp4 default;
205 create table rlp4_1 partition of rlp4 for values from (20) to (25);
206 create table rlp4_2 partition of rlp4 for values from (25) to (29);
207 create table rlp5 partition of rlp for values from (31) to (maxvalue) partition by range (a);
208 create table rlp5_default partition of rlp5 default;
209 create table rlp5_1 partition of rlp5 for values from (31) to (40);
210 explain (costs off) select * from rlp where a < 1;
211 QUERY PLAN
212 ----------------------
213 Seq Scan on rlp1 rlp
214 Filter: (a < 1)
215 (2 rows)
217 explain (costs off) select * from rlp where 1 > a; /* commuted */
218 QUERY PLAN
219 ----------------------
220 Seq Scan on rlp1 rlp
221 Filter: (1 > a)
222 (2 rows)
224 explain (costs off) select * from rlp where a <= 1;
225 QUERY PLAN
226 ------------------------------
227 Append
228 -> Seq Scan on rlp1 rlp_1
229 Filter: (a <= 1)
230 -> Seq Scan on rlp2 rlp_2
231 Filter: (a <= 1)
232 (5 rows)
234 explain (costs off) select * from rlp where a = 1;
235 QUERY PLAN
236 ----------------------
237 Seq Scan on rlp2 rlp
238 Filter: (a = 1)
239 (2 rows)
241 explain (costs off) select * from rlp where a = 1::bigint; /* same as above */
242 QUERY PLAN
243 -----------------------------
244 Seq Scan on rlp2 rlp
245 Filter: (a = '1'::bigint)
246 (2 rows)
248 explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */
249 QUERY PLAN
250 -----------------------------------------------
251 Append
252 -> Seq Scan on rlp1 rlp_1
253 Filter: ((a)::numeric = '1'::numeric)
254 -> Seq Scan on rlp2 rlp_2
255 Filter: ((a)::numeric = '1'::numeric)
256 -> Seq Scan on rlp3abcd rlp_3
257 Filter: ((a)::numeric = '1'::numeric)
258 -> Seq Scan on rlp3efgh rlp_4
259 Filter: ((a)::numeric = '1'::numeric)
260 -> Seq Scan on rlp3nullxy rlp_5
261 Filter: ((a)::numeric = '1'::numeric)
262 -> Seq Scan on rlp3_default rlp_6
263 Filter: ((a)::numeric = '1'::numeric)
264 -> Seq Scan on rlp4_1 rlp_7
265 Filter: ((a)::numeric = '1'::numeric)
266 -> Seq Scan on rlp4_2 rlp_8
267 Filter: ((a)::numeric = '1'::numeric)
268 -> Seq Scan on rlp4_default rlp_9
269 Filter: ((a)::numeric = '1'::numeric)
270 -> Seq Scan on rlp5_1 rlp_10
271 Filter: ((a)::numeric = '1'::numeric)
272 -> Seq Scan on rlp5_default rlp_11
273 Filter: ((a)::numeric = '1'::numeric)
274 -> Seq Scan on rlp_default_10 rlp_12
275 Filter: ((a)::numeric = '1'::numeric)
276 -> Seq Scan on rlp_default_30 rlp_13
277 Filter: ((a)::numeric = '1'::numeric)
278 -> Seq Scan on rlp_default_null rlp_14
279 Filter: ((a)::numeric = '1'::numeric)
280 -> Seq Scan on rlp_default_default rlp_15
281 Filter: ((a)::numeric = '1'::numeric)
282 (31 rows)
284 explain (costs off) select * from rlp where a <= 10;
285 QUERY PLAN
286 ---------------------------------------------
287 Append
288 -> Seq Scan on rlp1 rlp_1
289 Filter: (a <= 10)
290 -> Seq Scan on rlp2 rlp_2
291 Filter: (a <= 10)
292 -> Seq Scan on rlp_default_10 rlp_3
293 Filter: (a <= 10)
294 -> Seq Scan on rlp_default_default rlp_4
295 Filter: (a <= 10)
296 (9 rows)
298 explain (costs off) select * from rlp where a > 10;
299 QUERY PLAN
300 ----------------------------------------------
301 Append
302 -> Seq Scan on rlp3abcd rlp_1
303 Filter: (a > 10)
304 -> Seq Scan on rlp3efgh rlp_2
305 Filter: (a > 10)
306 -> Seq Scan on rlp3nullxy rlp_3
307 Filter: (a > 10)
308 -> Seq Scan on rlp3_default rlp_4
309 Filter: (a > 10)
310 -> Seq Scan on rlp4_1 rlp_5
311 Filter: (a > 10)
312 -> Seq Scan on rlp4_2 rlp_6
313 Filter: (a > 10)
314 -> Seq Scan on rlp4_default rlp_7
315 Filter: (a > 10)
316 -> Seq Scan on rlp5_1 rlp_8
317 Filter: (a > 10)
318 -> Seq Scan on rlp5_default rlp_9
319 Filter: (a > 10)
320 -> Seq Scan on rlp_default_30 rlp_10
321 Filter: (a > 10)
322 -> Seq Scan on rlp_default_default rlp_11
323 Filter: (a > 10)
324 (23 rows)
326 explain (costs off) select * from rlp where a < 15;
327 QUERY PLAN
328 ---------------------------------------------
329 Append
330 -> Seq Scan on rlp1 rlp_1
331 Filter: (a < 15)
332 -> Seq Scan on rlp2 rlp_2
333 Filter: (a < 15)
334 -> Seq Scan on rlp_default_10 rlp_3
335 Filter: (a < 15)
336 -> Seq Scan on rlp_default_default rlp_4
337 Filter: (a < 15)
338 (9 rows)
340 explain (costs off) select * from rlp where a <= 15;
341 QUERY PLAN
342 ---------------------------------------------
343 Append
344 -> Seq Scan on rlp1 rlp_1
345 Filter: (a <= 15)
346 -> Seq Scan on rlp2 rlp_2
347 Filter: (a <= 15)
348 -> Seq Scan on rlp3abcd rlp_3
349 Filter: (a <= 15)
350 -> Seq Scan on rlp3efgh rlp_4
351 Filter: (a <= 15)
352 -> Seq Scan on rlp3nullxy rlp_5
353 Filter: (a <= 15)
354 -> Seq Scan on rlp3_default rlp_6
355 Filter: (a <= 15)
356 -> Seq Scan on rlp_default_10 rlp_7
357 Filter: (a <= 15)
358 -> Seq Scan on rlp_default_default rlp_8
359 Filter: (a <= 15)
360 (17 rows)
362 explain (costs off) select * from rlp where a > 15 and b = 'ab';
363 QUERY PLAN
364 ---------------------------------------------------------
365 Append
366 -> Seq Scan on rlp3abcd rlp_1
367 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
368 -> Seq Scan on rlp4_1 rlp_2
369 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
370 -> Seq Scan on rlp4_2 rlp_3
371 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
372 -> Seq Scan on rlp4_default rlp_4
373 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
374 -> Seq Scan on rlp5_1 rlp_5
375 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
376 -> Seq Scan on rlp5_default rlp_6
377 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
378 -> Seq Scan on rlp_default_30 rlp_7
379 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
380 -> Seq Scan on rlp_default_default rlp_8
381 Filter: ((a > 15) AND ((b)::text = 'ab'::text))
382 (17 rows)
384 explain (costs off) select * from rlp where a = 16;
385 QUERY PLAN
386 --------------------------------------
387 Append
388 -> Seq Scan on rlp3abcd rlp_1
389 Filter: (a = 16)
390 -> Seq Scan on rlp3efgh rlp_2
391 Filter: (a = 16)
392 -> Seq Scan on rlp3nullxy rlp_3
393 Filter: (a = 16)
394 -> Seq Scan on rlp3_default rlp_4
395 Filter: (a = 16)
396 (9 rows)
398 explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here');
399 QUERY PLAN
400 ----------------------------------------------------------------------
401 Seq Scan on rlp3_default rlp
402 Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[])))
403 (2 rows)
405 explain (costs off) select * from rlp where a = 16 and b < 'ab';
406 QUERY PLAN
407 ---------------------------------------------------
408 Seq Scan on rlp3_default rlp
409 Filter: (((b)::text < 'ab'::text) AND (a = 16))
410 (2 rows)
412 explain (costs off) select * from rlp where a = 16 and b <= 'ab';
413 QUERY PLAN
414 ----------------------------------------------------------
415 Append
416 -> Seq Scan on rlp3abcd rlp_1
417 Filter: (((b)::text <= 'ab'::text) AND (a = 16))
418 -> Seq Scan on rlp3_default rlp_2
419 Filter: (((b)::text <= 'ab'::text) AND (a = 16))
420 (5 rows)
422 explain (costs off) select * from rlp where a = 16 and b is null;
423 QUERY PLAN
424 --------------------------------------
425 Seq Scan on rlp3nullxy rlp
426 Filter: ((b IS NULL) AND (a = 16))
427 (2 rows)
429 explain (costs off) select * from rlp where a = 16 and b is not null;
430 QUERY PLAN
431 ------------------------------------------------
432 Append
433 -> Seq Scan on rlp3abcd rlp_1
434 Filter: ((b IS NOT NULL) AND (a = 16))
435 -> Seq Scan on rlp3efgh rlp_2
436 Filter: ((b IS NOT NULL) AND (a = 16))
437 -> Seq Scan on rlp3nullxy rlp_3
438 Filter: ((b IS NOT NULL) AND (a = 16))
439 -> Seq Scan on rlp3_default rlp_4
440 Filter: ((b IS NOT NULL) AND (a = 16))
441 (9 rows)
443 explain (costs off) select * from rlp where a is null;
444 QUERY PLAN
445 ----------------------------------
446 Seq Scan on rlp_default_null rlp
447 Filter: (a IS NULL)
448 (2 rows)
450 explain (costs off) select * from rlp where a is not null;
451 QUERY PLAN
452 ----------------------------------------------
453 Append
454 -> Seq Scan on rlp1 rlp_1
455 Filter: (a IS NOT NULL)
456 -> Seq Scan on rlp2 rlp_2
457 Filter: (a IS NOT NULL)
458 -> Seq Scan on rlp3abcd rlp_3
459 Filter: (a IS NOT NULL)
460 -> Seq Scan on rlp3efgh rlp_4
461 Filter: (a IS NOT NULL)
462 -> Seq Scan on rlp3nullxy rlp_5
463 Filter: (a IS NOT NULL)
464 -> Seq Scan on rlp3_default rlp_6
465 Filter: (a IS NOT NULL)
466 -> Seq Scan on rlp4_1 rlp_7
467 Filter: (a IS NOT NULL)
468 -> Seq Scan on rlp4_2 rlp_8
469 Filter: (a IS NOT NULL)
470 -> Seq Scan on rlp4_default rlp_9
471 Filter: (a IS NOT NULL)
472 -> Seq Scan on rlp5_1 rlp_10
473 Filter: (a IS NOT NULL)
474 -> Seq Scan on rlp5_default rlp_11
475 Filter: (a IS NOT NULL)
476 -> Seq Scan on rlp_default_10 rlp_12
477 Filter: (a IS NOT NULL)
478 -> Seq Scan on rlp_default_30 rlp_13
479 Filter: (a IS NOT NULL)
480 -> Seq Scan on rlp_default_default rlp_14
481 Filter: (a IS NOT NULL)
482 (29 rows)
484 explain (costs off) select * from rlp where a > 30;
485 QUERY PLAN
486 ---------------------------------------------
487 Append
488 -> Seq Scan on rlp5_1 rlp_1
489 Filter: (a > 30)
490 -> Seq Scan on rlp5_default rlp_2
491 Filter: (a > 30)
492 -> Seq Scan on rlp_default_default rlp_3
493 Filter: (a > 30)
494 (7 rows)
496 explain (costs off) select * from rlp where a = 30; /* only default is scanned */
497 QUERY PLAN
498 --------------------------------
499 Seq Scan on rlp_default_30 rlp
500 Filter: (a = 30)
501 (2 rows)
503 explain (costs off) select * from rlp where a <= 31;
504 QUERY PLAN
505 ----------------------------------------------
506 Append
507 -> Seq Scan on rlp1 rlp_1
508 Filter: (a <= 31)
509 -> Seq Scan on rlp2 rlp_2
510 Filter: (a <= 31)
511 -> Seq Scan on rlp3abcd rlp_3
512 Filter: (a <= 31)
513 -> Seq Scan on rlp3efgh rlp_4
514 Filter: (a <= 31)
515 -> Seq Scan on rlp3nullxy rlp_5
516 Filter: (a <= 31)
517 -> Seq Scan on rlp3_default rlp_6
518 Filter: (a <= 31)
519 -> Seq Scan on rlp4_1 rlp_7
520 Filter: (a <= 31)
521 -> Seq Scan on rlp4_2 rlp_8
522 Filter: (a <= 31)
523 -> Seq Scan on rlp4_default rlp_9
524 Filter: (a <= 31)
525 -> Seq Scan on rlp5_1 rlp_10
526 Filter: (a <= 31)
527 -> Seq Scan on rlp_default_10 rlp_11
528 Filter: (a <= 31)
529 -> Seq Scan on rlp_default_30 rlp_12
530 Filter: (a <= 31)
531 -> Seq Scan on rlp_default_default rlp_13
532 Filter: (a <= 31)
533 (27 rows)
535 explain (costs off) select * from rlp where a = 1 or a = 7;
536 QUERY PLAN
537 --------------------------------
538 Seq Scan on rlp2 rlp
539 Filter: ((a = 1) OR (a = 7))
540 (2 rows)
542 explain (costs off) select * from rlp where a = 1 or b = 'ab';
543 QUERY PLAN
544 -------------------------------------------------------
545 Append
546 -> Seq Scan on rlp1 rlp_1
547 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
548 -> Seq Scan on rlp2 rlp_2
549 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
550 -> Seq Scan on rlp3abcd rlp_3
551 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
552 -> Seq Scan on rlp4_1 rlp_4
553 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
554 -> Seq Scan on rlp4_2 rlp_5
555 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
556 -> Seq Scan on rlp4_default rlp_6
557 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
558 -> Seq Scan on rlp5_1 rlp_7
559 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
560 -> Seq Scan on rlp5_default rlp_8
561 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
562 -> Seq Scan on rlp_default_10 rlp_9
563 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
564 -> Seq Scan on rlp_default_30 rlp_10
565 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
566 -> Seq Scan on rlp_default_null rlp_11
567 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
568 -> Seq Scan on rlp_default_default rlp_12
569 Filter: ((a = 1) OR ((b)::text = 'ab'::text))
570 (25 rows)
572 explain (costs off) select * from rlp where a > 20 and a < 27;
573 QUERY PLAN
574 -----------------------------------------
575 Append
576 -> Seq Scan on rlp4_1 rlp_1
577 Filter: ((a > 20) AND (a < 27))
578 -> Seq Scan on rlp4_2 rlp_2
579 Filter: ((a > 20) AND (a < 27))
580 (5 rows)
582 explain (costs off) select * from rlp where a = 29;
583 QUERY PLAN
584 ------------------------------
585 Seq Scan on rlp4_default rlp
586 Filter: (a = 29)
587 (2 rows)
589 explain (costs off) select * from rlp where a >= 29;
590 QUERY PLAN
591 ---------------------------------------------
592 Append
593 -> Seq Scan on rlp4_default rlp_1
594 Filter: (a >= 29)
595 -> Seq Scan on rlp5_1 rlp_2
596 Filter: (a >= 29)
597 -> Seq Scan on rlp5_default rlp_3
598 Filter: (a >= 29)
599 -> Seq Scan on rlp_default_30 rlp_4
600 Filter: (a >= 29)
601 -> Seq Scan on rlp_default_default rlp_5
602 Filter: (a >= 29)
603 (11 rows)
605 explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
606 QUERY PLAN
607 ------------------------------------------------------
608 Append
609 -> Seq Scan on rlp1 rlp_1
610 Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
611 -> Seq Scan on rlp4_1 rlp_2
612 Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
613 (5 rows)
615 -- where clause contradicts sub-partition's constraint
616 explain (costs off) select * from rlp where a = 20 or a = 40;
617 QUERY PLAN
618 ----------------------------------------
619 Append
620 -> Seq Scan on rlp4_1 rlp_1
621 Filter: ((a = 20) OR (a = 40))
622 -> Seq Scan on rlp5_default rlp_2
623 Filter: ((a = 20) OR (a = 40))
624 (5 rows)
626 explain (costs off) select * from rlp3 where a = 20; /* empty */
627 QUERY PLAN
628 --------------------------
629 Result
630 One-Time Filter: false
631 (2 rows)
633 -- redundant clauses are eliminated
634 explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
635 QUERY PLAN
636 ----------------------------------
637 Seq Scan on rlp_default_10 rlp
638 Filter: ((a > 1) AND (a = 10))
639 (2 rows)
641 explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
642 QUERY PLAN
643 ----------------------------------------------
644 Append
645 -> Seq Scan on rlp3abcd rlp_1
646 Filter: ((a > 1) AND (a >= 15))
647 -> Seq Scan on rlp3efgh rlp_2
648 Filter: ((a > 1) AND (a >= 15))
649 -> Seq Scan on rlp3nullxy rlp_3
650 Filter: ((a > 1) AND (a >= 15))
651 -> Seq Scan on rlp3_default rlp_4
652 Filter: ((a > 1) AND (a >= 15))
653 -> Seq Scan on rlp4_1 rlp_5
654 Filter: ((a > 1) AND (a >= 15))
655 -> Seq Scan on rlp4_2 rlp_6
656 Filter: ((a > 1) AND (a >= 15))
657 -> Seq Scan on rlp4_default rlp_7
658 Filter: ((a > 1) AND (a >= 15))
659 -> Seq Scan on rlp5_1 rlp_8
660 Filter: ((a > 1) AND (a >= 15))
661 -> Seq Scan on rlp5_default rlp_9
662 Filter: ((a > 1) AND (a >= 15))
663 -> Seq Scan on rlp_default_30 rlp_10
664 Filter: ((a > 1) AND (a >= 15))
665 -> Seq Scan on rlp_default_default rlp_11
666 Filter: ((a > 1) AND (a >= 15))
667 (23 rows)
669 explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
670 QUERY PLAN
671 --------------------------
672 Result
673 One-Time Filter: false
674 (2 rows)
676 explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
677 QUERY PLAN
678 -------------------------------------------------------------------
679 Append
680 -> Seq Scan on rlp2 rlp_1
681 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
682 -> Seq Scan on rlp3abcd rlp_2
683 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
684 -> Seq Scan on rlp3efgh rlp_3
685 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
686 -> Seq Scan on rlp3nullxy rlp_4
687 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
688 -> Seq Scan on rlp3_default rlp_5
689 Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
690 (11 rows)
692 -- multi-column keys
693 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
694 create table mc3p_default partition of mc3p default;
695 create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
696 create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10);
697 create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10);
698 create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20);
699 create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue);
700 create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10);
701 create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20);
702 create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
703 explain (costs off) select * from mc3p where a = 1;
704 QUERY PLAN
705 ---------------------------------------
706 Append
707 -> Seq Scan on mc3p0 mc3p_1
708 Filter: (a = 1)
709 -> Seq Scan on mc3p1 mc3p_2
710 Filter: (a = 1)
711 -> Seq Scan on mc3p_default mc3p_3
712 Filter: (a = 1)
713 (7 rows)
715 explain (costs off) select * from mc3p where a = 1 and abs(b) < 1;
716 QUERY PLAN
717 --------------------------------------------
718 Append
719 -> Seq Scan on mc3p0 mc3p_1
720 Filter: ((a = 1) AND (abs(b) < 1))
721 -> Seq Scan on mc3p_default mc3p_2
722 Filter: ((a = 1) AND (abs(b) < 1))
723 (5 rows)
725 explain (costs off) select * from mc3p where a = 1 and abs(b) = 1;
726 QUERY PLAN
727 --------------------------------------------
728 Append
729 -> Seq Scan on mc3p0 mc3p_1
730 Filter: ((a = 1) AND (abs(b) = 1))
731 -> Seq Scan on mc3p1 mc3p_2
732 Filter: ((a = 1) AND (abs(b) = 1))
733 -> Seq Scan on mc3p_default mc3p_3
734 Filter: ((a = 1) AND (abs(b) = 1))
735 (7 rows)
737 explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8;
738 QUERY PLAN
739 --------------------------------------------------------
740 Append
741 -> Seq Scan on mc3p0 mc3p_1
742 Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
743 -> Seq Scan on mc3p1 mc3p_2
744 Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
745 (5 rows)
747 explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
748 QUERY PLAN
749 -----------------------------------------------------------------
750 Append
751 -> Seq Scan on mc3p1 mc3p_1
752 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
753 -> Seq Scan on mc3p2 mc3p_2
754 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
755 -> Seq Scan on mc3p3 mc3p_3
756 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
757 -> Seq Scan on mc3p4 mc3p_4
758 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
759 -> Seq Scan on mc3p_default mc3p_5
760 Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
761 (11 rows)
763 explain (costs off) select * from mc3p where a > 10;
764 QUERY PLAN
765 ---------------------------------------
766 Append
767 -> Seq Scan on mc3p5 mc3p_1
768 Filter: (a > 10)
769 -> Seq Scan on mc3p6 mc3p_2
770 Filter: (a > 10)
771 -> Seq Scan on mc3p7 mc3p_3
772 Filter: (a > 10)
773 -> Seq Scan on mc3p_default mc3p_4
774 Filter: (a > 10)
775 (9 rows)
777 explain (costs off) select * from mc3p where a >= 10;
778 QUERY PLAN
779 ---------------------------------------
780 Append
781 -> Seq Scan on mc3p1 mc3p_1
782 Filter: (a >= 10)
783 -> Seq Scan on mc3p2 mc3p_2
784 Filter: (a >= 10)
785 -> Seq Scan on mc3p3 mc3p_3
786 Filter: (a >= 10)
787 -> Seq Scan on mc3p4 mc3p_4
788 Filter: (a >= 10)
789 -> Seq Scan on mc3p5 mc3p_5
790 Filter: (a >= 10)
791 -> Seq Scan on mc3p6 mc3p_6
792 Filter: (a >= 10)
793 -> Seq Scan on mc3p7 mc3p_7
794 Filter: (a >= 10)
795 -> Seq Scan on mc3p_default mc3p_8
796 Filter: (a >= 10)
797 (17 rows)
799 explain (costs off) select * from mc3p where a < 10;
800 QUERY PLAN
801 ---------------------------------------
802 Append
803 -> Seq Scan on mc3p0 mc3p_1
804 Filter: (a < 10)
805 -> Seq Scan on mc3p1 mc3p_2
806 Filter: (a < 10)
807 -> Seq Scan on mc3p_default mc3p_3
808 Filter: (a < 10)
809 (7 rows)
811 explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
812 QUERY PLAN
813 -----------------------------------------------
814 Append
815 -> Seq Scan on mc3p0 mc3p_1
816 Filter: ((a <= 10) AND (abs(b) < 10))
817 -> Seq Scan on mc3p1 mc3p_2
818 Filter: ((a <= 10) AND (abs(b) < 10))
819 -> Seq Scan on mc3p2 mc3p_3
820 Filter: ((a <= 10) AND (abs(b) < 10))
821 -> Seq Scan on mc3p_default mc3p_4
822 Filter: ((a <= 10) AND (abs(b) < 10))
823 (9 rows)
825 explain (costs off) select * from mc3p where a = 11 and abs(b) = 0;
826 QUERY PLAN
827 ---------------------------------------
828 Seq Scan on mc3p_default mc3p
829 Filter: ((a = 11) AND (abs(b) = 0))
830 (2 rows)
832 explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
833 QUERY PLAN
834 ------------------------------------------------------
835 Seq Scan on mc3p6 mc3p
836 Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))
837 (2 rows)
839 explain (costs off) select * from mc3p where a > 20;
840 QUERY PLAN
841 ---------------------------------------
842 Append
843 -> Seq Scan on mc3p7 mc3p_1
844 Filter: (a > 20)
845 -> Seq Scan on mc3p_default mc3p_2
846 Filter: (a > 20)
847 (5 rows)
849 explain (costs off) select * from mc3p where a >= 20;
850 QUERY PLAN
851 ---------------------------------------
852 Append
853 -> Seq Scan on mc3p5 mc3p_1
854 Filter: (a >= 20)
855 -> Seq Scan on mc3p6 mc3p_2
856 Filter: (a >= 20)
857 -> Seq Scan on mc3p7 mc3p_3
858 Filter: (a >= 20)
859 -> Seq Scan on mc3p_default mc3p_4
860 Filter: (a >= 20)
861 (9 rows)
863 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);
864 QUERY PLAN
865 ---------------------------------------------------------------------------------------------------------------------------------
866 Append
867 -> Seq Scan on mc3p1 mc3p_1
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)))
869 -> Seq Scan on mc3p2 mc3p_2
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)))
871 -> Seq Scan on mc3p5 mc3p_3
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)))
873 -> Seq Scan on mc3p_default mc3p_4
874 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)))
875 (9 rows)
877 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;
878 QUERY PLAN
879 --------------------------------------------------------------------------------------------------------------------------------------------
880 Append
881 -> Seq Scan on mc3p0 mc3p_1
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))
883 -> Seq Scan on mc3p1 mc3p_2
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))
885 -> Seq Scan on mc3p2 mc3p_3
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))
887 -> Seq Scan on mc3p5 mc3p_4
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))
889 -> Seq Scan on mc3p_default mc3p_5
890 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))
891 (11 rows)
893 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;
894 QUERY PLAN
895 -------------------------------------------------------------------------------------------------------------------------------------------------------
896 Append
897 -> Seq Scan on mc3p0 mc3p_1
898 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))
899 -> Seq Scan on mc3p1 mc3p_2
900 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))
901 -> Seq Scan on mc3p2 mc3p_3
902 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))
903 -> Seq Scan on mc3p5 mc3p_4
904 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))
905 -> Seq Scan on mc3p_default mc3p_5
906 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))
907 (11 rows)
909 explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
910 QUERY PLAN
911 ------------------------------------------------------
912 Append
913 -> Seq Scan on mc3p0 mc3p_1
914 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
915 -> Seq Scan on mc3p1 mc3p_2
916 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
917 -> Seq Scan on mc3p2 mc3p_3
918 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
919 -> Seq Scan on mc3p3 mc3p_4
920 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
921 -> Seq Scan on mc3p4 mc3p_5
922 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
923 -> Seq Scan on mc3p5 mc3p_6
924 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
925 -> Seq Scan on mc3p6 mc3p_7
926 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
927 -> Seq Scan on mc3p7 mc3p_8
928 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
929 -> Seq Scan on mc3p_default mc3p_9
930 Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
931 (19 rows)
933 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10);
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) = 10)))
939 -> Seq Scan on mc3p1 mc3p_2
940 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
941 -> Seq Scan on mc3p2 mc3p_3
942 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
943 -> Seq Scan on mc3p3 mc3p_4
944 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
945 -> Seq Scan on mc3p4 mc3p_5
946 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
947 -> Seq Scan on mc3p_default mc3p_6
948 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
949 (13 rows)
951 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
952 QUERY PLAN
953 -----------------------------------------------------------------------------
954 Append
955 -> Seq Scan on mc3p0 mc3p_1
956 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
957 -> Seq Scan on mc3p1 mc3p_2
958 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
959 -> Seq Scan on mc3p2 mc3p_3
960 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
961 -> Seq Scan on mc3p_default mc3p_4
962 Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
963 (9 rows)
965 -- a simpler multi-column keys case
966 create table mc2p (a int, b int) partition by range (a, b);
967 create table mc2p_default partition of mc2p default;
968 create table mc2p0 partition of mc2p for values from (minvalue, minvalue) to (1, minvalue);
969 create table mc2p1 partition of mc2p for values from (1, minvalue) to (1, 1);
970 create table mc2p2 partition of mc2p for values from (1, 1) to (2, minvalue);
971 create table mc2p3 partition of mc2p for values from (2, minvalue) to (2, 1);
972 create table mc2p4 partition of mc2p for values from (2, 1) to (2, maxvalue);
973 create table mc2p5 partition of mc2p for values from (2, maxvalue) to (maxvalue, maxvalue);
974 explain (costs off) select * from mc2p where a < 2;
975 QUERY PLAN
976 ---------------------------------------
977 Append
978 -> Seq Scan on mc2p0 mc2p_1
979 Filter: (a < 2)
980 -> Seq Scan on mc2p1 mc2p_2
981 Filter: (a < 2)
982 -> Seq Scan on mc2p2 mc2p_3
983 Filter: (a < 2)
984 -> Seq Scan on mc2p_default mc2p_4
985 Filter: (a < 2)
986 (9 rows)
988 explain (costs off) select * from mc2p where a = 2 and b < 1;
989 QUERY PLAN
990 ---------------------------------
991 Seq Scan on mc2p3 mc2p
992 Filter: ((b < 1) AND (a = 2))
993 (2 rows)
995 explain (costs off) select * from mc2p where a > 1;
996 QUERY PLAN
997 ---------------------------------------
998 Append
999 -> Seq Scan on mc2p2 mc2p_1
1000 Filter: (a > 1)
1001 -> Seq Scan on mc2p3 mc2p_2
1002 Filter: (a > 1)
1003 -> Seq Scan on mc2p4 mc2p_3
1004 Filter: (a > 1)
1005 -> Seq Scan on mc2p5 mc2p_4
1006 Filter: (a > 1)
1007 -> Seq Scan on mc2p_default mc2p_5
1008 Filter: (a > 1)
1009 (11 rows)
1011 explain (costs off) select * from mc2p where a = 1 and b > 1;
1012 QUERY PLAN
1013 ---------------------------------
1014 Seq Scan on mc2p2 mc2p
1015 Filter: ((b > 1) AND (a = 1))
1016 (2 rows)
1018 -- all partitions but the default one should be pruned
1019 explain (costs off) select * from mc2p where a = 1 and b is null;
1020 QUERY PLAN
1021 -------------------------------------
1022 Seq Scan on mc2p_default mc2p
1023 Filter: ((b IS NULL) AND (a = 1))
1024 (2 rows)
1026 explain (costs off) select * from mc2p where a is null and b is null;
1027 QUERY PLAN
1028 -----------------------------------------
1029 Seq Scan on mc2p_default mc2p
1030 Filter: ((a IS NULL) AND (b IS NULL))
1031 (2 rows)
1033 explain (costs off) select * from mc2p where a is null and b = 1;
1034 QUERY PLAN
1035 -------------------------------------
1036 Seq Scan on mc2p_default mc2p
1037 Filter: ((a IS NULL) AND (b = 1))
1038 (2 rows)
1040 explain (costs off) select * from mc2p where a is null;
1041 QUERY PLAN
1042 -------------------------------
1043 Seq Scan on mc2p_default mc2p
1044 Filter: (a IS NULL)
1045 (2 rows)
1047 explain (costs off) select * from mc2p where b is null;
1048 QUERY PLAN
1049 -------------------------------
1050 Seq Scan on mc2p_default mc2p
1051 Filter: (b IS NULL)
1052 (2 rows)
1054 -- boolean partitioning
1055 create table boolpart (a bool) partition by list (a);
1056 create table boolpart_default partition of boolpart default;
1057 create table boolpart_t partition of boolpart for values in ('true');
1058 create table boolpart_f partition of boolpart for values in ('false');
1059 insert into boolpart values (true), (false), (null);
1060 explain (costs off) select * from boolpart where a in (true, false);
1061 QUERY PLAN
1062 ------------------------------------------------
1063 Append
1064 -> Seq Scan on boolpart_f boolpart_1
1065 Filter: (a = ANY ('{t,f}'::boolean[]))
1066 -> Seq Scan on boolpart_t boolpart_2
1067 Filter: (a = ANY ('{t,f}'::boolean[]))
1068 (5 rows)
1070 explain (costs off) select * from boolpart where a = false;
1071 QUERY PLAN
1072 ---------------------------------
1073 Seq Scan on boolpart_f boolpart
1074 Filter: (NOT a)
1075 (2 rows)
1077 explain (costs off) select * from boolpart where not a = false;
1078 QUERY PLAN
1079 ---------------------------------
1080 Seq Scan on boolpart_t boolpart
1081 Filter: a
1082 (2 rows)
1084 explain (costs off) select * from boolpart where a is true or a is not true;
1085 QUERY PLAN
1086 --------------------------------------------------
1087 Append
1088 -> Seq Scan on boolpart_f boolpart_1
1089 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1090 -> Seq Scan on boolpart_t boolpart_2
1091 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1092 -> Seq Scan on boolpart_default boolpart_3
1093 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1094 (7 rows)
1096 explain (costs off) select * from boolpart where a is not true;
1097 QUERY PLAN
1098 -----------------------------------------------
1099 Append
1100 -> Seq Scan on boolpart_f boolpart_1
1101 Filter: (a IS NOT TRUE)
1102 -> Seq Scan on boolpart_default boolpart_2
1103 Filter: (a IS NOT TRUE)
1104 (5 rows)
1106 explain (costs off) select * from boolpart where a is not true and a is not false;
1107 QUERY PLAN
1108 --------------------------------------------------
1109 Seq Scan on boolpart_default boolpart
1110 Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1111 (2 rows)
1113 explain (costs off) select * from boolpart where a is unknown;
1114 QUERY PLAN
1115 ---------------------------------------
1116 Seq Scan on boolpart_default boolpart
1117 Filter: (a IS UNKNOWN)
1118 (2 rows)
1120 explain (costs off) select * from boolpart where a is not unknown;
1121 QUERY PLAN
1122 -----------------------------------------------
1123 Append
1124 -> Seq Scan on boolpart_f boolpart_1
1125 Filter: (a IS NOT UNKNOWN)
1126 -> Seq Scan on boolpart_t boolpart_2
1127 Filter: (a IS NOT UNKNOWN)
1128 -> Seq Scan on boolpart_default boolpart_3
1129 Filter: (a IS NOT UNKNOWN)
1130 (7 rows)
1132 select * from boolpart where a in (true, false);
1133 a
1134 ---
1135 f
1136 t
1137 (2 rows)
1139 select * from boolpart where a = false;
1140 a
1141 ---
1142 f
1143 (1 row)
1145 select * from boolpart where not a = false;
1146 a
1147 ---
1148 t
1149 (1 row)
1151 select * from boolpart where a is true or a is not true;
1152 a
1153 ---
1154 f
1155 t
1157 (3 rows)
1159 select * from boolpart where a is not true;
1160 a
1161 ---
1162 f
1164 (2 rows)
1166 select * from boolpart where a is not true and a is not false;
1167 a
1168 ---
1170 (1 row)
1172 select * from boolpart where a is unknown;
1173 a
1174 ---
1176 (1 row)
1178 select * from boolpart where a is not unknown;
1179 a
1180 ---
1181 f
1182 t
1183 (2 rows)
1185 -- try some other permutations with a NULL partition instead of a DEFAULT
1186 delete from boolpart where a is null;
1187 create table boolpart_null partition of boolpart for values in (null);
1188 insert into boolpart values(null);
1189 explain (costs off) select * from boolpart where a is not true;
1190 QUERY PLAN
1191 --------------------------------------------
1192 Append
1193 -> Seq Scan on boolpart_f boolpart_1
1194 Filter: (a IS NOT TRUE)
1195 -> Seq Scan on boolpart_null boolpart_2
1196 Filter: (a IS NOT TRUE)
1197 (5 rows)
1199 explain (costs off) select * from boolpart where a is not true and a is not false;
1200 QUERY PLAN
1201 --------------------------------------------------
1202 Seq Scan on boolpart_null boolpart
1203 Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1204 (2 rows)
1206 explain (costs off) select * from boolpart where a is not false;
1207 QUERY PLAN
1208 --------------------------------------------
1209 Append
1210 -> Seq Scan on boolpart_t boolpart_1
1211 Filter: (a IS NOT FALSE)
1212 -> Seq Scan on boolpart_null boolpart_2
1213 Filter: (a IS NOT FALSE)
1214 (5 rows)
1216 explain (costs off) select * from boolpart where a is not unknown;
1217 QUERY PLAN
1218 -----------------------------------------------
1219 Append
1220 -> Seq Scan on boolpart_f boolpart_1
1221 Filter: (a IS NOT UNKNOWN)
1222 -> Seq Scan on boolpart_t boolpart_2
1223 Filter: (a IS NOT UNKNOWN)
1224 -> Seq Scan on boolpart_default boolpart_3
1225 Filter: (a IS NOT UNKNOWN)
1226 (7 rows)
1228 select * from boolpart where a is not true;
1229 a
1230 ---
1231 f
1233 (2 rows)
1235 select * from boolpart where a is not true and a is not false;
1236 a
1237 ---
1239 (1 row)
1241 select * from boolpart where a is not false;
1242 a
1243 ---
1244 t
1246 (2 rows)
1248 select * from boolpart where a is not unknown;
1249 a
1250 ---
1251 f
1252 t
1253 (2 rows)
1255 -- check that all partitions are pruned when faced with conflicting clauses
1256 explain (costs off) select * from boolpart where a is not unknown and a is unknown;
1257 QUERY PLAN
1258 --------------------------
1259 Result
1260 One-Time Filter: false
1261 (2 rows)
1263 explain (costs off) select * from boolpart where a is false and a is unknown;
1264 QUERY PLAN
1265 --------------------------
1266 Result
1267 One-Time Filter: false
1268 (2 rows)
1270 explain (costs off) select * from boolpart where a is true and a is unknown;
1271 QUERY PLAN
1272 --------------------------
1273 Result
1274 One-Time Filter: false
1275 (2 rows)
1277 -- inverse boolean partitioning - a seemingly unlikely design, but we've got
1278 -- code for it, so we'd better test it.
1279 create table iboolpart (a bool) partition by list ((not a));
1280 create table iboolpart_default partition of iboolpart default;
1281 create table iboolpart_f partition of iboolpart for values in ('true');
1282 create table iboolpart_t partition of iboolpart for values in ('false');
1283 insert into iboolpart values (true), (false), (null);
1284 explain (costs off) select * from iboolpart where a in (true, false);
1285 QUERY PLAN
1286 -------------------------------------------------
1287 Append
1288 -> Seq Scan on iboolpart_t iboolpart_1
1289 Filter: (a = ANY ('{t,f}'::boolean[]))
1290 -> Seq Scan on iboolpart_f iboolpart_2
1291 Filter: (a = ANY ('{t,f}'::boolean[]))
1292 -> Seq Scan on iboolpart_default iboolpart_3
1293 Filter: (a = ANY ('{t,f}'::boolean[]))
1294 (7 rows)
1296 explain (costs off) select * from iboolpart where a = false;
1297 QUERY PLAN
1298 -----------------------------------
1299 Seq Scan on iboolpart_f iboolpart
1300 Filter: (NOT a)
1301 (2 rows)
1303 explain (costs off) select * from iboolpart where not a = false;
1304 QUERY PLAN
1305 -----------------------------------
1306 Seq Scan on iboolpart_t iboolpart
1307 Filter: a
1308 (2 rows)
1310 explain (costs off) select * from iboolpart where a is true or a is not true;
1311 QUERY PLAN
1312 --------------------------------------------------
1313 Append
1314 -> Seq Scan on iboolpart_t iboolpart_1
1315 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1316 -> Seq Scan on iboolpart_f iboolpart_2
1317 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1318 -> Seq Scan on iboolpart_default iboolpart_3
1319 Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1320 (7 rows)
1322 explain (costs off) select * from iboolpart where a is not true;
1323 QUERY PLAN
1324 -------------------------------------------------
1325 Append
1326 -> Seq Scan on iboolpart_t iboolpart_1
1327 Filter: (a IS NOT TRUE)
1328 -> Seq Scan on iboolpart_f iboolpart_2
1329 Filter: (a IS NOT TRUE)
1330 -> Seq Scan on iboolpart_default iboolpart_3
1331 Filter: (a IS NOT TRUE)
1332 (7 rows)
1334 explain (costs off) select * from iboolpart where a is not true and a is not false;
1335 QUERY PLAN
1336 --------------------------------------------------------
1337 Append
1338 -> Seq Scan on iboolpart_t iboolpart_1
1339 Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1340 -> Seq Scan on iboolpart_f iboolpart_2
1341 Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1342 -> Seq Scan on iboolpart_default iboolpart_3
1343 Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1344 (7 rows)
1346 explain (costs off) select * from iboolpart where a is unknown;
1347 QUERY PLAN
1348 -------------------------------------------------
1349 Append
1350 -> Seq Scan on iboolpart_t iboolpart_1
1351 Filter: (a IS UNKNOWN)
1352 -> Seq Scan on iboolpart_f iboolpart_2
1353 Filter: (a IS UNKNOWN)
1354 -> Seq Scan on iboolpart_default iboolpart_3
1355 Filter: (a IS UNKNOWN)
1356 (7 rows)
1358 explain (costs off) select * from iboolpart where a is not unknown;
1359 QUERY PLAN
1360 -------------------------------------------------
1361 Append
1362 -> Seq Scan on iboolpart_t iboolpart_1
1363 Filter: (a IS NOT UNKNOWN)
1364 -> Seq Scan on iboolpart_f iboolpart_2
1365 Filter: (a IS NOT UNKNOWN)
1366 -> Seq Scan on iboolpart_default iboolpart_3
1367 Filter: (a IS NOT UNKNOWN)
1368 (7 rows)
1370 select * from iboolpart where a in (true, false);
1371 a
1372 ---
1373 t
1374 f
1375 (2 rows)
1377 select * from iboolpart where a = false;
1378 a
1379 ---
1380 f
1381 (1 row)
1383 select * from iboolpart where not a = false;
1384 a
1385 ---
1386 t
1387 (1 row)
1389 select * from iboolpart where a is true or a is not true;
1390 a
1391 ---
1392 t
1393 f
1395 (3 rows)
1397 select * from iboolpart where a is not true;
1398 a
1399 ---
1400 f
1402 (2 rows)
1404 select * from iboolpart where a is not true and a is not false;
1405 a
1406 ---
1408 (1 row)
1410 select * from iboolpart where a is unknown;
1411 a
1412 ---
1414 (1 row)
1416 select * from iboolpart where a is not unknown;
1417 a
1418 ---
1419 t
1420 f
1421 (2 rows)
1423 -- Try some other permutations with a NULL partition instead of a DEFAULT
1424 delete from iboolpart where a is null;
1425 create table iboolpart_null partition of iboolpart for values in (null);
1426 insert into iboolpart values(null);
1427 -- Pruning shouldn't take place for these. Just check the result is correct
1428 select * from iboolpart where a is not true;
1429 a
1430 ---
1431 f
1433 (2 rows)
1435 select * from iboolpart where a is not true and a is not false;
1436 a
1437 ---
1439 (1 row)
1441 select * from iboolpart where a is not false;
1442 a
1443 ---
1444 t
1446 (2 rows)
1448 create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
1449 create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
1450 create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
1451 create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
1452 create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
1453 create table boolrangep_null partition of boolrangep default;
1454 -- try a more complex case that's been known to trip up pruning in the past
1455 explain (costs off) select * from boolrangep where not a and not b and c = 25;
1456 QUERY PLAN
1457 ----------------------------------------------
1458 Seq Scan on boolrangep_ff1 boolrangep
1459 Filter: ((NOT a) AND (NOT b) AND (c = 25))
1460 (2 rows)
1462 -- ensure we prune boolrangep_tf
1463 explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
1464 QUERY PLAN
1465 ------------------------------------------------------------
1466 Append
1467 -> Seq Scan on boolrangep_ff1 boolrangep_1
1468 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1469 -> Seq Scan on boolrangep_ff2 boolrangep_2
1470 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1471 -> Seq Scan on boolrangep_ft boolrangep_3
1472 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1473 -> Seq Scan on boolrangep_null boolrangep_4
1474 Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1475 (9 rows)
1477 -- ensure we prune everything apart from boolrangep_tf and boolrangep_null
1478 explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
1479 QUERY PLAN
1480 -------------------------------------------------------------
1481 Append
1482 -> Seq Scan on boolrangep_tf boolrangep_1
1483 Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
1484 -> Seq Scan on boolrangep_null boolrangep_2
1485 Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
1486 (5 rows)
1488 -- test scalar-to-array operators
1489 create table coercepart (a varchar) partition by list (a);
1490 create table coercepart_ab partition of coercepart for values in ('ab');
1491 create table coercepart_bc partition of coercepart for values in ('bc');
1492 create table coercepart_cd partition of coercepart for values in ('cd');
1493 explain (costs off) select * from coercepart where a in ('ab', to_char(125, '999'));
1494 QUERY PLAN
1495 ------------------------------------------------------------------------------------------------------------------------------
1496 Append
1497 -> Seq Scan on coercepart_ab coercepart_1
1498 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[]))
1499 -> Seq Scan on coercepart_bc coercepart_2
1500 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[]))
1501 -> Seq Scan on coercepart_cd coercepart_3
1502 Filter: ((a)::text = ANY ((ARRAY['ab'::character varying, (to_char(125, '999'::text))::character varying])::text[]))
1503 (7 rows)
1505 explain (costs off) select * from coercepart where a ~ any ('{ab}');
1506 QUERY PLAN
1507 ----------------------------------------------------
1508 Append
1509 -> Seq Scan on coercepart_ab coercepart_1
1510 Filter: ((a)::text ~ ANY ('{ab}'::text[]))
1511 -> Seq Scan on coercepart_bc coercepart_2
1512 Filter: ((a)::text ~ ANY ('{ab}'::text[]))
1513 -> Seq Scan on coercepart_cd coercepart_3
1514 Filter: ((a)::text ~ ANY ('{ab}'::text[]))
1515 (7 rows)
1517 explain (costs off) select * from coercepart where a !~ all ('{ab}');
1518 QUERY PLAN
1519 -----------------------------------------------------
1520 Append
1521 -> Seq Scan on coercepart_ab coercepart_1
1522 Filter: ((a)::text !~ ALL ('{ab}'::text[]))
1523 -> Seq Scan on coercepart_bc coercepart_2
1524 Filter: ((a)::text !~ ALL ('{ab}'::text[]))
1525 -> Seq Scan on coercepart_cd coercepart_3
1526 Filter: ((a)::text !~ ALL ('{ab}'::text[]))
1527 (7 rows)
1529 explain (costs off) select * from coercepart where a ~ any ('{ab,bc}');
1530 QUERY PLAN
1531 -------------------------------------------------------
1532 Append
1533 -> Seq Scan on coercepart_ab coercepart_1
1534 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
1535 -> Seq Scan on coercepart_bc coercepart_2
1536 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
1537 -> Seq Scan on coercepart_cd coercepart_3
1538 Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
1539 (7 rows)
1541 explain (costs off) select * from coercepart where a !~ all ('{ab,bc}');
1542 QUERY PLAN
1543 --------------------------------------------------------
1544 Append
1545 -> Seq Scan on coercepart_ab coercepart_1
1546 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
1547 -> Seq Scan on coercepart_bc coercepart_2
1548 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
1549 -> Seq Scan on coercepart_cd coercepart_3
1550 Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
1551 (7 rows)
1553 explain (costs off) select * from coercepart where a = any ('{ab,bc}');
1554 QUERY PLAN
1555 -------------------------------------------------------
1556 Append
1557 -> Seq Scan on coercepart_ab coercepart_1
1558 Filter: ((a)::text = ANY ('{ab,bc}'::text[]))
1559 -> Seq Scan on coercepart_bc coercepart_2
1560 Filter: ((a)::text = ANY ('{ab,bc}'::text[]))
1561 (5 rows)
1563 explain (costs off) select * from coercepart where a = any ('{ab,null}');
1564 QUERY PLAN
1565 ---------------------------------------------------
1566 Seq Scan on coercepart_ab coercepart
1567 Filter: ((a)::text = ANY ('{ab,NULL}'::text[]))
1568 (2 rows)
1570 explain (costs off) select * from coercepart where a = any (null::text[]);
1571 QUERY PLAN
1572 --------------------------
1573 Result
1574 One-Time Filter: false
1575 (2 rows)
1577 explain (costs off) select * from coercepart where a = all ('{ab}');
1578 QUERY PLAN
1579 ----------------------------------------------
1580 Seq Scan on coercepart_ab coercepart
1581 Filter: ((a)::text = ALL ('{ab}'::text[]))
1582 (2 rows)
1584 explain (costs off) select * from coercepart where a = all ('{ab,bc}');
1585 QUERY PLAN
1586 --------------------------
1587 Result
1588 One-Time Filter: false
1589 (2 rows)
1591 explain (costs off) select * from coercepart where a = all ('{ab,null}');
1592 QUERY PLAN
1593 --------------------------
1594 Result
1595 One-Time Filter: false
1596 (2 rows)
1598 explain (costs off) select * from coercepart where a = all (null::text[]);
1599 QUERY PLAN
1600 --------------------------
1601 Result
1602 One-Time Filter: false
1603 (2 rows)
1605 drop table coercepart;
1606 CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a);
1607 CREATE TABLE part_p1 PARTITION OF part FOR VALUES IN (-2,-1,0,1,2);
1608 CREATE TABLE part_p2 PARTITION OF part DEFAULT PARTITION BY RANGE(a);
1609 CREATE TABLE part_p2_p1 PARTITION OF part_p2 DEFAULT;
1610 CREATE TABLE part_rev (b INT, c INT, a INT);
1611 ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- fail
1612 ERROR: table "part_rev" contains column "c" not found in parent "part"
1613 DETAIL: The new partition may contain only the columns present in parent.
1614 ALTER TABLE part_rev DROP COLUMN c;
1615 ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- now it's ok
1616 INSERT INTO part VALUES (-1,-1), (1,1), (2,NULL), (NULL,-2),(NULL,NULL);
1617 EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS NULL ORDER BY 1, 2, 3;
1618 QUERY PLAN
1619 ---------------------------------------------------------
1620 Sort
1621 Sort Key: ((part.tableoid)::regclass), part.a, part.b
1622 -> Seq Scan on part_p2_p1 part
1623 Filter: (a IS NULL)
1624 (4 rows)
1626 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x;
1627 QUERY PLAN
1628 -----------------------------------------------
1629 Sort
1630 Output: p.x, p.b
1631 Sort Key: p.x
1632 -> Append
1633 -> Seq Scan on public.part_p1 p_1
1634 Output: p_1.x, p_1.b
1635 -> Seq Scan on public.part_rev p_2
1636 Output: p_2.x, p_2.b
1637 -> Seq Scan on public.part_p2_p1 p_3
1638 Output: p_3.x, p_3.b
1639 (10 rows)
1641 --
1642 -- some more cases
1643 --
1644 --
1645 -- pruning for partitioned table appearing inside a sub-query
1646 --
1647 -- pruning won't work for mc3p, because some keys are Params
1648 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;
1649 QUERY PLAN
1650 -----------------------------------------------------------------------
1651 Nested Loop
1652 -> Append
1653 -> Seq Scan on mc2p1 t1_1
1654 Filter: (a = 1)
1655 -> Seq Scan on mc2p2 t1_2
1656 Filter: (a = 1)
1657 -> Seq Scan on mc2p_default t1_3
1658 Filter: (a = 1)
1659 -> Aggregate
1660 -> Append
1661 -> Seq Scan on mc3p0 t2_1
1662 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1663 -> Seq Scan on mc3p1 t2_2
1664 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1665 -> Seq Scan on mc3p2 t2_3
1666 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1667 -> Seq Scan on mc3p3 t2_4
1668 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1669 -> Seq Scan on mc3p4 t2_5
1670 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1671 -> Seq Scan on mc3p5 t2_6
1672 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1673 -> Seq Scan on mc3p6 t2_7
1674 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1675 -> Seq Scan on mc3p7 t2_8
1676 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1677 -> Seq Scan on mc3p_default t2_9
1678 Filter: ((a = t1.b) AND (c = 1) AND (abs(b) = 1))
1679 (28 rows)
1681 -- pruning should work fine, because values for a prefix of keys (a, b) are
1682 -- available
1683 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;
1684 QUERY PLAN
1685 -----------------------------------------------------------------------
1686 Nested Loop
1687 -> Append
1688 -> Seq Scan on mc2p1 t1_1
1689 Filter: (a = 1)
1690 -> Seq Scan on mc2p2 t1_2
1691 Filter: (a = 1)
1692 -> Seq Scan on mc2p_default t1_3
1693 Filter: (a = 1)
1694 -> Aggregate
1695 -> Append
1696 -> Seq Scan on mc3p0 t2_1
1697 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
1698 -> Seq Scan on mc3p1 t2_2
1699 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
1700 -> Seq Scan on mc3p_default t2_3
1701 Filter: ((c = t1.b) AND (a = 1) AND (abs(b) = 1))
1702 (16 rows)
1704 -- also here, because values for all keys are provided
1705 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;
1706 QUERY PLAN
1707 --------------------------------------------------------------
1708 Nested Loop
1709 -> Aggregate
1710 -> Seq Scan on mc3p1 t2
1711 Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1))
1712 -> Append
1713 -> Seq Scan on mc2p1 t1_1
1714 Filter: (a = 1)
1715 -> Seq Scan on mc2p2 t1_2
1716 Filter: (a = 1)
1717 -> Seq Scan on mc2p_default t1_3
1718 Filter: (a = 1)
1719 (11 rows)
1721 --
1722 -- pruning with clauses containing <> operator
1723 --
1724 -- doesn't prune range partitions
1725 create table rp (a int) partition by range (a);
1726 create table rp0 partition of rp for values from (minvalue) to (1);
1727 create table rp1 partition of rp for values from (1) to (2);
1728 create table rp2 partition of rp for values from (2) to (maxvalue);
1729 explain (costs off) select * from rp where a <> 1;
1730 QUERY PLAN
1731 ----------------------------
1732 Append
1733 -> Seq Scan on rp0 rp_1
1734 Filter: (a <> 1)
1735 -> Seq Scan on rp1 rp_2
1736 Filter: (a <> 1)
1737 -> Seq Scan on rp2 rp_3
1738 Filter: (a <> 1)
1739 (7 rows)
1741 explain (costs off) select * from rp where a <> 1 and a <> 2;
1742 QUERY PLAN
1743 -----------------------------------------
1744 Append
1745 -> Seq Scan on rp0 rp_1
1746 Filter: ((a <> 1) AND (a <> 2))
1747 -> Seq Scan on rp1 rp_2
1748 Filter: ((a <> 1) AND (a <> 2))
1749 -> Seq Scan on rp2 rp_3
1750 Filter: ((a <> 1) AND (a <> 2))
1751 (7 rows)
1753 -- null partition should be eliminated due to strict <> clause.
1754 explain (costs off) select * from lp where a <> 'a';
1755 QUERY PLAN
1756 ------------------------------------
1757 Append
1758 -> Seq Scan on lp_ad lp_1
1759 Filter: (a <> 'a'::bpchar)
1760 -> Seq Scan on lp_bc lp_2
1761 Filter: (a <> 'a'::bpchar)
1762 -> Seq Scan on lp_ef lp_3
1763 Filter: (a <> 'a'::bpchar)
1764 -> Seq Scan on lp_g lp_4
1765 Filter: (a <> 'a'::bpchar)
1766 -> Seq Scan on lp_default lp_5
1767 Filter: (a <> 'a'::bpchar)
1768 (11 rows)
1770 -- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL.
1771 explain (costs off) select * from lp where a <> 'a' and a is null;
1772 QUERY PLAN
1773 --------------------------
1774 Result
1775 One-Time Filter: false
1776 (2 rows)
1778 explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
1779 QUERY PLAN
1780 ------------------------------------------------------------------------------
1781 Append
1782 -> Seq Scan on lp_bc lp_1
1783 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1784 -> Seq Scan on lp_ef lp_2
1785 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1786 -> Seq Scan on lp_g lp_3
1787 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1788 -> Seq Scan on lp_null lp_4
1789 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1790 -> Seq Scan on lp_default lp_5
1791 Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
1792 (11 rows)
1794 -- check that it also works for a partitioned table that's not root,
1795 -- which in this case are partitions of rlp that are themselves
1796 -- list-partitioned on b
1797 explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null;
1798 QUERY PLAN
1799 ------------------------------------------------------------------------------------------------------------------------------------------
1800 Append
1801 -> Seq Scan on rlp3efgh rlp_1
1802 Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
1803 -> Seq Scan on rlp3_default rlp_2
1804 Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
1805 (5 rows)
1807 --
1808 -- different collations for different keys with same expression
1809 --
1810 create table coll_pruning_multi (a text) partition by range (substr(a, 1) collate "POSIX", substr(a, 1) collate "C");
1811 create table coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a') to ('a', 'e');
1812 create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e') to ('a', 'z');
1813 create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a') to ('b', 'e');
1814 -- no pruning, because no value for the leading key
1815 explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C";
1816 QUERY PLAN
1817 ------------------------------------------------------------
1818 Append
1819 -> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1
1820 Filter: (substr(a, 1) = 'e'::text COLLATE "C")
1821 -> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2
1822 Filter: (substr(a, 1) = 'e'::text COLLATE "C")
1823 -> Seq Scan on coll_pruning_multi3 coll_pruning_multi_3
1824 Filter: (substr(a, 1) = 'e'::text COLLATE "C")
1825 (7 rows)
1827 -- pruning, with a value provided for the leading key
1828 explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' collate "POSIX";
1829 QUERY PLAN
1830 ------------------------------------------------------------
1831 Append
1832 -> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1
1833 Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
1834 -> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2
1835 Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
1836 (5 rows)
1838 -- pruning, with values provided for both keys
1839 explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX";
1840 QUERY PLAN
1841 ---------------------------------------------------------------------------------------------------
1842 Seq Scan on coll_pruning_multi2 coll_pruning_multi
1843 Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX"))
1844 (2 rows)
1846 --
1847 -- LIKE operators don't prune
1848 --
1849 create table like_op_noprune (a text) partition by list (a);
1850 create table like_op_noprune1 partition of like_op_noprune for values in ('ABC');
1851 create table like_op_noprune2 partition of like_op_noprune for values in ('BCD');
1852 explain (costs off) select * from like_op_noprune where a like '%BC';
1853 QUERY PLAN
1854 ------------------------------------------------------
1855 Append
1856 -> Seq Scan on like_op_noprune1 like_op_noprune_1
1857 Filter: (a ~~ '%BC'::text)
1858 -> Seq Scan on like_op_noprune2 like_op_noprune_2
1859 Filter: (a ~~ '%BC'::text)
1860 (5 rows)
1862 --
1863 -- tests wherein clause value requires a cross-type comparison function
1864 --
1865 create table lparted_by_int2 (a smallint) partition by list (a);
1866 create table lparted_by_int2_1 partition of lparted_by_int2 for values in (1);
1867 create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384);
1868 explain (costs off) select * from lparted_by_int2 where a = 100_000_000_000_000;
1869 QUERY PLAN
1870 --------------------------
1871 Result
1872 One-Time Filter: false
1873 (2 rows)
1875 create table rparted_by_int2 (a smallint) partition by range (a);
1876 create table rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10);
1877 create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384);
1878 -- all partitions pruned
1879 explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000;
1880 QUERY PLAN
1881 --------------------------
1882 Result
1883 One-Time Filter: false
1884 (2 rows)
1886 create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue);
1887 -- all partitions but rparted_by_int2_maxvalue pruned
1888 explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000;
1889 QUERY PLAN
1890 ------------------------------------------------------
1891 Seq Scan on rparted_by_int2_maxvalue rparted_by_int2
1892 Filter: (a > '100000000000000'::bigint)
1893 (2 rows)
1895 drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, iboolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
1896 --
1897 -- Test Partition pruning for HASH partitioning
1898 --
1899 -- Use hand-rolled hash functions and operator classes to get predictable
1900 -- result on different machines. See the definitions of
1901 -- part_test_int4_ops and part_test_text_ops in test_setup.sql.
1902 --
1903 create table hp (a int, b text, c int)
1904 partition by hash (a part_test_int4_ops, b part_test_text_ops);
1905 create table hp0 partition of hp for values with (modulus 4, remainder 0);
1906 create table hp3 partition of hp for values with (modulus 4, remainder 3);
1907 create table hp1 partition of hp for values with (modulus 4, remainder 1);
1908 create table hp2 partition of hp for values with (modulus 4, remainder 2);
1909 insert into hp values (null, null, 0);
1910 insert into hp values (1, null, 1);
1911 insert into hp values (1, 'xxx', 2);
1912 insert into hp values (null, 'xxx', 3);
1913 insert into hp values (2, 'xxx', 4);
1914 insert into hp values (1, 'abcde', 5);
1915 select tableoid::regclass, * from hp order by c;
1916 tableoid | a | b | c
1917 ----------+---+-------+---
1918 hp0 | | | 0
1919 hp1 | 1 | | 1
1920 hp0 | 1 | xxx | 2
1921 hp2 | | xxx | 3
1922 hp3 | 2 | xxx | 4
1923 hp2 | 1 | abcde | 5
1924 (6 rows)
1926 -- partial keys won't prune, nor would non-equality conditions
1927 explain (costs off) select * from hp where a = 1;
1928 QUERY PLAN
1929 ----------------------------
1930 Append
1931 -> Seq Scan on hp0 hp_1
1932 Filter: (a = 1)
1933 -> Seq Scan on hp1 hp_2
1934 Filter: (a = 1)
1935 -> Seq Scan on hp2 hp_3
1936 Filter: (a = 1)
1937 -> Seq Scan on hp3 hp_4
1938 Filter: (a = 1)
1939 (9 rows)
1941 explain (costs off) select * from hp where b = 'xxx';
1942 QUERY PLAN
1943 -----------------------------------
1944 Append
1945 -> Seq Scan on hp0 hp_1
1946 Filter: (b = 'xxx'::text)
1947 -> Seq Scan on hp1 hp_2
1948 Filter: (b = 'xxx'::text)
1949 -> Seq Scan on hp2 hp_3
1950 Filter: (b = 'xxx'::text)
1951 -> Seq Scan on hp3 hp_4
1952 Filter: (b = 'xxx'::text)
1953 (9 rows)
1955 explain (costs off) select * from hp where a is null;
1956 QUERY PLAN
1957 -----------------------------
1958 Append
1959 -> Seq Scan on hp0 hp_1
1960 Filter: (a IS NULL)
1961 -> Seq Scan on hp1 hp_2
1962 Filter: (a IS NULL)
1963 -> Seq Scan on hp2 hp_3
1964 Filter: (a IS NULL)
1965 -> Seq Scan on hp3 hp_4
1966 Filter: (a IS NULL)
1967 (9 rows)
1969 explain (costs off) select * from hp where b is null;
1970 QUERY PLAN
1971 -----------------------------
1972 Append
1973 -> Seq Scan on hp0 hp_1
1974 Filter: (b IS NULL)
1975 -> Seq Scan on hp1 hp_2
1976 Filter: (b IS NULL)
1977 -> Seq Scan on hp2 hp_3
1978 Filter: (b IS NULL)
1979 -> Seq Scan on hp3 hp_4
1980 Filter: (b IS NULL)
1981 (9 rows)
1983 explain (costs off) select * from hp where a < 1 and b = 'xxx';
1984 QUERY PLAN
1985 -------------------------------------------------
1986 Append
1987 -> Seq Scan on hp0 hp_1
1988 Filter: ((a < 1) AND (b = 'xxx'::text))
1989 -> Seq Scan on hp1 hp_2
1990 Filter: ((a < 1) AND (b = 'xxx'::text))
1991 -> Seq Scan on hp2 hp_3
1992 Filter: ((a < 1) AND (b = 'xxx'::text))
1993 -> Seq Scan on hp3 hp_4
1994 Filter: ((a < 1) AND (b = 'xxx'::text))
1995 (9 rows)
1997 explain (costs off) select * from hp where a <> 1 and b = 'yyy';
1998 QUERY PLAN
1999 --------------------------------------------------
2000 Append
2001 -> Seq Scan on hp0 hp_1
2002 Filter: ((a <> 1) AND (b = 'yyy'::text))
2003 -> Seq Scan on hp1 hp_2
2004 Filter: ((a <> 1) AND (b = 'yyy'::text))
2005 -> Seq Scan on hp2 hp_3
2006 Filter: ((a <> 1) AND (b = 'yyy'::text))
2007 -> Seq Scan on hp3 hp_4
2008 Filter: ((a <> 1) AND (b = 'yyy'::text))
2009 (9 rows)
2011 explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
2012 QUERY PLAN
2013 ---------------------------------------------------
2014 Append
2015 -> Seq Scan on hp0 hp_1
2016 Filter: ((a <> 1) AND (b <> 'xxx'::text))
2017 -> Seq Scan on hp1 hp_2
2018 Filter: ((a <> 1) AND (b <> 'xxx'::text))
2019 -> Seq Scan on hp2 hp_3
2020 Filter: ((a <> 1) AND (b <> 'xxx'::text))
2021 -> Seq Scan on hp3 hp_4
2022 Filter: ((a <> 1) AND (b <> 'xxx'::text))
2023 (9 rows)
2025 -- pruning should work if either a value or a IS NULL clause is provided for
2026 -- each of the keys
2027 explain (costs off) select * from hp where a is null and b is null;
2028 QUERY PLAN
2029 -----------------------------------------
2030 Seq Scan on hp0 hp
2031 Filter: ((a IS NULL) AND (b IS NULL))
2032 (2 rows)
2034 explain (costs off) select * from hp where a = 1 and b is null;
2035 QUERY PLAN
2036 -------------------------------------
2037 Seq Scan on hp1 hp
2038 Filter: ((b IS NULL) AND (a = 1))
2039 (2 rows)
2041 explain (costs off) select * from hp where a = 1 and b = 'xxx';
2042 QUERY PLAN
2043 -------------------------------------------
2044 Seq Scan on hp0 hp
2045 Filter: ((a = 1) AND (b = 'xxx'::text))
2046 (2 rows)
2048 explain (costs off) select * from hp where a is null and b = 'xxx';
2049 QUERY PLAN
2050 -----------------------------------------------
2051 Seq Scan on hp2 hp
2052 Filter: ((a IS NULL) AND (b = 'xxx'::text))
2053 (2 rows)
2055 explain (costs off) select * from hp where a = 2 and b = 'xxx';
2056 QUERY PLAN
2057 -------------------------------------------
2058 Seq Scan on hp3 hp
2059 Filter: ((a = 2) AND (b = 'xxx'::text))
2060 (2 rows)
2062 explain (costs off) select * from hp where a = 1 and b = 'abcde';
2063 QUERY PLAN
2064 ---------------------------------------------
2065 Seq Scan on hp2 hp
2066 Filter: ((a = 1) AND (b = 'abcde'::text))
2067 (2 rows)
2069 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);
2070 QUERY PLAN
2071 -------------------------------------------------------------------------------------------------------------------------
2072 Append
2073 -> Seq Scan on hp0 hp_1
2074 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
2075 -> Seq Scan on hp2 hp_2
2076 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
2077 -> Seq Scan on hp3 hp_3
2078 Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
2079 (7 rows)
2081 -- test pruning when not all the partitions exist
2082 drop table hp1;
2083 drop table hp3;
2084 explain (costs off) select * from hp where a = 1 and b = 'abcde';
2085 QUERY PLAN
2086 ---------------------------------------------
2087 Seq Scan on hp2 hp
2088 Filter: ((a = 1) AND (b = 'abcde'::text))
2089 (2 rows)
2091 explain (costs off) select * from hp where a = 1 and b = 'abcde' and
2092 (c = 2 or c = 3);
2093 QUERY PLAN
2094 ----------------------------------------------------------------------
2095 Seq Scan on hp2 hp
2096 Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
2097 (2 rows)
2099 drop table hp2;
2100 explain (costs off) select * from hp where a = 1 and b = 'abcde' and
2101 (c = 2 or c = 3);
2102 QUERY PLAN
2103 --------------------------
2104 Result
2105 One-Time Filter: false
2106 (2 rows)
2108 --
2109 -- Test runtime partition pruning
2110 --
2111 create table ab (a int not null, b int not null) partition by list (a);
2112 create table ab_a2 partition of ab for values in(2) partition by list (b);
2113 create table ab_a2_b1 partition of ab_a2 for values in (1);
2114 create table ab_a2_b2 partition of ab_a2 for values in (2);
2115 create table ab_a2_b3 partition of ab_a2 for values in (3);
2116 create table ab_a1 partition of ab for values in(1) partition by list (b);
2117 create table ab_a1_b1 partition of ab_a1 for values in (1);
2118 create table ab_a1_b2 partition of ab_a1 for values in (2);
2119 create table ab_a1_b3 partition of ab_a1 for values in (3);
2120 create table ab_a3 partition of ab for values in(3) partition by list (b);
2121 create table ab_a3_b1 partition of ab_a3 for values in (1);
2122 create table ab_a3_b2 partition of ab_a3 for values in (2);
2123 create table ab_a3_b3 partition of ab_a3 for values in (3);
2124 -- Disallow index only scans as concurrent transactions may stop visibility
2125 -- bits being set causing "Heap Fetches" to be unstable in the EXPLAIN ANALYZE
2126 -- output.
2127 set enable_indexonlyscan = off;
2128 prepare ab_q1 (int, int, int) as
2129 select * from ab where a between $1 and $2 and b <= $3;
2130 explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2, 3);
2131 QUERY PLAN
2132 ---------------------------------------------------------
2133 Append (actual rows=0 loops=1)
2134 Subplans Removed: 6
2135 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
2136 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2137 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
2138 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2139 -> Seq Scan on ab_a2_b3 ab_3 (actual rows=0 loops=1)
2140 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2141 (8 rows)
2143 explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (1, 2, 3);
2144 QUERY PLAN
2145 ---------------------------------------------------------
2146 Append (actual rows=0 loops=1)
2147 Subplans Removed: 3
2148 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
2149 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2150 -> Seq Scan on ab_a1_b2 ab_2 (actual rows=0 loops=1)
2151 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2152 -> Seq Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
2153 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2154 -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
2155 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2156 -> Seq Scan on ab_a2_b2 ab_5 (actual rows=0 loops=1)
2157 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2158 -> Seq Scan on ab_a2_b3 ab_6 (actual rows=0 loops=1)
2159 Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
2160 (14 rows)
2162 deallocate ab_q1;
2163 -- Runtime pruning after optimizer pruning
2164 prepare ab_q1 (int, int) as
2165 select a from ab where a between $1 and $2 and b < 3;
2166 explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 2);
2167 QUERY PLAN
2168 ---------------------------------------------------------
2169 Append (actual rows=0 loops=1)
2170 Subplans Removed: 4
2171 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
2172 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
2173 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
2174 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
2175 (6 rows)
2177 explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q1 (2, 4);
2178 QUERY PLAN
2179 ---------------------------------------------------------
2180 Append (actual rows=0 loops=1)
2181 Subplans Removed: 2
2182 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
2183 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
2184 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
2185 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
2186 -> Seq Scan on ab_a3_b1 ab_3 (actual rows=0 loops=1)
2187 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
2188 -> Seq Scan on ab_a3_b2 ab_4 (actual rows=0 loops=1)
2189 Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
2190 (10 rows)
2192 -- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at
2193 -- different levels of partitioning.
2194 prepare ab_q2 (int, int) as
2195 select a from ab where a between $1 and $2 and b < (select 3);
2196 explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q2 (2, 2);
2197 QUERY PLAN
2198 -----------------------------------------------------------------------
2199 Append (actual rows=0 loops=1)
2200 Subplans Removed: 6
2201 InitPlan 1
2202 -> Result (actual rows=1 loops=1)
2203 -> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
2204 Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1))
2205 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
2206 Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1))
2207 -> Seq Scan on ab_a2_b3 ab_3 (never executed)
2208 Filter: ((a >= $1) AND (a <= $2) AND (b < (InitPlan 1).col1))
2209 (10 rows)
2211 -- As above, but swap the PARAM_EXEC Param to the first partition level
2212 prepare ab_q3 (int, int) as
2213 select a from ab where b between $1 and $2 and a < (select 3);
2214 explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q3 (2, 2);
2215 QUERY PLAN
2216 -----------------------------------------------------------------------
2217 Append (actual rows=0 loops=1)
2218 Subplans Removed: 6
2219 InitPlan 1
2220 -> Result (actual rows=1 loops=1)
2221 -> Seq Scan on ab_a1_b2 ab_1 (actual rows=0 loops=1)
2222 Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1))
2223 -> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
2224 Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1))
2225 -> Seq Scan on ab_a3_b2 ab_3 (never executed)
2226 Filter: ((b >= $1) AND (b <= $2) AND (a < (InitPlan 1).col1))
2227 (10 rows)
2229 --
2230 -- Test runtime pruning with hash partitioned tables
2231 --
2232 -- recreate partitions dropped above
2233 create table hp1 partition of hp for values with (modulus 4, remainder 1);
2234 create table hp2 partition of hp for values with (modulus 4, remainder 2);
2235 create table hp3 partition of hp for values with (modulus 4, remainder 3);
2236 -- Ensure we correctly prune unneeded partitions when there is an IS NULL qual
2237 prepare hp_q1 (text) as
2238 select * from hp where a is null and b = $1;
2239 explain (costs off) execute hp_q1('xxx');
2240 QUERY PLAN
2241 --------------------------------------------
2242 Append
2243 Subplans Removed: 3
2244 -> Seq Scan on hp2 hp_1
2245 Filter: ((a IS NULL) AND (b = $1))
2246 (4 rows)
2248 deallocate hp_q1;
2249 drop table hp;
2250 -- Test a backwards Append scan
2251 create table list_part (a int) partition by list (a);
2252 create table list_part1 partition of list_part for values in (1);
2253 create table list_part2 partition of list_part for values in (2);
2254 create table list_part3 partition of list_part for values in (3);
2255 create table list_part4 partition of list_part for values in (4);
2256 insert into list_part select generate_series(1,4);
2257 begin;
2258 -- Don't select an actual value out of the table as the order of the Append's
2259 -- subnodes may not be stable.
2260 declare cur SCROLL CURSOR for select 1 from list_part where a > (select 1) and a < (select 4);
2261 -- move beyond the final row
2262 move 3 from cur;
2263 -- Ensure we get two rows.
2264 fetch backward all from cur;
2265 ?column?
2266 ----------
2267 1
2268 1
2269 (2 rows)
2271 commit;
2272 begin;
2273 -- Test run-time pruning using stable functions
2274 create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
2275 -- Ensure pruning works using a stable function containing no Vars
2276 explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1);
2277 QUERY PLAN
2278 ------------------------------------------------------------------
2279 Append (actual rows=1 loops=1)
2280 Subplans Removed: 3
2281 -> Seq Scan on list_part1 list_part_1 (actual rows=1 loops=1)
2282 Filter: (a = list_part_fn(1))
2283 (4 rows)
2285 -- Ensure pruning does not take place when the function has a Var parameter
2286 explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a);
2287 QUERY PLAN
2288 ------------------------------------------------------------------
2289 Append (actual rows=4 loops=1)
2290 -> Seq Scan on list_part1 list_part_1 (actual rows=1 loops=1)
2291 Filter: (a = list_part_fn(a))
2292 -> Seq Scan on list_part2 list_part_2 (actual rows=1 loops=1)
2293 Filter: (a = list_part_fn(a))
2294 -> Seq Scan on list_part3 list_part_3 (actual rows=1 loops=1)
2295 Filter: (a = list_part_fn(a))
2296 -> Seq Scan on list_part4 list_part_4 (actual rows=1 loops=1)
2297 Filter: (a = list_part_fn(a))
2298 (9 rows)
2300 -- Ensure pruning does not take place when the expression contains a Var.
2301 explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a;
2302 QUERY PLAN
2303 ------------------------------------------------------------------
2304 Append (actual rows=0 loops=1)
2305 -> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1)
2306 Filter: (a = (list_part_fn(1) + a))
2307 Rows Removed by Filter: 1
2308 -> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1)
2309 Filter: (a = (list_part_fn(1) + a))
2310 Rows Removed by Filter: 1
2311 -> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1)
2312 Filter: (a = (list_part_fn(1) + a))
2313 Rows Removed by Filter: 1
2314 -> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1)
2315 Filter: (a = (list_part_fn(1) + a))
2316 Rows Removed by Filter: 1
2317 (13 rows)
2319 rollback;
2320 drop table list_part;
2321 -- Parallel append
2322 -- Parallel queries won't necessarily get as many workers as the planner
2323 -- asked for. This affects not only the "Workers Launched:" field of EXPLAIN
2324 -- results, but also row counts and loop counts for parallel scans, Gathers,
2325 -- and everything in between. This function filters out the values we can't
2326 -- rely on to be stable.
2327 -- This removes enough info that you might wonder why bother with EXPLAIN
2328 -- ANALYZE at all. The answer is that we need to see '(never executed)'
2329 -- notations because that's the only way to verify runtime pruning.
2330 create function explain_parallel_append(text) returns setof text
2331 language plpgsql as
2332 $$
2333 declare
2334 ln text;
2335 begin
2336 for ln in
2337 execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s',
2338 $1)
2339 loop
2340 ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
2341 ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
2342 ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
2343 return next ln;
2344 end loop;
2345 end;
2346 $$;
2347 prepare ab_q4 (int, int) as
2348 select avg(a) from ab where a between $1 and $2 and b < 4;
2349 -- Encourage use of parallel plans
2350 set parallel_setup_cost = 0;
2351 set parallel_tuple_cost = 0;
2352 set min_parallel_table_scan_size = 0;
2353 set max_parallel_workers_per_gather = 2;
2354 select explain_parallel_append('execute ab_q4 (2, 2)');
2355 explain_parallel_append
2356 ------------------------------------------------------------------------------------
2357 Finalize Aggregate (actual rows=N loops=N)
2358 -> Gather (actual rows=N loops=N)
2359 Workers Planned: 2
2360 Workers Launched: N
2361 -> Partial Aggregate (actual rows=N loops=N)
2362 -> Parallel Append (actual rows=N loops=N)
2363 Subplans Removed: 6
2364 -> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N)
2365 Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
2366 -> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N)
2367 Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
2368 -> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N)
2369 Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
2370 (13 rows)
2372 -- Test run-time pruning with IN lists.
2373 prepare ab_q5 (int, int, int) as
2374 select avg(a) from ab where a in($1,$2,$3) and b < 4;
2375 select explain_parallel_append('execute ab_q5 (1, 1, 1)');
2376 explain_parallel_append
2377 ------------------------------------------------------------------------------------
2378 Finalize Aggregate (actual rows=N loops=N)
2379 -> Gather (actual rows=N loops=N)
2380 Workers Planned: 2
2381 Workers Launched: N
2382 -> Partial Aggregate (actual rows=N loops=N)
2383 -> Parallel Append (actual rows=N loops=N)
2384 Subplans Removed: 6
2385 -> Parallel Seq Scan on ab_a1_b1 ab_1 (actual rows=N loops=N)
2386 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2387 -> Parallel Seq Scan on ab_a1_b2 ab_2 (actual rows=N loops=N)
2388 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2389 -> Parallel Seq Scan on ab_a1_b3 ab_3 (actual rows=N loops=N)
2390 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2391 (13 rows)
2393 select explain_parallel_append('execute ab_q5 (2, 3, 3)');
2394 explain_parallel_append
2395 ------------------------------------------------------------------------------------
2396 Finalize Aggregate (actual rows=N loops=N)
2397 -> Gather (actual rows=N loops=N)
2398 Workers Planned: 2
2399 Workers Launched: N
2400 -> Partial Aggregate (actual rows=N loops=N)
2401 -> Parallel Append (actual rows=N loops=N)
2402 Subplans Removed: 3
2403 -> Parallel Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N)
2404 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2405 -> Parallel Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N)
2406 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2407 -> Parallel Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N)
2408 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2409 -> Parallel Seq Scan on ab_a3_b1 ab_4 (actual rows=N loops=N)
2410 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2411 -> Parallel Seq Scan on ab_a3_b2 ab_5 (actual rows=N loops=N)
2412 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2413 -> Parallel Seq Scan on ab_a3_b3 ab_6 (actual rows=N loops=N)
2414 Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
2415 (19 rows)
2417 -- Try some params whose values do not belong to any partition.
2418 select explain_parallel_append('execute ab_q5 (33, 44, 55)');
2419 explain_parallel_append
2420 -----------------------------------------------------------
2421 Finalize Aggregate (actual rows=N loops=N)
2422 -> Gather (actual rows=N loops=N)
2423 Workers Planned: 2
2424 Workers Launched: N
2425 -> Partial Aggregate (actual rows=N loops=N)
2426 -> Parallel Append (actual rows=N loops=N)
2427 Subplans Removed: 9
2428 (7 rows)
2430 -- Test Parallel Append with PARAM_EXEC Params
2431 select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
2432 explain_parallel_append
2433 ------------------------------------------------------------------------------------------------
2434 Aggregate (actual rows=N loops=N)
2435 InitPlan 1
2436 -> Result (actual rows=N loops=N)
2437 InitPlan 2
2438 -> Result (actual rows=N loops=N)
2439 -> Gather (actual rows=N loops=N)
2440 Workers Planned: 2
2441 Workers Launched: N
2442 -> Parallel Append (actual rows=N loops=N)
2443 -> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
2444 Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1)))
2445 -> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
2446 Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1)))
2447 -> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
2448 Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1)))
2449 (15 rows)
2451 -- Test pruning during parallel nested loop query
2452 create table lprt_a (a int not null);
2453 -- Insert some values we won't find in ab
2454 insert into lprt_a select 0 from generate_series(1,100);
2455 -- and insert some values that we should find.
2456 insert into lprt_a values(1),(1);
2457 analyze lprt_a;
2458 create index ab_a2_b1_a_idx on ab_a2_b1 (a);
2459 create index ab_a2_b2_a_idx on ab_a2_b2 (a);
2460 create index ab_a2_b3_a_idx on ab_a2_b3 (a);
2461 create index ab_a1_b1_a_idx on ab_a1_b1 (a);
2462 create index ab_a1_b2_a_idx on ab_a1_b2 (a);
2463 create index ab_a1_b3_a_idx on ab_a1_b3 (a);
2464 create index ab_a3_b1_a_idx on ab_a3_b1 (a);
2465 create index ab_a3_b2_a_idx on ab_a3_b2 (a);
2466 create index ab_a3_b3_a_idx on ab_a3_b3 (a);
2467 set enable_hashjoin = 0;
2468 set enable_mergejoin = 0;
2469 set enable_memoize = 0;
2470 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)');
2471 explain_parallel_append
2472 --------------------------------------------------------------------------------------------------------
2473 Finalize Aggregate (actual rows=N loops=N)
2474 -> Gather (actual rows=N loops=N)
2475 Workers Planned: 1
2476 Workers Launched: N
2477 -> Partial Aggregate (actual rows=N loops=N)
2478 -> Nested Loop (actual rows=N loops=N)
2479 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2480 Filter: (a = ANY ('{0,0,1}'::integer[]))
2481 -> Append (actual rows=N loops=N)
2482 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2483 Index Cond: (a = a.a)
2484 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2485 Index Cond: (a = a.a)
2486 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2487 Index Cond: (a = a.a)
2488 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2489 Index Cond: (a = a.a)
2490 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2491 Index Cond: (a = a.a)
2492 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2493 Index Cond: (a = a.a)
2494 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2495 Index Cond: (a = a.a)
2496 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2497 Index Cond: (a = a.a)
2498 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2499 Index Cond: (a = a.a)
2500 (27 rows)
2502 -- Ensure the same partitions are pruned when we make the nested loop
2503 -- parameter an Expr rather than a plain Param.
2504 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)');
2505 explain_parallel_append
2506 --------------------------------------------------------------------------------------------------------
2507 Finalize Aggregate (actual rows=N loops=N)
2508 -> Gather (actual rows=N loops=N)
2509 Workers Planned: 1
2510 Workers Launched: N
2511 -> Partial Aggregate (actual rows=N loops=N)
2512 -> Nested Loop (actual rows=N loops=N)
2513 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2514 Filter: (a = ANY ('{0,0,1}'::integer[]))
2515 -> Append (actual rows=N loops=N)
2516 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2517 Index Cond: (a = (a.a + 0))
2518 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2519 Index Cond: (a = (a.a + 0))
2520 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2521 Index Cond: (a = (a.a + 0))
2522 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2523 Index Cond: (a = (a.a + 0))
2524 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2525 Index Cond: (a = (a.a + 0))
2526 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2527 Index Cond: (a = (a.a + 0))
2528 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2529 Index Cond: (a = (a.a + 0))
2530 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2531 Index Cond: (a = (a.a + 0))
2532 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2533 Index Cond: (a = (a.a + 0))
2534 (27 rows)
2536 insert into lprt_a values(3),(3);
2537 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)');
2538 explain_parallel_append
2539 --------------------------------------------------------------------------------------------------------
2540 Finalize Aggregate (actual rows=N loops=N)
2541 -> Gather (actual rows=N loops=N)
2542 Workers Planned: 1
2543 Workers Launched: N
2544 -> Partial Aggregate (actual rows=N loops=N)
2545 -> Nested Loop (actual rows=N loops=N)
2546 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2547 Filter: (a = ANY ('{1,0,3}'::integer[]))
2548 -> Append (actual rows=N loops=N)
2549 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2550 Index Cond: (a = a.a)
2551 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2552 Index Cond: (a = a.a)
2553 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2554 Index Cond: (a = a.a)
2555 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2556 Index Cond: (a = a.a)
2557 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2558 Index Cond: (a = a.a)
2559 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2560 Index Cond: (a = a.a)
2561 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (actual rows=N loops=N)
2562 Index Cond: (a = a.a)
2563 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (actual rows=N loops=N)
2564 Index Cond: (a = a.a)
2565 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N)
2566 Index Cond: (a = a.a)
2567 (27 rows)
2569 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)');
2570 explain_parallel_append
2571 --------------------------------------------------------------------------------------------------------
2572 Finalize Aggregate (actual rows=N loops=N)
2573 -> Gather (actual rows=N loops=N)
2574 Workers Planned: 1
2575 Workers Launched: N
2576 -> Partial Aggregate (actual rows=N loops=N)
2577 -> Nested Loop (actual rows=N loops=N)
2578 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2579 Filter: (a = ANY ('{1,0,0}'::integer[]))
2580 Rows Removed by Filter: N
2581 -> Append (actual rows=N loops=N)
2582 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
2583 Index Cond: (a = a.a)
2584 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
2585 Index Cond: (a = a.a)
2586 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
2587 Index Cond: (a = a.a)
2588 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2589 Index Cond: (a = a.a)
2590 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2591 Index Cond: (a = a.a)
2592 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2593 Index Cond: (a = a.a)
2594 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2595 Index Cond: (a = a.a)
2596 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2597 Index Cond: (a = a.a)
2598 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2599 Index Cond: (a = a.a)
2600 (28 rows)
2602 delete from lprt_a where a = 1;
2603 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)');
2604 explain_parallel_append
2605 -------------------------------------------------------------------------------------------------
2606 Finalize Aggregate (actual rows=N loops=N)
2607 -> Gather (actual rows=N loops=N)
2608 Workers Planned: 1
2609 Workers Launched: N
2610 -> Partial Aggregate (actual rows=N loops=N)
2611 -> Nested Loop (actual rows=N loops=N)
2612 -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N)
2613 Filter: (a = ANY ('{1,0,0}'::integer[]))
2614 Rows Removed by Filter: N
2615 -> Append (actual rows=N loops=N)
2616 -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed)
2617 Index Cond: (a = a.a)
2618 -> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (never executed)
2619 Index Cond: (a = a.a)
2620 -> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (never executed)
2621 Index Cond: (a = a.a)
2622 -> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
2623 Index Cond: (a = a.a)
2624 -> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
2625 Index Cond: (a = a.a)
2626 -> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
2627 Index Cond: (a = a.a)
2628 -> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
2629 Index Cond: (a = a.a)
2630 -> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
2631 Index Cond: (a = a.a)
2632 -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
2633 Index Cond: (a = a.a)
2634 (28 rows)
2636 reset enable_hashjoin;
2637 reset enable_mergejoin;
2638 reset enable_memoize;
2639 reset parallel_setup_cost;
2640 reset parallel_tuple_cost;
2641 reset min_parallel_table_scan_size;
2642 reset max_parallel_workers_per_gather;
2643 -- Test run-time partition pruning with an initplan
2644 explain (analyze, costs off, summary off, timing off, buffers off)
2645 select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
2646 QUERY PLAN
2647 -------------------------------------------------------------------------
2648 Append (actual rows=0 loops=1)
2649 InitPlan 1
2650 -> Aggregate (actual rows=1 loops=1)
2651 -> Seq Scan on lprt_a (actual rows=102 loops=1)
2652 InitPlan 2
2653 -> Aggregate (actual rows=1 loops=1)
2654 -> Seq Scan on lprt_a lprt_a_1 (actual rows=102 loops=1)
2655 -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed)
2656 Recheck Cond: (a = (InitPlan 1).col1)
2657 Filter: (b = (InitPlan 2).col1)
2658 -> Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
2659 Index Cond: (a = (InitPlan 1).col1)
2660 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed)
2661 Recheck Cond: (a = (InitPlan 1).col1)
2662 Filter: (b = (InitPlan 2).col1)
2663 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
2664 Index Cond: (a = (InitPlan 1).col1)
2665 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed)
2666 Recheck Cond: (a = (InitPlan 1).col1)
2667 Filter: (b = (InitPlan 2).col1)
2668 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
2669 Index Cond: (a = (InitPlan 1).col1)
2670 -> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed)
2671 Recheck Cond: (a = (InitPlan 1).col1)
2672 Filter: (b = (InitPlan 2).col1)
2673 -> Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
2674 Index Cond: (a = (InitPlan 1).col1)
2675 -> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed)
2676 Recheck Cond: (a = (InitPlan 1).col1)
2677 Filter: (b = (InitPlan 2).col1)
2678 -> Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
2679 Index Cond: (a = (InitPlan 1).col1)
2680 -> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed)
2681 Recheck Cond: (a = (InitPlan 1).col1)
2682 Filter: (b = (InitPlan 2).col1)
2683 -> Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
2684 Index Cond: (a = (InitPlan 1).col1)
2685 -> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed)
2686 Recheck Cond: (a = (InitPlan 1).col1)
2687 Filter: (b = (InitPlan 2).col1)
2688 -> Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
2689 Index Cond: (a = (InitPlan 1).col1)
2690 -> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0 loops=1)
2691 Recheck Cond: (a = (InitPlan 1).col1)
2692 Filter: (b = (InitPlan 2).col1)
2693 -> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
2694 Index Cond: (a = (InitPlan 1).col1)
2695 -> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed)
2696 Recheck Cond: (a = (InitPlan 1).col1)
2697 Filter: (b = (InitPlan 2).col1)
2698 -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
2699 Index Cond: (a = (InitPlan 1).col1)
2700 (52 rows)
2702 -- Test run-time partition pruning with UNION ALL parents
2703 explain (analyze, costs off, summary off, timing off, buffers off)
2704 select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
2705 QUERY PLAN
2706 -------------------------------------------------------------------------------
2707 Append (actual rows=0 loops=1)
2708 InitPlan 1
2709 -> Result (actual rows=1 loops=1)
2710 -> Append (actual rows=0 loops=1)
2711 -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
2712 Recheck Cond: (a = 1)
2713 Filter: (b = (InitPlan 1).col1)
2714 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2715 Index Cond: (a = 1)
2716 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
2717 Recheck Cond: (a = 1)
2718 Filter: (b = (InitPlan 1).col1)
2719 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
2720 Index Cond: (a = 1)
2721 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
2722 Recheck Cond: (a = 1)
2723 Filter: (b = (InitPlan 1).col1)
2724 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
2725 Index Cond: (a = 1)
2726 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
2727 Filter: (b = (InitPlan 1).col1)
2728 -> Seq Scan on ab_a1_b2 ab_2 (never executed)
2729 Filter: (b = (InitPlan 1).col1)
2730 -> Seq Scan on ab_a1_b3 ab_3 (never executed)
2731 Filter: (b = (InitPlan 1).col1)
2732 -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
2733 Filter: (b = (InitPlan 1).col1)
2734 -> Seq Scan on ab_a2_b2 ab_5 (never executed)
2735 Filter: (b = (InitPlan 1).col1)
2736 -> Seq Scan on ab_a2_b3 ab_6 (never executed)
2737 Filter: (b = (InitPlan 1).col1)
2738 -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1)
2739 Filter: (b = (InitPlan 1).col1)
2740 -> Seq Scan on ab_a3_b2 ab_8 (never executed)
2741 Filter: (b = (InitPlan 1).col1)
2742 -> Seq Scan on ab_a3_b3 ab_9 (never executed)
2743 Filter: (b = (InitPlan 1).col1)
2744 (37 rows)
2746 -- A case containing a UNION ALL with a non-partitioned child.
2747 explain (analyze, costs off, summary off, timing off, buffers off)
2748 select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
2749 QUERY PLAN
2750 -------------------------------------------------------------------------------
2751 Append (actual rows=0 loops=1)
2752 InitPlan 1
2753 -> Result (actual rows=1 loops=1)
2754 -> Append (actual rows=0 loops=1)
2755 -> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
2756 Recheck Cond: (a = 1)
2757 Filter: (b = (InitPlan 1).col1)
2758 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2759 Index Cond: (a = 1)
2760 -> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
2761 Recheck Cond: (a = 1)
2762 Filter: (b = (InitPlan 1).col1)
2763 -> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
2764 Index Cond: (a = 1)
2765 -> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
2766 Recheck Cond: (a = 1)
2767 Filter: (b = (InitPlan 1).col1)
2768 -> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
2769 Index Cond: (a = 1)
2770 -> Result (actual rows=0 loops=1)
2771 One-Time Filter: (5 = (InitPlan 1).col1)
2772 -> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
2773 Filter: (b = (InitPlan 1).col1)
2774 -> Seq Scan on ab_a1_b2 ab_2 (never executed)
2775 Filter: (b = (InitPlan 1).col1)
2776 -> Seq Scan on ab_a1_b3 ab_3 (never executed)
2777 Filter: (b = (InitPlan 1).col1)
2778 -> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
2779 Filter: (b = (InitPlan 1).col1)
2780 -> Seq Scan on ab_a2_b2 ab_5 (never executed)
2781 Filter: (b = (InitPlan 1).col1)
2782 -> Seq Scan on ab_a2_b3 ab_6 (never executed)
2783 Filter: (b = (InitPlan 1).col1)
2784 -> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1)
2785 Filter: (b = (InitPlan 1).col1)
2786 -> Seq Scan on ab_a3_b2 ab_8 (never executed)
2787 Filter: (b = (InitPlan 1).col1)
2788 -> Seq Scan on ab_a3_b3 ab_9 (never executed)
2789 Filter: (b = (InitPlan 1).col1)
2790 (39 rows)
2792 -- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
2793 create table xy_1 (x int, y int);
2794 insert into xy_1 values(100,-10);
2795 set enable_bitmapscan = 0;
2796 set enable_indexscan = 0;
2797 prepare ab_q6 as
2798 select * from (
2799 select tableoid::regclass,a,b from ab
2800 union all
2801 select tableoid::regclass,x,y from xy_1
2802 union all
2803 select tableoid::regclass,a,b from ab
2804 ) ab where a = $1 and b = (select -10);
2805 -- Ensure the xy_1 subplan is not pruned.
2806 explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6(1);
2807 QUERY PLAN
2808 --------------------------------------------------------
2809 Append (actual rows=0 loops=1)
2810 Subplans Removed: 12
2811 InitPlan 1
2812 -> Result (actual rows=1 loops=1)
2813 -> Seq Scan on ab_a1_b1 ab_1 (never executed)
2814 Filter: ((a = $1) AND (b = (InitPlan 1).col1))
2815 -> Seq Scan on ab_a1_b2 ab_2 (never executed)
2816 Filter: ((a = $1) AND (b = (InitPlan 1).col1))
2817 -> Seq Scan on ab_a1_b3 ab_3 (never executed)
2818 Filter: ((a = $1) AND (b = (InitPlan 1).col1))
2819 -> Seq Scan on xy_1 (actual rows=0 loops=1)
2820 Filter: ((x = $1) AND (y = (InitPlan 1).col1))
2821 Rows Removed by Filter: 1
2822 -> Seq Scan on ab_a1_b1 ab_4 (never executed)
2823 Filter: ((a = $1) AND (b = (InitPlan 1).col1))
2824 -> Seq Scan on ab_a1_b2 ab_5 (never executed)
2825 Filter: ((a = $1) AND (b = (InitPlan 1).col1))
2826 -> Seq Scan on ab_a1_b3 ab_6 (never executed)
2827 Filter: ((a = $1) AND (b = (InitPlan 1).col1))
2828 (19 rows)
2830 -- Ensure we see just the xy_1 row.
2831 execute ab_q6(100);
2832 tableoid | a | b
2833 ----------+-----+-----
2834 xy_1 | 100 | -10
2835 (1 row)
2837 reset enable_bitmapscan;
2838 reset enable_indexscan;
2839 deallocate ab_q1;
2840 deallocate ab_q2;
2841 deallocate ab_q3;
2842 deallocate ab_q4;
2843 deallocate ab_q5;
2844 deallocate ab_q6;
2845 -- UPDATE on a partition subtree has been seen to have problems.
2846 insert into ab values (1,2);
2847 select explain_analyze('
2848 update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;');
2849 explain_analyze
2850 -------------------------------------------------------------------------------------------
2851 Update on ab_a1 (actual rows=0 loops=1)
2852 Update on ab_a1_b1 ab_a1_1
2853 Update on ab_a1_b2 ab_a1_2
2854 Update on ab_a1_b3 ab_a1_3
2855 -> Nested Loop (actual rows=1 loops=1)
2856 -> Append (actual rows=1 loops=1)
2857 -> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1)
2858 Recheck Cond: (a = 1)
2859 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2860 Index Cond: (a = 1)
2861 -> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
2862 Recheck Cond: (a = 1)
2863 Heap Blocks: exact=1
2864 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
2865 Index Cond: (a = 1)
2866 -> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
2867 Recheck Cond: (a = 1)
2868 Heap Blocks: exact=1
2869 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
2870 Index Cond: (a = 1)
2871 -> Materialize (actual rows=1 loops=1)
2872 Storage: Memory Maximum Storage: NkB
2873 -> Append (actual rows=1 loops=1)
2874 -> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
2875 Recheck Cond: (a = 1)
2876 -> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
2877 Index Cond: (a = 1)
2878 -> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
2879 Recheck Cond: (a = 1)
2880 Heap Blocks: exact=1
2881 -> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
2882 Index Cond: (a = 1)
2883 -> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
2884 Recheck Cond: (a = 1)
2885 Heap Blocks: exact=1
2886 -> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
2887 Index Cond: (a = 1)
2888 (37 rows)
2890 table ab;
2891 a | b
2892 ---+---
2893 1 | 3
2894 (1 row)
2896 -- Test UPDATE where source relation has run-time pruning enabled
2897 truncate ab;
2898 insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
2899 select explain_analyze('
2900 update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);');
2901 explain_analyze
2902 ------------------------------------------------------------------------------
2903 Update on ab_a1 (actual rows=0 loops=1)
2904 Update on ab_a1_b1 ab_a1_1
2905 Update on ab_a1_b2 ab_a1_2
2906 Update on ab_a1_b3 ab_a1_3
2907 InitPlan 1
2908 -> Result (actual rows=1 loops=1)
2909 -> Nested Loop (actual rows=3 loops=1)
2910 -> Append (actual rows=3 loops=1)
2911 -> Seq Scan on ab_a1_b1 ab_a1_1 (actual rows=1 loops=1)
2912 -> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
2913 -> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1 loops=1)
2914 -> Materialize (actual rows=1 loops=3)
2915 Storage: Memory Maximum Storage: NkB
2916 -> Append (actual rows=1 loops=1)
2917 -> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1 loops=1)
2918 Filter: (b = (InitPlan 1).col1)
2919 -> Seq Scan on ab_a2_b2 ab_a2_2 (never executed)
2920 Filter: (b = (InitPlan 1).col1)
2921 -> Seq Scan on ab_a2_b3 ab_a2_3 (never executed)
2922 Filter: (b = (InitPlan 1).col1)
2923 (20 rows)
2925 select tableoid::regclass, * from ab;
2926 tableoid | a | b
2927 ----------+---+---
2928 ab_a1_b3 | 1 | 3
2929 ab_a1_b3 | 1 | 3
2930 ab_a1_b3 | 1 | 3
2931 ab_a2_b1 | 2 | 1
2932 (4 rows)
2934 drop table ab, lprt_a;
2935 -- Join
2936 create table tbl1(col1 int);
2937 insert into tbl1 values (501), (505);
2938 -- Basic table
2939 create table tprt (col1 int) partition by range (col1);
2940 create table tprt_1 partition of tprt for values from (1) to (501);
2941 create table tprt_2 partition of tprt for values from (501) to (1001);
2942 create table tprt_3 partition of tprt for values from (1001) to (2001);
2943 create table tprt_4 partition of tprt for values from (2001) to (3001);
2944 create table tprt_5 partition of tprt for values from (3001) to (4001);
2945 create table tprt_6 partition of tprt for values from (4001) to (5001);
2946 create index tprt1_idx on tprt_1 (col1);
2947 create index tprt2_idx on tprt_2 (col1);
2948 create index tprt3_idx on tprt_3 (col1);
2949 create index tprt4_idx on tprt_4 (col1);
2950 create index tprt5_idx on tprt_5 (col1);
2951 create index tprt6_idx on tprt_6 (col1);
2952 insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
2953 set enable_hashjoin = off;
2954 set enable_mergejoin = off;
2955 explain (analyze, costs off, summary off, timing off, buffers off)
2956 select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
2957 QUERY PLAN
2958 --------------------------------------------------------------------------
2959 Nested Loop (actual rows=6 loops=1)
2960 -> Seq Scan on tbl1 (actual rows=2 loops=1)
2961 -> Append (actual rows=3 loops=2)
2962 -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=2)
2963 Index Cond: (col1 < tbl1.col1)
2964 -> Index Scan using tprt2_idx on tprt_2 (actual rows=2 loops=1)
2965 Index Cond: (col1 < tbl1.col1)
2966 -> Index Scan using tprt3_idx on tprt_3 (never executed)
2967 Index Cond: (col1 < tbl1.col1)
2968 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2969 Index Cond: (col1 < tbl1.col1)
2970 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2971 Index Cond: (col1 < tbl1.col1)
2972 -> Index Scan using tprt6_idx on tprt_6 (never executed)
2973 Index Cond: (col1 < tbl1.col1)
2974 (15 rows)
2976 explain (analyze, costs off, summary off, timing off, buffers off)
2977 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
2978 QUERY PLAN
2979 --------------------------------------------------------------------------
2980 Nested Loop (actual rows=2 loops=1)
2981 -> Seq Scan on tbl1 (actual rows=2 loops=1)
2982 -> Append (actual rows=1 loops=2)
2983 -> Index Scan using tprt1_idx on tprt_1 (never executed)
2984 Index Cond: (col1 = tbl1.col1)
2985 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
2986 Index Cond: (col1 = tbl1.col1)
2987 -> Index Scan using tprt3_idx on tprt_3 (never executed)
2988 Index Cond: (col1 = tbl1.col1)
2989 -> Index Scan using tprt4_idx on tprt_4 (never executed)
2990 Index Cond: (col1 = tbl1.col1)
2991 -> Index Scan using tprt5_idx on tprt_5 (never executed)
2992 Index Cond: (col1 = tbl1.col1)
2993 -> Index Scan using tprt6_idx on tprt_6 (never executed)
2994 Index Cond: (col1 = tbl1.col1)
2995 (15 rows)
2997 select tbl1.col1, tprt.col1 from tbl1
2998 inner join tprt on tbl1.col1 > tprt.col1
2999 order by tbl1.col1, tprt.col1;
3000 col1 | col1
3001 ------+------
3002 501 | 10
3003 501 | 20
3004 505 | 10
3005 505 | 20
3006 505 | 501
3007 505 | 502
3008 (6 rows)
3010 select tbl1.col1, tprt.col1 from tbl1
3011 inner join tprt on tbl1.col1 = tprt.col1
3012 order by tbl1.col1, tprt.col1;
3013 col1 | col1
3014 ------+------
3015 501 | 501
3016 505 | 505
3017 (2 rows)
3019 -- Multiple partitions
3020 insert into tbl1 values (1001), (1010), (1011);
3021 explain (analyze, costs off, summary off, timing off, buffers off)
3022 select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
3023 QUERY PLAN
3024 --------------------------------------------------------------------------
3025 Nested Loop (actual rows=23 loops=1)
3026 -> Seq Scan on tbl1 (actual rows=5 loops=1)
3027 -> Append (actual rows=5 loops=5)
3028 -> Index Scan using tprt1_idx on tprt_1 (actual rows=2 loops=5)
3029 Index Cond: (col1 < tbl1.col1)
3030 -> Index Scan using tprt2_idx on tprt_2 (actual rows=3 loops=4)
3031 Index Cond: (col1 < tbl1.col1)
3032 -> Index Scan using tprt3_idx on tprt_3 (actual rows=1 loops=2)
3033 Index Cond: (col1 < tbl1.col1)
3034 -> Index Scan using tprt4_idx on tprt_4 (never executed)
3035 Index Cond: (col1 < tbl1.col1)
3036 -> Index Scan using tprt5_idx on tprt_5 (never executed)
3037 Index Cond: (col1 < tbl1.col1)
3038 -> Index Scan using tprt6_idx on tprt_6 (never executed)
3039 Index Cond: (col1 < tbl1.col1)
3040 (15 rows)
3042 explain (analyze, costs off, summary off, timing off, buffers off)
3043 select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
3044 QUERY PLAN
3045 --------------------------------------------------------------------------
3046 Nested Loop (actual rows=3 loops=1)
3047 -> Seq Scan on tbl1 (actual rows=5 loops=1)
3048 -> Append (actual rows=1 loops=5)
3049 -> Index Scan using tprt1_idx on tprt_1 (never executed)
3050 Index Cond: (col1 = tbl1.col1)
3051 -> Index Scan using tprt2_idx on tprt_2 (actual rows=1 loops=2)
3052 Index Cond: (col1 = tbl1.col1)
3053 -> Index Scan using tprt3_idx on tprt_3 (actual rows=0 loops=3)
3054 Index Cond: (col1 = tbl1.col1)
3055 -> Index Scan using tprt4_idx on tprt_4 (never executed)
3056 Index Cond: (col1 = tbl1.col1)
3057 -> Index Scan using tprt5_idx on tprt_5 (never executed)
3058 Index Cond: (col1 = tbl1.col1)
3059 -> Index Scan using tprt6_idx on tprt_6 (never executed)
3060 Index Cond: (col1 = tbl1.col1)
3061 (15 rows)
3063 select tbl1.col1, tprt.col1 from tbl1
3064 inner join tprt on tbl1.col1 > tprt.col1
3065 order by tbl1.col1, tprt.col1;
3066 col1 | col1
3067 ------+------
3068 501 | 10
3069 501 | 20
3070 505 | 10
3071 505 | 20
3072 505 | 501
3073 505 | 502
3074 1001 | 10
3075 1001 | 20
3076 1001 | 501
3077 1001 | 502
3078 1001 | 505
3079 1010 | 10
3080 1010 | 20
3081 1010 | 501
3082 1010 | 502
3083 1010 | 505
3084 1010 | 1001
3085 1011 | 10
3086 1011 | 20
3087 1011 | 501
3088 1011 | 502
3089 1011 | 505
3090 1011 | 1001
3091 (23 rows)
3093 select tbl1.col1, tprt.col1 from tbl1
3094 inner join tprt on tbl1.col1 = tprt.col1
3095 order by tbl1.col1, tprt.col1;
3096 col1 | col1
3097 ------+------
3098 501 | 501
3099 505 | 505
3100 1001 | 1001
3101 (3 rows)
3103 -- Last partition
3104 delete from tbl1;
3105 insert into tbl1 values (4400);
3106 explain (analyze, costs off, summary off, timing off, buffers off)
3107 select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
3108 QUERY PLAN
3109 --------------------------------------------------------------------------
3110 Nested Loop (actual rows=1 loops=1)
3111 -> Seq Scan on tbl1 (actual rows=1 loops=1)
3112 -> Append (actual rows=1 loops=1)
3113 -> Index Scan using tprt1_idx on tprt_1 (never executed)
3114 Index Cond: (col1 > tbl1.col1)
3115 -> Index Scan using tprt2_idx on tprt_2 (never executed)
3116 Index Cond: (col1 > tbl1.col1)
3117 -> Index Scan using tprt3_idx on tprt_3 (never executed)
3118 Index Cond: (col1 > tbl1.col1)
3119 -> Index Scan using tprt4_idx on tprt_4 (never executed)
3120 Index Cond: (col1 > tbl1.col1)
3121 -> Index Scan using tprt5_idx on tprt_5 (never executed)
3122 Index Cond: (col1 > tbl1.col1)
3123 -> Index Scan using tprt6_idx on tprt_6 (actual rows=1 loops=1)
3124 Index Cond: (col1 > tbl1.col1)
3125 (15 rows)
3127 select tbl1.col1, tprt.col1 from tbl1
3128 inner join tprt on tbl1.col1 < tprt.col1
3129 order by tbl1.col1, tprt.col1;
3130 col1 | col1
3131 ------+------
3132 4400 | 4500
3133 (1 row)
3135 -- No matching partition
3136 delete from tbl1;
3137 insert into tbl1 values (10000);
3138 explain (analyze, costs off, summary off, timing off, buffers off)
3139 select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
3140 QUERY PLAN
3141 -------------------------------------------------------------------
3142 Nested Loop (actual rows=0 loops=1)
3143 -> Seq Scan on tbl1 (actual rows=1 loops=1)
3144 -> Append (actual rows=0 loops=1)
3145 -> Index Scan using tprt1_idx on tprt_1 (never executed)
3146 Index Cond: (col1 = tbl1.col1)
3147 -> Index Scan using tprt2_idx on tprt_2 (never executed)
3148 Index Cond: (col1 = tbl1.col1)
3149 -> Index Scan using tprt3_idx on tprt_3 (never executed)
3150 Index Cond: (col1 = tbl1.col1)
3151 -> Index Scan using tprt4_idx on tprt_4 (never executed)
3152 Index Cond: (col1 = tbl1.col1)
3153 -> Index Scan using tprt5_idx on tprt_5 (never executed)
3154 Index Cond: (col1 = tbl1.col1)
3155 -> Index Scan using tprt6_idx on tprt_6 (never executed)
3156 Index Cond: (col1 = tbl1.col1)
3157 (15 rows)
3159 select tbl1.col1, tprt.col1 from tbl1
3160 inner join tprt on tbl1.col1 = tprt.col1
3161 order by tbl1.col1, tprt.col1;
3162 col1 | col1
3163 ------+------
3164 (0 rows)
3166 drop table tbl1, tprt;
3167 -- Test with columns defined in varying orders between each level
3168 create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
3169 create table part_bac (b int not null, a int not null, c int not null) partition by list (b);
3170 create table part_cab (c int not null, a int not null, b int not null) partition by list (c);
3171 create table part_abc_p1 (a int not null, b int not null, c int not null);
3172 alter table part_abc attach partition part_bac for values in(1);
3173 alter table part_bac attach partition part_cab for values in(2);
3174 alter table part_cab attach partition part_abc_p1 for values in(3);
3175 prepare part_abc_q1 (int, int, int) as
3176 select * from part_abc where a = $1 and b = $2 and c = $3;
3177 -- Single partition should be scanned.
3178 explain (analyze, costs off, summary off, timing off, buffers off) execute part_abc_q1 (1, 2, 3);
3179 QUERY PLAN
3180 ----------------------------------------------------------
3181 Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1)
3182 Filter: ((a = $1) AND (b = $2) AND (c = $3))
3183 (2 rows)
3185 deallocate part_abc_q1;
3186 drop table part_abc;
3187 -- Ensure that an Append node properly handles a sub-partitioned table
3188 -- matching without any of its leaf partitions matching the clause.
3189 create table listp (a int, b int) partition by list (a);
3190 create table listp_1 partition of listp for values in(1) partition by list (b);
3191 create table listp_1_1 partition of listp_1 for values in(1);
3192 create table listp_2 partition of listp for values in(2) partition by list (b);
3193 create table listp_2_1 partition of listp_2 for values in(2);
3194 select * from listp where b = 1;
3195 a | b
3196 ---+---
3197 (0 rows)
3199 -- Ensure that an Append node properly can handle selection of all first level
3200 -- partitions before finally detecting the correct set of 2nd level partitions
3201 -- which match the given parameter.
3202 prepare q1 (int,int) as select * from listp where b in ($1,$2);
3203 explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,1);
3204 QUERY PLAN
3205 -------------------------------------------------------------
3206 Append (actual rows=0 loops=1)
3207 Subplans Removed: 1
3208 -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1)
3209 Filter: (b = ANY (ARRAY[$1, $2]))
3210 (4 rows)
3212 explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (2,2);
3213 QUERY PLAN
3214 -------------------------------------------------------------
3215 Append (actual rows=0 loops=1)
3216 Subplans Removed: 1
3217 -> Seq Scan on listp_2_1 listp_1 (actual rows=0 loops=1)
3218 Filter: (b = ANY (ARRAY[$1, $2]))
3219 (4 rows)
3221 -- Try with no matching partitions.
3222 explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (0,0);
3223 QUERY PLAN
3224 --------------------------------
3225 Append (actual rows=0 loops=1)
3226 Subplans Removed: 2
3227 (2 rows)
3229 deallocate q1;
3230 -- Test more complex cases where a not-equal condition further eliminates partitions.
3231 prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
3232 -- Both partitions allowed by IN clause, but one disallowed by <> clause
3233 explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,0);
3234 QUERY PLAN
3235 -------------------------------------------------------------------------
3236 Append (actual rows=0 loops=1)
3237 Subplans Removed: 1
3238 -> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1)
3239 Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
3240 (4 rows)
3242 -- Both partitions allowed by IN clause, then both excluded again by <> clauses.
3243 explain (analyze, costs off, summary off, timing off, buffers off) execute q1 (1,2,2,1);
3244 QUERY PLAN
3245 --------------------------------
3246 Append (actual rows=0 loops=1)
3247 Subplans Removed: 2
3248 (2 rows)
3250 -- Ensure Params that evaluate to NULL properly prune away all partitions
3251 explain (analyze, costs off, summary off, timing off, buffers off)
3252 select * from listp where a = (select null::int);
3253 QUERY PLAN
3254 ------------------------------------------------------
3255 Append (actual rows=0 loops=1)
3256 InitPlan 1
3257 -> Result (actual rows=1 loops=1)
3258 -> Seq Scan on listp_1_1 listp_1 (never executed)
3259 Filter: (a = (InitPlan 1).col1)
3260 -> Seq Scan on listp_2_1 listp_2 (never executed)
3261 Filter: (a = (InitPlan 1).col1)
3262 (7 rows)
3264 drop table listp;
3265 --
3266 -- check that stable query clauses are only used in run-time pruning
3267 --
3268 create table stable_qual_pruning (a timestamp) partition by range (a);
3269 create table stable_qual_pruning1 partition of stable_qual_pruning
3270 for values from ('2000-01-01') to ('2000-02-01');
3271 create table stable_qual_pruning2 partition of stable_qual_pruning
3272 for values from ('2000-02-01') to ('2000-03-01');
3273 create table stable_qual_pruning3 partition of stable_qual_pruning
3274 for values from ('3000-02-01') to ('3000-03-01');
3275 -- comparison against a stable value requires run-time pruning
3276 explain (analyze, costs off, summary off, timing off, buffers off)
3277 select * from stable_qual_pruning where a < localtimestamp;
3278 QUERY PLAN
3279 --------------------------------------------------------------------------------------
3280 Append (actual rows=0 loops=1)
3281 Subplans Removed: 1
3282 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
3283 Filter: (a < LOCALTIMESTAMP)
3284 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1)
3285 Filter: (a < LOCALTIMESTAMP)
3286 (6 rows)
3288 -- timestamp < timestamptz comparison is only stable, not immutable
3289 explain (analyze, costs off, summary off, timing off, buffers off)
3290 select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
3291 QUERY PLAN
3292 --------------------------------------------------------------------------------------
3293 Append (actual rows=0 loops=1)
3294 Subplans Removed: 2
3295 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
3296 Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone)
3297 (4 rows)
3299 -- check ScalarArrayOp cases
3300 explain (analyze, costs off, summary off, timing off, buffers off)
3301 select * from stable_qual_pruning
3302 where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
3303 QUERY PLAN
3304 --------------------------------
3305 Result (actual rows=0 loops=1)
3306 One-Time Filter: false
3307 (2 rows)
3309 explain (analyze, costs off, summary off, timing off, buffers off)
3310 select * from stable_qual_pruning
3311 where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
3312 QUERY PLAN
3313 ----------------------------------------------------------------------------------------------------------------
3314 Seq Scan on stable_qual_pruning2 stable_qual_pruning (actual rows=0 loops=1)
3315 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[]))
3316 (2 rows)
3318 explain (analyze, costs off, summary off, timing off, buffers off)
3319 select * from stable_qual_pruning
3320 where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
3321 QUERY PLAN
3322 ------------------------------------------------------------------------------------------------------------
3323 Append (actual rows=0 loops=1)
3324 Subplans Removed: 2
3325 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1)
3326 Filter: (a = ANY (ARRAY['Tue Feb 01 00:00:00 2000'::timestamp without time zone, LOCALTIMESTAMP]))
3327 (4 rows)
3329 explain (analyze, costs off, summary off, timing off, buffers off)
3330 select * from stable_qual_pruning
3331 where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
3332 QUERY PLAN
3333 --------------------------------
3334 Append (actual rows=0 loops=1)
3335 Subplans Removed: 3
3336 (2 rows)
3338 explain (analyze, costs off, summary off, timing off, buffers off)
3339 select * from stable_qual_pruning
3340 where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
3341 QUERY PLAN
3342 ---------------------------------------------------------------------------------------------------------------------------
3343 Append (actual rows=0 loops=1)
3344 Subplans Removed: 2
3345 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1)
3346 Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[]))
3347 (4 rows)
3349 explain (analyze, costs off, summary off, timing off, buffers off)
3350 select * from stable_qual_pruning
3351 where a = any(null::timestamptz[]);
3352 QUERY PLAN
3353 --------------------------------------------------------------------------------------
3354 Append (actual rows=0 loops=1)
3355 -> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
3356 Filter: (a = ANY (NULL::timestamp with time zone[]))
3357 -> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1)
3358 Filter: (a = ANY (NULL::timestamp with time zone[]))
3359 -> Seq Scan on stable_qual_pruning3 stable_qual_pruning_3 (actual rows=0 loops=1)
3360 Filter: (a = ANY (NULL::timestamp with time zone[]))
3361 (7 rows)
3363 drop table stable_qual_pruning;
3364 --
3365 -- Check that pruning with composite range partitioning works correctly when
3366 -- it must ignore clauses for trailing keys once it has seen a clause with
3367 -- non-inclusive operator for an earlier key
3368 --
3369 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
3370 create table mc3p0 partition of mc3p
3371 for values from (0, 0, 0) to (0, maxvalue, maxvalue);
3372 create table mc3p1 partition of mc3p
3373 for values from (1, 1, 1) to (2, minvalue, minvalue);
3374 create table mc3p2 partition of mc3p
3375 for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
3376 insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
3377 explain (analyze, costs off, summary off, timing off, buffers off)
3378 select * from mc3p where a < 3 and abs(b) = 1;
3379 QUERY PLAN
3380 --------------------------------------------------------
3381 Append (actual rows=3 loops=1)
3382 -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
3383 Filter: ((a < 3) AND (abs(b) = 1))
3384 -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1)
3385 Filter: ((a < 3) AND (abs(b) = 1))
3386 -> Seq Scan on mc3p2 mc3p_3 (actual rows=1 loops=1)
3387 Filter: ((a < 3) AND (abs(b) = 1))
3388 (7 rows)
3390 --
3391 -- Check that pruning with composite range partitioning works correctly when
3392 -- a combination of runtime parameters is specified, not all of whose values
3393 -- are available at the same time
3394 --
3395 prepare ps1 as
3396 select * from mc3p where a = $1 and abs(b) < (select 3);
3397 explain (analyze, costs off, summary off, timing off, buffers off)
3398 execute ps1(1);
3399 QUERY PLAN
3400 -------------------------------------------------------------
3401 Append (actual rows=1 loops=1)
3402 Subplans Removed: 2
3403 InitPlan 1
3404 -> Result (actual rows=1 loops=1)
3405 -> Seq Scan on mc3p1 mc3p_1 (actual rows=1 loops=1)
3406 Filter: ((a = $1) AND (abs(b) < (InitPlan 1).col1))
3407 (6 rows)
3409 deallocate ps1;
3410 prepare ps2 as
3411 select * from mc3p where a <= $1 and abs(b) < (select 3);
3412 explain (analyze, costs off, summary off, timing off, buffers off)
3413 execute ps2(1);
3414 QUERY PLAN
3415 --------------------------------------------------------------
3416 Append (actual rows=2 loops=1)
3417 Subplans Removed: 1
3418 InitPlan 1
3419 -> Result (actual rows=1 loops=1)
3420 -> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
3421 Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1))
3422 -> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1)
3423 Filter: ((a <= $1) AND (abs(b) < (InitPlan 1).col1))
3424 (8 rows)
3426 deallocate ps2;
3427 drop table mc3p;
3428 -- Ensure runtime pruning works with initplans params with boolean types
3429 create table boolvalues (value bool not null);
3430 insert into boolvalues values('t'),('f');
3431 create table boolp (a bool) partition by list (a);
3432 create table boolp_t partition of boolp for values in('t');
3433 create table boolp_f partition of boolp for values in('f');
3434 explain (analyze, costs off, summary off, timing off, buffers off)
3435 select * from boolp where a = (select value from boolvalues where value);
3436 QUERY PLAN
3437 -----------------------------------------------------------
3438 Append (actual rows=0 loops=1)
3439 InitPlan 1
3440 -> Seq Scan on boolvalues (actual rows=1 loops=1)
3441 Filter: value
3442 Rows Removed by Filter: 1
3443 -> Seq Scan on boolp_f boolp_1 (never executed)
3444 Filter: (a = (InitPlan 1).col1)
3445 -> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1)
3446 Filter: (a = (InitPlan 1).col1)
3447 (9 rows)
3449 explain (analyze, costs off, summary off, timing off, buffers off)
3450 select * from boolp where a = (select value from boolvalues where not value);
3451 QUERY PLAN
3452 -----------------------------------------------------------
3453 Append (actual rows=0 loops=1)
3454 InitPlan 1
3455 -> Seq Scan on boolvalues (actual rows=1 loops=1)
3456 Filter: (NOT value)
3457 Rows Removed by Filter: 1
3458 -> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1)
3459 Filter: (a = (InitPlan 1).col1)
3460 -> Seq Scan on boolp_t boolp_2 (never executed)
3461 Filter: (a = (InitPlan 1).col1)
3462 (9 rows)
3464 drop table boolp;
3465 --
3466 -- Test run-time pruning of MergeAppend subnodes
3467 --
3468 set enable_seqscan = off;
3469 set enable_sort = off;
3470 create table ma_test (a int, b int) partition by range (a);
3471 create table ma_test_p1 partition of ma_test for values from (0) to (10);
3472 create table ma_test_p2 partition of ma_test for values from (10) to (20);
3473 create table ma_test_p3 partition of ma_test for values from (20) to (30);
3474 insert into ma_test select x,x from generate_series(0,29) t(x);
3475 create index on ma_test (b);
3476 analyze ma_test;
3477 prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
3478 explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(15);
3479 QUERY PLAN
3480 -----------------------------------------------------------------------------------------
3481 Merge Append (actual rows=2 loops=1)
3482 Sort Key: ma_test.b
3483 Subplans Removed: 1
3484 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1)
3485 Filter: ((a >= $1) AND ((a % 10) = 5))
3486 Rows Removed by Filter: 9
3487 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1)
3488 Filter: ((a >= $1) AND ((a % 10) = 5))
3489 Rows Removed by Filter: 9
3490 (9 rows)
3492 execute mt_q1(15);
3493 a
3494 ----
3495 15
3496 25
3497 (2 rows)
3499 explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(25);
3500 QUERY PLAN
3501 -----------------------------------------------------------------------------------------
3502 Merge Append (actual rows=1 loops=1)
3503 Sort Key: ma_test.b
3504 Subplans Removed: 2
3505 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1)
3506 Filter: ((a >= $1) AND ((a % 10) = 5))
3507 Rows Removed by Filter: 9
3508 (6 rows)
3510 execute mt_q1(25);
3511 a
3512 ----
3513 25
3514 (1 row)
3516 -- Ensure MergeAppend behaves correctly when no subplans match
3517 explain (analyze, costs off, summary off, timing off, buffers off) execute mt_q1(35);
3518 QUERY PLAN
3519 --------------------------------------
3520 Merge Append (actual rows=0 loops=1)
3521 Sort Key: ma_test.b
3522 Subplans Removed: 3
3523 (3 rows)
3525 execute mt_q1(35);
3526 a
3527 ---
3528 (0 rows)
3530 deallocate mt_q1;
3531 prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
3532 -- Ensure output list looks sane when the MergeAppend has no subplans.
3533 explain (analyze, verbose, costs off, summary off, timing off, buffers off) execute mt_q2 (35);
3534 QUERY PLAN
3535 --------------------------------------------
3536 Limit (actual rows=0 loops=1)
3537 Output: ma_test.a, ma_test.b
3538 -> Merge Append (actual rows=0 loops=1)
3539 Sort Key: ma_test.b
3540 Subplans Removed: 3
3541 (5 rows)
3543 deallocate mt_q2;
3544 -- ensure initplan params properly prune partitions
3545 explain (analyze, costs off, summary off, timing off, buffers off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
3546 QUERY PLAN
3547 -----------------------------------------------------------------------------------------------
3548 Merge Append (actual rows=20 loops=1)
3549 Sort Key: ma_test.b
3550 InitPlan 2
3551 -> Result (actual rows=1 loops=1)
3552 InitPlan 1
3553 -> Limit (actual rows=1 loops=1)
3554 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=1 loops=1)
3555 Index Cond: (b IS NOT NULL)
3556 -> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed)
3557 Filter: (a >= (InitPlan 2).col1)
3558 -> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=10 loops=1)
3559 Filter: (a >= (InitPlan 2).col1)
3560 -> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=10 loops=1)
3561 Filter: (a >= (InitPlan 2).col1)
3562 (14 rows)
3564 reset enable_seqscan;
3565 reset enable_sort;
3566 drop table ma_test;
3567 reset enable_indexonlyscan;
3568 --
3569 -- check that pruning works properly when the partition key is of a
3570 -- pseudotype
3571 --
3572 -- array type list partition key
3573 create table pp_arrpart (a int[]) partition by list (a);
3574 create table pp_arrpart1 partition of pp_arrpart for values in ('{1}');
3575 create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}');
3576 explain (costs off) select * from pp_arrpart where a = '{1}';
3577 QUERY PLAN
3578 ------------------------------------
3579 Seq Scan on pp_arrpart1 pp_arrpart
3580 Filter: (a = '{1}'::integer[])
3581 (2 rows)
3583 explain (costs off) select * from pp_arrpart where a = '{1, 2}';
3584 QUERY PLAN
3585 --------------------------
3586 Result
3587 One-Time Filter: false
3588 (2 rows)
3590 explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
3591 QUERY PLAN
3592 ----------------------------------------------------------------------
3593 Append
3594 -> Seq Scan on pp_arrpart1 pp_arrpart_1
3595 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3596 -> Seq Scan on pp_arrpart2 pp_arrpart_2
3597 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3598 (5 rows)
3600 explain (costs off) update pp_arrpart set a = a where a = '{1}';
3601 QUERY PLAN
3602 --------------------------------------------
3603 Update on pp_arrpart
3604 Update on pp_arrpart1 pp_arrpart_1
3605 -> Seq Scan on pp_arrpart1 pp_arrpart_1
3606 Filter: (a = '{1}'::integer[])
3607 (4 rows)
3609 explain (costs off) delete from pp_arrpart where a = '{1}';
3610 QUERY PLAN
3611 --------------------------------------------
3612 Delete on pp_arrpart
3613 Delete on pp_arrpart1 pp_arrpart_1
3614 -> Seq Scan on pp_arrpart1 pp_arrpart_1
3615 Filter: (a = '{1}'::integer[])
3616 (4 rows)
3618 drop table pp_arrpart;
3619 -- array type hash partition key
3620 create table pph_arrpart (a int[]) partition by hash (a);
3621 create table pph_arrpart1 partition of pph_arrpart for values with (modulus 2, remainder 0);
3622 create table pph_arrpart2 partition of pph_arrpart for values with (modulus 2, remainder 1);
3623 insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}');
3624 select tableoid::regclass, * from pph_arrpart order by 1;
3625 tableoid | a
3626 --------------+-------
3627 pph_arrpart1 | {1,2}
3628 pph_arrpart1 | {4,5}
3629 pph_arrpart2 | {1}
3630 (3 rows)
3632 explain (costs off) select * from pph_arrpart where a = '{1}';
3633 QUERY PLAN
3634 --------------------------------------
3635 Seq Scan on pph_arrpart2 pph_arrpart
3636 Filter: (a = '{1}'::integer[])
3637 (2 rows)
3639 explain (costs off) select * from pph_arrpart where a = '{1, 2}';
3640 QUERY PLAN
3641 --------------------------------------
3642 Seq Scan on pph_arrpart1 pph_arrpart
3643 Filter: (a = '{1,2}'::integer[])
3644 (2 rows)
3646 explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}');
3647 QUERY PLAN
3648 ----------------------------------------------------------------------
3649 Append
3650 -> Seq Scan on pph_arrpart1 pph_arrpart_1
3651 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3652 -> Seq Scan on pph_arrpart2 pph_arrpart_2
3653 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
3654 (5 rows)
3656 drop table pph_arrpart;
3657 -- enum type list partition key
3658 create type pp_colors as enum ('green', 'blue', 'black');
3659 create table pp_enumpart (a pp_colors) partition by list (a);
3660 create table pp_enumpart_green partition of pp_enumpart for values in ('green');
3661 create table pp_enumpart_blue partition of pp_enumpart for values in ('blue');
3662 explain (costs off) select * from pp_enumpart where a = 'blue';
3663 QUERY PLAN
3664 ------------------------------------------
3665 Seq Scan on pp_enumpart_blue pp_enumpart
3666 Filter: (a = 'blue'::pp_colors)
3667 (2 rows)
3669 explain (costs off) select * from pp_enumpart where a = 'black';
3670 QUERY PLAN
3671 --------------------------
3672 Result
3673 One-Time Filter: false
3674 (2 rows)
3676 drop table pp_enumpart;
3677 drop type pp_colors;
3678 -- record type as partition key
3679 create type pp_rectype as (a int, b int);
3680 create table pp_recpart (a pp_rectype) partition by list (a);
3681 create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)');
3682 create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)');
3683 explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
3684 QUERY PLAN
3685 --------------------------------------
3686 Seq Scan on pp_recpart_11 pp_recpart
3687 Filter: (a = '(1,1)'::pp_rectype)
3688 (2 rows)
3690 explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
3691 QUERY PLAN
3692 --------------------------
3693 Result
3694 One-Time Filter: false
3695 (2 rows)
3697 drop table pp_recpart;
3698 drop type pp_rectype;
3699 -- range type partition key
3700 create table pp_intrangepart (a int4range) partition by list (a);
3701 create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]');
3702 create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)');
3703 explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
3704 QUERY PLAN
3705 -----------------------------------------------
3706 Seq Scan on pp_intrangepart12 pp_intrangepart
3707 Filter: (a = '[1,3)'::int4range)
3708 (2 rows)
3710 explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
3711 QUERY PLAN
3712 --------------------------
3713 Result
3714 One-Time Filter: false
3715 (2 rows)
3717 drop table pp_intrangepart;
3718 --
3719 -- Ensure the enable_partition_prune GUC properly disables partition pruning.
3720 --
3721 create table pp_lp (a int, value int) partition by list (a);
3722 create table pp_lp1 partition of pp_lp for values in(1);
3723 create table pp_lp2 partition of pp_lp for values in(2);
3724 explain (costs off) select * from pp_lp where a = 1;
3725 QUERY PLAN
3726 --------------------------
3727 Seq Scan on pp_lp1 pp_lp
3728 Filter: (a = 1)
3729 (2 rows)
3731 explain (costs off) update pp_lp set value = 10 where a = 1;
3732 QUERY PLAN
3733 ----------------------------------
3734 Update on pp_lp
3735 Update on pp_lp1 pp_lp_1
3736 -> Seq Scan on pp_lp1 pp_lp_1
3737 Filter: (a = 1)
3738 (4 rows)
3740 explain (costs off) delete from pp_lp where a = 1;
3741 QUERY PLAN
3742 ----------------------------------
3743 Delete on pp_lp
3744 Delete on pp_lp1 pp_lp_1
3745 -> Seq Scan on pp_lp1 pp_lp_1
3746 Filter: (a = 1)
3747 (4 rows)
3749 set enable_partition_pruning = off;
3750 set constraint_exclusion = 'partition'; -- this should not affect the result.
3751 explain (costs off) select * from pp_lp where a = 1;
3752 QUERY PLAN
3753 ----------------------------------
3754 Append
3755 -> Seq Scan on pp_lp1 pp_lp_1
3756 Filter: (a = 1)
3757 -> Seq Scan on pp_lp2 pp_lp_2
3758 Filter: (a = 1)
3759 (5 rows)
3761 explain (costs off) update pp_lp set value = 10 where a = 1;
3762 QUERY PLAN
3763 ----------------------------------------
3764 Update on pp_lp
3765 Update on pp_lp1 pp_lp_1
3766 Update on pp_lp2 pp_lp_2
3767 -> Append
3768 -> Seq Scan on pp_lp1 pp_lp_1
3769 Filter: (a = 1)
3770 -> Seq Scan on pp_lp2 pp_lp_2
3771 Filter: (a = 1)
3772 (8 rows)
3774 explain (costs off) delete from pp_lp where a = 1;
3775 QUERY PLAN
3776 ----------------------------------------
3777 Delete on pp_lp
3778 Delete on pp_lp1 pp_lp_1
3779 Delete on pp_lp2 pp_lp_2
3780 -> Append
3781 -> Seq Scan on pp_lp1 pp_lp_1
3782 Filter: (a = 1)
3783 -> Seq Scan on pp_lp2 pp_lp_2
3784 Filter: (a = 1)
3785 (8 rows)
3787 set constraint_exclusion = 'off'; -- this should not affect the result.
3788 explain (costs off) select * from pp_lp where a = 1;
3789 QUERY PLAN
3790 ----------------------------------
3791 Append
3792 -> Seq Scan on pp_lp1 pp_lp_1
3793 Filter: (a = 1)
3794 -> Seq Scan on pp_lp2 pp_lp_2
3795 Filter: (a = 1)
3796 (5 rows)
3798 explain (costs off) update pp_lp set value = 10 where a = 1;
3799 QUERY PLAN
3800 ----------------------------------------
3801 Update on pp_lp
3802 Update on pp_lp1 pp_lp_1
3803 Update on pp_lp2 pp_lp_2
3804 -> Append
3805 -> Seq Scan on pp_lp1 pp_lp_1
3806 Filter: (a = 1)
3807 -> Seq Scan on pp_lp2 pp_lp_2
3808 Filter: (a = 1)
3809 (8 rows)
3811 explain (costs off) delete from pp_lp where a = 1;
3812 QUERY PLAN
3813 ----------------------------------------
3814 Delete on pp_lp
3815 Delete on pp_lp1 pp_lp_1
3816 Delete on pp_lp2 pp_lp_2
3817 -> Append
3818 -> Seq Scan on pp_lp1 pp_lp_1
3819 Filter: (a = 1)
3820 -> Seq Scan on pp_lp2 pp_lp_2
3821 Filter: (a = 1)
3822 (8 rows)
3824 drop table pp_lp;
3825 -- Ensure enable_partition_prune does not affect non-partitioned tables.
3826 create table inh_lp (a int, value int);
3827 create table inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp);
3828 NOTICE: merging column "a" with inherited definition
3829 NOTICE: merging column "value" with inherited definition
3830 create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp);
3831 NOTICE: merging column "a" with inherited definition
3832 NOTICE: merging column "value" with inherited definition
3833 set constraint_exclusion = 'partition';
3834 -- inh_lp2 should be removed in the following 3 cases.
3835 explain (costs off) select * from inh_lp where a = 1;
3836 QUERY PLAN
3837 ------------------------------------
3838 Append
3839 -> Seq Scan on inh_lp inh_lp_1
3840 Filter: (a = 1)
3841 -> Seq Scan on inh_lp1 inh_lp_2
3842 Filter: (a = 1)
3843 (5 rows)
3845 explain (costs off) update inh_lp set value = 10 where a = 1;
3846 QUERY PLAN
3847 ------------------------------------------------
3848 Update on inh_lp
3849 Update on inh_lp inh_lp_1
3850 Update on inh_lp1 inh_lp_2
3851 -> Result
3852 -> Append
3853 -> Seq Scan on inh_lp inh_lp_1
3854 Filter: (a = 1)
3855 -> Seq Scan on inh_lp1 inh_lp_2
3856 Filter: (a = 1)
3857 (9 rows)
3859 explain (costs off) delete from inh_lp where a = 1;
3860 QUERY PLAN
3861 ------------------------------------------
3862 Delete on inh_lp
3863 Delete on inh_lp inh_lp_1
3864 Delete on inh_lp1 inh_lp_2
3865 -> Append
3866 -> Seq Scan on inh_lp inh_lp_1
3867 Filter: (a = 1)
3868 -> Seq Scan on inh_lp1 inh_lp_2
3869 Filter: (a = 1)
3870 (8 rows)
3872 -- Ensure we don't exclude normal relations when we only expect to exclude
3873 -- inheritance children
3874 explain (costs off) update inh_lp1 set value = 10 where a = 2;
3875 QUERY PLAN
3876 ---------------------------
3877 Update on inh_lp1
3878 -> Seq Scan on inh_lp1
3879 Filter: (a = 2)
3880 (3 rows)
3882 drop table inh_lp cascade;
3883 NOTICE: drop cascades to 2 other objects
3884 DETAIL: drop cascades to table inh_lp1
3885 drop cascades to table inh_lp2
3886 reset enable_partition_pruning;
3887 reset constraint_exclusion;
3888 -- Check pruning for a partition tree containing only temporary relations
3889 create temp table pp_temp_parent (a int) partition by list (a);
3890 create temp table pp_temp_part_1 partition of pp_temp_parent for values in (1);
3891 create temp table pp_temp_part_def partition of pp_temp_parent default;
3892 explain (costs off) select * from pp_temp_parent where true;
3893 QUERY PLAN
3894 -----------------------------------------------------
3895 Append
3896 -> Seq Scan on pp_temp_part_1 pp_temp_parent_1
3897 -> Seq Scan on pp_temp_part_def pp_temp_parent_2
3898 (3 rows)
3900 explain (costs off) select * from pp_temp_parent where a = 2;
3901 QUERY PLAN
3902 ---------------------------------------------
3903 Seq Scan on pp_temp_part_def pp_temp_parent
3904 Filter: (a = 2)
3905 (2 rows)
3907 drop table pp_temp_parent;
3908 -- Stress run-time partition pruning a bit more, per bug reports
3909 create temp table p (a int, b int, c int) partition by list (a);
3910 create temp table p1 partition of p for values in (1);
3911 create temp table p2 partition of p for values in (2);
3912 create temp table q (a int, b int, c int) partition by list (a);
3913 create temp table q1 partition of q for values in (1) partition by list (b);
3914 create temp table q11 partition of q1 for values in (1) partition by list (c);
3915 create temp table q111 partition of q11 for values in (1);
3916 create temp table q2 partition of q for values in (2) partition by list (b);
3917 create temp table q21 partition of q2 for values in (1);
3918 create temp table q22 partition of q2 for values in (2);
3919 insert into q22 values (2, 2, 3);
3920 explain (costs off)
3921 select *
3922 from (
3923 select * from p
3924 union all
3925 select * from q1
3926 union all
3927 select 1, 1, 1
3928 ) s(a, b, c)
3929 where s.a = 1 and s.b = 1 and s.c = (select 1);
3930 QUERY PLAN
3931 -------------------------------------------------------------------
3932 Append
3933 InitPlan 1
3934 -> Result
3935 -> Seq Scan on p1 p
3936 Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1))
3937 -> Seq Scan on q111 q1
3938 Filter: ((a = 1) AND (b = 1) AND (c = (InitPlan 1).col1))
3939 -> Result
3940 One-Time Filter: (1 = (InitPlan 1).col1)
3941 (9 rows)
3943 select *
3944 from (
3945 select * from p
3946 union all
3947 select * from q1
3948 union all
3949 select 1, 1, 1
3950 ) s(a, b, c)
3951 where s.a = 1 and s.b = 1 and s.c = (select 1);
3952 a | b | c
3953 ---+---+---
3954 1 | 1 | 1
3955 (1 row)
3957 prepare q (int, int) as
3958 select *
3959 from (
3960 select * from p
3961 union all
3962 select * from q1
3963 union all
3964 select 1, 1, 1
3965 ) s(a, b, c)
3966 where s.a = $1 and s.b = $2 and s.c = (select 1);
3967 explain (costs off) execute q (1, 1);
3968 QUERY PLAN
3969 ------------------------------------------------------------------------------
3970 Append
3971 Subplans Removed: 1
3972 InitPlan 1
3973 -> Result
3974 -> Seq Scan on p1 p
3975 Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1))
3976 -> Seq Scan on q111 q1
3977 Filter: ((a = $1) AND (b = $2) AND (c = (InitPlan 1).col1))
3978 -> Result
3979 One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = (InitPlan 1).col1))
3980 (10 rows)
3982 execute q (1, 1);
3983 a | b | c
3984 ---+---+---
3985 1 | 1 | 1
3986 (1 row)
3988 drop table p, q;
3989 -- Ensure run-time pruning works correctly when we match a partitioned table
3990 -- on the first level but find no matching partitions on the second level.
3991 create table listp (a int, b int) partition by list (a);
3992 create table listp1 partition of listp for values in(1);
3993 create table listp2 partition of listp for values in(2) partition by list(b);
3994 create table listp2_10 partition of listp2 for values in (10);
3995 explain (analyze, costs off, summary off, timing off, buffers off)
3996 select * from listp where a = (select 2) and b <> 10;
3997 QUERY PLAN
3998 ---------------------------------------------------
3999 Seq Scan on listp1 listp (actual rows=0 loops=1)
4000 Filter: ((b <> 10) AND (a = (InitPlan 1).col1))
4001 InitPlan 1
4002 -> Result (never executed)
4003 (4 rows)
4005 --
4006 -- check that a partition directly accessed in a query is excluded with
4007 -- constraint_exclusion = on
4008 --
4009 -- turn off partition pruning, so that it doesn't interfere
4010 set enable_partition_pruning to off;
4011 -- setting constraint_exclusion to 'partition' disables exclusion
4012 set constraint_exclusion to 'partition';
4013 explain (costs off) select * from listp1 where a = 2;
4014 QUERY PLAN
4015 --------------------
4016 Seq Scan on listp1
4017 Filter: (a = 2)
4018 (2 rows)
4020 explain (costs off) update listp1 set a = 1 where a = 2;
4021 QUERY PLAN
4022 --------------------------
4023 Update on listp1
4024 -> Seq Scan on listp1
4025 Filter: (a = 2)
4026 (3 rows)
4028 -- constraint exclusion enabled
4029 set constraint_exclusion to 'on';
4030 explain (costs off) select * from listp1 where a = 2;
4031 QUERY PLAN
4032 --------------------------
4033 Result
4034 One-Time Filter: false
4035 (2 rows)
4037 explain (costs off) update listp1 set a = 1 where a = 2;
4038 QUERY PLAN
4039 --------------------------------
4040 Update on listp1
4041 -> Result
4042 One-Time Filter: false
4043 (3 rows)
4045 reset constraint_exclusion;
4046 reset enable_partition_pruning;
4047 drop table listp;
4048 -- Ensure run-time pruning works correctly for nested Append nodes
4049 set parallel_setup_cost to 0;
4050 set parallel_tuple_cost to 0;
4051 create table listp (a int) partition by list(a);
4052 create table listp_12 partition of listp for values in(1,2) partition by list(a);
4053 create table listp_12_1 partition of listp_12 for values in(1);
4054 create table listp_12_2 partition of listp_12 for values in(2);
4055 -- Force the 2nd subnode of the Append to be non-parallel. This results in
4056 -- a nested Append node because the mixed parallel / non-parallel paths cannot
4057 -- be pulled into the top-level Append.
4058 alter table listp_12_1 set (parallel_workers = 0);
4059 -- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in
4060 -- the plan as it's pulled in setref.c due to having just a single subnode).
4061 select explain_parallel_append('select * from listp where a = (select 1);');
4062 explain_parallel_append
4063 ----------------------------------------------------------------------
4064 Gather (actual rows=N loops=N)
4065 Workers Planned: 2
4066 Workers Launched: N
4067 InitPlan 1
4068 -> Result (actual rows=N loops=N)
4069 -> Parallel Append (actual rows=N loops=N)
4070 -> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N)
4071 Filter: (a = (InitPlan 1).col1)
4072 -> Parallel Seq Scan on listp_12_2 listp_2 (never executed)
4073 Filter: (a = (InitPlan 1).col1)
4074 (10 rows)
4076 -- Like the above but throw some more complexity at the planner by adding
4077 -- a UNION ALL. We expect both sides of the union not to scan the
4078 -- non-required partitions.
4079 select explain_parallel_append(
4080 'select * from listp where a = (select 1)
4081 union all
4082 select * from listp where a = (select 2);');
4083 explain_parallel_append
4084 -----------------------------------------------------------------------------------
4085 Gather (actual rows=N loops=N)
4086 Workers Planned: 2
4087 Workers Launched: N
4088 -> Parallel Append (actual rows=N loops=N)
4089 -> Parallel Append (actual rows=N loops=N)
4090 InitPlan 2
4091 -> Result (actual rows=N loops=N)
4092 -> Seq Scan on listp_12_1 listp_1 (never executed)
4093 Filter: (a = (InitPlan 2).col1)
4094 -> Parallel Seq Scan on listp_12_2 listp_2 (actual rows=N loops=N)
4095 Filter: (a = (InitPlan 2).col1)
4096 -> Parallel Append (actual rows=N loops=N)
4097 InitPlan 1
4098 -> Result (actual rows=N loops=N)
4099 -> Seq Scan on listp_12_1 listp_4 (actual rows=N loops=N)
4100 Filter: (a = (InitPlan 1).col1)
4101 -> Parallel Seq Scan on listp_12_2 listp_5 (never executed)
4102 Filter: (a = (InitPlan 1).col1)
4103 (18 rows)
4105 drop table listp;
4106 reset parallel_tuple_cost;
4107 reset parallel_setup_cost;
4108 -- Test case for run-time pruning with a nested Merge Append
4109 set enable_sort to 0;
4110 create table rangep (a int, b int) partition by range (a);
4111 create table rangep_0_to_100 partition of rangep for values from (0) to (100) partition by list (b);
4112 -- We need 3 sub-partitions. 1 to validate pruning worked and another two
4113 -- because a single remaining partition would be pulled up to the main Append.
4114 create table rangep_0_to_100_1 partition of rangep_0_to_100 for values in(1);
4115 create table rangep_0_to_100_2 partition of rangep_0_to_100 for values in(2);
4116 create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3);
4117 create table rangep_100_to_200 partition of rangep for values from (100) to (200);
4118 create index on rangep (a);
4119 -- Ensure run-time pruning works on the nested Merge Append
4120 explain (analyze on, costs off, timing off, summary off, buffers off)
4121 select * from rangep where b IN((select 1),(select 2)) order by a;
4122 QUERY PLAN
4123 ------------------------------------------------------------------------------------------------------------
4124 Append (actual rows=0 loops=1)
4125 InitPlan 1
4126 -> Result (actual rows=1 loops=1)
4127 InitPlan 2
4128 -> Result (actual rows=1 loops=1)
4129 -> Merge Append (actual rows=0 loops=1)
4130 Sort Key: rangep_2.a
4131 -> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0 loops=1)
4132 Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
4133 -> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1)
4134 Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
4135 -> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed)
4136 Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
4137 -> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0 loops=1)
4138 Filter: (b = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1]))
4139 (15 rows)
4141 reset enable_sort;
4142 drop table rangep;
4143 --
4144 -- Check that gen_prune_steps_from_opexps() works well for various cases of
4145 -- clauses for different partition keys
4146 --
4147 create table rp_prefix_test1 (a int, b varchar) partition by range(a, b);
4148 create table rp_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b');
4149 create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b');
4150 -- Don't call get_steps_using_prefix() with the last partition key b plus
4151 -- an empty prefix
4152 explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a';
4153 QUERY PLAN
4154 --------------------------------------------------
4155 Seq Scan on rp_prefix_test1_p1 rp_prefix_test1
4156 Filter: ((a <= 1) AND ((b)::text = 'a'::text))
4157 (2 rows)
4159 create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c);
4160 create table rp_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10);
4161 create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10);
4162 -- Don't call get_steps_using_prefix() with the last partition key c plus
4163 -- an invalid prefix (ie, b = 1)
4164 explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0;
4165 QUERY PLAN
4166 ------------------------------------------------
4167 Seq Scan on rp_prefix_test2_p1 rp_prefix_test2
4168 Filter: ((a <= 1) AND (c >= 0) AND (b = 1))
4169 (2 rows)
4171 create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d);
4172 create table rp_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10);
4173 create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10);
4174 -- Test that get_steps_using_prefix() handles a prefix that contains multiple
4175 -- clauses for the partition key b (ie, b >= 1 and b >= 2)
4176 explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0;
4177 QUERY PLAN
4178 --------------------------------------------------------------------------
4179 Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
4180 Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0))
4181 (2 rows)
4183 -- Test that get_steps_using_prefix() handles a prefix that contains multiple
4184 -- clauses for the partition key b (ie, b >= 1 and b = 2) (This also tests
4185 -- that the caller arranges clauses in that prefix in the required order)
4186 explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b = 2 and c = 2 and d >= 0;
4187 QUERY PLAN
4188 ------------------------------------------------------------------------
4189 Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
4190 Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (b = 2) AND (c = 2))
4191 (2 rows)
4193 drop table rp_prefix_test1;
4194 drop table rp_prefix_test2;
4195 drop table rp_prefix_test3;
4196 --
4197 -- Test that get_steps_using_prefix() handles IS NULL clauses correctly
4198 --
4199 create table hp_prefix_test (a int, b int, c int, d int)
4200 partition by hash (a part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d part_test_int4_ops);
4201 -- create 8 partitions
4202 select 'create table hp_prefix_test_p' || x::text || ' partition of hp_prefix_test for values with (modulus 8, remainder ' || x::text || ');'
4203 from generate_Series(0,7) x;
4204 ?column?
4205 ------------------------------------------------------------------------------------------------------
4206 create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0);
4207 create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1);
4208 create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2);
4209 create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3);
4210 create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4);
4211 create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5);
4212 create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6);
4213 create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7);
4214 (8 rows)
4216 \gexec
4217 create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0);
4218 create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1);
4219 create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2);
4220 create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3);
4221 create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4);
4222 create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5);
4223 create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6);
4224 create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7);
4225 -- insert 16 rows, one row for each test to perform.
4226 insert into hp_prefix_test
4227 select
4228 case a when 0 then null else 1 end,
4229 case b when 0 then null else 2 end,
4230 case c when 0 then null else 3 end,
4231 case d when 0 then null else 4 end
4232 from
4233 generate_series(0,1) a,
4234 generate_series(0,1) b,
4235 generate_Series(0,1) c,
4236 generate_Series(0,1) d;
4237 -- Ensure partition pruning works correctly for each combination of IS NULL
4238 -- and equality quals. This may seem a little excessive, but there have been
4239 -- a number of bugs in this area over the years. We make use of row only
4240 -- output to reduce the size of the expected results.
4241 \t on
4242 select
4243 'explain (costs off) select tableoid::regclass,* from hp_prefix_test where ' ||
4244 string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
4245 from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s)
4246 group by g.s
4247 order by g.s;
4248 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
4249 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
4250 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
4251 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
4252 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
4253 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
4254 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
4255 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
4256 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
4257 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
4258 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
4259 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
4260 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
4261 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
4262 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
4263 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
4265 \gexec
4266 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
4267 Seq Scan on hp_prefix_test_p0 hp_prefix_test
4268 Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d IS NULL))
4270 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
4271 Seq Scan on hp_prefix_test_p1 hp_prefix_test
4272 Filter: ((b IS NULL) AND (c IS NULL) AND (d IS NULL) AND (a = 1))
4274 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
4275 Seq Scan on hp_prefix_test_p2 hp_prefix_test
4276 Filter: ((a IS NULL) AND (c IS NULL) AND (d IS NULL) AND (b = 2))
4278 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
4279 Seq Scan on hp_prefix_test_p4 hp_prefix_test
4280 Filter: ((c IS NULL) AND (d IS NULL) AND (a = 1) AND (b = 2))
4282 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
4283 Seq Scan on hp_prefix_test_p3 hp_prefix_test
4284 Filter: ((a IS NULL) AND (b IS NULL) AND (d IS NULL) AND (c = 3))
4286 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
4287 Seq Scan on hp_prefix_test_p7 hp_prefix_test
4288 Filter: ((b IS NULL) AND (d IS NULL) AND (a = 1) AND (c = 3))
4290 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
4291 Seq Scan on hp_prefix_test_p4 hp_prefix_test
4292 Filter: ((a IS NULL) AND (d IS NULL) AND (b = 2) AND (c = 3))
4294 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
4295 Seq Scan on hp_prefix_test_p5 hp_prefix_test
4296 Filter: ((d IS NULL) AND (a = 1) AND (b = 2) AND (c = 3))
4298 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
4299 Seq Scan on hp_prefix_test_p4 hp_prefix_test
4300 Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d = 4))
4302 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
4303 Seq Scan on hp_prefix_test_p6 hp_prefix_test
4304 Filter: ((b IS NULL) AND (c IS NULL) AND (a = 1) AND (d = 4))
4306 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
4307 Seq Scan on hp_prefix_test_p5 hp_prefix_test
4308 Filter: ((a IS NULL) AND (c IS NULL) AND (b = 2) AND (d = 4))
4310 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
4311 Seq Scan on hp_prefix_test_p6 hp_prefix_test
4312 Filter: ((c IS NULL) AND (a = 1) AND (b = 2) AND (d = 4))
4314 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
4315 Seq Scan on hp_prefix_test_p4 hp_prefix_test
4316 Filter: ((a IS NULL) AND (b IS NULL) AND (c = 3) AND (d = 4))
4318 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
4319 Seq Scan on hp_prefix_test_p5 hp_prefix_test
4320 Filter: ((b IS NULL) AND (a = 1) AND (c = 3) AND (d = 4))
4322 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
4323 Seq Scan on hp_prefix_test_p6 hp_prefix_test
4324 Filter: ((a IS NULL) AND (b = 2) AND (c = 3) AND (d = 4))
4326 explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
4327 Seq Scan on hp_prefix_test_p4 hp_prefix_test
4328 Filter: ((a = 1) AND (b = 2) AND (c = 3) AND (d = 4))
4330 -- And ensure we get exactly 1 row from each. Again, all 16 possible combinations.
4331 select
4332 'select tableoid::regclass,* from hp_prefix_test where ' ||
4333 string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
4334 from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s)
4335 group by g.s
4336 order by g.s;
4337 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
4338 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
4339 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
4340 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
4341 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
4342 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
4343 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
4344 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
4345 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
4346 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
4347 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
4348 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
4349 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
4350 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
4351 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
4352 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
4354 \gexec
4355 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
4356 hp_prefix_test_p0 | | | |
4358 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
4359 hp_prefix_test_p1 | 1 | | |
4361 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
4362 hp_prefix_test_p2 | | 2 | |
4364 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
4365 hp_prefix_test_p4 | 1 | 2 | |
4367 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
4368 hp_prefix_test_p3 | | | 3 |
4370 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
4371 hp_prefix_test_p7 | 1 | | 3 |
4373 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
4374 hp_prefix_test_p4 | | 2 | 3 |
4376 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
4377 hp_prefix_test_p5 | 1 | 2 | 3 |
4379 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
4380 hp_prefix_test_p4 | | | | 4
4382 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
4383 hp_prefix_test_p6 | 1 | | | 4
4385 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
4386 hp_prefix_test_p5 | | 2 | | 4
4388 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
4389 hp_prefix_test_p6 | 1 | 2 | | 4
4391 select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
4392 hp_prefix_test_p4 | | | 3 | 4
4394 select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
4395 hp_prefix_test_p5 | 1 | | 3 | 4
4397 select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
4398 hp_prefix_test_p6 | | 2 | 3 | 4
4400 select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
4401 hp_prefix_test_p4 | 1 | 2 | 3 | 4
4403 \t off
4404 drop table hp_prefix_test;
4405 --
4406 -- Check that gen_partprune_steps() detects self-contradiction from clauses
4407 -- regardless of the order of the clauses (Here we use a custom operator to
4408 -- prevent the equivclass.c machinery from reordering the clauses)
4409 --
4410 create operator === (
4411 leftarg = int4,
4412 rightarg = int4,
4413 procedure = int4eq,
4414 commutator = ===,
4415 hashes
4416 );
4417 create operator class part_test_int4_ops2
4418 for type int4
4419 using hash as
4420 operator 1 ===,
4421 function 2 part_hashint4_noop(int4, int8);
4422 create table hp_contradict_test (a int, b int) partition by hash (a part_test_int4_ops2, b part_test_int4_ops2);
4423 create table hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0);
4424 create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1);
4425 explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1;
4426 QUERY PLAN
4427 --------------------------
4428 Result
4429 One-Time Filter: false
4430 (2 rows)
4432 explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null;
4433 QUERY PLAN
4434 --------------------------
4435 Result
4436 One-Time Filter: false
4437 (2 rows)
4439 drop table hp_contradict_test;
4440 drop operator class part_test_int4_ops2 using hash;
4441 drop operator ===(int4, int4);
4442 drop function explain_analyze(text);