3 -- Test partitionwise join between partitioned tables
5 -- Enable partitionwise join, which by default is disabled.
6 SET enable_partitionwise_join to true;
8 -- partitioned by a single column
10 CREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a);
11 CREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (250);
12 CREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (500) TO (600);
13 CREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (250) TO (500);
14 INSERT INTO prt1 SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 2 = 0;
15 CREATE INDEX iprt1_p1_a on prt1_p1(a);
16 CREATE INDEX iprt1_p2_a on prt1_p2(a);
17 CREATE INDEX iprt1_p3_a on prt1_p3(a);
19 CREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b);
20 CREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (250);
21 CREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (250) TO (500);
22 CREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (500) TO (600);
23 INSERT INTO prt2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(0, 599) i WHERE i % 3 = 0;
24 CREATE INDEX iprt2_p1_b on prt2_p1(b);
25 CREATE INDEX iprt2_p2_b on prt2_p2(b);
26 CREATE INDEX iprt2_p3_b on prt2_p3(b);
30 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
32 --------------------------------------------------
37 Hash Cond: (t2_1.b = t1_1.a)
38 -> Seq Scan on prt2_p1 t2_1
40 -> Seq Scan on prt1_p1 t1_1
43 Hash Cond: (t2_2.b = t1_2.a)
44 -> Seq Scan on prt2_p2 t2_2
46 -> Seq Scan on prt1_p2 t1_2
49 Hash Cond: (t2_3.b = t1_3.a)
50 -> Seq Scan on prt2_p3 t2_3
52 -> Seq Scan on prt1_p3 t1_3
56 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
58 -----+------+-----+------
60 150 | 0150 | 150 | 0150
61 300 | 0300 | 300 | 0300
62 450 | 0450 | 450 | 0450
65 -- inner join with partially-redundant join clauses
67 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
69 ---------------------------------------------------------------
74 Merge Cond: (t1_1.a = t2_1.a)
75 -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
78 -> Seq Scan on prt2_p1 t2_1
81 Hash Cond: (t1_2.a = t2_2.a)
82 -> Seq Scan on prt1_p2 t1_2
84 -> Seq Scan on prt2_p2 t2_2
87 Hash Cond: (t1_3.a = t2_3.a)
88 -> Seq Scan on prt1_p3 t1_3
90 -> Seq Scan on prt2_p3 t2_3
94 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.a AND t1.a = t2.b ORDER BY t1.a, t2.b;
96 ----+------+----+------
100 18 | 0018 | 18 | 0018
101 24 | 0024 | 24 | 0024
104 -- left outer join, 3-way
106 SELECT COUNT(*) FROM prt1 t1
107 LEFT JOIN prt1 t2 ON t1.a = t2.a
108 LEFT JOIN prt1 t3 ON t2.a = t3.a;
110 --------------------------------------------------------
114 Hash Cond: (t2_1.a = t3_1.a)
116 Hash Cond: (t1_1.a = t2_1.a)
117 -> Seq Scan on prt1_p1 t1_1
119 -> Seq Scan on prt1_p1 t2_1
121 -> Seq Scan on prt1_p1 t3_1
123 Hash Cond: (t2_2.a = t3_2.a)
125 Hash Cond: (t1_2.a = t2_2.a)
126 -> Seq Scan on prt1_p2 t1_2
128 -> Seq Scan on prt1_p2 t2_2
130 -> Seq Scan on prt1_p2 t3_2
132 Hash Cond: (t2_3.a = t3_3.a)
134 Hash Cond: (t1_3.a = t2_3.a)
135 -> Seq Scan on prt1_p3 t1_3
137 -> Seq Scan on prt1_p3 t2_3
139 -> Seq Scan on prt1_p3 t3_3
142 SELECT COUNT(*) FROM prt1 t1
143 LEFT JOIN prt1 t2 ON t1.a = t2.a
144 LEFT JOIN prt1 t3 ON t2.a = t3.a;
150 -- left outer join, with whole-row reference; partitionwise join does not apply
152 SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
154 --------------------------------------------------
158 Hash Cond: (t2.b = t1.a)
160 -> Seq Scan on prt2_p1 t2_1
161 -> Seq Scan on prt2_p2 t2_2
162 -> Seq Scan on prt2_p3 t2_3
165 -> Seq Scan on prt1_p1 t1_1
167 -> Seq Scan on prt1_p2 t1_2
169 -> Seq Scan on prt1_p3 t1_3
173 SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
175 --------------+--------------
176 (0,0,0000) | (0,0,0000)
179 (150,0,0150) | (0,150,0150)
182 (300,0,0300) | (0,300,0300)
185 (450,0,0450) | (0,450,0450)
192 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
194 ---------------------------------------------------------------
199 Hash Cond: (t1_1.a = t2_1.b)
200 -> Seq Scan on prt1_p1 t1_1
202 -> Seq Scan on prt2_p1 t2_1
205 Hash Cond: (t1_2.a = t2_2.b)
206 -> Seq Scan on prt1_p2 t1_2
208 -> Seq Scan on prt2_p2 t2_2
210 -> Nested Loop Left Join
211 -> Seq Scan on prt2_p3 t2_3
213 -> Index Scan using iprt1_p3_a on prt1_p3 t1_3
214 Index Cond: (a = t2_3.b)
217 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
219 -----+------+-----+------
221 150 | 0150 | 150 | 0150
222 300 | 0300 | 300 | 0300
223 450 | 0450 | 450 | 0450
230 -- full outer join, with placeholder vars
232 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
234 ----------------------------------------------------------------
236 Sort Key: prt1.a, prt2.b
239 Hash Cond: (prt1_1.a = prt2_1.b)
240 Filter: (((50) = prt1_1.a) OR ((75) = prt2_1.b))
241 -> Seq Scan on prt1_p1 prt1_1
244 -> Seq Scan on prt2_p1 prt2_1
247 Hash Cond: (prt1_2.a = prt2_2.b)
248 Filter: (((50) = prt1_2.a) OR ((75) = prt2_2.b))
249 -> Seq Scan on prt1_p2 prt1_2
252 -> Seq Scan on prt2_p2 prt2_2
255 Hash Cond: (prt1_3.a = prt2_3.b)
256 Filter: (((50) = prt1_3.a) OR ((75) = prt2_3.b))
257 -> Seq Scan on prt1_p3 prt1_3
260 -> Seq Scan on prt2_p3 prt2_3
264 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
266 ----+------+----+------
271 -- Join with pruned partitions from joining relations
273 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
275 -----------------------------------------------------
279 Hash Cond: (t2.b = t1.a)
280 -> Seq Scan on prt2_p2 t2
283 -> Seq Scan on prt1_p2 t1
284 Filter: ((a < 450) AND (b = 0))
287 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a < 450 AND t2.b > 250 AND t1.b = 0 ORDER BY t1.a, t2.b;
289 -----+------+-----+------
290 300 | 0300 | 300 | 0300
293 -- Currently we can't do partitioned join if nullable-side partitions are pruned
295 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
297 -----------------------------------------------------------
299 Sort Key: prt1.a, prt2.b
301 Hash Cond: (prt2.b = prt1.a)
303 -> Seq Scan on prt2_p2 prt2_1
305 -> Seq Scan on prt2_p3 prt2_2
309 -> Seq Scan on prt1_p1 prt1_1
310 Filter: ((a < 450) AND (b = 0))
311 -> Seq Scan on prt1_p2 prt1_2
312 Filter: ((a < 450) AND (b = 0))
315 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
317 -----+------+-----+------
324 300 | 0300 | 300 | 0300
329 -- Currently we can't do partitioned join if nullable-side partitions are pruned
331 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
333 ----------------------------------------------------
335 Sort Key: prt1.a, prt2.b
337 Hash Cond: (prt1.a = prt2.b)
338 Filter: ((prt1.b = 0) OR (prt2.a = 0))
340 -> Seq Scan on prt1_p1 prt1_1
342 -> Seq Scan on prt1_p2 prt1_2
346 -> Seq Scan on prt2_p2 prt2_1
348 -> Seq Scan on prt2_p3 prt2_2
352 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
354 -----+------+-----+------
361 300 | 0300 | 300 | 0300
371 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) AND t1.b = 0 ORDER BY t1.a;
373 --------------------------------------------------
378 Hash Cond: (t1_1.a = t2_1.b)
379 -> Seq Scan on prt1_p1 t1_1
382 -> Seq Scan on prt2_p1 t2_1
385 Hash Cond: (t1_2.a = t2_2.b)
386 -> Seq Scan on prt1_p2 t1_2
389 -> Seq Scan on prt2_p2 t2_2
391 -> Nested Loop Semi Join
392 Join Filter: (t1_3.a = t2_3.b)
393 -> Seq Scan on prt1_p3 t1_3
396 -> Seq Scan on prt2_p3 t2_3
400 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t2.b FROM prt2 t2 WHERE t2.a = 0) AND t1.b = 0 ORDER BY t1.a;
409 -- Anti-join with aggregates
411 SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS (SELECT 1 FROM prt2 t2 WHERE t1.a = t2.b);
413 --------------------------------------------------
417 Hash Cond: (t1_1.a = t2_1.b)
418 -> Seq Scan on prt1_p1 t1_1
420 -> Seq Scan on prt2_p1 t2_1
422 Hash Cond: (t1_2.a = t2_2.b)
423 -> Seq Scan on prt1_p2 t1_2
425 -> Seq Scan on prt2_p2 t2_2
427 Hash Cond: (t1_3.a = t2_3.b)
428 -> Seq Scan on prt1_p3 t1_3
430 -> Seq Scan on prt2_p3 t2_3
433 SELECT sum(t1.a), avg(t1.a), sum(t1.b), avg(t1.b) FROM prt1 t1 WHERE NOT EXISTS (SELECT 1 FROM prt2 t2 WHERE t1.a = t2.b);
434 sum | avg | sum | avg
435 -------+----------------------+------+---------------------
436 60000 | 300.0000000000000000 | 2400 | 12.0000000000000000
441 SELECT * FROM prt1 t1 LEFT JOIN LATERAL
442 (SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
443 ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a;
445 --------------------------------------------------------------------------
449 -> Nested Loop Left Join
450 -> Seq Scan on prt1_p1 t1_1
453 -> Index Only Scan using iprt1_p1_a on prt1_p1 t2_1
454 Index Cond: (a = t1_1.a)
455 -> Index Scan using iprt2_p1_b on prt2_p1 t3_1
456 Index Cond: (b = t2_1.a)
457 -> Nested Loop Left Join
458 -> Seq Scan on prt1_p2 t1_2
461 -> Index Only Scan using iprt1_p2_a on prt1_p2 t2_2
462 Index Cond: (a = t1_2.a)
463 -> Index Scan using iprt2_p2_b on prt2_p2 t3_2
464 Index Cond: (b = t2_2.a)
465 -> Nested Loop Left Join
466 -> Seq Scan on prt1_p3 t1_3
469 -> Index Only Scan using iprt1_p3_a on prt1_p3 t2_3
470 Index Cond: (a = t1_3.a)
471 -> Index Scan using iprt2_p3_b on prt2_p3 t3_3
472 Index Cond: (b = t2_3.a)
475 SELECT * FROM prt1 t1 LEFT JOIN LATERAL
476 (SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
477 ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a;
478 a | b | c | t2a | t3a | least
479 -----+---+------+-----+-----+-------
480 0 | 0 | 0000 | 0 | 0 | 0
483 150 | 0 | 0150 | 150 | 0 | 150
486 300 | 0 | 0300 | 300 | 0 | 300
489 450 | 0 | 0450 | 450 | 0 | 450
495 SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
496 (SELECT t2.a AS t2a, t3.a AS t3a, t2.b t2b, t2.c t2c, least(t1.a,t2.a,t3.b) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
497 ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a;
499 --------------------------------------------------------------
503 Hash Cond: ((t1.c)::text = (t2.c)::text)
504 Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
506 -> Seq Scan on prt1_p1 t1_1
507 -> Seq Scan on prt1_p2 t1_2
508 -> Seq Scan on prt1_p3 t1_3
512 Hash Cond: (t2_1.a = t3_1.b)
513 -> Seq Scan on prt1_p1 t2_1
515 -> Seq Scan on prt2_p1 t3_1
517 Hash Cond: (t2_2.a = t3_2.b)
518 -> Seq Scan on prt1_p2 t2_2
520 -> Seq Scan on prt2_p2 t3_2
522 Hash Cond: (t2_3.a = t3_3.b)
523 -> Seq Scan on prt1_p3 t2_3
525 -> Seq Scan on prt2_p3 t3_3
528 SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
529 (SELECT t2.a AS t2a, t3.a AS t3a, t2.b t2b, t2.c t2c, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
530 ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a;
547 -- lateral reference in sample scan
549 SELECT * FROM prt1 t1 JOIN LATERAL
550 (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
553 -------------------------------------------------------------
556 -> Seq Scan on prt1_p1 t1_1
557 -> Sample Scan on prt1_p1 t2_1
558 Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
561 -> Seq Scan on prt1_p2 t1_2
562 -> Sample Scan on prt1_p2 t2_2
563 Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
566 -> Seq Scan on prt1_p3 t1_3
567 -> Sample Scan on prt1_p3 t2_3
568 Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
572 -- lateral reference in scan's restriction clauses
574 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
575 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
576 ON t1.a = s.b WHERE s.t1b = s.a;
578 ---------------------------------------------------------------
582 -> Seq Scan on prt1_p1 t1_1
583 -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
584 Index Cond: (b = t1_1.a)
587 -> Seq Scan on prt1_p2 t1_2
588 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
589 Index Cond: (b = t1_2.a)
592 -> Seq Scan on prt1_p3 t1_3
593 -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
594 Index Cond: (b = t1_3.a)
598 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
599 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
600 ON t1.a = s.b WHERE s.t1b = s.a;
607 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
608 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
609 ON t1.a = s.b WHERE s.t1b = s.b;
611 --------------------------------------------------------------------
615 -> Seq Scan on prt1_p1 t1_1
616 -> Index Only Scan using iprt2_p1_b on prt2_p1 t2_1
617 Index Cond: (b = t1_1.a)
620 -> Seq Scan on prt1_p2 t1_2
621 -> Index Only Scan using iprt2_p2_b on prt2_p2 t2_2
622 Index Cond: (b = t1_2.a)
625 -> Seq Scan on prt1_p3 t1_3
626 -> Index Only Scan using iprt2_p3_b on prt2_p3 t2_3
627 Index Cond: (b = t1_3.a)
631 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
632 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
633 ON t1.a = s.b WHERE s.t1b = s.b;
639 -- bug with inadequate sort key representation
640 SET enable_partitionwise_aggregate TO true;
641 SET enable_hashjoin TO false;
643 SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
644 WHERE a BETWEEN 490 AND 510
645 GROUP BY 1, 2 ORDER BY 1, 2;
647 -----------------------------------------------------------------------------------------------------------------
649 Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
651 Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
653 Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
655 Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
657 Merge Cond: ((prt1.a = p2.a) AND (prt1.b = p2.b))
658 Filter: ((COALESCE(prt1.a, p2.a) >= 490) AND (COALESCE(prt1.a, p2.a) <= 510))
660 Sort Key: prt1.a, prt1.b
661 -> Seq Scan on prt1_p1 prt1
664 -> Seq Scan on prt2_p1 p2
666 Group Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
668 Sort Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
670 Merge Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
671 Filter: ((COALESCE(prt1_1.a, p2_1.a) >= 490) AND (COALESCE(prt1_1.a, p2_1.a) <= 510))
673 Sort Key: prt1_1.a, prt1_1.b
674 -> Seq Scan on prt1_p2 prt1_1
676 Sort Key: p2_1.a, p2_1.b
677 -> Seq Scan on prt2_p2 p2_1
679 Group Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
681 Sort Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
683 Merge Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
684 Filter: ((COALESCE(prt1_2.a, p2_2.a) >= 490) AND (COALESCE(prt1_2.a, p2_2.a) <= 510))
686 Sort Key: prt1_2.a, prt1_2.b
687 -> Seq Scan on prt1_p3 prt1_2
689 Sort Key: p2_2.a, p2_2.b
690 -> Seq Scan on prt2_p3 p2_2
693 SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
694 WHERE a BETWEEN 490 AND 510
695 GROUP BY 1, 2 ORDER BY 1, 2;
714 RESET enable_partitionwise_aggregate;
715 RESET enable_hashjoin;
717 -- partitioned by expression
719 CREATE TABLE prt1_e (a int, b int, c int) PARTITION BY RANGE(((a + b)/2));
720 CREATE TABLE prt1_e_p1 PARTITION OF prt1_e FOR VALUES FROM (0) TO (250);
721 CREATE TABLE prt1_e_p2 PARTITION OF prt1_e FOR VALUES FROM (250) TO (500);
722 CREATE TABLE prt1_e_p3 PARTITION OF prt1_e FOR VALUES FROM (500) TO (600);
723 INSERT INTO prt1_e SELECT i, i, i % 25 FROM generate_series(0, 599, 2) i;
724 CREATE INDEX iprt1_e_p1_ab2 on prt1_e_p1(((a+b)/2));
725 CREATE INDEX iprt1_e_p2_ab2 on prt1_e_p2(((a+b)/2));
726 CREATE INDEX iprt1_e_p3_ab2 on prt1_e_p3(((a+b)/2));
728 CREATE TABLE prt2_e (a int, b int, c int) PARTITION BY RANGE(((b + a)/2));
729 CREATE TABLE prt2_e_p1 PARTITION OF prt2_e FOR VALUES FROM (0) TO (250);
730 CREATE TABLE prt2_e_p2 PARTITION OF prt2_e FOR VALUES FROM (250) TO (500);
731 CREATE TABLE prt2_e_p3 PARTITION OF prt2_e FOR VALUES FROM (500) TO (600);
732 INSERT INTO prt2_e SELECT i, i, i % 25 FROM generate_series(0, 599, 3) i;
735 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 = (t2.b + t2.a)/2 AND t1.c = 0 ORDER BY t1.a, t2.b;
737 ------------------------------------------------------------------------------
742 Hash Cond: (((t2_1.b + t2_1.a) / 2) = ((t1_1.a + t1_1.b) / 2))
743 -> Seq Scan on prt2_e_p1 t2_1
745 -> Seq Scan on prt1_e_p1 t1_1
748 Hash Cond: (((t2_2.b + t2_2.a) / 2) = ((t1_2.a + t1_2.b) / 2))
749 -> Seq Scan on prt2_e_p2 t2_2
751 -> Seq Scan on prt1_e_p2 t1_2
754 Hash Cond: (((t2_3.b + t2_3.a) / 2) = ((t1_3.a + t1_3.b) / 2))
755 -> Seq Scan on prt2_e_p3 t2_3
757 -> Seq Scan on prt1_e_p3 t1_3
761 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 = (t2.b + t2.a)/2 AND t1.c = 0 ORDER BY t1.a, t2.b;
774 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
776 ---------------------------------------------------------------------
781 Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
783 Hash Cond: (t2_1.b = t1_1.a)
784 -> Seq Scan on prt2_p1 t2_1
786 -> Seq Scan on prt1_p1 t1_1
788 -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3_1
789 Index Cond: (((a + b) / 2) = t2_1.b)
791 Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
793 Hash Cond: (t2_2.b = t1_2.a)
794 -> Seq Scan on prt2_p2 t2_2
796 -> Seq Scan on prt1_p2 t1_2
798 -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_2
799 Index Cond: (((a + b) / 2) = t2_2.b)
801 Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
803 Hash Cond: (t2_3.b = t1_3.a)
804 -> Seq Scan on prt2_p3 t2_3
806 -> Seq Scan on prt1_p3 t1_3
808 -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3
809 Index Cond: (((a + b) / 2) = t2_3.b)
812 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM prt1 t1, prt2 t2, prt1_e t3 WHERE t1.a = t2.b AND t1.a = (t3.a + t3.b)/2 AND t1.b = 0 ORDER BY t1.a, t2.b;
813 a | c | b | c | ?column? | c
814 -----+------+-----+------+----------+---
815 0 | 0000 | 0 | 0000 | 0 | 0
816 150 | 0150 | 150 | 0150 | 300 | 0
817 300 | 0300 | 300 | 0300 | 600 | 0
818 450 | 0450 | 450 | 0450 | 900 | 0
822 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
824 --------------------------------------------------------------
826 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
829 Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
830 -> Seq Scan on prt1_e_p1 t3_1
833 Hash Cond: (t2_1.b = t1_1.a)
834 -> Seq Scan on prt2_p1 t2_1
836 -> Seq Scan on prt1_p1 t1_1
839 Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
840 -> Seq Scan on prt1_e_p2 t3_2
843 Hash Cond: (t2_2.b = t1_2.a)
844 -> Seq Scan on prt2_p2 t2_2
846 -> Seq Scan on prt1_p2 t1_2
849 Hash Cond: (((t3_3.a + t3_3.b) / 2) = t1_3.a)
850 -> Seq Scan on prt1_e_p3 t3_3
853 Hash Cond: (t2_3.b = t1_3.a)
854 -> Seq Scan on prt2_p3 t2_3
856 -> Seq Scan on prt1_p3 t1_3
860 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) LEFT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
861 a | c | b | c | ?column? | c
862 -----+------+-----+------+----------+---
863 0 | 0000 | 0 | 0000 | 0 | 0
864 50 | 0050 | | | 100 | 0
865 100 | 0100 | | | 200 | 0
866 150 | 0150 | 150 | 0150 | 300 | 0
867 200 | 0200 | | | 400 | 0
868 250 | 0250 | | | 500 | 0
869 300 | 0300 | 300 | 0300 | 600 | 0
870 350 | 0350 | | | 700 | 0
871 400 | 0400 | | | 800 | 0
872 450 | 0450 | 450 | 0450 | 900 | 0
873 500 | 0500 | | | 1000 | 0
874 550 | 0550 | | | 1100 | 0
878 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
880 -------------------------------------------------------------------
882 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
884 -> Nested Loop Left Join
886 Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
887 -> Seq Scan on prt1_p1 t1_1
889 -> Seq Scan on prt1_e_p1 t3_1
891 -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
892 Index Cond: (b = t1_1.a)
893 -> Nested Loop Left Join
895 Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
896 -> Seq Scan on prt1_p2 t1_2
898 -> Seq Scan on prt1_e_p2 t3_2
900 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
901 Index Cond: (b = t1_2.a)
902 -> Nested Loop Left Join
904 Hash Cond: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
905 -> Seq Scan on prt1_p3 t1_3
907 -> Seq Scan on prt1_e_p3 t3_3
909 -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
910 Index Cond: (b = t1_3.a)
913 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
914 a | c | b | c | ?column? | c
915 -----+------+-----+------+----------+---
916 0 | 0000 | 0 | 0000 | 0 | 0
917 50 | 0050 | | | 100 | 0
918 100 | 0100 | | | 200 | 0
919 150 | 0150 | 150 | 0150 | 300 | 0
920 200 | 0200 | | | 400 | 0
921 250 | 0250 | | | 500 | 0
922 300 | 0300 | 300 | 0300 | 600 | 0
923 350 | 0350 | | | 700 | 0
924 400 | 0400 | | | 800 | 0
925 450 | 0450 | 450 | 0450 | 900 | 0
926 500 | 0500 | | | 1000 | 0
927 550 | 0550 | | | 1100 | 0
934 SELECT COUNT(*) FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b) FULL JOIN prt2 p3(b,a,c) USING (a, b)
935 WHERE a BETWEEN 490 AND 510;
937 -----------------------------------------------------------------------------------------------------------------------------------------
941 Hash Cond: ((COALESCE(prt1_1.a, p2_1.a) = p3_1.a) AND (COALESCE(prt1_1.b, p2_1.b) = p3_1.b))
942 Filter: ((COALESCE(COALESCE(prt1_1.a, p2_1.a), p3_1.a) >= 490) AND (COALESCE(COALESCE(prt1_1.a, p2_1.a), p3_1.a) <= 510))
944 Hash Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
945 -> Seq Scan on prt1_p1 prt1_1
947 -> Seq Scan on prt2_p1 p2_1
949 -> Seq Scan on prt2_p1 p3_1
951 Hash Cond: ((COALESCE(prt1_2.a, p2_2.a) = p3_2.a) AND (COALESCE(prt1_2.b, p2_2.b) = p3_2.b))
952 Filter: ((COALESCE(COALESCE(prt1_2.a, p2_2.a), p3_2.a) >= 490) AND (COALESCE(COALESCE(prt1_2.a, p2_2.a), p3_2.a) <= 510))
954 Hash Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
955 -> Seq Scan on prt1_p2 prt1_2
957 -> Seq Scan on prt2_p2 p2_2
959 -> Seq Scan on prt2_p2 p3_2
961 Hash Cond: ((COALESCE(prt1_3.a, p2_3.a) = p3_3.a) AND (COALESCE(prt1_3.b, p2_3.b) = p3_3.b))
962 Filter: ((COALESCE(COALESCE(prt1_3.a, p2_3.a), p3_3.a) >= 490) AND (COALESCE(COALESCE(prt1_3.a, p2_3.a), p3_3.a) <= 510))
964 Hash Cond: ((prt1_3.a = p2_3.a) AND (prt1_3.b = p2_3.b))
965 -> Seq Scan on prt1_p3 prt1_3
967 -> Seq Scan on prt2_p3 p2_3
969 -> Seq Scan on prt2_p3 p3_3
972 SELECT COUNT(*) FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b) FULL JOIN prt2 p3(b,a,c) USING (a, b)
973 WHERE a BETWEEN 490 AND 510;
983 SELECT COUNT(*) FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b) FULL JOIN prt2 p3(b,a,c) USING (a, b) FULL JOIN prt1 p4 (a,b,c) USING (a, b)
984 WHERE a BETWEEN 490 AND 510;
986 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
990 Hash Cond: ((COALESCE(COALESCE(prt1_1.a, p2_1.a), p3_1.a) = p4_1.a) AND (COALESCE(COALESCE(prt1_1.b, p2_1.b), p3_1.b) = p4_1.b))
991 Filter: ((COALESCE(COALESCE(COALESCE(prt1_1.a, p2_1.a), p3_1.a), p4_1.a) >= 490) AND (COALESCE(COALESCE(COALESCE(prt1_1.a, p2_1.a), p3_1.a), p4_1.a) <= 510))
993 Hash Cond: ((COALESCE(prt1_1.a, p2_1.a) = p3_1.a) AND (COALESCE(prt1_1.b, p2_1.b) = p3_1.b))
995 Hash Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
996 -> Seq Scan on prt1_p1 prt1_1
998 -> Seq Scan on prt2_p1 p2_1
1000 -> Seq Scan on prt2_p1 p3_1
1002 -> Seq Scan on prt1_p1 p4_1
1004 Hash Cond: ((COALESCE(COALESCE(prt1_2.a, p2_2.a), p3_2.a) = p4_2.a) AND (COALESCE(COALESCE(prt1_2.b, p2_2.b), p3_2.b) = p4_2.b))
1005 Filter: ((COALESCE(COALESCE(COALESCE(prt1_2.a, p2_2.a), p3_2.a), p4_2.a) >= 490) AND (COALESCE(COALESCE(COALESCE(prt1_2.a, p2_2.a), p3_2.a), p4_2.a) <= 510))
1007 Hash Cond: ((COALESCE(prt1_2.a, p2_2.a) = p3_2.a) AND (COALESCE(prt1_2.b, p2_2.b) = p3_2.b))
1009 Hash Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
1010 -> Seq Scan on prt1_p2 prt1_2
1012 -> Seq Scan on prt2_p2 p2_2
1014 -> Seq Scan on prt2_p2 p3_2
1016 -> Seq Scan on prt1_p2 p4_2
1018 Hash Cond: ((COALESCE(COALESCE(prt1_3.a, p2_3.a), p3_3.a) = p4_3.a) AND (COALESCE(COALESCE(prt1_3.b, p2_3.b), p3_3.b) = p4_3.b))
1019 Filter: ((COALESCE(COALESCE(COALESCE(prt1_3.a, p2_3.a), p3_3.a), p4_3.a) >= 490) AND (COALESCE(COALESCE(COALESCE(prt1_3.a, p2_3.a), p3_3.a), p4_3.a) <= 510))
1021 Hash Cond: ((COALESCE(prt1_3.a, p2_3.a) = p3_3.a) AND (COALESCE(prt1_3.b, p2_3.b) = p3_3.b))
1023 Hash Cond: ((prt1_3.a = p2_3.a) AND (prt1_3.b = p2_3.b))
1024 -> Seq Scan on prt1_p3 prt1_3
1026 -> Seq Scan on prt2_p3 p2_3
1028 -> Seq Scan on prt2_p3 p3_3
1030 -> Seq Scan on prt1_p3 p4_3
1033 SELECT COUNT(*) FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b) FULL JOIN prt2 p3(b,a,c) USING (a, b) FULL JOIN prt1 p4 (a,b,c) USING (a, b)
1034 WHERE a BETWEEN 490 AND 510;
1040 -- Cases with non-nullable expressions in subquery results;
1041 -- make sure these go to null as expected
1043 SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b;
1045 ------------------------------------------------------------------------------------------------------------
1047 Sort Key: prt1.a, prt2.b, ((prt1_e.a + prt1_e.b))
1050 Hash Cond: (prt1_1.a = ((prt1_e_1.a + prt1_e_1.b) / 2))
1051 Filter: ((prt1_1.a = (50)) OR (prt2_1.b = (75)) OR (((prt1_e_1.a + prt1_e_1.b) / 2) = (50)))
1053 Hash Cond: (prt1_1.a = prt2_1.b)
1054 -> Seq Scan on prt1_p1 prt1_1
1057 -> Seq Scan on prt2_p1 prt2_1
1060 -> Seq Scan on prt1_e_p1 prt1_e_1
1063 Hash Cond: (prt1_2.a = ((prt1_e_2.a + prt1_e_2.b) / 2))
1064 Filter: ((prt1_2.a = (50)) OR (prt2_2.b = (75)) OR (((prt1_e_2.a + prt1_e_2.b) / 2) = (50)))
1066 Hash Cond: (prt1_2.a = prt2_2.b)
1067 -> Seq Scan on prt1_p2 prt1_2
1070 -> Seq Scan on prt2_p2 prt2_2
1073 -> Seq Scan on prt1_e_p2 prt1_e_2
1076 Hash Cond: (prt1_3.a = ((prt1_e_3.a + prt1_e_3.b) / 2))
1077 Filter: ((prt1_3.a = (50)) OR (prt2_3.b = (75)) OR (((prt1_e_3.a + prt1_e_3.b) / 2) = (50)))
1079 Hash Cond: (prt1_3.a = prt2_3.b)
1080 -> Seq Scan on prt1_p3 prt1_3
1083 -> Seq Scan on prt2_p3 prt2_3
1086 -> Seq Scan on prt1_e_p3 prt1_e_3
1090 SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL JOIN (SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE prt1_e.c = 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY t1.a, t2.b, t3.a + t3.b;
1091 a | phv | b | phv | ?column? | phv
1092 ----+-----+----+-----+----------+-----
1093 50 | 50 | | | 100 | 50
1099 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a;
1101 ---------------------------------------------------------------------------------
1106 Join Filter: (t1_2.a = t1_5.b)
1110 Hash Cond: (((t2_1.a + t2_1.b) / 2) = t1_5.b)
1111 -> Seq Scan on prt1_e_p1 t2_1
1113 -> Seq Scan on prt2_p1 t1_5
1115 -> Index Scan using iprt1_p1_a on prt1_p1 t1_2
1116 Index Cond: (a = ((t2_1.a + t2_1.b) / 2))
1119 Join Filter: (t1_3.a = t1_6.b)
1123 Hash Cond: (((t2_2.a + t2_2.b) / 2) = t1_6.b)
1124 -> Seq Scan on prt1_e_p2 t2_2
1126 -> Seq Scan on prt2_p2 t1_6
1128 -> Index Scan using iprt1_p2_a on prt1_p2 t1_3
1129 Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
1132 Join Filter: (t1_4.a = t1_7.b)
1136 -> Seq Scan on prt2_p3 t1_7
1138 -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t2_3
1139 Index Cond: (((a + b) / 2) = t1_7.b)
1140 -> Index Scan using iprt1_p3_a on prt1_p3 t1_4
1141 Index Cond: (a = ((t2_3.a + t2_3.b) / 2))
1145 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHERE t1.a = 0 AND t1.b = (t2.a + t2.b)/2) AND t1.b = 0 ORDER BY t1.a;
1155 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
1157 ---------------------------------------------------------------------------
1165 Hash Cond: (t1_6.b = ((t1_9.a + t1_9.b) / 2))
1166 -> Seq Scan on prt2_p1 t1_6
1168 -> Seq Scan on prt1_e_p1 t1_9
1170 -> Index Scan using iprt1_p1_a on prt1_p1 t1_3
1171 Index Cond: (a = t1_6.b)
1177 Hash Cond: (t1_7.b = ((t1_10.a + t1_10.b) / 2))
1178 -> Seq Scan on prt2_p2 t1_7
1180 -> Seq Scan on prt1_e_p2 t1_10
1182 -> Index Scan using iprt1_p2_a on prt1_p2 t1_4
1183 Index Cond: (a = t1_7.b)
1189 Hash Cond: (t1_8.b = ((t1_11.a + t1_11.b) / 2))
1190 -> Seq Scan on prt2_p3 t1_8
1192 -> Seq Scan on prt1_e_p3 t1_11
1194 -> Index Scan using iprt1_p3_a on prt1_p3 t1_5
1195 Index Cond: (a = t1_8.b)
1199 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
1209 SET enable_hashjoin TO off;
1210 SET enable_nestloop TO off;
1212 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
1214 ------------------------------------------------------------------
1218 Merge Cond: (t1_3.a = t1_6.b)
1221 -> Seq Scan on prt1_p1 t1_3
1224 Merge Cond: (t1_6.b = (((t1_9.a + t1_9.b) / 2)))
1227 -> Seq Scan on prt2_p1 t1_6
1229 Sort Key: (((t1_9.a + t1_9.b) / 2))
1230 -> Seq Scan on prt1_e_p1 t1_9
1233 Merge Cond: (t1_4.a = t1_7.b)
1236 -> Seq Scan on prt1_p2 t1_4
1239 Merge Cond: (t1_7.b = (((t1_10.a + t1_10.b) / 2)))
1242 -> Seq Scan on prt2_p2 t1_7
1244 Sort Key: (((t1_10.a + t1_10.b) / 2))
1245 -> Seq Scan on prt1_e_p2 t1_10
1248 Merge Cond: (t1_5.a = t1_8.b)
1251 -> Seq Scan on prt1_p3 t1_5
1254 Merge Cond: (t1_8.b = (((t1_11.a + t1_11.b) / 2)))
1257 -> Seq Scan on prt2_p3 t1_8
1259 Sort Key: (((t1_11.a + t1_11.b) / 2))
1260 -> Seq Scan on prt1_e_p3 t1_11
1264 SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (SELECT (t1.a + t1.b)/2 FROM prt1_e t1 WHERE t1.c = 0)) AND t1.b = 0 ORDER BY t1.a;
1274 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
1276 ----------------------------------------------------------------------------
1278 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
1281 Merge Cond: (t1_1.a = t2_1.b)
1285 Merge Cond: ((((t3_1.a + t3_1.b) / 2)) = t1_1.a)
1287 Sort Key: (((t3_1.a + t3_1.b) / 2))
1288 -> Seq Scan on prt1_e_p1 t3_1
1292 -> Seq Scan on prt1_p1 t1_1
1295 -> Seq Scan on prt2_p1 t2_1
1297 Merge Cond: (t1_2.a = t2_2.b)
1301 Merge Cond: ((((t3_2.a + t3_2.b) / 2)) = t1_2.a)
1303 Sort Key: (((t3_2.a + t3_2.b) / 2))
1304 -> Seq Scan on prt1_e_p2 t3_2
1308 -> Seq Scan on prt1_p2 t1_2
1311 -> Seq Scan on prt2_p2 t2_2
1313 Merge Cond: (t1_3.a = t2_3.b)
1317 Merge Cond: ((((t3_3.a + t3_3.b) / 2)) = t1_3.a)
1319 Sort Key: (((t3_3.a + t3_3.b) / 2))
1320 -> Seq Scan on prt1_e_p3 t3_3
1324 -> Seq Scan on prt1_p3 t1_3
1327 -> Seq Scan on prt2_p3 t2_3
1330 SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t3.c = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
1331 a | c | b | c | ?column? | c
1332 -----+------+-----+------+----------+---
1333 0 | 0000 | 0 | 0000 | 0 | 0
1334 50 | 0050 | | | 100 | 0
1335 100 | 0100 | | | 200 | 0
1336 150 | 0150 | 150 | 0150 | 300 | 0
1337 200 | 0200 | | | 400 | 0
1338 250 | 0250 | | | 500 | 0
1339 300 | 0300 | 300 | 0300 | 600 | 0
1340 350 | 0350 | | | 700 | 0
1341 400 | 0400 | | | 800 | 0
1342 450 | 0450 | 450 | 0450 | 900 | 0
1343 500 | 0500 | | | 1000 | 0
1344 550 | 0550 | | | 1100 | 0
1347 -- MergeAppend on nullable column
1348 -- This should generate a partitionwise join, but currently fails to
1350 SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
1352 -----------------------------------------------------------
1354 Sort Key: prt1.a, prt2.b
1355 Presorted Key: prt1.a
1357 Merge Cond: (prt1.a = prt2.b)
1361 -> Seq Scan on prt1_p1 prt1_1
1362 Filter: ((a < 450) AND (b = 0))
1363 -> Seq Scan on prt1_p2 prt1_2
1364 Filter: ((a < 450) AND (b = 0))
1368 -> Seq Scan on prt2_p2 prt2_1
1370 -> Seq Scan on prt2_p3 prt2_2
1374 SELECT t1.a, t2.b FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
1388 -- merge join when expression with whole-row reference needs to be sorted;
1389 -- partitionwise join does not apply
1391 SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
1393 -----------------------------------------------------------------------------------------
1395 Merge Cond: ((t1.a = t2.b) AND (((((t1.*)::prt1))::text) = ((((t2.*)::prt2))::text)))
1397 Sort Key: t1.a, ((((t1.*)::prt1))::text)
1400 -> Seq Scan on prt1_p1 t1_1
1401 -> Seq Scan on prt1_p2 t1_2
1402 -> Seq Scan on prt1_p3 t1_3
1404 Sort Key: t2.b, ((((t2.*)::prt2))::text)
1407 -> Seq Scan on prt2_p1 t2_1
1408 -> Seq Scan on prt2_p2 t2_2
1409 -> Seq Scan on prt2_p3 t2_3
1412 SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
1422 RESET enable_hashjoin;
1423 RESET enable_nestloop;
1425 -- partitioned by multiple columns
1427 CREATE TABLE prt1_m (a int, b int, c int) PARTITION BY RANGE(a, ((a + b)/2));
1428 CREATE TABLE prt1_m_p1 PARTITION OF prt1_m FOR VALUES FROM (0, 0) TO (250, 250);
1429 CREATE TABLE prt1_m_p2 PARTITION OF prt1_m FOR VALUES FROM (250, 250) TO (500, 500);
1430 CREATE TABLE prt1_m_p3 PARTITION OF prt1_m FOR VALUES FROM (500, 500) TO (600, 600);
1431 INSERT INTO prt1_m SELECT i, i, i % 25 FROM generate_series(0, 599, 2) i;
1433 CREATE TABLE prt2_m (a int, b int, c int) PARTITION BY RANGE(((b + a)/2), b);
1434 CREATE TABLE prt2_m_p1 PARTITION OF prt2_m FOR VALUES FROM (0, 0) TO (250, 250);
1435 CREATE TABLE prt2_m_p2 PARTITION OF prt2_m FOR VALUES FROM (250, 250) TO (500, 500);
1436 CREATE TABLE prt2_m_p3 PARTITION OF prt2_m FOR VALUES FROM (500, 500) TO (600, 600);
1437 INSERT INTO prt2_m SELECT i, i, i % 25 FROM generate_series(0, 599, 3) i;
1440 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_m WHERE prt1_m.c = 0) t1 FULL JOIN (SELECT * FROM prt2_m WHERE prt2_m.c = 0) t2 ON (t1.a = (t2.b + t2.a)/2 AND t2.b = (t1.a + t1.b)/2) ORDER BY t1.a, t2.b;
1442 ------------------------------------------------------------------------------------------------------------------------------
1444 Sort Key: prt1_m.a, prt2_m.b
1447 Hash Cond: ((prt1_m_1.a = ((prt2_m_1.b + prt2_m_1.a) / 2)) AND (((prt1_m_1.a + prt1_m_1.b) / 2) = prt2_m_1.b))
1448 -> Seq Scan on prt1_m_p1 prt1_m_1
1451 -> Seq Scan on prt2_m_p1 prt2_m_1
1454 Hash Cond: ((prt1_m_2.a = ((prt2_m_2.b + prt2_m_2.a) / 2)) AND (((prt1_m_2.a + prt1_m_2.b) / 2) = prt2_m_2.b))
1455 -> Seq Scan on prt1_m_p2 prt1_m_2
1458 -> Seq Scan on prt2_m_p2 prt2_m_2
1461 Hash Cond: ((prt1_m_3.a = ((prt2_m_3.b + prt2_m_3.a) / 2)) AND (((prt1_m_3.a + prt1_m_3.b) / 2) = prt2_m_3.b))
1462 -> Seq Scan on prt1_m_p3 prt1_m_3
1465 -> Seq Scan on prt2_m_p3 prt2_m_3
1469 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_m WHERE prt1_m.c = 0) t1 FULL JOIN (SELECT * FROM prt2_m WHERE prt2_m.c = 0) t2 ON (t1.a = (t2.b + t2.a)/2 AND t2.b = (t1.a + t1.b)/2) ORDER BY t1.a, t2.b;
1491 -- tests for list partitioned tables.
1493 CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
1494 CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0003', '0004', '0010');
1495 CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0001', '0005', '0002', '0009');
1496 CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007', '0008', '0011');
1497 INSERT INTO plt1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
1499 CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
1500 CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0000', '0003', '0004', '0010');
1501 CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0001', '0005', '0002', '0009');
1502 CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0011');
1503 INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
1506 -- list partitioned by expression
1508 CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY LIST(ltrim(c, 'A'));
1509 CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0000', '0003', '0004', '0010');
1510 CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0001', '0005', '0002', '0009');
1511 CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0006', '0007', '0008', '0011');
1512 INSERT INTO plt1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
1514 -- test partition matching with N-way join
1516 SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1518 --------------------------------------------------------------------------------
1520 Group Key: t1.c, t3.c
1522 Sort Key: t1.c, t3.c
1525 Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
1527 Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1528 -> Seq Scan on plt1_p1 t1_1
1530 -> Seq Scan on plt2_p1 t2_1
1532 -> Seq Scan on plt1_e_p1 t3_1
1534 Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
1536 Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1537 -> Seq Scan on plt1_p2 t1_2
1539 -> Seq Scan on plt2_p2 t2_2
1541 -> Seq Scan on plt1_e_p2 t3_2
1543 Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
1545 Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
1546 -> Seq Scan on plt1_p3 t1_3
1548 -> Seq Scan on plt2_p3 t2_3
1550 -> Seq Scan on plt1_e_p3 t3_3
1553 SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, plt2 t2, plt1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1554 avg | avg | avg | c | c | c
1555 ----------------------+----------------------+-----------------------+------+------+-------
1556 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
1557 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1558 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
1559 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
1560 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1561 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
1562 324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
1563 375.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
1564 423.0000000000000000 | 423.0000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
1565 474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
1566 525.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
1567 573.0000000000000000 | 573.0000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
1570 -- joins where one of the relations is proven empty
1572 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a = 1 AND t1.a = 2;
1574 --------------------------
1576 One-Time Filter: false
1580 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 LEFT JOIN prt2 t2 ON t1.a = t2.b;
1582 --------------------------
1584 One-Time Filter: false
1588 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b, prt1 t3 WHERE t2.b = t3.a;
1590 --------------------------------------------------
1592 Hash Cond: (t2.b = a)
1595 Hash Cond: (t3_1.a = t2_1.b)
1596 -> Seq Scan on prt1_p1 t3_1
1598 -> Seq Scan on prt2_p1 t2_1
1600 Hash Cond: (t3_2.a = t2_2.b)
1601 -> Seq Scan on prt1_p2 t3_2
1603 -> Seq Scan on prt2_p2 t2_2
1605 Hash Cond: (t3_3.a = t2_3.b)
1606 -> Seq Scan on prt1_p3 t3_3
1608 -> Seq Scan on prt2_p3 t2_3
1611 One-Time Filter: false
1615 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1 FULL JOIN prt2 t2 ON t1.a = t2.b WHERE t2.a = 0 ORDER BY t1.a, t2.b;
1617 --------------------------------------------
1621 Hash Cond: (t2.b = a)
1623 -> Seq Scan on prt2_p1 t2_1
1625 -> Seq Scan on prt2_p2 t2_2
1627 -> Seq Scan on prt2_p3 t2_3
1631 One-Time Filter: false
1635 -- tests for hash partitioned tables.
1637 CREATE TABLE pht1 (a int, b int, c text) PARTITION BY HASH(c);
1638 CREATE TABLE pht1_p1 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
1639 CREATE TABLE pht1_p2 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
1640 CREATE TABLE pht1_p3 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
1641 INSERT INTO pht1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
1643 CREATE TABLE pht2 (a int, b int, c text) PARTITION BY HASH(c);
1644 CREATE TABLE pht2_p1 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
1645 CREATE TABLE pht2_p2 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
1646 CREATE TABLE pht2_p3 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
1647 INSERT INTO pht2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
1650 -- hash partitioned by expression
1652 CREATE TABLE pht1_e (a int, b int, c text) PARTITION BY HASH(ltrim(c, 'A'));
1653 CREATE TABLE pht1_e_p1 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 0);
1654 CREATE TABLE pht1_e_p2 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 1);
1655 CREATE TABLE pht1_e_p3 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 2);
1656 INSERT INTO pht1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 299, 2) i;
1658 -- test partition matching with N-way join
1660 SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1662 --------------------------------------------------------------------------------
1664 Group Key: t1.c, t3.c
1666 Sort Key: t1.c, t3.c
1669 Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
1671 Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1672 -> Seq Scan on pht1_p1 t1_1
1674 -> Seq Scan on pht2_p1 t2_1
1676 -> Seq Scan on pht1_e_p1 t3_1
1678 Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
1680 Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1681 -> Seq Scan on pht1_p2 t1_2
1683 -> Seq Scan on pht2_p2 t2_2
1685 -> Seq Scan on pht1_e_p2 t3_2
1687 Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
1689 Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
1690 -> Seq Scan on pht1_p3 t1_3
1692 -> Seq Scan on pht2_p3 t2_3
1694 -> Seq Scan on pht1_e_p3 t3_3
1697 SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1, pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') = t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
1698 avg | avg | avg | c | c | c
1699 ----------------------+----------------------+----------------------+------+------+-------
1700 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
1701 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1702 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
1703 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
1704 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1705 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
1708 -- test default partition behavior for range
1709 ALTER TABLE prt1 DETACH PARTITION prt1_p3;
1710 ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
1712 ALTER TABLE prt2 DETACH PARTITION prt2_p3;
1713 ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
1716 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
1718 --------------------------------------------------
1723 Hash Cond: (t2_1.b = t1_1.a)
1724 -> Seq Scan on prt2_p1 t2_1
1726 -> Seq Scan on prt1_p1 t1_1
1729 Hash Cond: (t2_2.b = t1_2.a)
1730 -> Seq Scan on prt2_p2 t2_2
1732 -> Seq Scan on prt1_p2 t1_2
1735 Hash Cond: (t2_3.b = t1_3.a)
1736 -> Seq Scan on prt2_p3 t2_3
1738 -> Seq Scan on prt1_p3 t1_3
1742 -- test default partition behavior for list
1743 ALTER TABLE plt1 DETACH PARTITION plt1_p3;
1744 ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
1746 ALTER TABLE plt2 DETACH PARTITION plt2_p3;
1747 ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
1750 SELECT avg(t1.a), avg(t2.b), t1.c, t2.c FROM plt1 t1 RIGHT JOIN plt2 t2 ON t1.c = t2.c WHERE t1.a % 25 = 0 GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
1752 --------------------------------------------------------
1759 Hash Cond: (t2_1.c = t1_1.c)
1760 -> Seq Scan on plt2_p1 t2_1
1762 -> Seq Scan on plt1_p1 t1_1
1763 Filter: ((a % 25) = 0)
1765 Hash Cond: (t2_2.c = t1_2.c)
1766 -> Seq Scan on plt2_p2 t2_2
1768 -> Seq Scan on plt1_p2 t1_2
1769 Filter: ((a % 25) = 0)
1771 Hash Cond: (t2_3.c = t1_3.c)
1772 -> Seq Scan on plt2_p3 t2_3
1774 -> Seq Scan on plt1_p3 t1_3
1775 Filter: ((a % 25) = 0)
1779 -- multiple levels of partitioning
1781 CREATE TABLE prt1_l (a int, b int, c varchar) PARTITION BY RANGE(a);
1782 CREATE TABLE prt1_l_p1 PARTITION OF prt1_l FOR VALUES FROM (0) TO (250);
1783 CREATE TABLE prt1_l_p2 PARTITION OF prt1_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c);
1784 CREATE TABLE prt1_l_p2_p1 PARTITION OF prt1_l_p2 FOR VALUES IN ('0000', '0001');
1785 CREATE TABLE prt1_l_p2_p2 PARTITION OF prt1_l_p2 FOR VALUES IN ('0002', '0003');
1786 CREATE TABLE prt1_l_p3 PARTITION OF prt1_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (b);
1787 CREATE TABLE prt1_l_p3_p1 PARTITION OF prt1_l_p3 FOR VALUES FROM (0) TO (13);
1788 CREATE TABLE prt1_l_p3_p2 PARTITION OF prt1_l_p3 FOR VALUES FROM (13) TO (25);
1789 INSERT INTO prt1_l SELECT i, i % 25, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 2) i;
1791 CREATE TABLE prt2_l (a int, b int, c varchar) PARTITION BY RANGE(b);
1792 CREATE TABLE prt2_l_p1 PARTITION OF prt2_l FOR VALUES FROM (0) TO (250);
1793 CREATE TABLE prt2_l_p2 PARTITION OF prt2_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c);
1794 CREATE TABLE prt2_l_p2_p1 PARTITION OF prt2_l_p2 FOR VALUES IN ('0000', '0001');
1795 CREATE TABLE prt2_l_p2_p2 PARTITION OF prt2_l_p2 FOR VALUES IN ('0002', '0003');
1796 CREATE TABLE prt2_l_p3 PARTITION OF prt2_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (a);
1797 CREATE TABLE prt2_l_p3_p1 PARTITION OF prt2_l_p3 FOR VALUES FROM (0) TO (13);
1798 CREATE TABLE prt2_l_p3_p2 PARTITION OF prt2_l_p3 FOR VALUES FROM (13) TO (25);
1799 INSERT INTO prt2_l SELECT i % 25, i, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 3) i;
1801 -- inner join, qual covering only top-level partitions
1803 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
1805 -------------------------------------------------------------
1810 Hash Cond: (t2_1.b = t1_1.a)
1811 -> Seq Scan on prt2_l_p1 t2_1
1813 -> Seq Scan on prt1_l_p1 t1_1
1816 Hash Cond: (t2_3.b = t1_3.a)
1818 -> Seq Scan on prt2_l_p2_p1 t2_3
1819 -> Seq Scan on prt2_l_p2_p2 t2_4
1822 -> Seq Scan on prt1_l_p2_p1 t1_3
1824 -> Seq Scan on prt1_l_p2_p2 t1_4
1827 Hash Cond: (t2_6.b = t1_5.a)
1829 -> Seq Scan on prt2_l_p3_p1 t2_6
1830 -> Seq Scan on prt2_l_p3_p2 t2_7
1832 -> Seq Scan on prt1_l_p3_p1 t1_5
1836 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = 0 ORDER BY t1.a, t2.b;
1838 -----+------+-----+------
1840 150 | 0002 | 150 | 0002
1841 300 | 0000 | 300 | 0000
1842 450 | 0002 | 450 | 0002
1845 -- inner join with partially-redundant join clauses
1847 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.a AND t1.a = t2.b AND t1.c = t2.c ORDER BY t1.a, t2.b;
1849 ------------------------------------------------------------------------------------
1854 Hash Cond: ((t1_1.a = t2_1.a) AND ((t1_1.c)::text = (t2_1.c)::text))
1855 -> Seq Scan on prt1_l_p1 t1_1
1857 -> Seq Scan on prt2_l_p1 t2_1
1860 Hash Cond: ((t1_2.a = t2_2.a) AND ((t1_2.c)::text = (t2_2.c)::text))
1861 -> Seq Scan on prt1_l_p2_p1 t1_2
1863 -> Seq Scan on prt2_l_p2_p1 t2_2
1866 Hash Cond: ((t1_3.a = t2_3.a) AND ((t1_3.c)::text = (t2_3.c)::text))
1867 -> Seq Scan on prt1_l_p2_p2 t1_3
1869 -> Seq Scan on prt2_l_p2_p2 t2_3
1872 Hash Cond: ((t1_5.a = t2_5.a) AND ((t1_5.c)::text = (t2_5.c)::text))
1874 -> Seq Scan on prt1_l_p3_p1 t1_5
1875 -> Seq Scan on prt1_l_p3_p2 t1_6
1878 -> Seq Scan on prt2_l_p3_p1 t2_5
1880 -> Seq Scan on prt2_l_p3_p2 t2_6
1884 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.a AND t1.a = t2.b AND t1.c = t2.c ORDER BY t1.a, t2.b;
1886 ----+------+----+------
1889 12 | 0000 | 12 | 0000
1890 18 | 0002 | 18 | 0002
1891 24 | 0000 | 24 | 0000
1896 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
1898 ------------------------------------------------------------------------------------
1900 Sort Key: t1.a, t2.b
1903 Hash Cond: ((t2_1.b = t1_1.a) AND ((t2_1.c)::text = (t1_1.c)::text))
1904 -> Seq Scan on prt2_l_p1 t2_1
1906 -> Seq Scan on prt1_l_p1 t1_1
1909 Hash Cond: ((t2_2.b = t1_2.a) AND ((t2_2.c)::text = (t1_2.c)::text))
1910 -> Seq Scan on prt2_l_p2_p1 t2_2
1912 -> Seq Scan on prt1_l_p2_p1 t1_2
1915 Hash Cond: ((t2_3.b = t1_3.a) AND ((t2_3.c)::text = (t1_3.c)::text))
1916 -> Seq Scan on prt2_l_p2_p2 t2_3
1918 -> Seq Scan on prt1_l_p2_p2 t1_3
1921 Hash Cond: ((t2_5.b = t1_4.a) AND ((t2_5.c)::text = (t1_4.c)::text))
1923 -> Seq Scan on prt2_l_p3_p1 t2_5
1924 -> Seq Scan on prt2_l_p3_p2 t2_6
1926 -> Seq Scan on prt1_l_p3_p1 t1_4
1930 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t1.b = 0 ORDER BY t1.a, t2.b;
1932 -----+------+-----+------
1936 150 | 0002 | 150 | 0002
1939 300 | 0000 | 300 | 0000
1942 450 | 0002 | 450 | 0002
1949 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b;
1951 ------------------------------------------------------------------------------------
1953 Sort Key: t1.a, t2.b
1956 Hash Cond: ((t1_1.a = t2_1.b) AND ((t1_1.c)::text = (t2_1.c)::text))
1957 -> Seq Scan on prt1_l_p1 t1_1
1959 -> Seq Scan on prt2_l_p1 t2_1
1962 Hash Cond: ((t1_2.a = t2_2.b) AND ((t1_2.c)::text = (t2_2.c)::text))
1963 -> Seq Scan on prt1_l_p2_p1 t1_2
1965 -> Seq Scan on prt2_l_p2_p1 t2_2
1968 Hash Cond: ((t1_3.a = t2_3.b) AND ((t1_3.c)::text = (t2_3.c)::text))
1969 -> Seq Scan on prt1_l_p2_p2 t1_3
1971 -> Seq Scan on prt2_l_p2_p2 t2_3
1974 Hash Cond: ((t1_5.a = t2_4.b) AND ((t1_5.c)::text = (t2_4.c)::text))
1976 -> Seq Scan on prt1_l_p3_p1 t1_5
1977 -> Seq Scan on prt1_l_p3_p2 t1_6
1979 -> Seq Scan on prt2_l_p3_p1 t2_4
1983 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.c = t2.c WHERE t2.a = 0 ORDER BY t1.a, t2.b;
1985 -----+------+-----+------
1987 150 | 0002 | 150 | 0002
1988 300 | 0000 | 300 | 0000
1989 450 | 0002 | 450 | 0002
1998 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b;
2000 ----------------------------------------------------------------------------------------------------
2002 Sort Key: prt1_l.a, prt2_l.b
2005 Hash Cond: ((prt1_l_1.a = prt2_l_1.b) AND ((prt1_l_1.c)::text = (prt2_l_1.c)::text))
2006 -> Seq Scan on prt1_l_p1 prt1_l_1
2009 -> Seq Scan on prt2_l_p1 prt2_l_1
2012 Hash Cond: ((prt1_l_2.a = prt2_l_2.b) AND ((prt1_l_2.c)::text = (prt2_l_2.c)::text))
2013 -> Seq Scan on prt1_l_p2_p1 prt1_l_2
2016 -> Seq Scan on prt2_l_p2_p1 prt2_l_2
2019 Hash Cond: ((prt1_l_3.a = prt2_l_3.b) AND ((prt1_l_3.c)::text = (prt2_l_3.c)::text))
2020 -> Seq Scan on prt1_l_p2_p2 prt1_l_3
2023 -> Seq Scan on prt2_l_p2_p2 prt2_l_3
2026 Hash Cond: ((prt1_l_4.a = prt2_l_4.b) AND ((prt1_l_4.c)::text = (prt2_l_4.c)::text))
2027 -> Seq Scan on prt1_l_p3_p1 prt1_l_4
2030 -> Seq Scan on prt2_l_p3_p1 prt2_l_4
2034 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE prt1_l.b = 0) t1 FULL JOIN (SELECT * FROM prt2_l WHERE prt2_l.a = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c) ORDER BY t1.a, t2.b;
2036 -----+------+-----+------
2040 150 | 0002 | 150 | 0002
2043 300 | 0000 | 300 | 0000
2046 450 | 0002 | 450 | 0002
2055 -- lateral partitionwise join
2057 SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
2058 (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss
2059 ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a;
2061 -----------------------------------------------------------------------------------------------
2065 -> Nested Loop Left Join
2066 -> Seq Scan on prt1_l_p1 t1_1
2069 Hash Cond: ((t3_1.b = t2_1.a) AND ((t3_1.c)::text = (t2_1.c)::text))
2070 -> Seq Scan on prt2_l_p1 t3_1
2072 -> Seq Scan on prt1_l_p1 t2_1
2073 Filter: ((t1_1.a = a) AND ((t1_1.c)::text = (c)::text))
2074 -> Nested Loop Left Join
2075 -> Seq Scan on prt1_l_p2_p1 t1_2
2078 Hash Cond: ((t3_2.b = t2_2.a) AND ((t3_2.c)::text = (t2_2.c)::text))
2079 -> Seq Scan on prt2_l_p2_p1 t3_2
2081 -> Seq Scan on prt1_l_p2_p1 t2_2
2082 Filter: ((t1_2.a = a) AND ((t1_2.c)::text = (c)::text))
2083 -> Nested Loop Left Join
2084 -> Seq Scan on prt1_l_p2_p2 t1_3
2087 Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text = (t2_3.c)::text))
2088 -> Seq Scan on prt2_l_p2_p2 t3_3
2090 -> Seq Scan on prt1_l_p2_p2 t2_3
2091 Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text))
2092 -> Nested Loop Left Join
2093 -> Seq Scan on prt1_l_p3_p1 t1_4
2096 Hash Cond: ((t3_5.b = t2_5.a) AND ((t3_5.c)::text = (t2_5.c)::text))
2098 -> Seq Scan on prt2_l_p3_p1 t3_5
2099 -> Seq Scan on prt2_l_p3_p2 t3_6
2102 -> Seq Scan on prt1_l_p3_p1 t2_5
2103 Filter: ((t1_4.a = a) AND ((t1_4.c)::text = (c)::text))
2104 -> Seq Scan on prt1_l_p3_p2 t2_6
2105 Filter: ((t1_4.a = a) AND ((t1_4.c)::text = (c)::text))
2108 SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
2109 (SELECT t2.a AS t2a, t2.c AS t2c, t2.b AS t2b, t3.b AS t3b, least(t1.a,t2.a,t3.b) FROM prt1_l t2 JOIN prt2_l t3 ON (t2.a = t3.b AND t2.c = t3.c)) ss
2110 ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a;
2111 a | b | c | t2a | t2c | t2b | t3b | least
2112 -----+---+------+-----+------+-----+-----+-------
2113 0 | 0 | 0000 | 0 | 0000 | 0 | 0 | 0
2114 50 | 0 | 0002 | | | | |
2115 100 | 0 | 0000 | | | | |
2116 150 | 0 | 0002 | 150 | 0002 | 0 | 150 | 150
2117 200 | 0 | 0000 | | | | |
2118 250 | 0 | 0002 | | | | |
2119 300 | 0 | 0000 | 300 | 0000 | 0 | 300 | 300
2120 350 | 0 | 0002 | | | | |
2121 400 | 0 | 0000 | | | | |
2122 450 | 0 | 0002 | 450 | 0002 | 0 | 450 | 450
2123 500 | 0 | 0000 | | | | |
2124 550 | 0 | 0002 | | | | |
2127 -- partitionwise join with lateral reference in sample scan
2129 SELECT * FROM prt1_l t1 JOIN LATERAL
2130 (SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
2131 ON t1.a = s.a AND t1.b = s.b AND t1.c = s.c;
2133 ----------------------------------------------------------------------------------------
2136 -> Seq Scan on prt1_l_p1 t1_1
2137 -> Sample Scan on prt1_l_p1 t2_1
2138 Sampling: system (t1_1.a) REPEATABLE (t1_1.b)
2139 Filter: ((t1_1.a = a) AND (t1_1.b = b) AND ((t1_1.c)::text = (c)::text))
2141 -> Seq Scan on prt1_l_p2_p1 t1_2
2142 -> Sample Scan on prt1_l_p2_p1 t2_2
2143 Sampling: system (t1_2.a) REPEATABLE (t1_2.b)
2144 Filter: ((t1_2.a = a) AND (t1_2.b = b) AND ((t1_2.c)::text = (c)::text))
2146 -> Seq Scan on prt1_l_p2_p2 t1_3
2147 -> Sample Scan on prt1_l_p2_p2 t2_3
2148 Sampling: system (t1_3.a) REPEATABLE (t1_3.b)
2149 Filter: ((t1_3.a = a) AND (t1_3.b = b) AND ((t1_3.c)::text = (c)::text))
2151 -> Seq Scan on prt1_l_p3_p1 t1_4
2152 -> Sample Scan on prt1_l_p3_p1 t2_4
2153 Sampling: system (t1_4.a) REPEATABLE (t1_4.b)
2154 Filter: ((t1_4.a = a) AND (t1_4.b = b) AND ((t1_4.c)::text = (c)::text))
2156 -> Seq Scan on prt1_l_p3_p2 t1_5
2157 -> Sample Scan on prt1_l_p3_p2 t2_5
2158 Sampling: system (t1_5.a) REPEATABLE (t1_5.b)
2159 Filter: ((t1_5.a = a) AND (t1_5.b = b) AND ((t1_5.c)::text = (c)::text))
2162 -- partitionwise join with lateral reference in scan's restriction clauses
2164 SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL
2165 (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s
2166 ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c
2169 ---------------------------------------------------------------------------------------------------------------
2173 -> Seq Scan on prt1_l_p1 t1_1
2174 -> Seq Scan on prt2_l_p1 t2_1
2175 Filter: ((a = t1_1.b) AND (t1_1.a = b) AND (t1_1.b = a) AND ((t1_1.c)::text = (c)::text))
2177 -> Seq Scan on prt1_l_p2_p1 t1_2
2178 -> Seq Scan on prt2_l_p2_p1 t2_2
2179 Filter: ((a = t1_2.b) AND (t1_2.a = b) AND (t1_2.b = a) AND ((t1_2.c)::text = (c)::text))
2181 -> Seq Scan on prt1_l_p2_p2 t1_3
2182 -> Seq Scan on prt2_l_p2_p2 t2_3
2183 Filter: ((a = t1_3.b) AND (t1_3.a = b) AND (t1_3.b = a) AND ((t1_3.c)::text = (c)::text))
2185 -> Seq Scan on prt1_l_p3_p1 t1_4
2186 -> Seq Scan on prt2_l_p3_p1 t2_4
2187 Filter: ((a = t1_4.b) AND (t1_4.a = b) AND (t1_4.b = a) AND ((t1_4.c)::text = (c)::text))
2189 -> Seq Scan on prt1_l_p3_p2 t1_5
2190 -> Seq Scan on prt2_l_p3_p2 t2_5
2191 Filter: ((a = t1_5.b) AND (t1_5.a = b) AND (t1_5.b = a) AND ((t1_5.c)::text = (c)::text))
2194 SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL
2195 (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s
2196 ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c
2203 -- join with one side empty
2205 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2) t1 RIGHT JOIN prt2_l t2 ON t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c;
2207 -------------------------------------------------------------------------
2209 Hash Cond: ((t2.b = a) AND (t2.a = b) AND ((t2.c)::text = (c)::text))
2211 -> Seq Scan on prt2_l_p1 t2_1
2212 -> Seq Scan on prt2_l_p2_p1 t2_2
2213 -> Seq Scan on prt2_l_p2_p2 t2_3
2214 -> Seq Scan on prt2_l_p3_p1 t2_4
2215 -> Seq Scan on prt2_l_p3_p2 t2_5
2218 One-Time Filter: false
2221 -- Test case to verify proper handling of subqueries in a partitioned delete.
2222 -- The weird-looking lateral join is just there to force creation of a
2223 -- nestloop parameter within the subquery, which exposes the problem if the
2224 -- planner fails to make multiple copies of the subquery as appropriate.
2230 LATERAL (SELECT int4_tbl.f1 FROM int8_tbl LIMIT 2) ss
2231 WHERE prt1_l.c IS NULL);
2233 ----------------------------------------------------------
2235 Delete on prt1_l_p1 prt1_l_1
2236 Delete on prt1_l_p3_p1 prt1_l_2
2237 Delete on prt1_l_p3_p2 prt1_l_3
2238 -> Nested Loop Semi Join
2240 -> Seq Scan on prt1_l_p1 prt1_l_1
2242 -> Seq Scan on prt1_l_p3_p1 prt1_l_2
2244 -> Seq Scan on prt1_l_p3_p2 prt1_l_3
2248 -> Seq Scan on int4_tbl
2249 -> Subquery Scan on ss
2251 -> Seq Scan on int8_tbl
2255 -- negative testcases
2257 CREATE TABLE prt1_n (a int, b int, c varchar) PARTITION BY RANGE(c);
2258 CREATE TABLE prt1_n_p1 PARTITION OF prt1_n FOR VALUES FROM ('0000') TO ('0250');
2259 CREATE TABLE prt1_n_p2 PARTITION OF prt1_n FOR VALUES FROM ('0250') TO ('0500');
2260 INSERT INTO prt1_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 499, 2) i;
2262 CREATE TABLE prt2_n (a int, b int, c text) PARTITION BY LIST(c);
2263 CREATE TABLE prt2_n_p1 PARTITION OF prt2_n FOR VALUES IN ('0000', '0003', '0004', '0010', '0006', '0007');
2264 CREATE TABLE prt2_n_p2 PARTITION OF prt2_n FOR VALUES IN ('0001', '0005', '0002', '0009', '0008', '0011');
2265 INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
2267 CREATE TABLE prt3_n (a int, b int, c text) PARTITION BY LIST(c);
2268 CREATE TABLE prt3_n_p1 PARTITION OF prt3_n FOR VALUES IN ('0000', '0004', '0006', '0007');
2269 CREATE TABLE prt3_n_p2 PARTITION OF prt3_n FOR VALUES IN ('0001', '0002', '0008', '0010');
2270 CREATE TABLE prt3_n_p3 PARTITION OF prt3_n FOR VALUES IN ('0003', '0005', '0009', '0011');
2271 INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
2273 CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a);
2274 CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300);
2275 CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500);
2276 CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600);
2277 INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
2279 -- partitionwise join can not be applied if the partition ranges differ
2281 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
2283 ----------------------------------------------
2285 Hash Cond: (t1.a = t2.a)
2287 -> Seq Scan on prt1_p1 t1_1
2288 -> Seq Scan on prt1_p2 t1_2
2289 -> Seq Scan on prt1_p3 t1_3
2292 -> Seq Scan on prt4_n_p1 t2_1
2293 -> Seq Scan on prt4_n_p2 t2_2
2294 -> Seq Scan on prt4_n_p3 t2_3
2298 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2, prt2 t3 WHERE t1.a = t2.a and t1.a = t3.b;
2300 --------------------------------------------------------
2302 Hash Cond: (t2.a = t1.a)
2304 -> Seq Scan on prt4_n_p1 t2_1
2305 -> Seq Scan on prt4_n_p2 t2_2
2306 -> Seq Scan on prt4_n_p3 t2_3
2310 Hash Cond: (t1_1.a = t3_1.b)
2311 -> Seq Scan on prt1_p1 t1_1
2313 -> Seq Scan on prt2_p1 t3_1
2315 Hash Cond: (t1_2.a = t3_2.b)
2316 -> Seq Scan on prt1_p2 t1_2
2318 -> Seq Scan on prt2_p2 t3_2
2320 Hash Cond: (t1_3.a = t3_3.b)
2321 -> Seq Scan on prt1_p3 t1_3
2323 -> Seq Scan on prt2_p3 t3_3
2326 -- partitionwise join can not be applied if there are no equi-join conditions
2327 -- between partition keys
2329 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON (t1.a < t2.b);
2331 ---------------------------------------------------------
2332 Nested Loop Left Join
2334 -> Seq Scan on prt1_p1 t1_1
2335 -> Seq Scan on prt1_p2 t1_2
2336 -> Seq Scan on prt1_p3 t1_3
2338 -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
2339 Index Cond: (b > t1.a)
2340 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
2341 Index Cond: (b > t1.a)
2342 -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
2343 Index Cond: (b > t1.a)
2346 -- equi-join with join condition on partial keys does not qualify for
2347 -- partitionwise join
2349 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1, prt2_m t2 WHERE t1.a = (t2.b + t2.a)/2;
2351 ----------------------------------------------
2353 Hash Cond: (((t2.b + t2.a) / 2) = t1.a)
2355 -> Seq Scan on prt2_m_p1 t2_1
2356 -> Seq Scan on prt2_m_p2 t2_2
2357 -> Seq Scan on prt2_m_p3 t2_3
2360 -> Seq Scan on prt1_m_p1 t1_1
2361 -> Seq Scan on prt1_m_p2 t1_2
2362 -> Seq Scan on prt1_m_p3 t1_3
2365 -- equi-join between out-of-order partition key columns does not qualify for
2366 -- partitionwise join
2368 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.a = t2.b;
2370 ----------------------------------------------
2372 Hash Cond: (t1.a = t2.b)
2374 -> Seq Scan on prt1_m_p1 t1_1
2375 -> Seq Scan on prt1_m_p2 t1_2
2376 -> Seq Scan on prt1_m_p3 t1_3
2379 -> Seq Scan on prt2_m_p1 t2_1
2380 -> Seq Scan on prt2_m_p2 t2_2
2381 -> Seq Scan on prt2_m_p3 t2_3
2384 -- equi-join between non-key columns does not qualify for partitionwise join
2386 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.c = t2.c;
2388 ----------------------------------------------
2390 Hash Cond: (t1.c = t2.c)
2392 -> Seq Scan on prt1_m_p1 t1_1
2393 -> Seq Scan on prt1_m_p2 t1_2
2394 -> Seq Scan on prt1_m_p3 t1_3
2397 -> Seq Scan on prt2_m_p1 t2_1
2398 -> Seq Scan on prt2_m_p2 t2_2
2399 -> Seq Scan on prt2_m_p3 t2_3
2402 -- partitionwise join can not be applied for a join between list and range
2403 -- partitioned tables
2405 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c);
2407 ----------------------------------------------
2409 Hash Cond: (t2.c = (t1.c)::text)
2411 -> Seq Scan on prt2_n_p1 t2_1
2412 -> Seq Scan on prt2_n_p2 t2_2
2415 -> Seq Scan on prt1_n_p1 t1_1
2416 -> Seq Scan on prt1_n_p2 t1_2
2419 -- partitionwise join can not be applied between tables with different
2422 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n t2 ON (t1.c = t2.c) JOIN plt1 t3 ON (t1.c = t3.c);
2424 ----------------------------------------------------------
2426 Hash Cond: (t2.c = (t1.c)::text)
2428 -> Seq Scan on prt2_n_p1 t2_1
2429 -> Seq Scan on prt2_n_p2 t2_2
2432 Hash Cond: (t3.c = (t1.c)::text)
2434 -> Seq Scan on plt1_p1 t3_1
2435 -> Seq Scan on plt1_p2 t3_2
2436 -> Seq Scan on plt1_p3 t3_3
2439 -> Seq Scan on prt1_n_p1 t1_1
2440 -> Seq Scan on prt1_n_p2 t1_2
2443 -- partitionwise join can not be applied for a join between key column and
2446 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
2448 ----------------------------------------------
2450 Hash Cond: ((t2.c)::text = (t1.c)::text)
2452 -> Seq Scan on prt1_p1 t2_1
2453 -> Seq Scan on prt1_p2 t2_2
2454 -> Seq Scan on prt1_p3 t2_3
2457 -> Seq Scan on prt1_n_p1 t1_1
2458 -> Seq Scan on prt1_n_p2 t1_2
2462 -- Test some other plan types in a partitionwise join (unfortunately,
2463 -- we need larger tables to get the planner to choose these plan types)
2465 create temp table prtx1 (a integer, b integer, c integer)
2466 partition by range (a);
2467 create temp table prtx1_1 partition of prtx1 for values from (1) to (11);
2468 create temp table prtx1_2 partition of prtx1 for values from (11) to (21);
2469 create temp table prtx1_3 partition of prtx1 for values from (21) to (31);
2470 create temp table prtx2 (a integer, b integer, c integer)
2471 partition by range (a);
2472 create temp table prtx2_1 partition of prtx2 for values from (1) to (11);
2473 create temp table prtx2_2 partition of prtx2 for values from (11) to (21);
2474 create temp table prtx2_3 partition of prtx2 for values from (21) to (31);
2475 insert into prtx1 select 1 + i%30, i, i
2476 from generate_series(1,1000) i;
2477 insert into prtx2 select 1 + i%30, i, i
2478 from generate_series(1,500) i, generate_series(1,10) j;
2479 create index on prtx2 (b);
2480 create index on prtx2 (c);
2485 where not exists (select 1 from prtx2
2486 where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
2489 -------------------------------------------------------------
2491 -> Nested Loop Anti Join
2492 -> Seq Scan on prtx1_1
2493 Filter: ((a < 20) AND (c = 120))
2494 -> Bitmap Heap Scan on prtx2_1
2495 Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
2496 Filter: (a = prtx1_1.a)
2498 -> Bitmap Index Scan on prtx2_1_b_idx
2499 Index Cond: (b = prtx1_1.b)
2500 -> Bitmap Index Scan on prtx2_1_c_idx
2501 Index Cond: (c = 123)
2502 -> Nested Loop Anti Join
2503 -> Seq Scan on prtx1_2
2504 Filter: ((a < 20) AND (c = 120))
2505 -> Bitmap Heap Scan on prtx2_2
2506 Recheck Cond: ((b = prtx1_2.b) AND (c = 123))
2507 Filter: (a = prtx1_2.a)
2509 -> Bitmap Index Scan on prtx2_2_b_idx
2510 Index Cond: (b = prtx1_2.b)
2511 -> Bitmap Index Scan on prtx2_2_c_idx
2512 Index Cond: (c = 123)
2516 where not exists (select 1 from prtx2
2517 where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
2526 where not exists (select 1 from prtx2
2527 where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
2530 -----------------------------------------------------------------
2532 -> Nested Loop Anti Join
2533 -> Seq Scan on prtx1_1
2534 Filter: ((a < 20) AND (c = 91))
2535 -> Bitmap Heap Scan on prtx2_1
2536 Recheck Cond: ((b = (prtx1_1.b + 1)) OR (c = 99))
2537 Filter: (a = prtx1_1.a)
2539 -> Bitmap Index Scan on prtx2_1_b_idx
2540 Index Cond: (b = (prtx1_1.b + 1))
2541 -> Bitmap Index Scan on prtx2_1_c_idx
2542 Index Cond: (c = 99)
2543 -> Nested Loop Anti Join
2544 -> Seq Scan on prtx1_2
2545 Filter: ((a < 20) AND (c = 91))
2546 -> Bitmap Heap Scan on prtx2_2
2547 Recheck Cond: ((b = (prtx1_2.b + 1)) OR (c = 99))
2548 Filter: (a = prtx1_2.a)
2550 -> Bitmap Index Scan on prtx2_2_b_idx
2551 Index Cond: (b = (prtx1_2.b + 1))
2552 -> Bitmap Index Scan on prtx2_2_c_idx
2553 Index Cond: (c = 99)
2557 where not exists (select 1 from prtx2
2558 where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
2566 -- Test advanced partition-matching algorithm for partitioned join
2568 -- Tests for range-partitioned tables
2569 CREATE TABLE prt1_adv (a int, b int, c varchar) PARTITION BY RANGE (a);
2570 CREATE TABLE prt1_adv_p1 PARTITION OF prt1_adv FOR VALUES FROM (100) TO (200);
2571 CREATE TABLE prt1_adv_p2 PARTITION OF prt1_adv FOR VALUES FROM (200) TO (300);
2572 CREATE TABLE prt1_adv_p3 PARTITION OF prt1_adv FOR VALUES FROM (300) TO (400);
2573 CREATE INDEX prt1_adv_a_idx ON prt1_adv (a);
2574 INSERT INTO prt1_adv SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
2576 CREATE TABLE prt2_adv (a int, b int, c varchar) PARTITION BY RANGE (b);
2577 CREATE TABLE prt2_adv_p1 PARTITION OF prt2_adv FOR VALUES FROM (100) TO (150);
2578 CREATE TABLE prt2_adv_p2 PARTITION OF prt2_adv FOR VALUES FROM (200) TO (300);
2579 CREATE TABLE prt2_adv_p3 PARTITION OF prt2_adv FOR VALUES FROM (350) TO (500);
2580 CREATE INDEX prt2_adv_b_idx ON prt2_adv (b);
2581 INSERT INTO prt2_adv_p1 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(100, 149) i;
2582 INSERT INTO prt2_adv_p2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(200, 299) i;
2583 INSERT INTO prt2_adv_p3 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(350, 499) i;
2587 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2589 ------------------------------------------------------
2594 Hash Cond: (t2_1.b = t1_1.a)
2595 -> Seq Scan on prt2_adv_p1 t2_1
2597 -> Seq Scan on prt1_adv_p1 t1_1
2600 Hash Cond: (t2_2.b = t1_2.a)
2601 -> Seq Scan on prt2_adv_p2 t2_2
2603 -> Seq Scan on prt1_adv_p2 t1_2
2606 Hash Cond: (t2_3.b = t1_3.a)
2607 -> Seq Scan on prt2_adv_p3 t2_3
2609 -> Seq Scan on prt1_adv_p3 t1_3
2613 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2615 -----+------+-----+------
2616 100 | 0100 | 100 | 0100
2617 125 | 0125 | 125 | 0125
2618 200 | 0200 | 200 | 0200
2619 225 | 0225 | 225 | 0225
2620 250 | 0250 | 250 | 0250
2621 275 | 0275 | 275 | 0275
2622 350 | 0350 | 350 | 0350
2623 375 | 0375 | 375 | 0375
2628 SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2630 ------------------------------------------------------
2634 -> Hash Right Semi Join
2635 Hash Cond: (t2_1.b = t1_1.a)
2636 -> Seq Scan on prt2_adv_p1 t2_1
2638 -> Seq Scan on prt1_adv_p1 t1_1
2640 -> Hash Right Semi Join
2641 Hash Cond: (t2_2.b = t1_2.a)
2642 -> Seq Scan on prt2_adv_p2 t2_2
2644 -> Seq Scan on prt1_adv_p2 t1_2
2646 -> Hash Right Semi Join
2647 Hash Cond: (t2_3.b = t1_3.a)
2648 -> Seq Scan on prt2_adv_p3 t2_3
2650 -> Seq Scan on prt1_adv_p3 t1_3
2654 SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2669 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2671 ------------------------------------------------------
2673 Sort Key: t1.a, t2.b
2676 Hash Cond: (t2_1.b = t1_1.a)
2677 -> Seq Scan on prt2_adv_p1 t2_1
2679 -> Seq Scan on prt1_adv_p1 t1_1
2682 Hash Cond: (t2_2.b = t1_2.a)
2683 -> Seq Scan on prt2_adv_p2 t2_2
2685 -> Seq Scan on prt1_adv_p2 t1_2
2688 Hash Cond: (t2_3.b = t1_3.a)
2689 -> Seq Scan on prt2_adv_p3 t2_3
2691 -> Seq Scan on prt1_adv_p3 t1_3
2695 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2697 -----+------+-----+------
2698 100 | 0100 | 100 | 0100
2699 125 | 0125 | 125 | 0125
2702 200 | 0200 | 200 | 0200
2703 225 | 0225 | 225 | 0225
2704 250 | 0250 | 250 | 0250
2705 275 | 0275 | 275 | 0275
2708 350 | 0350 | 350 | 0350
2709 375 | 0375 | 375 | 0375
2714 SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2716 ------------------------------------------------------
2720 -> Hash Right Anti Join
2721 Hash Cond: (t2_1.b = t1_1.a)
2722 -> Seq Scan on prt2_adv_p1 t2_1
2724 -> Seq Scan on prt1_adv_p1 t1_1
2726 -> Hash Right Anti Join
2727 Hash Cond: (t2_2.b = t1_2.a)
2728 -> Seq Scan on prt2_adv_p2 t2_2
2730 -> Seq Scan on prt1_adv_p2 t1_2
2732 -> Hash Right Anti Join
2733 Hash Cond: (t2_3.b = t1_3.a)
2734 -> Seq Scan on prt2_adv_p3 t2_3
2736 -> Seq Scan on prt1_adv_p3 t1_3
2740 SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2751 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 175 phv, * FROM prt1_adv WHERE prt1_adv.b = 0) t1 FULL JOIN (SELECT 425 phv, * FROM prt2_adv WHERE prt2_adv.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
2753 --------------------------------------------------------------------------
2755 Sort Key: prt1_adv.a, prt2_adv.b
2758 Hash Cond: (prt1_adv_1.a = prt2_adv_1.b)
2759 Filter: (((175) = prt1_adv_1.a) OR ((425) = prt2_adv_1.b))
2760 -> Seq Scan on prt1_adv_p1 prt1_adv_1
2763 -> Seq Scan on prt2_adv_p1 prt2_adv_1
2766 Hash Cond: (prt1_adv_2.a = prt2_adv_2.b)
2767 Filter: (((175) = prt1_adv_2.a) OR ((425) = prt2_adv_2.b))
2768 -> Seq Scan on prt1_adv_p2 prt1_adv_2
2771 -> Seq Scan on prt2_adv_p2 prt2_adv_2
2774 Hash Cond: (prt2_adv_3.b = prt1_adv_3.a)
2775 Filter: (((175) = prt1_adv_3.a) OR ((425) = prt2_adv_3.b))
2776 -> Seq Scan on prt2_adv_p3 prt2_adv_3
2779 -> Seq Scan on prt1_adv_p3 prt1_adv_3
2783 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 175 phv, * FROM prt1_adv WHERE prt1_adv.b = 0) t1 FULL JOIN (SELECT 425 phv, * FROM prt2_adv WHERE prt2_adv.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
2785 -----+------+-----+------
2790 -- Test cases where one side has an extra partition
2791 CREATE TABLE prt2_adv_extra PARTITION OF prt2_adv FOR VALUES FROM (500) TO (MAXVALUE);
2792 INSERT INTO prt2_adv SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(500, 599) i;
2796 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2798 ------------------------------------------------------
2803 Hash Cond: (t2_1.b = t1_1.a)
2804 -> Seq Scan on prt2_adv_p1 t2_1
2806 -> Seq Scan on prt1_adv_p1 t1_1
2809 Hash Cond: (t2_2.b = t1_2.a)
2810 -> Seq Scan on prt2_adv_p2 t2_2
2812 -> Seq Scan on prt1_adv_p2 t1_2
2815 Hash Cond: (t2_3.b = t1_3.a)
2816 -> Seq Scan on prt2_adv_p3 t2_3
2818 -> Seq Scan on prt1_adv_p3 t1_3
2822 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2824 -----+------+-----+------
2825 100 | 0100 | 100 | 0100
2826 125 | 0125 | 125 | 0125
2827 200 | 0200 | 200 | 0200
2828 225 | 0225 | 225 | 0225
2829 250 | 0250 | 250 | 0250
2830 275 | 0275 | 275 | 0275
2831 350 | 0350 | 350 | 0350
2832 375 | 0375 | 375 | 0375
2837 SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2839 ------------------------------------------------------
2843 -> Hash Right Semi Join
2844 Hash Cond: (t2_1.b = t1_1.a)
2845 -> Seq Scan on prt2_adv_p1 t2_1
2847 -> Seq Scan on prt1_adv_p1 t1_1
2849 -> Hash Right Semi Join
2850 Hash Cond: (t2_2.b = t1_2.a)
2851 -> Seq Scan on prt2_adv_p2 t2_2
2853 -> Seq Scan on prt1_adv_p2 t1_2
2855 -> Hash Right Semi Join
2856 Hash Cond: (t2_3.b = t1_3.a)
2857 -> Seq Scan on prt2_adv_p3 t2_3
2859 -> Seq Scan on prt1_adv_p3 t1_3
2863 SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2878 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2880 ------------------------------------------------------
2882 Sort Key: t1.a, t2.b
2885 Hash Cond: (t2_1.b = t1_1.a)
2886 -> Seq Scan on prt2_adv_p1 t2_1
2888 -> Seq Scan on prt1_adv_p1 t1_1
2891 Hash Cond: (t2_2.b = t1_2.a)
2892 -> Seq Scan on prt2_adv_p2 t2_2
2894 -> Seq Scan on prt1_adv_p2 t1_2
2897 Hash Cond: (t2_3.b = t1_3.a)
2898 -> Seq Scan on prt2_adv_p3 t2_3
2900 -> Seq Scan on prt1_adv_p3 t1_3
2904 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
2906 -----+------+-----+------
2907 100 | 0100 | 100 | 0100
2908 125 | 0125 | 125 | 0125
2911 200 | 0200 | 200 | 0200
2912 225 | 0225 | 225 | 0225
2913 250 | 0250 | 250 | 0250
2914 275 | 0275 | 275 | 0275
2917 350 | 0350 | 350 | 0350
2918 375 | 0375 | 375 | 0375
2921 -- left join; currently we can't do partitioned join if there are no matched
2922 -- partitions on the nullable side
2924 SELECT t1.b, t1.c, t2.a, t2.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2 ON (t1.b = t2.a) WHERE t1.a = 0 ORDER BY t1.b, t2.a;
2926 ---------------------------------------------------------
2928 Sort Key: t1.b, t2.a
2930 Hash Cond: (t2.a = t1.b)
2932 -> Seq Scan on prt1_adv_p1 t2_1
2933 -> Seq Scan on prt1_adv_p2 t2_2
2934 -> Seq Scan on prt1_adv_p3 t2_3
2937 -> Seq Scan on prt2_adv_p1 t1_1
2939 -> Seq Scan on prt2_adv_p2 t1_2
2941 -> Seq Scan on prt2_adv_p3 t1_3
2943 -> Seq Scan on prt2_adv_extra t1_4
2949 SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2951 ------------------------------------------------------
2955 -> Hash Right Anti Join
2956 Hash Cond: (t2_1.b = t1_1.a)
2957 -> Seq Scan on prt2_adv_p1 t2_1
2959 -> Seq Scan on prt1_adv_p1 t1_1
2961 -> Hash Right Anti Join
2962 Hash Cond: (t2_2.b = t1_2.a)
2963 -> Seq Scan on prt2_adv_p2 t2_2
2965 -> Seq Scan on prt1_adv_p2 t1_2
2967 -> Hash Right Anti Join
2968 Hash Cond: (t2_3.b = t1_3.a)
2969 -> Seq Scan on prt2_adv_p3 t2_3
2971 -> Seq Scan on prt1_adv_p3 t1_3
2975 SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
2984 -- anti join; currently we can't do partitioned join if there are no matched
2985 -- partitions on the nullable side
2987 SELECT t1.* FROM prt2_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt1_adv t2 WHERE t1.b = t2.a) AND t1.a = 0 ORDER BY t1.b;
2989 ---------------------------------------------------------
2992 -> Hash Right Anti Join
2993 Hash Cond: (t2.a = t1.b)
2995 -> Seq Scan on prt1_adv_p1 t2_1
2996 -> Seq Scan on prt1_adv_p2 t2_2
2997 -> Seq Scan on prt1_adv_p3 t2_3
3000 -> Seq Scan on prt2_adv_p1 t1_1
3002 -> Seq Scan on prt2_adv_p2 t1_2
3004 -> Seq Scan on prt2_adv_p3 t1_3
3006 -> Seq Scan on prt2_adv_extra t1_4
3010 -- full join; currently we can't do partitioned join if there are no matched
3011 -- partitions on the nullable side
3013 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 175 phv, * FROM prt1_adv WHERE prt1_adv.b = 0) t1 FULL JOIN (SELECT 425 phv, * FROM prt2_adv WHERE prt2_adv.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
3015 ----------------------------------------------------------------
3017 Sort Key: prt1_adv.a, prt2_adv.b
3019 Hash Cond: (prt2_adv.b = prt1_adv.a)
3020 Filter: (((175) = prt1_adv.a) OR ((425) = prt2_adv.b))
3022 -> Seq Scan on prt2_adv_p1 prt2_adv_1
3024 -> Seq Scan on prt2_adv_p2 prt2_adv_2
3026 -> Seq Scan on prt2_adv_p3 prt2_adv_3
3028 -> Seq Scan on prt2_adv_extra prt2_adv_4
3032 -> Seq Scan on prt1_adv_p1 prt1_adv_1
3034 -> Seq Scan on prt1_adv_p2 prt1_adv_2
3036 -> Seq Scan on prt1_adv_p3 prt1_adv_3
3040 -- 3-way join where not every pair of relations can do partitioned join
3042 SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2 ON (t1.b = t2.a) INNER JOIN prt1_adv t3 ON (t1.b = t3.a) WHERE t1.a = 0 ORDER BY t1.b, t2.a, t3.a;
3044 --------------------------------------------------------------------------------
3046 Sort Key: t1.b, t2.a
3048 -> Nested Loop Left Join
3050 -> Seq Scan on prt2_adv_p1 t1_1
3052 -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t3_1
3053 Index Cond: (a = t1_1.b)
3054 -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t2_1
3055 Index Cond: (a = t1_1.b)
3057 Hash Cond: (t2_2.a = t1_2.b)
3058 -> Seq Scan on prt1_adv_p2 t2_2
3061 Hash Cond: (t3_2.a = t1_2.b)
3062 -> Seq Scan on prt1_adv_p2 t3_2
3064 -> Seq Scan on prt2_adv_p2 t1_2
3067 Hash Cond: (t2_3.a = t1_3.b)
3068 -> Seq Scan on prt1_adv_p3 t2_3
3071 Hash Cond: (t3_3.a = t1_3.b)
3072 -> Seq Scan on prt1_adv_p3 t3_3
3074 -> Seq Scan on prt2_adv_p3 t1_3
3078 SELECT t1.b, t1.c, t2.a, t2.c, t3.a, t3.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2 ON (t1.b = t2.a) INNER JOIN prt1_adv t3 ON (t1.b = t3.a) WHERE t1.a = 0 ORDER BY t1.b, t2.a, t3.a;
3079 b | c | a | c | a | c
3080 -----+------+-----+------+-----+------
3081 100 | 0100 | 100 | 0100 | 100 | 0100
3082 125 | 0125 | 125 | 0125 | 125 | 0125
3083 200 | 0200 | 200 | 0200 | 200 | 0200
3084 225 | 0225 | 225 | 0225 | 225 | 0225
3085 250 | 0250 | 250 | 0250 | 250 | 0250
3086 275 | 0275 | 275 | 0275 | 275 | 0275
3087 350 | 0350 | 350 | 0350 | 350 | 0350
3088 375 | 0375 | 375 | 0375 | 375 | 0375
3091 DROP TABLE prt2_adv_extra;
3092 -- Test cases where a partition on one side matches multiple partitions on
3093 -- the other side; we currently can't do partitioned join in such cases
3094 ALTER TABLE prt2_adv DETACH PARTITION prt2_adv_p3;
3095 -- Split prt2_adv_p3 into two partitions so that prt1_adv_p3 matches both
3096 CREATE TABLE prt2_adv_p3_1 PARTITION OF prt2_adv FOR VALUES FROM (350) TO (375);
3097 CREATE TABLE prt2_adv_p3_2 PARTITION OF prt2_adv FOR VALUES FROM (375) TO (500);
3098 INSERT INTO prt2_adv SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(350, 499) i;
3102 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
3104 ------------------------------------------------------
3108 Hash Cond: (t2.b = t1.a)
3110 -> Seq Scan on prt2_adv_p1 t2_1
3111 -> Seq Scan on prt2_adv_p2 t2_2
3112 -> Seq Scan on prt2_adv_p3_1 t2_3
3113 -> Seq Scan on prt2_adv_p3_2 t2_4
3116 -> Seq Scan on prt1_adv_p1 t1_1
3118 -> Seq Scan on prt1_adv_p2 t1_2
3120 -> Seq Scan on prt1_adv_p3 t1_3
3126 SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
3128 ------------------------------------------------------
3131 -> Hash Right Semi Join
3132 Hash Cond: (t2.b = t1.a)
3134 -> Seq Scan on prt2_adv_p1 t2_1
3135 -> Seq Scan on prt2_adv_p2 t2_2
3136 -> Seq Scan on prt2_adv_p3_1 t2_3
3137 -> Seq Scan on prt2_adv_p3_2 t2_4
3140 -> Seq Scan on prt1_adv_p1 t1_1
3142 -> Seq Scan on prt1_adv_p2 t1_2
3144 -> Seq Scan on prt1_adv_p3 t1_3
3150 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
3152 ------------------------------------------------------
3154 Sort Key: t1.a, t2.b
3156 Hash Cond: (t2.b = t1.a)
3158 -> Seq Scan on prt2_adv_p1 t2_1
3159 -> Seq Scan on prt2_adv_p2 t2_2
3160 -> Seq Scan on prt2_adv_p3_1 t2_3
3161 -> Seq Scan on prt2_adv_p3_2 t2_4
3164 -> Seq Scan on prt1_adv_p1 t1_1
3166 -> Seq Scan on prt1_adv_p2 t1_2
3168 -> Seq Scan on prt1_adv_p3 t1_3
3174 SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
3176 ------------------------------------------------------
3179 -> Hash Right Anti Join
3180 Hash Cond: (t2.b = t1.a)
3182 -> Seq Scan on prt2_adv_p1 t2_1
3183 -> Seq Scan on prt2_adv_p2 t2_2
3184 -> Seq Scan on prt2_adv_p3_1 t2_3
3185 -> Seq Scan on prt2_adv_p3_2 t2_4
3188 -> Seq Scan on prt1_adv_p1 t1_1
3190 -> Seq Scan on prt1_adv_p2 t1_2
3192 -> Seq Scan on prt1_adv_p3 t1_3
3198 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT 175 phv, * FROM prt1_adv WHERE prt1_adv.b = 0) t1 FULL JOIN (SELECT 425 phv, * FROM prt2_adv WHERE prt2_adv.a = 0) t2 ON (t1.a = t2.b) WHERE t1.phv = t1.a OR t2.phv = t2.b ORDER BY t1.a, t2.b;
3200 ----------------------------------------------------------------
3202 Sort Key: prt1_adv.a, prt2_adv.b
3204 Hash Cond: (prt2_adv.b = prt1_adv.a)
3205 Filter: (((175) = prt1_adv.a) OR ((425) = prt2_adv.b))
3207 -> Seq Scan on prt2_adv_p1 prt2_adv_1
3209 -> Seq Scan on prt2_adv_p2 prt2_adv_2
3211 -> Seq Scan on prt2_adv_p3_1 prt2_adv_3
3213 -> Seq Scan on prt2_adv_p3_2 prt2_adv_4
3217 -> Seq Scan on prt1_adv_p1 prt1_adv_1
3219 -> Seq Scan on prt1_adv_p2 prt1_adv_2
3221 -> Seq Scan on prt1_adv_p3 prt1_adv_3
3225 DROP TABLE prt2_adv_p3_1;
3226 DROP TABLE prt2_adv_p3_2;
3228 -- Test default partitions
3229 ALTER TABLE prt1_adv DETACH PARTITION prt1_adv_p1;
3230 -- Change prt1_adv_p1 to the default partition
3231 ALTER TABLE prt1_adv ATTACH PARTITION prt1_adv_p1 DEFAULT;
3232 ALTER TABLE prt1_adv DETACH PARTITION prt1_adv_p3;
3234 -- We can do partitioned join even if only one of relations has the default
3237 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
3239 ------------------------------------------------------
3244 Hash Cond: (t2_1.b = t1_2.a)
3245 -> Seq Scan on prt2_adv_p1 t2_1
3247 -> Seq Scan on prt1_adv_p1 t1_2
3250 Hash Cond: (t2_2.b = t1_1.a)
3251 -> Seq Scan on prt2_adv_p2 t2_2
3253 -> Seq Scan on prt1_adv_p2 t1_1
3257 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
3259 -----+------+-----+------
3260 100 | 0100 | 100 | 0100
3261 125 | 0125 | 125 | 0125
3262 200 | 0200 | 200 | 0200
3263 225 | 0225 | 225 | 0225
3264 250 | 0250 | 250 | 0250
3265 275 | 0275 | 275 | 0275
3268 -- Restore prt1_adv_p3
3269 ALTER TABLE prt1_adv ATTACH PARTITION prt1_adv_p3 FOR VALUES FROM (300) TO (400);
3271 -- Restore prt2_adv_p3
3272 ALTER TABLE prt2_adv ATTACH PARTITION prt2_adv_p3 FOR VALUES FROM (350) TO (500);
3274 -- Partitioned join can't be applied because the default partition of prt1_adv
3275 -- matches prt2_adv_p1 and prt2_adv_p3
3277 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
3279 ------------------------------------------------------
3283 Hash Cond: (t2.b = t1.a)
3285 -> Seq Scan on prt2_adv_p1 t2_1
3286 -> Seq Scan on prt2_adv_p2 t2_2
3287 -> Seq Scan on prt2_adv_p3 t2_3
3290 -> Seq Scan on prt1_adv_p2 t1_1
3292 -> Seq Scan on prt1_adv_p3 t1_2
3294 -> Seq Scan on prt1_adv_p1 t1_3
3298 ALTER TABLE prt2_adv DETACH PARTITION prt2_adv_p3;
3299 -- Change prt2_adv_p3 to the default partition
3300 ALTER TABLE prt2_adv ATTACH PARTITION prt2_adv_p3 DEFAULT;
3302 -- Partitioned join can't be applied because the default partition of prt1_adv
3303 -- matches prt2_adv_p1 and prt2_adv_p3
3305 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a, t2.b;
3307 ------------------------------------------------------
3311 Hash Cond: (t2.b = t1.a)
3313 -> Seq Scan on prt2_adv_p1 t2_1
3314 -> Seq Scan on prt2_adv_p2 t2_2
3315 -> Seq Scan on prt2_adv_p3 t2_3
3318 -> Seq Scan on prt1_adv_p2 t1_1
3320 -> Seq Scan on prt1_adv_p3 t1_2
3322 -> Seq Scan on prt1_adv_p1 t1_3
3326 DROP TABLE prt1_adv_p3;
3328 DROP TABLE prt2_adv_p3;
3330 CREATE TABLE prt3_adv (a int, b int, c varchar) PARTITION BY RANGE (a);
3331 CREATE TABLE prt3_adv_p1 PARTITION OF prt3_adv FOR VALUES FROM (200) TO (300);
3332 CREATE TABLE prt3_adv_p2 PARTITION OF prt3_adv FOR VALUES FROM (300) TO (400);
3333 CREATE INDEX prt3_adv_a_idx ON prt3_adv (a);
3334 INSERT INTO prt3_adv SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(200, 399) i;
3336 -- 3-way join to test the default partition of a join relation
3338 SELECT t1.a, t1.c, t2.b, t2.c, t3.a, t3.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) LEFT JOIN prt3_adv t3 ON (t1.a = t3.a) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a;
3340 ------------------------------------------------------------------
3342 Sort Key: t1.a, t2.b, t3.a
3345 Hash Cond: (t3_1.a = t1_1.a)
3346 -> Seq Scan on prt3_adv_p1 t3_1
3349 Hash Cond: (t2_2.b = t1_1.a)
3350 -> Seq Scan on prt2_adv_p2 t2_2
3352 -> Seq Scan on prt1_adv_p2 t1_1
3355 Hash Cond: (t3_2.a = t1_2.a)
3356 -> Seq Scan on prt3_adv_p2 t3_2
3359 Hash Cond: (t2_1.b = t1_2.a)
3360 -> Seq Scan on prt2_adv_p1 t2_1
3362 -> Seq Scan on prt1_adv_p1 t1_2
3366 SELECT t1.a, t1.c, t2.b, t2.c, t3.a, t3.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) LEFT JOIN prt3_adv t3 ON (t1.a = t3.a) WHERE t1.b = 0 ORDER BY t1.a, t2.b, t3.a;
3367 a | c | b | c | a | c
3368 -----+------+-----+------+-----+------
3369 100 | 0100 | 100 | 0100 | |
3370 125 | 0125 | 125 | 0125 | |
3373 200 | 0200 | 200 | 0200 | 200 | 0200
3374 225 | 0225 | 225 | 0225 | 225 | 0225
3375 250 | 0250 | 250 | 0250 | 250 | 0250
3376 275 | 0275 | 275 | 0275 | 275 | 0275
3379 DROP TABLE prt1_adv;
3380 DROP TABLE prt2_adv;
3381 DROP TABLE prt3_adv;
3382 -- Test interaction of partitioned join with partition pruning
3383 CREATE TABLE prt1_adv (a int, b int, c varchar) PARTITION BY RANGE (a);
3384 CREATE TABLE prt1_adv_p1 PARTITION OF prt1_adv FOR VALUES FROM (100) TO (200);
3385 CREATE TABLE prt1_adv_p2 PARTITION OF prt1_adv FOR VALUES FROM (200) TO (300);
3386 CREATE TABLE prt1_adv_p3 PARTITION OF prt1_adv FOR VALUES FROM (300) TO (400);
3387 CREATE INDEX prt1_adv_a_idx ON prt1_adv (a);
3388 INSERT INTO prt1_adv SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
3390 CREATE TABLE prt2_adv (a int, b int, c varchar) PARTITION BY RANGE (b);
3391 CREATE TABLE prt2_adv_p1 PARTITION OF prt2_adv FOR VALUES FROM (100) TO (200);
3392 CREATE TABLE prt2_adv_p2 PARTITION OF prt2_adv FOR VALUES FROM (200) TO (400);
3393 CREATE INDEX prt2_adv_b_idx ON prt2_adv (b);
3394 INSERT INTO prt2_adv SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
3397 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
3399 -----------------------------------------------------------
3404 Hash Cond: (t2_1.b = t1_1.a)
3405 -> Seq Scan on prt2_adv_p1 t2_1
3407 -> Seq Scan on prt1_adv_p1 t1_1
3408 Filter: ((a < 300) AND (b = 0))
3410 Hash Cond: (t2_2.b = t1_2.a)
3411 -> Seq Scan on prt2_adv_p2 t2_2
3413 -> Seq Scan on prt1_adv_p2 t1_2
3414 Filter: ((a < 300) AND (b = 0))
3417 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
3419 -----+------+-----+------
3420 100 | 0100 | 100 | 0100
3421 125 | 0125 | 125 | 0125
3422 150 | 0150 | 150 | 0150
3423 175 | 0175 | 175 | 0175
3424 200 | 0200 | 200 | 0200
3425 225 | 0225 | 225 | 0225
3426 250 | 0250 | 250 | 0250
3427 275 | 0275 | 275 | 0275
3430 DROP TABLE prt1_adv_p3;
3431 CREATE TABLE prt1_adv_default PARTITION OF prt1_adv DEFAULT;
3433 CREATE TABLE prt2_adv_default PARTITION OF prt2_adv DEFAULT;
3436 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
3438 --------------------------------------------------------------------------
3443 Hash Cond: (t2_1.b = t1_1.a)
3444 -> Seq Scan on prt2_adv_p1 t2_1
3446 -> Seq Scan on prt1_adv_p1 t1_1
3447 Filter: ((a >= 100) AND (a < 300) AND (b = 0))
3449 Hash Cond: (t2_2.b = t1_2.a)
3450 -> Seq Scan on prt2_adv_p2 t2_2
3452 -> Seq Scan on prt1_adv_p2 t1_2
3453 Filter: ((a >= 100) AND (a < 300) AND (b = 0))
3456 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.a >= 100 AND t1.a < 300 AND t1.b = 0 ORDER BY t1.a, t2.b;
3458 -----+------+-----+------
3459 100 | 0100 | 100 | 0100
3460 125 | 0125 | 125 | 0125
3461 150 | 0150 | 150 | 0150
3462 175 | 0175 | 175 | 0175
3463 200 | 0200 | 200 | 0200
3464 225 | 0225 | 225 | 0225
3465 250 | 0250 | 250 | 0250
3466 275 | 0275 | 275 | 0275
3469 DROP TABLE prt1_adv;
3470 DROP TABLE prt2_adv;
3471 -- Tests for list-partitioned tables
3472 CREATE TABLE plt1_adv (a int, b int, c text) PARTITION BY LIST (c);
3473 CREATE TABLE plt1_adv_p1 PARTITION OF plt1_adv FOR VALUES IN ('0001', '0003');
3474 CREATE TABLE plt1_adv_p2 PARTITION OF plt1_adv FOR VALUES IN ('0004', '0006');
3475 CREATE TABLE plt1_adv_p3 PARTITION OF plt1_adv FOR VALUES IN ('0008', '0009');
3476 INSERT INTO plt1_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 3, 4, 6, 8, 9);
3478 CREATE TABLE plt2_adv (a int, b int, c text) PARTITION BY LIST (c);
3479 CREATE TABLE plt2_adv_p1 PARTITION OF plt2_adv FOR VALUES IN ('0002', '0003');
3480 CREATE TABLE plt2_adv_p2 PARTITION OF plt2_adv FOR VALUES IN ('0004', '0006');
3481 CREATE TABLE plt2_adv_p3 PARTITION OF plt2_adv FOR VALUES IN ('0007', '0009');
3482 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (2, 3, 4, 6, 7, 9);
3486 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3488 --------------------------------------------------------------------
3493 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3494 -> Seq Scan on plt2_adv_p1 t2_1
3496 -> Seq Scan on plt1_adv_p1 t1_1
3499 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3500 -> Seq Scan on plt2_adv_p2 t2_2
3502 -> Seq Scan on plt1_adv_p2 t1_2
3505 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3506 -> Seq Scan on plt2_adv_p3 t2_3
3508 -> Seq Scan on plt1_adv_p3 t1_3
3512 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3514 ---+------+---+------
3523 SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3525 --------------------------------------------------------------------
3529 -> Hash Right Semi Join
3530 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3531 -> Seq Scan on plt2_adv_p1 t2_1
3533 -> Seq Scan on plt1_adv_p1 t1_1
3535 -> Hash Right Semi Join
3536 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3537 -> Seq Scan on plt2_adv_p2 t2_2
3539 -> Seq Scan on plt1_adv_p2 t1_2
3541 -> Hash Right Semi Join
3542 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3543 -> Seq Scan on plt2_adv_p3 t2_3
3545 -> Seq Scan on plt1_adv_p3 t1_3
3549 SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3560 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3562 --------------------------------------------------------------------
3567 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3568 -> Seq Scan on plt2_adv_p1 t2_1
3570 -> Seq Scan on plt1_adv_p1 t1_1
3573 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3574 -> Seq Scan on plt2_adv_p2 t2_2
3576 -> Seq Scan on plt1_adv_p2 t1_2
3579 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3580 -> Seq Scan on plt2_adv_p3 t2_3
3582 -> Seq Scan on plt1_adv_p3 t1_3
3586 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3588 ---+------+---+------
3599 SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3601 --------------------------------------------------------------------
3605 -> Hash Right Anti Join
3606 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3607 -> Seq Scan on plt2_adv_p1 t2_1
3609 -> Seq Scan on plt1_adv_p1 t1_1
3611 -> Hash Right Anti Join
3612 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3613 -> Seq Scan on plt2_adv_p2 t2_2
3615 -> Seq Scan on plt1_adv_p2 t1_2
3617 -> Hash Right Anti Join
3618 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3619 -> Seq Scan on plt2_adv_p3 t2_3
3621 -> Seq Scan on plt1_adv_p3 t1_3
3625 SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3634 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
3636 -----------------------------------------------------------------------------------
3638 Sort Key: t1.a, t2.a
3641 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
3642 Filter: ((COALESCE(t1_1.b, 0) < 10) AND (COALESCE(t2_1.b, 0) < 10))
3643 -> Seq Scan on plt1_adv_p1 t1_1
3645 -> Seq Scan on plt2_adv_p1 t2_1
3647 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
3648 Filter: ((COALESCE(t1_2.b, 0) < 10) AND (COALESCE(t2_2.b, 0) < 10))
3649 -> Seq Scan on plt1_adv_p2 t1_2
3651 -> Seq Scan on plt2_adv_p2 t2_2
3653 Hash Cond: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
3654 Filter: ((COALESCE(t1_3.b, 0) < 10) AND (COALESCE(t2_3.b, 0) < 10))
3655 -> Seq Scan on plt1_adv_p3 t1_3
3657 -> Seq Scan on plt2_adv_p3 t2_3
3660 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
3662 ---+------+---+------
3673 -- Test cases where one side has an extra partition
3674 CREATE TABLE plt2_adv_extra PARTITION OF plt2_adv FOR VALUES IN ('0000');
3675 INSERT INTO plt2_adv_extra VALUES (0, 0, '0000');
3679 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3681 --------------------------------------------------------------------
3686 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3687 -> Seq Scan on plt2_adv_p1 t2_1
3689 -> Seq Scan on plt1_adv_p1 t1_1
3692 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3693 -> Seq Scan on plt2_adv_p2 t2_2
3695 -> Seq Scan on plt1_adv_p2 t1_2
3698 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3699 -> Seq Scan on plt2_adv_p3 t2_3
3701 -> Seq Scan on plt1_adv_p3 t1_3
3705 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3707 ---+------+---+------
3716 SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3718 --------------------------------------------------------------------
3722 -> Hash Right Semi Join
3723 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3724 -> Seq Scan on plt2_adv_p1 t2_1
3726 -> Seq Scan on plt1_adv_p1 t1_1
3728 -> Hash Right Semi Join
3729 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3730 -> Seq Scan on plt2_adv_p2 t2_2
3732 -> Seq Scan on plt1_adv_p2 t1_2
3734 -> Hash Right Semi Join
3735 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3736 -> Seq Scan on plt2_adv_p3 t2_3
3738 -> Seq Scan on plt1_adv_p3 t1_3
3742 SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3753 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3755 --------------------------------------------------------------------
3760 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3761 -> Seq Scan on plt2_adv_p1 t2_1
3763 -> Seq Scan on plt1_adv_p1 t1_1
3766 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3767 -> Seq Scan on plt2_adv_p2 t2_2
3769 -> Seq Scan on plt1_adv_p2 t1_2
3772 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3773 -> Seq Scan on plt2_adv_p3 t2_3
3775 -> Seq Scan on plt1_adv_p3 t1_3
3779 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3781 ---+------+---+------
3790 -- left join; currently we can't do partitioned join if there are no matched
3791 -- partitions on the nullable side
3793 SELECT t1.a, t1.c, t2.a, t2.c FROM plt2_adv t1 LEFT JOIN plt1_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3795 ---------------------------------------------------------
3799 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3801 -> Seq Scan on plt1_adv_p1 t2_1
3802 -> Seq Scan on plt1_adv_p2 t2_2
3803 -> Seq Scan on plt1_adv_p3 t2_3
3806 -> Seq Scan on plt2_adv_extra t1_1
3808 -> Seq Scan on plt2_adv_p1 t1_2
3810 -> Seq Scan on plt2_adv_p2 t1_3
3812 -> Seq Scan on plt2_adv_p3 t1_4
3818 SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3820 --------------------------------------------------------------------
3824 -> Hash Right Anti Join
3825 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3826 -> Seq Scan on plt2_adv_p1 t2_1
3828 -> Seq Scan on plt1_adv_p1 t1_1
3830 -> Hash Right Anti Join
3831 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3832 -> Seq Scan on plt2_adv_p2 t2_2
3834 -> Seq Scan on plt1_adv_p2 t1_2
3836 -> Hash Right Anti Join
3837 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3838 -> Seq Scan on plt2_adv_p3 t2_3
3840 -> Seq Scan on plt1_adv_p3 t1_3
3844 SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3851 -- anti join; currently we can't do partitioned join if there are no matched
3852 -- partitions on the nullable side
3854 SELECT t1.* FROM plt2_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt1_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3856 ---------------------------------------------------------
3859 -> Hash Right Anti Join
3860 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3862 -> Seq Scan on plt1_adv_p1 t2_1
3863 -> Seq Scan on plt1_adv_p2 t2_2
3864 -> Seq Scan on plt1_adv_p3 t2_3
3867 -> Seq Scan on plt2_adv_extra t1_1
3869 -> Seq Scan on plt2_adv_p1 t1_2
3871 -> Seq Scan on plt2_adv_p2 t1_3
3873 -> Seq Scan on plt2_adv_p3 t1_4
3877 -- full join; currently we can't do partitioned join if there are no matched
3878 -- partitions on the nullable side
3880 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
3882 -------------------------------------------------------------------------
3884 Sort Key: t1.a, t2.a
3886 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3887 Filter: ((COALESCE(t1.b, 0) < 10) AND (COALESCE(t2.b, 0) < 10))
3889 -> Seq Scan on plt2_adv_extra t2_1
3890 -> Seq Scan on plt2_adv_p1 t2_2
3891 -> Seq Scan on plt2_adv_p2 t2_3
3892 -> Seq Scan on plt2_adv_p3 t2_4
3895 -> Seq Scan on plt1_adv_p1 t1_1
3896 -> Seq Scan on plt1_adv_p2 t1_2
3897 -> Seq Scan on plt1_adv_p3 t1_3
3900 DROP TABLE plt2_adv_extra;
3901 -- Test cases where a partition on one side matches multiple partitions on
3902 -- the other side; we currently can't do partitioned join in such cases
3903 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p2;
3904 -- Split plt2_adv_p2 into two partitions so that plt1_adv_p2 matches both
3905 CREATE TABLE plt2_adv_p2_1 PARTITION OF plt2_adv FOR VALUES IN ('0004');
3906 CREATE TABLE plt2_adv_p2_2 PARTITION OF plt2_adv FOR VALUES IN ('0006');
3907 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (4, 6);
3911 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3913 ------------------------------------------------------
3917 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3919 -> Seq Scan on plt2_adv_p1 t2_1
3920 -> Seq Scan on plt2_adv_p2_1 t2_2
3921 -> Seq Scan on plt2_adv_p2_2 t2_3
3922 -> Seq Scan on plt2_adv_p3 t2_4
3925 -> Seq Scan on plt1_adv_p1 t1_1
3927 -> Seq Scan on plt1_adv_p2 t1_2
3929 -> Seq Scan on plt1_adv_p3 t1_3
3935 SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3937 ------------------------------------------------------
3940 -> Hash Right Semi Join
3941 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3943 -> Seq Scan on plt2_adv_p1 t2_1
3944 -> Seq Scan on plt2_adv_p2_1 t2_2
3945 -> Seq Scan on plt2_adv_p2_2 t2_3
3946 -> Seq Scan on plt2_adv_p3 t2_4
3949 -> Seq Scan on plt1_adv_p1 t1_1
3951 -> Seq Scan on plt1_adv_p2 t1_2
3953 -> Seq Scan on plt1_adv_p3 t1_3
3959 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
3961 ------------------------------------------------------
3965 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3967 -> Seq Scan on plt2_adv_p1 t2_1
3968 -> Seq Scan on plt2_adv_p2_1 t2_2
3969 -> Seq Scan on plt2_adv_p2_2 t2_3
3970 -> Seq Scan on plt2_adv_p3 t2_4
3973 -> Seq Scan on plt1_adv_p1 t1_1
3975 -> Seq Scan on plt1_adv_p2 t1_2
3977 -> Seq Scan on plt1_adv_p3 t1_3
3983 SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
3985 ------------------------------------------------------
3988 -> Hash Right Anti Join
3989 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3991 -> Seq Scan on plt2_adv_p1 t2_1
3992 -> Seq Scan on plt2_adv_p2_1 t2_2
3993 -> Seq Scan on plt2_adv_p2_2 t2_3
3994 -> Seq Scan on plt2_adv_p3 t2_4
3997 -> Seq Scan on plt1_adv_p1 t1_1
3999 -> Seq Scan on plt1_adv_p2 t1_2
4001 -> Seq Scan on plt1_adv_p3 t1_3
4007 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
4009 -------------------------------------------------------------------------
4011 Sort Key: t1.a, t2.a
4013 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4014 Filter: ((COALESCE(t1.b, 0) < 10) AND (COALESCE(t2.b, 0) < 10))
4016 -> Seq Scan on plt2_adv_p1 t2_1
4017 -> Seq Scan on plt2_adv_p2_1 t2_2
4018 -> Seq Scan on plt2_adv_p2_2 t2_3
4019 -> Seq Scan on plt2_adv_p3 t2_4
4022 -> Seq Scan on plt1_adv_p1 t1_1
4023 -> Seq Scan on plt1_adv_p2 t1_2
4024 -> Seq Scan on plt1_adv_p3 t1_3
4027 DROP TABLE plt2_adv_p2_1;
4028 DROP TABLE plt2_adv_p2_2;
4029 -- Restore plt2_adv_p2
4030 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p2 FOR VALUES IN ('0004', '0006');
4031 -- Test NULL partitions
4032 ALTER TABLE plt1_adv DETACH PARTITION plt1_adv_p1;
4033 -- Change plt1_adv_p1 to the NULL partition
4034 CREATE TABLE plt1_adv_p1_null PARTITION OF plt1_adv FOR VALUES IN (NULL, '0001', '0003');
4035 INSERT INTO plt1_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 3);
4036 INSERT INTO plt1_adv VALUES (-1, -1, NULL);
4038 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p3;
4039 -- Change plt2_adv_p3 to the NULL partition
4040 CREATE TABLE plt2_adv_p3_null PARTITION OF plt2_adv FOR VALUES IN (NULL, '0007', '0009');
4041 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (7, 9);
4042 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4046 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4048 --------------------------------------------------------------------
4053 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4054 -> Seq Scan on plt2_adv_p1 t2_1
4056 -> Seq Scan on plt1_adv_p1_null t1_1
4059 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4060 -> Seq Scan on plt2_adv_p2 t2_2
4062 -> Seq Scan on plt1_adv_p2 t1_2
4065 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4066 -> Seq Scan on plt2_adv_p3_null t2_3
4068 -> Seq Scan on plt1_adv_p3 t1_3
4072 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4074 ---+------+---+------
4083 SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
4085 --------------------------------------------------------------------
4089 -> Hash Right Semi Join
4090 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4091 -> Seq Scan on plt2_adv_p1 t2_1
4093 -> Seq Scan on plt1_adv_p1_null t1_1
4095 -> Hash Right Semi Join
4096 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4097 -> Seq Scan on plt2_adv_p2 t2_2
4099 -> Seq Scan on plt1_adv_p2 t1_2
4101 -> Hash Right Semi Join
4102 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4103 -> Seq Scan on plt2_adv_p3_null t2_3
4105 -> Seq Scan on plt1_adv_p3 t1_3
4109 SELECT t1.* FROM plt1_adv t1 WHERE EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
4120 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4122 --------------------------------------------------------------------
4127 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4128 -> Seq Scan on plt2_adv_p1 t2_1
4130 -> Seq Scan on plt1_adv_p1_null t1_1
4133 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4134 -> Seq Scan on plt2_adv_p2 t2_2
4136 -> Seq Scan on plt1_adv_p2 t1_2
4139 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4140 -> Seq Scan on plt2_adv_p3_null t2_3
4142 -> Seq Scan on plt1_adv_p3 t1_3
4146 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4148 ----+------+---+------
4160 SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
4162 --------------------------------------------------------------------
4166 -> Hash Right Anti Join
4167 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4168 -> Seq Scan on plt2_adv_p1 t2_1
4170 -> Seq Scan on plt1_adv_p1_null t1_1
4172 -> Hash Right Anti Join
4173 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4174 -> Seq Scan on plt2_adv_p2 t2_2
4176 -> Seq Scan on plt1_adv_p2 t1_2
4178 -> Hash Right Anti Join
4179 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4180 -> Seq Scan on plt2_adv_p3_null t2_3
4182 -> Seq Scan on plt1_adv_p3 t1_3
4186 SELECT t1.* FROM plt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM plt2_adv t2 WHERE t1.a = t2.a AND t1.c = t2.c) AND t1.b < 10 ORDER BY t1.a;
4196 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
4198 -----------------------------------------------------------------------------------
4200 Sort Key: t1.a, t2.a
4203 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
4204 Filter: ((COALESCE(t1_1.b, 0) < 10) AND (COALESCE(t2_1.b, 0) < 10))
4205 -> Seq Scan on plt1_adv_p1_null t1_1
4207 -> Seq Scan on plt2_adv_p1 t2_1
4209 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
4210 Filter: ((COALESCE(t1_2.b, 0) < 10) AND (COALESCE(t2_2.b, 0) < 10))
4211 -> Seq Scan on plt1_adv_p2 t1_2
4213 -> Seq Scan on plt2_adv_p2 t2_2
4215 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4216 Filter: ((COALESCE(t1_3.b, 0) < 10) AND (COALESCE(t2_3.b, 0) < 10))
4217 -> Seq Scan on plt2_adv_p3_null t2_3
4219 -> Seq Scan on plt1_adv_p3 t1_3
4222 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
4224 ----+------+----+------
4237 DROP TABLE plt1_adv_p1_null;
4238 -- Restore plt1_adv_p1
4239 ALTER TABLE plt1_adv ATTACH PARTITION plt1_adv_p1 FOR VALUES IN ('0001', '0003');
4240 -- Add to plt1_adv the extra NULL partition containing only NULL values as the
4242 CREATE TABLE plt1_adv_extra PARTITION OF plt1_adv FOR VALUES IN (NULL);
4243 INSERT INTO plt1_adv VALUES (-1, -1, NULL);
4245 DROP TABLE plt2_adv_p3_null;
4246 -- Restore plt2_adv_p3
4247 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p3 FOR VALUES IN ('0007', '0009');
4251 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4253 --------------------------------------------------------------------
4258 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4259 -> Seq Scan on plt2_adv_p1 t2_1
4261 -> Seq Scan on plt1_adv_p1 t1_1
4264 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4265 -> Seq Scan on plt2_adv_p2 t2_2
4267 -> Seq Scan on plt1_adv_p2 t1_2
4270 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4271 -> Seq Scan on plt2_adv_p3 t2_3
4273 -> Seq Scan on plt1_adv_p3 t1_3
4277 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4279 ---+------+---+------
4286 -- left join; currently we can't do partitioned join if there are no matched
4287 -- partitions on the nullable side
4289 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4291 ---------------------------------------------------------
4295 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4297 -> Seq Scan on plt2_adv_p1 t2_1
4298 -> Seq Scan on plt2_adv_p2 t2_2
4299 -> Seq Scan on plt2_adv_p3 t2_3
4302 -> Seq Scan on plt1_adv_p1 t1_1
4304 -> Seq Scan on plt1_adv_p2 t1_2
4306 -> Seq Scan on plt1_adv_p3 t1_3
4308 -> Seq Scan on plt1_adv_extra t1_4
4312 -- full join; currently we can't do partitioned join if there are no matched
4313 -- partitions on the nullable side
4315 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
4317 -------------------------------------------------------------------------
4319 Sort Key: t1.a, t2.a
4321 Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
4322 Filter: ((COALESCE(t1.b, 0) < 10) AND (COALESCE(t2.b, 0) < 10))
4324 -> Seq Scan on plt1_adv_p1 t1_1
4325 -> Seq Scan on plt1_adv_p2 t1_2
4326 -> Seq Scan on plt1_adv_p3 t1_3
4327 -> Seq Scan on plt1_adv_extra t1_4
4330 -> Seq Scan on plt2_adv_p1 t2_1
4331 -> Seq Scan on plt2_adv_p2 t2_2
4332 -> Seq Scan on plt2_adv_p3 t2_3
4335 -- Add to plt2_adv the extra NULL partition containing only NULL values as the
4337 CREATE TABLE plt2_adv_extra PARTITION OF plt2_adv FOR VALUES IN (NULL);
4338 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4342 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4344 --------------------------------------------------------------------
4349 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4350 -> Seq Scan on plt2_adv_p1 t2_1
4352 -> Seq Scan on plt1_adv_p1 t1_1
4355 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4356 -> Seq Scan on plt2_adv_p2 t2_2
4358 -> Seq Scan on plt1_adv_p2 t1_2
4361 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4362 -> Seq Scan on plt2_adv_p3 t2_3
4364 -> Seq Scan on plt1_adv_p3 t1_3
4368 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4370 ---+------+---+------
4379 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4381 ----------------------------------------------------------------------
4386 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4387 -> Seq Scan on plt2_adv_p1 t2_1
4389 -> Seq Scan on plt1_adv_p1 t1_1
4392 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4393 -> Seq Scan on plt2_adv_p2 t2_2
4395 -> Seq Scan on plt1_adv_p2 t1_2
4398 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4399 -> Seq Scan on plt2_adv_p3 t2_3
4401 -> Seq Scan on plt1_adv_p3 t1_3
4403 -> Nested Loop Left Join
4404 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
4405 -> Seq Scan on plt1_adv_extra t1_4
4407 -> Seq Scan on plt2_adv_extra t2_4
4410 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4412 ----+------+---+------
4424 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
4426 -----------------------------------------------------------------------------------
4428 Sort Key: t1.a, t2.a
4431 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
4432 Filter: ((COALESCE(t1_1.b, 0) < 10) AND (COALESCE(t2_1.b, 0) < 10))
4433 -> Seq Scan on plt1_adv_p1 t1_1
4435 -> Seq Scan on plt2_adv_p1 t2_1
4437 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
4438 Filter: ((COALESCE(t1_2.b, 0) < 10) AND (COALESCE(t2_2.b, 0) < 10))
4439 -> Seq Scan on plt1_adv_p2 t1_2
4441 -> Seq Scan on plt2_adv_p2 t2_2
4443 Hash Cond: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
4444 Filter: ((COALESCE(t1_3.b, 0) < 10) AND (COALESCE(t2_3.b, 0) < 10))
4445 -> Seq Scan on plt1_adv_p3 t1_3
4447 -> Seq Scan on plt2_adv_p3 t2_3
4449 Hash Cond: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
4450 Filter: ((COALESCE(t1_4.b, 0) < 10) AND (COALESCE(t2_4.b, 0) < 10))
4451 -> Seq Scan on plt1_adv_extra t1_4
4453 -> Seq Scan on plt2_adv_extra t2_4
4456 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 FULL JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE coalesce(t1.b, 0) < 10 AND coalesce(t2.b, 0) < 10 ORDER BY t1.a, t2.a;
4458 ----+------+----+------
4471 -- 3-way join to test the NULL partition of a join relation
4473 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) LEFT JOIN plt1_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE t1.b < 10 ORDER BY t1.a;
4475 --------------------------------------------------------------------------------
4480 Hash Cond: ((t3_1.a = t1_1.a) AND (t3_1.c = t1_1.c))
4481 -> Seq Scan on plt1_adv_p1 t3_1
4484 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4485 -> Seq Scan on plt2_adv_p1 t2_1
4487 -> Seq Scan on plt1_adv_p1 t1_1
4490 Hash Cond: ((t3_2.a = t1_2.a) AND (t3_2.c = t1_2.c))
4491 -> Seq Scan on plt1_adv_p2 t3_2
4494 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4495 -> Seq Scan on plt2_adv_p2 t2_2
4497 -> Seq Scan on plt1_adv_p2 t1_2
4500 Hash Cond: ((t3_3.a = t1_3.a) AND (t3_3.c = t1_3.c))
4501 -> Seq Scan on plt1_adv_p3 t3_3
4504 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4505 -> Seq Scan on plt2_adv_p3 t2_3
4507 -> Seq Scan on plt1_adv_p3 t1_3
4509 -> Nested Loop Left Join
4510 Join Filter: ((t1_4.a = t3_4.a) AND (t1_4.c = t3_4.c))
4511 -> Nested Loop Left Join
4512 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
4513 -> Seq Scan on plt1_adv_extra t1_4
4515 -> Seq Scan on plt2_adv_extra t2_4
4516 -> Seq Scan on plt1_adv_extra t3_4
4519 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) LEFT JOIN plt1_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE t1.b < 10 ORDER BY t1.a;
4520 a | c | a | c | a | c
4521 ----+------+---+------+---+------
4523 1 | 0001 | | | 1 | 0001
4524 3 | 0003 | 3 | 0003 | 3 | 0003
4525 4 | 0004 | 4 | 0004 | 4 | 0004
4526 6 | 0006 | 6 | 0006 | 6 | 0006
4527 8 | 0008 | | | 8 | 0008
4528 9 | 0009 | 9 | 0009 | 9 | 0009
4531 DROP TABLE plt1_adv_extra;
4532 DROP TABLE plt2_adv_extra;
4533 -- Test default partitions
4534 ALTER TABLE plt1_adv DETACH PARTITION plt1_adv_p1;
4535 -- Change plt1_adv_p1 to the default partition
4536 ALTER TABLE plt1_adv ATTACH PARTITION plt1_adv_p1 DEFAULT;
4537 DROP TABLE plt1_adv_p3;
4539 DROP TABLE plt2_adv_p3;
4541 -- We can do partitioned join even if only one of relations has the default
4544 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4546 --------------------------------------------------------------------
4551 Hash Cond: ((t2_1.a = t1_2.a) AND (t2_1.c = t1_2.c))
4552 -> Seq Scan on plt2_adv_p1 t2_1
4554 -> Seq Scan on plt1_adv_p1 t1_2
4557 Hash Cond: ((t2_2.a = t1_1.a) AND (t2_2.c = t1_1.c))
4558 -> Seq Scan on plt2_adv_p2 t2_2
4560 -> Seq Scan on plt1_adv_p2 t1_1
4564 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4566 ---+------+---+------
4572 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p2;
4573 -- Change plt2_adv_p2 to contain '0005' in addition to '0004' and '0006' as
4575 CREATE TABLE plt2_adv_p2_ext PARTITION OF plt2_adv FOR VALUES IN ('0004', '0005', '0006');
4576 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (4, 5, 6);
4578 -- Partitioned join can't be applied because the default partition of plt1_adv
4579 -- matches plt2_adv_p1 and plt2_adv_p2_ext
4581 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4583 ------------------------------------------------------
4587 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4589 -> Seq Scan on plt2_adv_p1 t2_1
4590 -> Seq Scan on plt2_adv_p2_ext t2_2
4593 -> Seq Scan on plt1_adv_p2 t1_1
4595 -> Seq Scan on plt1_adv_p1 t1_2
4599 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p2_ext;
4600 -- Change plt2_adv_p2_ext to the default partition
4601 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p2_ext DEFAULT;
4603 -- Partitioned join can't be applied because the default partition of plt1_adv
4604 -- matches plt2_adv_p1 and plt2_adv_p2_ext
4606 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4608 ------------------------------------------------------
4612 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4614 -> Seq Scan on plt2_adv_p1 t2_1
4615 -> Seq Scan on plt2_adv_p2_ext t2_2
4618 -> Seq Scan on plt1_adv_p2 t1_1
4620 -> Seq Scan on plt1_adv_p1 t1_2
4624 DROP TABLE plt2_adv_p2_ext;
4625 -- Restore plt2_adv_p2
4626 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p2 FOR VALUES IN ('0004', '0006');
4628 CREATE TABLE plt3_adv (a int, b int, c text) PARTITION BY LIST (c);
4629 CREATE TABLE plt3_adv_p1 PARTITION OF plt3_adv FOR VALUES IN ('0004', '0006');
4630 CREATE TABLE plt3_adv_p2 PARTITION OF plt3_adv FOR VALUES IN ('0007', '0009');
4631 INSERT INTO plt3_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (4, 6, 7, 9);
4633 -- 3-way join to test the default partition of a join relation
4635 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) LEFT JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE t1.b < 10 ORDER BY t1.a;
4637 --------------------------------------------------------------------------------
4642 Hash Cond: ((t3_1.a = t1_1.a) AND (t3_1.c = t1_1.c))
4643 -> Seq Scan on plt3_adv_p1 t3_1
4646 Hash Cond: ((t2_2.a = t1_1.a) AND (t2_2.c = t1_1.c))
4647 -> Seq Scan on plt2_adv_p2 t2_2
4649 -> Seq Scan on plt1_adv_p2 t1_1
4652 Hash Cond: ((t3_2.a = t1_2.a) AND (t3_2.c = t1_2.c))
4653 -> Seq Scan on plt3_adv_p2 t3_2
4656 Hash Cond: ((t2_1.a = t1_2.a) AND (t2_1.c = t1_2.c))
4657 -> Seq Scan on plt2_adv_p1 t2_1
4659 -> Seq Scan on plt1_adv_p1 t1_2
4663 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) LEFT JOIN plt3_adv t3 ON (t1.a = t3.a AND t1.c = t3.c) WHERE t1.b < 10 ORDER BY t1.a;
4664 a | c | a | c | a | c
4665 ---+------+---+------+---+------
4667 3 | 0003 | 3 | 0003 | |
4668 4 | 0004 | 4 | 0004 | 4 | 0004
4669 6 | 0006 | 6 | 0006 | 6 | 0006
4672 -- Test cases where one side has the default partition while the other side
4673 -- has the NULL partition
4674 DROP TABLE plt2_adv_p1;
4675 -- Add the NULL partition to plt2_adv
4676 CREATE TABLE plt2_adv_p1_null PARTITION OF plt2_adv FOR VALUES IN (NULL, '0001', '0003');
4677 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 3);
4678 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4681 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4683 --------------------------------------------------------------------
4688 Hash Cond: ((t2_1.a = t1_2.a) AND (t2_1.c = t1_2.c))
4689 -> Seq Scan on plt2_adv_p1_null t2_1
4691 -> Seq Scan on plt1_adv_p1 t1_2
4694 Hash Cond: ((t2_2.a = t1_1.a) AND (t2_2.c = t1_1.c))
4695 -> Seq Scan on plt2_adv_p2 t2_2
4697 -> Seq Scan on plt1_adv_p2 t1_1
4701 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4703 ---+------+---+------
4710 DROP TABLE plt2_adv_p1_null;
4711 -- Add the NULL partition that contains only NULL values as the key values
4712 CREATE TABLE plt2_adv_p1_null PARTITION OF plt2_adv FOR VALUES IN (NULL);
4713 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4716 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4718 ------------------------------------------------------
4722 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4723 -> Seq Scan on plt2_adv_p2 t2
4725 -> Seq Scan on plt1_adv_p2 t1
4729 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.b < 10 ORDER BY t1.a;
4731 ---+------+---+------
4736 DROP TABLE plt1_adv;
4737 DROP TABLE plt2_adv;
4738 DROP TABLE plt3_adv;
4739 -- Test interaction of partitioned join with partition pruning
4740 CREATE TABLE plt1_adv (a int, b int, c text) PARTITION BY LIST (c);
4741 CREATE TABLE plt1_adv_p1 PARTITION OF plt1_adv FOR VALUES IN ('0001');
4742 CREATE TABLE plt1_adv_p2 PARTITION OF plt1_adv FOR VALUES IN ('0002');
4743 CREATE TABLE plt1_adv_p3 PARTITION OF plt1_adv FOR VALUES IN ('0003');
4744 CREATE TABLE plt1_adv_p4 PARTITION OF plt1_adv FOR VALUES IN (NULL, '0004', '0005');
4745 INSERT INTO plt1_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 2, 3, 4, 5);
4746 INSERT INTO plt1_adv VALUES (-1, -1, NULL);
4748 CREATE TABLE plt2_adv (a int, b int, c text) PARTITION BY LIST (c);
4749 CREATE TABLE plt2_adv_p1 PARTITION OF plt2_adv FOR VALUES IN ('0001', '0002');
4750 CREATE TABLE plt2_adv_p2 PARTITION OF plt2_adv FOR VALUES IN (NULL);
4751 CREATE TABLE plt2_adv_p3 PARTITION OF plt2_adv FOR VALUES IN ('0003');
4752 CREATE TABLE plt2_adv_p4 PARTITION OF plt2_adv FOR VALUES IN ('0004', '0005');
4753 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 2, 3, 4, 5);
4754 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4757 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 ORDER BY t1.a;
4759 -----------------------------------------------------------------------------------------
4764 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4765 -> Seq Scan on plt2_adv_p3 t2_1
4767 -> Seq Scan on plt1_adv_p3 t1_1
4768 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4770 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4771 -> Seq Scan on plt2_adv_p4 t2_2
4773 -> Seq Scan on plt1_adv_p4 t1_2
4774 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4777 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 ORDER BY t1.a;
4779 ---+------+---+------
4786 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
4788 --------------------------------------------------------
4792 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4793 -> Seq Scan on plt2_adv_p4 t2
4795 -> Seq Scan on plt1_adv_p4 t1
4796 Filter: ((c IS NULL) AND (b < 10))
4799 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
4805 CREATE TABLE plt1_adv_default PARTITION OF plt1_adv DEFAULT;
4807 CREATE TABLE plt2_adv_default PARTITION OF plt2_adv DEFAULT;
4810 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 ORDER BY t1.a;
4812 -----------------------------------------------------------------------------------------
4817 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4818 -> Seq Scan on plt2_adv_p3 t2_1
4820 -> Seq Scan on plt1_adv_p3 t1_1
4821 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4823 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4824 -> Seq Scan on plt2_adv_p4 t2_2
4826 -> Seq Scan on plt1_adv_p4 t1_2
4827 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4830 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 INNER JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IN ('0003', '0004', '0005') AND t1.b < 10 ORDER BY t1.a;
4832 ---+------+---+------
4839 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
4841 --------------------------------------------------------
4845 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4846 -> Seq Scan on plt2_adv_p4 t2
4848 -> Seq Scan on plt1_adv_p4 t1
4849 Filter: ((c IS NULL) AND (b < 10))
4852 SELECT t1.a, t1.c, t2.a, t2.c FROM plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE t1.c IS NULL AND t1.b < 10 ORDER BY t1.a;
4858 DROP TABLE plt1_adv;
4859 DROP TABLE plt2_adv;
4860 -- Test the process_outer_partition() code path
4861 CREATE TABLE plt1_adv (a int, b int, c text) PARTITION BY LIST (c);
4862 CREATE TABLE plt1_adv_p1 PARTITION OF plt1_adv FOR VALUES IN ('0000', '0001', '0002');
4863 CREATE TABLE plt1_adv_p2 PARTITION OF plt1_adv FOR VALUES IN ('0003', '0004');
4864 INSERT INTO plt1_adv SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i;
4866 CREATE TABLE plt2_adv (a int, b int, c text) PARTITION BY LIST (c);
4867 CREATE TABLE plt2_adv_p1 PARTITION OF plt2_adv FOR VALUES IN ('0002');
4868 CREATE TABLE plt2_adv_p2 PARTITION OF plt2_adv FOR VALUES IN ('0003', '0004');
4869 INSERT INTO plt2_adv SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (2, 3, 4);
4871 CREATE TABLE plt3_adv (a int, b int, c text) PARTITION BY LIST (c);
4872 CREATE TABLE plt3_adv_p1 PARTITION OF plt3_adv FOR VALUES IN ('0001');
4873 CREATE TABLE plt3_adv_p2 PARTITION OF plt3_adv FOR VALUES IN ('0003', '0004');
4874 INSERT INTO plt3_adv SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i WHERE i % 5 IN (1, 3, 4);
4876 -- This tests that when merging partitions from plt1_adv and plt2_adv in
4877 -- merge_list_bounds(), process_outer_partition() returns an already-assigned
4878 -- merged partition when re-called with plt1_adv_p1 for the second list value
4879 -- '0001' of that partition
4881 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
4883 -----------------------------------------------------------------------------------------------
4885 Sort Key: t1.c, t1.a, t2.a, t3.a
4888 Hash Cond: (t1_1.c = t3_1.c)
4889 Filter: (((COALESCE(t1_1.a, 0) % 5) <> 3) AND ((COALESCE(t1_1.a, 0) % 5) <> 4))
4891 Hash Cond: (t1_1.c = t2_1.c)
4892 -> Seq Scan on plt1_adv_p1 t1_1
4894 -> Seq Scan on plt2_adv_p1 t2_1
4896 -> Seq Scan on plt3_adv_p1 t3_1
4898 Hash Cond: (t1_2.c = t3_2.c)
4899 Filter: (((COALESCE(t1_2.a, 0) % 5) <> 3) AND ((COALESCE(t1_2.a, 0) % 5) <> 4))
4901 Hash Cond: (t1_2.c = t2_2.c)
4902 -> Seq Scan on plt1_adv_p2 t1_2
4904 -> Seq Scan on plt2_adv_p2 t2_2
4906 -> Seq Scan on plt3_adv_p2 t3_2
4909 SELECT t1.a, t1.c, t2.a, t2.c, t3.a, t3.c FROM (plt1_adv t1 LEFT JOIN plt2_adv t2 ON (t1.c = t2.c)) FULL JOIN plt3_adv t3 ON (t1.c = t3.c) WHERE coalesce(t1.a, 0) % 5 != 3 AND coalesce(t1.a, 0) % 5 != 4 ORDER BY t1.c, t1.a, t2.a, t3.a;
4910 a | c | a | c | a | c
4911 ----+------+----+------+----+------
4917 1 | 0001 | | | 1 | 0001
4918 1 | 0001 | | | 6 | 0001
4919 1 | 0001 | | | 11 | 0001
4920 1 | 0001 | | | 16 | 0001
4921 1 | 0001 | | | 21 | 0001
4922 6 | 0001 | | | 1 | 0001
4923 6 | 0001 | | | 6 | 0001
4924 6 | 0001 | | | 11 | 0001
4925 6 | 0001 | | | 16 | 0001
4926 6 | 0001 | | | 21 | 0001
4927 11 | 0001 | | | 1 | 0001
4928 11 | 0001 | | | 6 | 0001
4929 11 | 0001 | | | 11 | 0001
4930 11 | 0001 | | | 16 | 0001
4931 11 | 0001 | | | 21 | 0001
4932 16 | 0001 | | | 1 | 0001
4933 16 | 0001 | | | 6 | 0001
4934 16 | 0001 | | | 11 | 0001
4935 16 | 0001 | | | 16 | 0001
4936 16 | 0001 | | | 21 | 0001
4937 21 | 0001 | | | 1 | 0001
4938 21 | 0001 | | | 6 | 0001
4939 21 | 0001 | | | 11 | 0001
4940 21 | 0001 | | | 16 | 0001
4941 21 | 0001 | | | 21 | 0001
4942 2 | 0002 | 2 | 0002 | |
4943 2 | 0002 | 7 | 0002 | |
4944 2 | 0002 | 12 | 0002 | |
4945 2 | 0002 | 17 | 0002 | |
4946 2 | 0002 | 22 | 0002 | |
4947 7 | 0002 | 2 | 0002 | |
4948 7 | 0002 | 7 | 0002 | |
4949 7 | 0002 | 12 | 0002 | |
4950 7 | 0002 | 17 | 0002 | |
4951 7 | 0002 | 22 | 0002 | |
4952 12 | 0002 | 2 | 0002 | |
4953 12 | 0002 | 7 | 0002 | |
4954 12 | 0002 | 12 | 0002 | |
4955 12 | 0002 | 17 | 0002 | |
4956 12 | 0002 | 22 | 0002 | |
4957 17 | 0002 | 2 | 0002 | |
4958 17 | 0002 | 7 | 0002 | |
4959 17 | 0002 | 12 | 0002 | |
4960 17 | 0002 | 17 | 0002 | |
4961 17 | 0002 | 22 | 0002 | |
4962 22 | 0002 | 2 | 0002 | |
4963 22 | 0002 | 7 | 0002 | |
4964 22 | 0002 | 12 | 0002 | |
4965 22 | 0002 | 17 | 0002 | |
4966 22 | 0002 | 22 | 0002 | |
4969 DROP TABLE plt1_adv;
4970 DROP TABLE plt2_adv;
4971 DROP TABLE plt3_adv;
4972 -- Tests for multi-level partitioned tables
4973 CREATE TABLE alpha (a double precision, b int, c text) PARTITION BY RANGE (a);
4974 CREATE TABLE alpha_neg PARTITION OF alpha FOR VALUES FROM ('-Infinity') TO (0) PARTITION BY RANGE (b);
4975 CREATE TABLE alpha_pos PARTITION OF alpha FOR VALUES FROM (0) TO (10.0) PARTITION BY LIST (c);
4976 CREATE TABLE alpha_neg_p1 PARTITION OF alpha_neg FOR VALUES FROM (100) TO (200);
4977 CREATE TABLE alpha_neg_p2 PARTITION OF alpha_neg FOR VALUES FROM (200) TO (300);
4978 CREATE TABLE alpha_neg_p3 PARTITION OF alpha_neg FOR VALUES FROM (300) TO (400);
4979 CREATE TABLE alpha_pos_p1 PARTITION OF alpha_pos FOR VALUES IN ('0001', '0003');
4980 CREATE TABLE alpha_pos_p2 PARTITION OF alpha_pos FOR VALUES IN ('0004', '0006');
4981 CREATE TABLE alpha_pos_p3 PARTITION OF alpha_pos FOR VALUES IN ('0008', '0009');
4982 INSERT INTO alpha_neg SELECT -1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(100, 399) i WHERE i % 10 IN (1, 3, 4, 6, 8, 9);
4983 INSERT INTO alpha_pos SELECT 1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(100, 399) i WHERE i % 10 IN (1, 3, 4, 6, 8, 9);
4985 CREATE TABLE beta (a double precision, b int, c text) PARTITION BY RANGE (a);
4986 CREATE TABLE beta_neg PARTITION OF beta FOR VALUES FROM (-10.0) TO (0) PARTITION BY RANGE (b);
4987 CREATE TABLE beta_pos PARTITION OF beta FOR VALUES FROM (0) TO ('Infinity') PARTITION BY LIST (c);
4988 CREATE TABLE beta_neg_p1 PARTITION OF beta_neg FOR VALUES FROM (100) TO (150);
4989 CREATE TABLE beta_neg_p2 PARTITION OF beta_neg FOR VALUES FROM (200) TO (300);
4990 CREATE TABLE beta_neg_p3 PARTITION OF beta_neg FOR VALUES FROM (350) TO (500);
4991 CREATE TABLE beta_pos_p1 PARTITION OF beta_pos FOR VALUES IN ('0002', '0003');
4992 CREATE TABLE beta_pos_p2 PARTITION OF beta_pos FOR VALUES IN ('0004', '0006');
4993 CREATE TABLE beta_pos_p3 PARTITION OF beta_pos FOR VALUES IN ('0007', '0009');
4994 INSERT INTO beta_neg SELECT -1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(100, 149) i WHERE i % 10 IN (2, 3, 4, 6, 7, 9);
4995 INSERT INTO beta_neg SELECT -1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(200, 299) i WHERE i % 10 IN (2, 3, 4, 6, 7, 9);
4996 INSERT INTO beta_neg SELECT -1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(350, 499) i WHERE i % 10 IN (2, 3, 4, 6, 7, 9);
4997 INSERT INTO beta_pos SELECT 1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(100, 149) i WHERE i % 10 IN (2, 3, 4, 6, 7, 9);
4998 INSERT INTO beta_pos SELECT 1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(200, 299) i WHERE i % 10 IN (2, 3, 4, 6, 7, 9);
4999 INSERT INTO beta_pos SELECT 1.0, i, to_char(i % 10, 'FM0000') FROM generate_series(350, 499) i WHERE i % 10 IN (2, 3, 4, 6, 7, 9);
5002 SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b) WHERE t1.b >= 125 AND t1.b < 225 ORDER BY t1.a, t1.b;
5004 --------------------------------------------------------------------
5006 Sort Key: t1.a, t1.b
5009 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b))
5010 -> Seq Scan on alpha_neg_p1 t1_1
5011 Filter: ((b >= 125) AND (b < 225))
5013 -> Seq Scan on beta_neg_p1 t2_1
5015 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.b = t1_2.b))
5016 -> Seq Scan on beta_neg_p2 t2_2
5018 -> Seq Scan on alpha_neg_p2 t1_2
5019 Filter: ((b >= 125) AND (b < 225))
5021 Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
5023 -> Seq Scan on beta_pos_p1 t2_4
5024 -> Seq Scan on beta_pos_p2 t2_5
5025 -> Seq Scan on beta_pos_p3 t2_6
5028 -> Seq Scan on alpha_pos_p1 t1_4
5029 Filter: ((b >= 125) AND (b < 225))
5030 -> Seq Scan on alpha_pos_p2 t1_5
5031 Filter: ((b >= 125) AND (b < 225))
5032 -> Seq Scan on alpha_pos_p3 t1_6
5033 Filter: ((b >= 125) AND (b < 225))
5036 SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b) WHERE t1.b >= 125 AND t1.b < 225 ORDER BY t1.a, t1.b;
5037 a | b | c | a | b | c
5038 ----+-----+------+----+-----+------
5039 -1 | 126 | 0006 | -1 | 126 | 0006
5040 -1 | 129 | 0009 | -1 | 129 | 0009
5041 -1 | 133 | 0003 | -1 | 133 | 0003
5042 -1 | 134 | 0004 | -1 | 134 | 0004
5043 -1 | 136 | 0006 | -1 | 136 | 0006
5044 -1 | 139 | 0009 | -1 | 139 | 0009
5045 -1 | 143 | 0003 | -1 | 143 | 0003
5046 -1 | 144 | 0004 | -1 | 144 | 0004
5047 -1 | 146 | 0006 | -1 | 146 | 0006
5048 -1 | 149 | 0009 | -1 | 149 | 0009
5049 -1 | 203 | 0003 | -1 | 203 | 0003
5050 -1 | 204 | 0004 | -1 | 204 | 0004
5051 -1 | 206 | 0006 | -1 | 206 | 0006
5052 -1 | 209 | 0009 | -1 | 209 | 0009
5053 -1 | 213 | 0003 | -1 | 213 | 0003
5054 -1 | 214 | 0004 | -1 | 214 | 0004
5055 -1 | 216 | 0006 | -1 | 216 | 0006
5056 -1 | 219 | 0009 | -1 | 219 | 0009
5057 -1 | 223 | 0003 | -1 | 223 | 0003
5058 -1 | 224 | 0004 | -1 | 224 | 0004
5059 1 | 126 | 0006 | 1 | 126 | 0006
5060 1 | 129 | 0009 | 1 | 129 | 0009
5061 1 | 133 | 0003 | 1 | 133 | 0003
5062 1 | 134 | 0004 | 1 | 134 | 0004
5063 1 | 136 | 0006 | 1 | 136 | 0006
5064 1 | 139 | 0009 | 1 | 139 | 0009
5065 1 | 143 | 0003 | 1 | 143 | 0003
5066 1 | 144 | 0004 | 1 | 144 | 0004
5067 1 | 146 | 0006 | 1 | 146 | 0006
5068 1 | 149 | 0009 | 1 | 149 | 0009
5069 1 | 203 | 0003 | 1 | 203 | 0003
5070 1 | 204 | 0004 | 1 | 204 | 0004
5071 1 | 206 | 0006 | 1 | 206 | 0006
5072 1 | 209 | 0009 | 1 | 209 | 0009
5073 1 | 213 | 0003 | 1 | 213 | 0003
5074 1 | 214 | 0004 | 1 | 214 | 0004
5075 1 | 216 | 0006 | 1 | 216 | 0006
5076 1 | 219 | 0009 | 1 | 219 | 0009
5077 1 | 223 | 0003 | 1 | 223 | 0003
5078 1 | 224 | 0004 | 1 | 224 | 0004
5082 SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b, t2.b;
5084 --------------------------------------------------------------------------------------------------------------------------------------
5086 Sort Key: t1.a, t1.b, t2.b
5089 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
5091 -> Seq Scan on alpha_neg_p1 t1_2
5092 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5093 -> Seq Scan on alpha_neg_p2 t1_3
5094 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5097 -> Seq Scan on beta_neg_p1 t2_2
5098 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5099 -> Seq Scan on beta_neg_p2 t2_3
5100 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5102 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
5103 -> Seq Scan on alpha_pos_p2 t1_4
5104 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5105 -> Seq Scan on beta_pos_p2 t2_4
5106 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5108 Join Filter: ((t1_5.a = t2_5.a) AND (t1_5.c = t2_5.c))
5109 -> Seq Scan on alpha_pos_p3 t1_5
5110 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5111 -> Seq Scan on beta_pos_p3 t2_5
5112 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5115 SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b, t2.b;
5116 a | b | c | a | b | c
5117 ----+-----+------+----+-----+------
5118 -1 | 104 | 0004 | -1 | 104 | 0004
5119 -1 | 104 | 0004 | -1 | 204 | 0004
5120 -1 | 109 | 0009 | -1 | 109 | 0009
5121 -1 | 109 | 0009 | -1 | 209 | 0009
5122 -1 | 204 | 0004 | -1 | 104 | 0004
5123 -1 | 204 | 0004 | -1 | 204 | 0004
5124 -1 | 209 | 0009 | -1 | 109 | 0009
5125 -1 | 209 | 0009 | -1 | 209 | 0009
5126 1 | 104 | 0004 | 1 | 104 | 0004
5127 1 | 104 | 0004 | 1 | 204 | 0004
5128 1 | 109 | 0009 | 1 | 109 | 0009
5129 1 | 109 | 0009 | 1 | 209 | 0009
5130 1 | 204 | 0004 | 1 | 104 | 0004
5131 1 | 204 | 0004 | 1 | 204 | 0004
5132 1 | 209 | 0009 | 1 | 109 | 0009
5133 1 | 209 | 0009 | 1 | 209 | 0009
5137 SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
5139 --------------------------------------------------------------------------------------------------------------------------------
5141 Sort Key: t1.a, t1.b
5144 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
5145 -> Seq Scan on alpha_neg_p1 t1_1
5146 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5148 -> Seq Scan on beta_neg_p1 t2_1
5149 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5151 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
5152 -> Seq Scan on alpha_neg_p2 t1_2
5153 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5155 -> Seq Scan on beta_neg_p2 t2_2
5156 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5158 Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
5159 -> Seq Scan on alpha_pos_p2 t1_3
5160 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5161 -> Seq Scan on beta_pos_p2 t2_3
5162 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5164 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.b = t2_4.b) AND (t1_4.c = t2_4.c))
5165 -> Seq Scan on alpha_pos_p3 t1_4
5166 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5167 -> Seq Scan on beta_pos_p3 t2_4
5168 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5171 SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) WHERE ((t1.b >= 100 AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
5172 a | b | c | a | b | c
5173 ----+-----+------+----+-----+------
5174 -1 | 104 | 0004 | -1 | 104 | 0004
5175 -1 | 109 | 0009 | -1 | 109 | 0009
5176 -1 | 204 | 0004 | -1 | 204 | 0004
5177 -1 | 209 | 0009 | -1 | 209 | 0009
5178 1 | 104 | 0004 | 1 | 104 | 0004
5179 1 | 109 | 0009 | 1 | 109 | 0009
5180 1 | 204 | 0004 | 1 | 204 | 0004
5181 1 | 209 | 0009 | 1 | 209 | 0009
5184 -- partitionwise join with fractional paths
5185 CREATE TABLE fract_t (id BIGINT, PRIMARY KEY (id)) PARTITION BY RANGE (id);
5186 CREATE TABLE fract_t0 PARTITION OF fract_t FOR VALUES FROM ('0') TO ('1000');
5187 CREATE TABLE fract_t1 PARTITION OF fract_t FOR VALUES FROM ('1000') TO ('2000');
5189 INSERT INTO fract_t (id) (SELECT generate_series(0, 1999));
5191 -- verify plan; nested index only scans
5192 SET max_parallel_workers_per_gather = 0;
5193 SET enable_partitionwise_join = on;
5195 SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
5197 -----------------------------------------------------------------------
5202 Merge Cond: (x_1.id = y_1.id)
5203 -> Index Only Scan using fract_t0_pkey on fract_t0 x_1
5204 -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
5206 Merge Cond: (x_2.id = y_2.id)
5207 -> Index Only Scan using fract_t1_pkey on fract_t1 x_2
5208 -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
5212 SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10;
5214 --------------------------------------------------------------------------------
5218 -> Nested Loop Left Join
5219 -> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
5220 -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
5221 Index Cond: (id = x_1.id)
5222 -> Nested Loop Left Join
5223 -> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
5224 -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
5225 Index Cond: (id = x_2.id)
5230 RESET max_parallel_workers_per_gather;
5231 RESET enable_partitionwise_join;