| blob | 108f9ecb445821d286ee74df408a9f31e8cabfd8 |
1 --
2 -- PARTITION_JOIN
3 -- Test partitionwise join between partitioned tables
4 --
5 -- Enable partitionwise join, which by default is disabled.
6 SET enable_partitionwise_join to true;
7 --
8 -- partitioned by a single column
9 --
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);
18 ANALYZE prt1;
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);
27 ANALYZE prt2;
28 -- inner join
29 EXPLAIN (COSTS OFF)
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;
31 QUERY PLAN
32 --------------------------------------------------
33 Sort
34 Sort Key: t1.a
35 -> Append
36 -> Hash Join
37 Hash Cond: (t2_1.b = t1_1.a)
38 -> Seq Scan on prt2_p1 t2_1
39 -> Hash
40 -> Seq Scan on prt1_p1 t1_1
41 Filter: (b = 0)
42 -> Hash Join
43 Hash Cond: (t2_2.b = t1_2.a)
44 -> Seq Scan on prt2_p2 t2_2
45 -> Hash
46 -> Seq Scan on prt1_p2 t1_2
47 Filter: (b = 0)
48 -> Hash Join
49 Hash Cond: (t2_3.b = t1_3.a)
50 -> Seq Scan on prt2_p3 t2_3
51 -> Hash
52 -> Seq Scan on prt1_p3 t1_3
53 Filter: (b = 0)
54 (21 rows)
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;
57 a | c | b | c
58 -----+------+-----+------
59 0 | 0000 | 0 | 0000
60 150 | 0150 | 150 | 0150
61 300 | 0300 | 300 | 0300
62 450 | 0450 | 450 | 0450
63 (4 rows)
65 -- inner join with partially-redundant join clauses
66 EXPLAIN (COSTS OFF)
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;
68 QUERY PLAN
69 ---------------------------------------------------------------
70 Sort
71 Sort Key: t1.a
72 -> Append
73 -> Merge Join
74 Merge Cond: (t1_1.a = t2_1.a)
75 -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
76 -> Sort
77 Sort Key: t2_1.b
78 -> Seq Scan on prt2_p1 t2_1
79 Filter: (a = b)
80 -> Hash Join
81 Hash Cond: (t1_2.a = t2_2.a)
82 -> Seq Scan on prt1_p2 t1_2
83 -> Hash
84 -> Seq Scan on prt2_p2 t2_2
85 Filter: (a = b)
86 -> Hash Join
87 Hash Cond: (t1_3.a = t2_3.a)
88 -> Seq Scan on prt1_p3 t1_3
89 -> Hash
90 -> Seq Scan on prt2_p3 t2_3
91 Filter: (a = b)
92 (22 rows)
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;
95 a | c | b | c
96 ----+------+----+------
97 0 | 0000 | 0 | 0000
98 6 | 0006 | 6 | 0006
99 12 | 0012 | 12 | 0012
100 18 | 0018 | 18 | 0018
101 24 | 0024 | 24 | 0024
102 (5 rows)
104 -- left outer join, 3-way
105 EXPLAIN (COSTS OFF)
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;
109 QUERY PLAN
110 --------------------------------------------------------
111 Aggregate
112 -> Append
113 -> Hash Left Join
114 Hash Cond: (t2_1.a = t3_1.a)
115 -> Hash Left Join
116 Hash Cond: (t1_1.a = t2_1.a)
117 -> Seq Scan on prt1_p1 t1_1
118 -> Hash
119 -> Seq Scan on prt1_p1 t2_1
120 -> Hash
121 -> Seq Scan on prt1_p1 t3_1
122 -> Hash Left Join
123 Hash Cond: (t2_2.a = t3_2.a)
124 -> Hash Left Join
125 Hash Cond: (t1_2.a = t2_2.a)
126 -> Seq Scan on prt1_p2 t1_2
127 -> Hash
128 -> Seq Scan on prt1_p2 t2_2
129 -> Hash
130 -> Seq Scan on prt1_p2 t3_2
131 -> Hash Left Join
132 Hash Cond: (t2_3.a = t3_3.a)
133 -> Hash Left Join
134 Hash Cond: (t1_3.a = t2_3.a)
135 -> Seq Scan on prt1_p3 t1_3
136 -> Hash
137 -> Seq Scan on prt1_p3 t2_3
138 -> Hash
139 -> Seq Scan on prt1_p3 t3_3
140 (29 rows)
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;
145 count
146 -------
147 300
148 (1 row)
150 -- left outer join, with whole-row reference; partitionwise join does not apply
151 EXPLAIN (COSTS OFF)
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;
153 QUERY PLAN
154 --------------------------------------------------
155 Sort
156 Sort Key: t1.a, t2.b
157 -> Hash Right Join
158 Hash Cond: (t2.b = t1.a)
159 -> Append
160 -> Seq Scan on prt2_p1 t2_1
161 -> Seq Scan on prt2_p2 t2_2
162 -> Seq Scan on prt2_p3 t2_3
163 -> Hash
164 -> Append
165 -> Seq Scan on prt1_p1 t1_1
166 Filter: (b = 0)
167 -> Seq Scan on prt1_p2 t1_2
168 Filter: (b = 0)
169 -> Seq Scan on prt1_p3 t1_3
170 Filter: (b = 0)
171 (16 rows)
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;
174 t1 | t2
175 --------------+--------------
176 (0,0,0000) | (0,0,0000)
177 (50,0,0050) |
178 (100,0,0100) |
179 (150,0,0150) | (0,150,0150)
180 (200,0,0200) |
181 (250,0,0250) |
182 (300,0,0300) | (0,300,0300)
183 (350,0,0350) |
184 (400,0,0400) |
185 (450,0,0450) | (0,450,0450)
186 (500,0,0500) |
187 (550,0,0550) |
188 (12 rows)
190 -- right outer join
191 EXPLAIN (COSTS OFF)
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;
193 QUERY PLAN
194 ---------------------------------------------------------------
195 Sort
196 Sort Key: t1.a, t2.b
197 -> Append
198 -> Hash Right Join
199 Hash Cond: (t1_1.a = t2_1.b)
200 -> Seq Scan on prt1_p1 t1_1
201 -> Hash
202 -> Seq Scan on prt2_p1 t2_1
203 Filter: (a = 0)
204 -> Hash Right Join
205 Hash Cond: (t1_2.a = t2_2.b)
206 -> Seq Scan on prt1_p2 t1_2
207 -> Hash
208 -> Seq Scan on prt2_p2 t2_2
209 Filter: (a = 0)
210 -> Nested Loop Left Join
211 -> Seq Scan on prt2_p3 t2_3
212 Filter: (a = 0)
213 -> Index Scan using iprt1_p3_a on prt1_p3 t1_3
214 Index Cond: (a = t2_3.b)
215 (20 rows)
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;
218 a | c | b | c
219 -----+------+-----+------
220 0 | 0000 | 0 | 0000
221 150 | 0150 | 150 | 0150
222 300 | 0300 | 300 | 0300
223 450 | 0450 | 450 | 0450
224 | | 75 | 0075
225 | | 225 | 0225
226 | | 375 | 0375
227 | | 525 | 0525
228 (8 rows)
230 -- full outer join, with placeholder vars
231 EXPLAIN (COSTS OFF)
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;
233 QUERY PLAN
234 ----------------------------------------------------------------
235 Sort
236 Sort Key: prt1.a, prt2.b
237 -> Append
238 -> Hash Full Join
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
242 Filter: (b = 0)
243 -> Hash
244 -> Seq Scan on prt2_p1 prt2_1
245 Filter: (a = 0)
246 -> Hash Full Join
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
250 Filter: (b = 0)
251 -> Hash
252 -> Seq Scan on prt2_p2 prt2_2
253 Filter: (a = 0)
254 -> Hash Full Join
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
258 Filter: (b = 0)
259 -> Hash
260 -> Seq Scan on prt2_p3 prt2_3
261 Filter: (a = 0)
262 (27 rows)
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;
265 a | c | b | c
266 ----+------+----+------
267 50 | 0050 | |
268 | | 75 | 0075
269 (2 rows)
271 -- Join with pruned partitions from joining relations
272 EXPLAIN (COSTS OFF)
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;
274 QUERY PLAN
275 -----------------------------------------------------
276 Sort
277 Sort Key: t1.a
278 -> Hash Join
279 Hash Cond: (t2.b = t1.a)
280 -> Seq Scan on prt2_p2 t2
281 Filter: (b > 250)
282 -> Hash
283 -> Seq Scan on prt1_p2 t1
284 Filter: ((a < 450) AND (b = 0))
285 (9 rows)
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;
288 a | c | b | c
289 -----+------+-----+------
290 300 | 0300 | 300 | 0300
291 (1 row)
293 -- Currently we can't do partitioned join if nullable-side partitions are pruned
294 EXPLAIN (COSTS OFF)
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;
296 QUERY PLAN
297 -----------------------------------------------------------
298 Sort
299 Sort Key: prt1.a, prt2.b
300 -> Hash Right Join
301 Hash Cond: (prt2.b = prt1.a)
302 -> Append
303 -> Seq Scan on prt2_p2 prt2_1
304 Filter: (b > 250)
305 -> Seq Scan on prt2_p3 prt2_2
306 Filter: (b > 250)
307 -> Hash
308 -> Append
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))
313 (15 rows)
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;
316 a | c | b | c
317 -----+------+-----+------
318 0 | 0000 | |
319 50 | 0050 | |
320 100 | 0100 | |
321 150 | 0150 | |
322 200 | 0200 | |
323 250 | 0250 | |
324 300 | 0300 | 300 | 0300
325 350 | 0350 | |
326 400 | 0400 | |
327 (9 rows)
329 -- Currently we can't do partitioned join if nullable-side partitions are pruned
330 EXPLAIN (COSTS OFF)
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;
332 QUERY PLAN
333 ----------------------------------------------------
334 Sort
335 Sort Key: prt1.a, prt2.b
336 -> Hash Full Join
337 Hash Cond: (prt1.a = prt2.b)
338 Filter: ((prt1.b = 0) OR (prt2.a = 0))
339 -> Append
340 -> Seq Scan on prt1_p1 prt1_1
341 Filter: (a < 450)
342 -> Seq Scan on prt1_p2 prt1_2
343 Filter: (a < 450)
344 -> Hash
345 -> Append
346 -> Seq Scan on prt2_p2 prt2_1
347 Filter: (b > 250)
348 -> Seq Scan on prt2_p3 prt2_2
349 Filter: (b > 250)
350 (16 rows)
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;
353 a | c | b | c
354 -----+------+-----+------
355 0 | 0000 | |
356 50 | 0050 | |
357 100 | 0100 | |
358 150 | 0150 | |
359 200 | 0200 | |
360 250 | 0250 | |
361 300 | 0300 | 300 | 0300
362 350 | 0350 | |
363 400 | 0400 | |
364 | | 375 | 0375
365 | | 450 | 0450
366 | | 525 | 0525
367 (12 rows)
369 -- Semi-join
370 EXPLAIN (COSTS OFF)
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;
372 QUERY PLAN
373 --------------------------------------------------
374 Sort
375 Sort Key: t1.a
376 -> Append
377 -> Hash Semi Join
378 Hash Cond: (t1_1.a = t2_1.b)
379 -> Seq Scan on prt1_p1 t1_1
380 Filter: (b = 0)
381 -> Hash
382 -> Seq Scan on prt2_p1 t2_1
383 Filter: (a = 0)
384 -> Hash Semi Join
385 Hash Cond: (t1_2.a = t2_2.b)
386 -> Seq Scan on prt1_p2 t1_2
387 Filter: (b = 0)
388 -> Hash
389 -> Seq Scan on prt2_p2 t2_2
390 Filter: (a = 0)
391 -> Nested Loop Semi Join
392 Join Filter: (t1_3.a = t2_3.b)
393 -> Seq Scan on prt1_p3 t1_3
394 Filter: (b = 0)
395 -> Materialize
396 -> Seq Scan on prt2_p3 t2_3
397 Filter: (a = 0)
398 (24 rows)
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;
401 a | b | c
402 -----+---+------
403 0 | 0 | 0000
404 150 | 0 | 0150
405 300 | 0 | 0300
406 450 | 0 | 0450
407 (4 rows)
409 -- Anti-join with aggregates
410 EXPLAIN (COSTS OFF)
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);
412 QUERY PLAN
413 --------------------------------------------------
414 Aggregate
415 -> Append
416 -> Hash Anti Join
417 Hash Cond: (t1_1.a = t2_1.b)
418 -> Seq Scan on prt1_p1 t1_1
419 -> Hash
420 -> Seq Scan on prt2_p1 t2_1
421 -> Hash Anti Join
422 Hash Cond: (t1_2.a = t2_2.b)
423 -> Seq Scan on prt1_p2 t1_2
424 -> Hash
425 -> Seq Scan on prt2_p2 t2_2
426 -> Hash Anti Join
427 Hash Cond: (t1_3.a = t2_3.b)
428 -> Seq Scan on prt1_p3 t1_3
429 -> Hash
430 -> Seq Scan on prt2_p3 t2_3
431 (17 rows)
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
437 (1 row)
439 -- lateral reference
440 EXPLAIN (COSTS OFF)
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;
444 QUERY PLAN
445 --------------------------------------------------------------------------
446 Sort
447 Sort Key: t1.a
448 -> Append
449 -> Nested Loop Left Join
450 -> Seq Scan on prt1_p1 t1_1
451 Filter: (b = 0)
452 -> Nested Loop
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
459 Filter: (b = 0)
460 -> Nested Loop
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
467 Filter: (b = 0)
468 -> Nested Loop
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)
473 (27 rows)
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
481 50 | 0 | 0050 | | |
482 100 | 0 | 0100 | | |
483 150 | 0 | 0150 | 150 | 0 | 150
484 200 | 0 | 0200 | | |
485 250 | 0 | 0250 | | |
486 300 | 0 | 0300 | 300 | 0 | 300
487 350 | 0 | 0350 | | |
488 400 | 0 | 0400 | | |
489 450 | 0 | 0450 | 450 | 0 | 450
490 500 | 0 | 0500 | | |
491 550 | 0 | 0550 | | |
492 (12 rows)
494 EXPLAIN (COSTS OFF)
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;
498 QUERY PLAN
499 --------------------------------------------------------------
500 Sort
501 Sort Key: t1.a
502 -> Hash Left Join
503 Hash Cond: ((t1.c)::text = (t2.c)::text)
504 Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
505 -> Append
506 -> Seq Scan on prt1_p1 t1_1
507 -> Seq Scan on prt1_p2 t1_2
508 -> Seq Scan on prt1_p3 t1_3
509 -> Hash
510 -> Append
511 -> Hash Join
512 Hash Cond: (t2_1.a = t3_1.b)
513 -> Seq Scan on prt1_p1 t2_1
514 -> Hash
515 -> Seq Scan on prt2_p1 t3_1
516 -> Hash Join
517 Hash Cond: (t2_2.a = t3_2.b)
518 -> Seq Scan on prt1_p2 t2_2
519 -> Hash
520 -> Seq Scan on prt2_p2 t3_2
521 -> Hash Join
522 Hash Cond: (t2_3.a = t3_3.b)
523 -> Seq Scan on prt1_p3 t2_3
524 -> Hash
525 -> Seq Scan on prt2_p3 t3_3
526 (26 rows)
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;
531 a | t2a | t2c
532 -----+-----+------
533 0 | 0 | 0000
534 50 | |
535 100 | |
536 150 | 150 | 0150
537 200 | |
538 250 | |
539 300 | 300 | 0300
540 350 | |
541 400 | |
542 450 | 450 | 0450
543 500 | |
544 550 | |
545 (12 rows)
547 -- lateral reference in sample scan
548 EXPLAIN (COSTS OFF)
549 SELECT * FROM prt1 t1 JOIN LATERAL
550 (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
551 ON t1.a = s.a;
552 QUERY PLAN
553 -------------------------------------------------------------
554 Append
555 -> Nested Loop
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)
559 Filter: (t1_1.a = a)
560 -> Nested Loop
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)
564 Filter: (t1_2.a = a)
565 -> Nested Loop
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)
569 Filter: (t1_3.a = a)
570 (16 rows)
572 -- lateral reference in scan's restriction clauses
573 EXPLAIN (COSTS OFF)
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;
577 QUERY PLAN
578 ---------------------------------------------------------------
579 Aggregate
580 -> Append
581 -> Nested Loop
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)
585 Filter: (t1_1.b = a)
586 -> Nested Loop
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)
590 Filter: (t1_2.b = a)
591 -> Nested Loop
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)
595 Filter: (t1_3.b = a)
596 (17 rows)
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;
601 count
602 -------
603 100
604 (1 row)
606 EXPLAIN (COSTS OFF)
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;
610 QUERY PLAN
611 --------------------------------------------------------------------
612 Aggregate
613 -> Append
614 -> Nested Loop
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)
618 Filter: (b = t1_1.b)
619 -> Nested Loop
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)
623 Filter: (b = t1_2.b)
624 -> Nested Loop
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)
628 Filter: (b = t1_3.b)
629 (17 rows)
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;
634 count
635 -------
636 5
637 (1 row)
639 -- bug with inadequate sort key representation
640 SET enable_partitionwise_aggregate TO true;
641 SET enable_hashjoin TO false;
642 EXPLAIN (COSTS OFF)
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;
646 QUERY PLAN
647 -----------------------------------------------------------------------------------------------------------------
648 Group
649 Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
650 -> Merge Append
651 Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
652 -> Group
653 Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
654 -> Sort
655 Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
656 -> Merge Full Join
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))
659 -> Sort
660 Sort Key: prt1.a, prt1.b
661 -> Seq Scan on prt1_p1 prt1
662 -> Sort
663 Sort Key: p2.a, p2.b
664 -> Seq Scan on prt2_p1 p2
665 -> Group
666 Group Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
667 -> Sort
668 Sort Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
669 -> Merge Full Join
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))
672 -> Sort
673 Sort Key: prt1_1.a, prt1_1.b
674 -> Seq Scan on prt1_p2 prt1_1
675 -> Sort
676 Sort Key: p2_1.a, p2_1.b
677 -> Seq Scan on prt2_p2 p2_1
678 -> Group
679 Group Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
680 -> Sort
681 Sort Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
682 -> Merge Full Join
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))
685 -> Sort
686 Sort Key: prt1_2.a, prt1_2.b
687 -> Seq Scan on prt1_p3 prt1_2
688 -> Sort
689 Sort Key: p2_2.a, p2_2.b
690 -> Seq Scan on prt2_p3 p2_2
691 (43 rows)
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;
696 a | b
697 -----+----
698 490 | 15
699 492 | 17
700 494 | 19
701 495 | 20
702 496 | 21
703 498 | 23
704 500 | 0
705 501 | 1
706 502 | 2
707 504 | 4
708 506 | 6
709 507 | 7
710 508 | 8
711 510 | 10
712 (14 rows)
714 RESET enable_partitionwise_aggregate;
715 RESET enable_hashjoin;
716 --
717 -- partitioned by expression
718 --
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));
727 ANALYZE prt1_e;
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;
733 ANALYZE prt2_e;
734 EXPLAIN (COSTS OFF)
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;
736 QUERY PLAN
737 ------------------------------------------------------------------------------
738 Sort
739 Sort Key: t1.a, t2.b
740 -> Append
741 -> Hash Join
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
744 -> Hash
745 -> Seq Scan on prt1_e_p1 t1_1
746 Filter: (c = 0)
747 -> Hash Join
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
750 -> Hash
751 -> Seq Scan on prt1_e_p2 t1_2
752 Filter: (c = 0)
753 -> Hash Join
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
756 -> Hash
757 -> Seq Scan on prt1_e_p3 t1_3
758 Filter: (c = 0)
759 (21 rows)
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;
762 a | c | b | c
763 -----+---+-----+---
764 0 | 0 | 0 | 0
765 150 | 0 | 150 | 0
766 300 | 0 | 300 | 0
767 450 | 0 | 450 | 0
768 (4 rows)
770 --
771 -- N-way join
772 --
773 EXPLAIN (COSTS OFF)
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;
775 QUERY PLAN
776 ---------------------------------------------------------------------
777 Sort
778 Sort Key: t1.a
779 -> Append
780 -> Nested Loop
781 Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
782 -> Hash Join
783 Hash Cond: (t2_1.b = t1_1.a)
784 -> Seq Scan on prt2_p1 t2_1
785 -> Hash
786 -> Seq Scan on prt1_p1 t1_1
787 Filter: (b = 0)
788 -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3_1
789 Index Cond: (((a + b) / 2) = t2_1.b)
790 -> Nested Loop
791 Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
792 -> Hash Join
793 Hash Cond: (t2_2.b = t1_2.a)
794 -> Seq Scan on prt2_p2 t2_2
795 -> Hash
796 -> Seq Scan on prt1_p2 t1_2
797 Filter: (b = 0)
798 -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_2
799 Index Cond: (((a + b) / 2) = t2_2.b)
800 -> Nested Loop
801 Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
802 -> Hash Join
803 Hash Cond: (t2_3.b = t1_3.a)
804 -> Seq Scan on prt2_p3 t2_3
805 -> Hash
806 -> Seq Scan on prt1_p3 t1_3
807 Filter: (b = 0)
808 -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3
809 Index Cond: (((a + b) / 2) = t2_3.b)
810 (33 rows)
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
819 (4 rows)
821 EXPLAIN (COSTS OFF)
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;
823 QUERY PLAN
824 --------------------------------------------------------------
825 Sort
826 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
827 -> Append
828 -> Hash Right Join
829 Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
830 -> Seq Scan on prt1_e_p1 t3_1
831 -> Hash
832 -> Hash Right Join
833 Hash Cond: (t2_1.b = t1_1.a)
834 -> Seq Scan on prt2_p1 t2_1
835 -> Hash
836 -> Seq Scan on prt1_p1 t1_1
837 Filter: (b = 0)
838 -> Hash Right Join
839 Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
840 -> Seq Scan on prt1_e_p2 t3_2
841 -> Hash
842 -> Hash Right Join
843 Hash Cond: (t2_2.b = t1_2.a)
844 -> Seq Scan on prt2_p2 t2_2
845 -> Hash
846 -> Seq Scan on prt1_p2 t1_2
847 Filter: (b = 0)
848 -> Hash Right Join
849 Hash Cond: (((t3_3.a + t3_3.b) / 2) = t1_3.a)
850 -> Seq Scan on prt1_e_p3 t3_3
851 -> Hash
852 -> Hash Right Join
853 Hash Cond: (t2_3.b = t1_3.a)
854 -> Seq Scan on prt2_p3 t2_3
855 -> Hash
856 -> Seq Scan on prt1_p3 t1_3
857 Filter: (b = 0)
858 (33 rows)
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
875 (12 rows)
877 EXPLAIN (COSTS OFF)
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;
879 QUERY PLAN
880 -------------------------------------------------------------------
881 Sort
882 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
883 -> Append
884 -> Nested Loop Left Join
885 -> Hash Right Join
886 Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
887 -> Seq Scan on prt1_p1 t1_1
888 -> Hash
889 -> Seq Scan on prt1_e_p1 t3_1
890 Filter: (c = 0)
891 -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
892 Index Cond: (b = t1_1.a)
893 -> Nested Loop Left Join
894 -> Hash Right Join
895 Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
896 -> Seq Scan on prt1_p2 t1_2
897 -> Hash
898 -> Seq Scan on prt1_e_p2 t3_2
899 Filter: (c = 0)
900 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
901 Index Cond: (b = t1_2.a)
902 -> Nested Loop Left Join
903 -> Hash Right Join
904 Hash Cond: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
905 -> Seq Scan on prt1_p3 t1_3
906 -> Hash
907 -> Seq Scan on prt1_e_p3 t3_3
908 Filter: (c = 0)
909 -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
910 Index Cond: (b = t1_3.a)
911 (30 rows)
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
928 (12 rows)
930 --
931 -- 3-way full join
932 --
933 EXPLAIN (COSTS OFF)
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;
936 QUERY PLAN
937 -----------------------------------------------------------------------------------------------------------------------------------------
938 Aggregate
939 -> Append
940 -> Hash Full Join
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))
943 -> Hash Full Join
944 Hash Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
945 -> Seq Scan on prt1_p1 prt1_1
946 -> Hash
947 -> Seq Scan on prt2_p1 p2_1
948 -> Hash
949 -> Seq Scan on prt2_p1 p3_1
950 -> Hash Full Join
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))
953 -> Hash Full Join
954 Hash Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
955 -> Seq Scan on prt1_p2 prt1_2
956 -> Hash
957 -> Seq Scan on prt2_p2 p2_2
958 -> Hash
959 -> Seq Scan on prt2_p2 p3_2
960 -> Hash Full Join
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))
963 -> Hash Full Join
964 Hash Cond: ((prt1_3.a = p2_3.a) AND (prt1_3.b = p2_3.b))
965 -> Seq Scan on prt1_p3 prt1_3
966 -> Hash
967 -> Seq Scan on prt2_p3 p2_3
968 -> Hash
969 -> Seq Scan on prt2_p3 p3_3
970 (32 rows)
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;
974 count
975 -------
976 14
977 (1 row)
979 --
980 -- 4-way full join
981 --
982 EXPLAIN (COSTS OFF)
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;
985 QUERY PLAN
986 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
987 Aggregate
988 -> Append
989 -> Hash Full Join
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))
992 -> Hash Full Join
993 Hash Cond: ((COALESCE(prt1_1.a, p2_1.a) = p3_1.a) AND (COALESCE(prt1_1.b, p2_1.b) = p3_1.b))
994 -> Hash Full Join
995 Hash Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
996 -> Seq Scan on prt1_p1 prt1_1
997 -> Hash
998 -> Seq Scan on prt2_p1 p2_1
999 -> Hash
1000 -> Seq Scan on prt2_p1 p3_1
1001 -> Hash
1002 -> Seq Scan on prt1_p1 p4_1
1003 -> Hash Full Join
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))
1006 -> Hash Full Join
1007 Hash Cond: ((COALESCE(prt1_2.a, p2_2.a) = p3_2.a) AND (COALESCE(prt1_2.b, p2_2.b) = p3_2.b))
1008 -> Hash Full Join
1009 Hash Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
1010 -> Seq Scan on prt1_p2 prt1_2
1011 -> Hash
1012 -> Seq Scan on prt2_p2 p2_2
1013 -> Hash
1014 -> Seq Scan on prt2_p2 p3_2
1015 -> Hash
1016 -> Seq Scan on prt1_p2 p4_2
1017 -> Hash Full Join
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))
1020 -> Hash Full Join
1021 Hash Cond: ((COALESCE(prt1_3.a, p2_3.a) = p3_3.a) AND (COALESCE(prt1_3.b, p2_3.b) = p3_3.b))
1022 -> Hash Full Join
1023 Hash Cond: ((prt1_3.a = p2_3.a) AND (prt1_3.b = p2_3.b))
1024 -> Seq Scan on prt1_p3 prt1_3
1025 -> Hash
1026 -> Seq Scan on prt2_p3 p2_3
1027 -> Hash
1028 -> Seq Scan on prt2_p3 p3_3
1029 -> Hash
1030 -> Seq Scan on prt1_p3 p4_3
1031 (44 rows)
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;
1035 count
1036 -------
1037 14
1038 (1 row)
1040 -- Cases with non-nullable expressions in subquery results;
1041 -- make sure these go to null as expected
1042 EXPLAIN (COSTS OFF)
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;
1044 QUERY PLAN
1045 ------------------------------------------------------------------------------------------------------------
1046 Sort
1047 Sort Key: prt1.a, prt2.b, ((prt1_e.a + prt1_e.b))
1048 -> Append
1049 -> Hash Full Join
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)))
1052 -> Hash Full Join
1053 Hash Cond: (prt1_1.a = prt2_1.b)
1054 -> Seq Scan on prt1_p1 prt1_1
1055 Filter: (b = 0)
1056 -> Hash
1057 -> Seq Scan on prt2_p1 prt2_1
1058 Filter: (a = 0)
1059 -> Hash
1060 -> Seq Scan on prt1_e_p1 prt1_e_1
1061 Filter: (c = 0)
1062 -> Hash Full Join
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)))
1065 -> Hash Full Join
1066 Hash Cond: (prt1_2.a = prt2_2.b)
1067 -> Seq Scan on prt1_p2 prt1_2
1068 Filter: (b = 0)
1069 -> Hash
1070 -> Seq Scan on prt2_p2 prt2_2
1071 Filter: (a = 0)
1072 -> Hash
1073 -> Seq Scan on prt1_e_p2 prt1_e_2
1074 Filter: (c = 0)
1075 -> Hash Full Join
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)))
1078 -> Hash Full Join
1079 Hash Cond: (prt1_3.a = prt2_3.b)
1080 -> Seq Scan on prt1_p3 prt1_3
1081 Filter: (b = 0)
1082 -> Hash
1083 -> Seq Scan on prt2_p3 prt2_3
1084 Filter: (a = 0)
1085 -> Hash
1086 -> Seq Scan on prt1_e_p3 prt1_e_3
1087 Filter: (c = 0)
1088 (42 rows)
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
1094 | | 75 | 75 | |
1095 (2 rows)
1097 -- Semi-join
1098 EXPLAIN (COSTS OFF)
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;
1100 QUERY PLAN
1101 ---------------------------------------------------------------------------------
1102 Sort
1103 Sort Key: t1.a
1104 -> Append
1105 -> Nested Loop
1106 Join Filter: (t1_2.a = t1_5.b)
1107 -> HashAggregate
1108 Group Key: t1_5.b
1109 -> Hash Join
1110 Hash Cond: (((t2_1.a + t2_1.b) / 2) = t1_5.b)
1111 -> Seq Scan on prt1_e_p1 t2_1
1112 -> Hash
1113 -> Seq Scan on prt2_p1 t1_5
1114 Filter: (a = 0)
1115 -> Index Scan using iprt1_p1_a on prt1_p1 t1_2
1116 Index Cond: (a = ((t2_1.a + t2_1.b) / 2))
1117 Filter: (b = 0)
1118 -> Nested Loop
1119 Join Filter: (t1_3.a = t1_6.b)
1120 -> HashAggregate
1121 Group Key: t1_6.b
1122 -> Hash Join
1123 Hash Cond: (((t2_2.a + t2_2.b) / 2) = t1_6.b)
1124 -> Seq Scan on prt1_e_p2 t2_2
1125 -> Hash
1126 -> Seq Scan on prt2_p2 t1_6
1127 Filter: (a = 0)
1128 -> Index Scan using iprt1_p2_a on prt1_p2 t1_3
1129 Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
1130 Filter: (b = 0)
1131 -> Nested Loop
1132 Join Filter: (t1_4.a = t1_7.b)
1133 -> HashAggregate
1134 Group Key: t1_7.b
1135 -> Nested Loop
1136 -> Seq Scan on prt2_p3 t1_7
1137 Filter: (a = 0)
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))
1142 Filter: (b = 0)
1143 (41 rows)
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;
1146 a | b | c
1147 -----+---+------
1148 0 | 0 | 0000
1149 150 | 0 | 0150
1150 300 | 0 | 0300
1151 450 | 0 | 0450
1152 (4 rows)
1154 EXPLAIN (COSTS OFF)
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;
1156 QUERY PLAN
1157 ---------------------------------------------------------------------------
1158 Sort
1159 Sort Key: t1.a
1160 -> Append
1161 -> Nested Loop
1162 -> HashAggregate
1163 Group Key: t1_6.b
1164 -> Hash Semi Join
1165 Hash Cond: (t1_6.b = ((t1_9.a + t1_9.b) / 2))
1166 -> Seq Scan on prt2_p1 t1_6
1167 -> Hash
1168 -> Seq Scan on prt1_e_p1 t1_9
1169 Filter: (c = 0)
1170 -> Index Scan using iprt1_p1_a on prt1_p1 t1_3
1171 Index Cond: (a = t1_6.b)
1172 Filter: (b = 0)
1173 -> Nested Loop
1174 -> HashAggregate
1175 Group Key: t1_7.b
1176 -> Hash Semi Join
1177 Hash Cond: (t1_7.b = ((t1_10.a + t1_10.b) / 2))
1178 -> Seq Scan on prt2_p2 t1_7
1179 -> Hash
1180 -> Seq Scan on prt1_e_p2 t1_10
1181 Filter: (c = 0)
1182 -> Index Scan using iprt1_p2_a on prt1_p2 t1_4
1183 Index Cond: (a = t1_7.b)
1184 Filter: (b = 0)
1185 -> Nested Loop
1186 -> HashAggregate
1187 Group Key: t1_8.b
1188 -> Hash Semi Join
1189 Hash Cond: (t1_8.b = ((t1_11.a + t1_11.b) / 2))
1190 -> Seq Scan on prt2_p3 t1_8
1191 -> Hash
1192 -> Seq Scan on prt1_e_p3 t1_11
1193 Filter: (c = 0)
1194 -> Index Scan using iprt1_p3_a on prt1_p3 t1_5
1195 Index Cond: (a = t1_8.b)
1196 Filter: (b = 0)
1197 (39 rows)
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;
1200 a | b | c
1201 -----+---+------
1202 0 | 0 | 0000
1203 150 | 0 | 0150
1204 300 | 0 | 0300
1205 450 | 0 | 0450
1206 (4 rows)
1208 -- test merge joins
1209 SET enable_hashjoin TO off;
1210 SET enable_nestloop TO off;
1211 EXPLAIN (COSTS 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;
1213 QUERY PLAN
1214 ------------------------------------------------------------------
1215 Merge Append
1216 Sort Key: t1.a
1217 -> Merge Semi Join
1218 Merge Cond: (t1_3.a = t1_6.b)
1219 -> Sort
1220 Sort Key: t1_3.a
1221 -> Seq Scan on prt1_p1 t1_3
1222 Filter: (b = 0)
1223 -> Merge Semi Join
1224 Merge Cond: (t1_6.b = (((t1_9.a + t1_9.b) / 2)))
1225 -> Sort
1226 Sort Key: t1_6.b
1227 -> Seq Scan on prt2_p1 t1_6
1228 -> Sort
1229 Sort Key: (((t1_9.a + t1_9.b) / 2))
1230 -> Seq Scan on prt1_e_p1 t1_9
1231 Filter: (c = 0)
1232 -> Merge Semi Join
1233 Merge Cond: (t1_4.a = t1_7.b)
1234 -> Sort
1235 Sort Key: t1_4.a
1236 -> Seq Scan on prt1_p2 t1_4
1237 Filter: (b = 0)
1238 -> Merge Semi Join
1239 Merge Cond: (t1_7.b = (((t1_10.a + t1_10.b) / 2)))
1240 -> Sort
1241 Sort Key: t1_7.b
1242 -> Seq Scan on prt2_p2 t1_7
1243 -> Sort
1244 Sort Key: (((t1_10.a + t1_10.b) / 2))
1245 -> Seq Scan on prt1_e_p2 t1_10
1246 Filter: (c = 0)
1247 -> Merge Semi Join
1248 Merge Cond: (t1_5.a = t1_8.b)
1249 -> Sort
1250 Sort Key: t1_5.a
1251 -> Seq Scan on prt1_p3 t1_5
1252 Filter: (b = 0)
1253 -> Merge Semi Join
1254 Merge Cond: (t1_8.b = (((t1_11.a + t1_11.b) / 2)))
1255 -> Sort
1256 Sort Key: t1_8.b
1257 -> Seq Scan on prt2_p3 t1_8
1258 -> Sort
1259 Sort Key: (((t1_11.a + t1_11.b) / 2))
1260 -> Seq Scan on prt1_e_p3 t1_11
1261 Filter: (c = 0)
1262 (47 rows)
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;
1265 a | b | c
1266 -----+---+------
1267 0 | 0 | 0000
1268 150 | 0 | 0150
1269 300 | 0 | 0300
1270 450 | 0 | 0450
1271 (4 rows)
1273 EXPLAIN (COSTS OFF)
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;
1275 QUERY PLAN
1276 ----------------------------------------------------------------------------
1277 Sort
1278 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
1279 -> Append
1280 -> Merge Left Join
1281 Merge Cond: (t1_1.a = t2_1.b)
1282 -> Sort
1283 Sort Key: t1_1.a
1284 -> Merge Left Join
1285 Merge Cond: ((((t3_1.a + t3_1.b) / 2)) = t1_1.a)
1286 -> Sort
1287 Sort Key: (((t3_1.a + t3_1.b) / 2))
1288 -> Seq Scan on prt1_e_p1 t3_1
1289 Filter: (c = 0)
1290 -> Sort
1291 Sort Key: t1_1.a
1292 -> Seq Scan on prt1_p1 t1_1
1293 -> Sort
1294 Sort Key: t2_1.b
1295 -> Seq Scan on prt2_p1 t2_1
1296 -> Merge Left Join
1297 Merge Cond: (t1_2.a = t2_2.b)
1298 -> Sort
1299 Sort Key: t1_2.a
1300 -> Merge Left Join
1301 Merge Cond: ((((t3_2.a + t3_2.b) / 2)) = t1_2.a)
1302 -> Sort
1303 Sort Key: (((t3_2.a + t3_2.b) / 2))
1304 -> Seq Scan on prt1_e_p2 t3_2
1305 Filter: (c = 0)
1306 -> Sort
1307 Sort Key: t1_2.a
1308 -> Seq Scan on prt1_p2 t1_2
1309 -> Sort
1310 Sort Key: t2_2.b
1311 -> Seq Scan on prt2_p2 t2_2
1312 -> Merge Left Join
1313 Merge Cond: (t1_3.a = t2_3.b)
1314 -> Sort
1315 Sort Key: t1_3.a
1316 -> Merge Left Join
1317 Merge Cond: ((((t3_3.a + t3_3.b) / 2)) = t1_3.a)
1318 -> Sort
1319 Sort Key: (((t3_3.a + t3_3.b) / 2))
1320 -> Seq Scan on prt1_e_p3 t3_3
1321 Filter: (c = 0)
1322 -> Sort
1323 Sort Key: t1_3.a
1324 -> Seq Scan on prt1_p3 t1_3
1325 -> Sort
1326 Sort Key: t2_3.b
1327 -> Seq Scan on prt2_p3 t2_3
1328 (51 rows)
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
1345 (12 rows)
1347 -- MergeAppend on nullable column
1348 -- This should generate a partitionwise join, but currently fails to
1349 EXPLAIN (COSTS OFF)
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;
1351 QUERY PLAN
1352 -----------------------------------------------------------
1353 Incremental Sort
1354 Sort Key: prt1.a, prt2.b
1355 Presorted Key: prt1.a
1356 -> Merge Left Join
1357 Merge Cond: (prt1.a = prt2.b)
1358 -> Sort
1359 Sort Key: prt1.a
1360 -> Append
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))
1365 -> Sort
1366 Sort Key: prt2.b
1367 -> Append
1368 -> Seq Scan on prt2_p2 prt2_1
1369 Filter: (b > 250)
1370 -> Seq Scan on prt2_p3 prt2_2
1371 Filter: (b > 250)
1372 (19 rows)
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;
1375 a | b
1376 -----+-----
1377 0 |
1378 50 |
1379 100 |
1380 150 |
1381 200 |
1382 250 |
1383 300 | 300
1384 350 |
1385 400 |
1386 (9 rows)
1388 -- merge join when expression with whole-row reference needs to be sorted;
1389 -- partitionwise join does not apply
1390 EXPLAIN (COSTS OFF)
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;
1392 QUERY PLAN
1393 -----------------------------------------------------------------------------------------
1394 Merge Join
1395 Merge Cond: ((t1.a = t2.b) AND (((((t1.*)::prt1))::text) = ((((t2.*)::prt2))::text)))
1396 -> Sort
1397 Sort Key: t1.a, ((((t1.*)::prt1))::text)
1398 -> Result
1399 -> Append
1400 -> Seq Scan on prt1_p1 t1_1
1401 -> Seq Scan on prt1_p2 t1_2
1402 -> Seq Scan on prt1_p3 t1_3
1403 -> Sort
1404 Sort Key: t2.b, ((((t2.*)::prt2))::text)
1405 -> Result
1406 -> Append
1407 -> Seq Scan on prt2_p1 t2_1
1408 -> Seq Scan on prt2_p2 t2_2
1409 -> Seq Scan on prt2_p3 t2_3
1410 (16 rows)
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;
1413 a | b
1414 ----+----
1415 0 | 0
1416 6 | 6
1417 12 | 12
1418 18 | 18
1419 24 | 24
1420 (5 rows)
1422 RESET enable_hashjoin;
1423 RESET enable_nestloop;
1424 --
1425 -- partitioned by multiple columns
1426 --
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;
1432 ANALYZE prt1_m;
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;
1438 ANALYZE prt2_m;
1439 EXPLAIN (COSTS OFF)
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;
1441 QUERY PLAN
1442 ------------------------------------------------------------------------------------------------------------------------------
1443 Sort
1444 Sort Key: prt1_m.a, prt2_m.b
1445 -> Append
1446 -> Hash Full Join
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
1449 Filter: (c = 0)
1450 -> Hash
1451 -> Seq Scan on prt2_m_p1 prt2_m_1
1452 Filter: (c = 0)
1453 -> Hash Full Join
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
1456 Filter: (c = 0)
1457 -> Hash
1458 -> Seq Scan on prt2_m_p2 prt2_m_2
1459 Filter: (c = 0)
1460 -> Hash Full Join
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
1463 Filter: (c = 0)
1464 -> Hash
1465 -> Seq Scan on prt2_m_p3 prt2_m_3
1466 Filter: (c = 0)
1467 (24 rows)
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;
1470 a | c | b | c
1471 -----+---+-----+---
1472 0 | 0 | 0 | 0
1473 50 | 0 | |
1474 100 | 0 | |
1475 150 | 0 | 150 | 0
1476 200 | 0 | |
1477 250 | 0 | |
1478 300 | 0 | 300 | 0
1479 350 | 0 | |
1480 400 | 0 | |
1481 450 | 0 | 450 | 0
1482 500 | 0 | |
1483 550 | 0 | |
1484 | | 75 | 0
1485 | | 225 | 0
1486 | | 375 | 0
1487 | | 525 | 0
1488 (16 rows)
1490 --
1491 -- tests for list partitioned tables.
1492 --
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;
1498 ANALYZE plt1;
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;
1504 ANALYZE plt2;
1505 --
1506 -- list partitioned by expression
1507 --
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;
1513 ANALYZE plt1_e;
1514 -- test partition matching with N-way join
1515 EXPLAIN (COSTS OFF)
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;
1517 QUERY PLAN
1518 --------------------------------------------------------------------------------
1519 GroupAggregate
1520 Group Key: t1.c, t3.c
1521 -> Sort
1522 Sort Key: t1.c, t3.c
1523 -> Append
1524 -> Hash Join
1525 Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
1526 -> Hash Join
1527 Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1528 -> Seq Scan on plt1_p1 t1_1
1529 -> Hash
1530 -> Seq Scan on plt2_p1 t2_1
1531 -> Hash
1532 -> Seq Scan on plt1_e_p1 t3_1
1533 -> Hash Join
1534 Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
1535 -> Hash Join
1536 Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1537 -> Seq Scan on plt1_p2 t1_2
1538 -> Hash
1539 -> Seq Scan on plt2_p2 t2_2
1540 -> Hash
1541 -> Seq Scan on plt1_e_p2 t3_2
1542 -> Hash Join
1543 Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
1544 -> Hash Join
1545 Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
1546 -> Seq Scan on plt1_p3 t1_3
1547 -> Hash
1548 -> Seq Scan on plt2_p3 t2_3
1549 -> Hash
1550 -> Seq Scan on plt1_e_p3 t3_3
1551 (32 rows)
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
1568 (12 rows)
1570 -- joins where one of the relations is proven empty
1571 EXPLAIN (COSTS OFF)
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;
1573 QUERY PLAN
1574 --------------------------
1575 Result
1576 One-Time Filter: false
1577 (2 rows)
1579 EXPLAIN (COSTS OFF)
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;
1581 QUERY PLAN
1582 --------------------------
1583 Result
1584 One-Time Filter: false
1585 (2 rows)
1587 EXPLAIN (COSTS OFF)
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;
1589 QUERY PLAN
1590 --------------------------------------------------
1591 Hash Left Join
1592 Hash Cond: (t2.b = a)
1593 -> Append
1594 -> Hash Join
1595 Hash Cond: (t3_1.a = t2_1.b)
1596 -> Seq Scan on prt1_p1 t3_1
1597 -> Hash
1598 -> Seq Scan on prt2_p1 t2_1
1599 -> Hash Join
1600 Hash Cond: (t3_2.a = t2_2.b)
1601 -> Seq Scan on prt1_p2 t3_2
1602 -> Hash
1603 -> Seq Scan on prt2_p2 t2_2
1604 -> Hash Join
1605 Hash Cond: (t3_3.a = t2_3.b)
1606 -> Seq Scan on prt1_p3 t3_3
1607 -> Hash
1608 -> Seq Scan on prt2_p3 t2_3
1609 -> Hash
1610 -> Result
1611 One-Time Filter: false
1612 (21 rows)
1614 EXPLAIN (COSTS OFF)
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;
1616 QUERY PLAN
1617 --------------------------------------------
1618 Sort
1619 Sort Key: a, t2.b
1620 -> Hash Left Join
1621 Hash Cond: (t2.b = a)
1622 -> Append
1623 -> Seq Scan on prt2_p1 t2_1
1624 Filter: (a = 0)
1625 -> Seq Scan on prt2_p2 t2_2
1626 Filter: (a = 0)
1627 -> Seq Scan on prt2_p3 t2_3
1628 Filter: (a = 0)
1629 -> Hash
1630 -> Result
1631 One-Time Filter: false
1632 (14 rows)
1634 --
1635 -- tests for hash partitioned tables.
1636 --
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;
1642 ANALYZE pht1;
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;
1648 ANALYZE pht2;
1649 --
1650 -- hash partitioned by expression
1651 --
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;
1657 ANALYZE pht1_e;
1658 -- test partition matching with N-way join
1659 EXPLAIN (COSTS OFF)
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;
1661 QUERY PLAN
1662 --------------------------------------------------------------------------------
1663 GroupAggregate
1664 Group Key: t1.c, t3.c
1665 -> Sort
1666 Sort Key: t1.c, t3.c
1667 -> Append
1668 -> Hash Join
1669 Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
1670 -> Hash Join
1671 Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1672 -> Seq Scan on pht1_p1 t1_1
1673 -> Hash
1674 -> Seq Scan on pht2_p1 t2_1
1675 -> Hash
1676 -> Seq Scan on pht1_e_p1 t3_1
1677 -> Hash Join
1678 Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
1679 -> Hash Join
1680 Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1681 -> Seq Scan on pht1_p2 t1_2
1682 -> Hash
1683 -> Seq Scan on pht2_p2 t2_2
1684 -> Hash
1685 -> Seq Scan on pht1_e_p2 t3_2
1686 -> Hash Join
1687 Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
1688 -> Hash Join
1689 Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
1690 -> Seq Scan on pht1_p3 t1_3
1691 -> Hash
1692 -> Seq Scan on pht2_p3 t2_3
1693 -> Hash
1694 -> Seq Scan on pht1_e_p3 t3_3
1695 (32 rows)
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
1706 (6 rows)
1708 -- test default partition behavior for range
1709 ALTER TABLE prt1 DETACH PARTITION prt1_p3;
1710 ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
1711 ANALYZE prt1;
1712 ALTER TABLE prt2 DETACH PARTITION prt2_p3;
1713 ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
1714 ANALYZE prt2;
1715 EXPLAIN (COSTS OFF)
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;
1717 QUERY PLAN
1718 --------------------------------------------------
1719 Sort
1720 Sort Key: t1.a
1721 -> Append
1722 -> Hash Join
1723 Hash Cond: (t2_1.b = t1_1.a)
1724 -> Seq Scan on prt2_p1 t2_1
1725 -> Hash
1726 -> Seq Scan on prt1_p1 t1_1
1727 Filter: (b = 0)
1728 -> Hash Join
1729 Hash Cond: (t2_2.b = t1_2.a)
1730 -> Seq Scan on prt2_p2 t2_2
1731 -> Hash
1732 -> Seq Scan on prt1_p2 t1_2
1733 Filter: (b = 0)
1734 -> Hash Join
1735 Hash Cond: (t2_3.b = t1_3.a)
1736 -> Seq Scan on prt2_p3 t2_3
1737 -> Hash
1738 -> Seq Scan on prt1_p3 t1_3
1739 Filter: (b = 0)
1740 (21 rows)
1742 -- test default partition behavior for list
1743 ALTER TABLE plt1 DETACH PARTITION plt1_p3;
1744 ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
1745 ANALYZE plt1;
1746 ALTER TABLE plt2 DETACH PARTITION plt2_p3;
1747 ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
1748 ANALYZE plt2;
1749 EXPLAIN (COSTS OFF)
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;
1751 QUERY PLAN
1752 --------------------------------------------------------
1753 Sort
1754 Sort Key: t1.c
1755 -> HashAggregate
1756 Group Key: t1.c
1757 -> Append
1758 -> Hash Join
1759 Hash Cond: (t2_1.c = t1_1.c)
1760 -> Seq Scan on plt2_p1 t2_1
1761 -> Hash
1762 -> Seq Scan on plt1_p1 t1_1
1763 Filter: ((a % 25) = 0)
1764 -> Hash Join
1765 Hash Cond: (t2_2.c = t1_2.c)
1766 -> Seq Scan on plt2_p2 t2_2
1767 -> Hash
1768 -> Seq Scan on plt1_p2 t1_2
1769 Filter: ((a % 25) = 0)
1770 -> Hash Join
1771 Hash Cond: (t2_3.c = t1_3.c)
1772 -> Seq Scan on plt2_p3 t2_3
1773 -> Hash
1774 -> Seq Scan on plt1_p3 t1_3
1775 Filter: ((a % 25) = 0)
1776 (23 rows)
1778 --
1779 -- multiple levels of partitioning
1780 --
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;
1790 ANALYZE prt1_l;
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;
1800 ANALYZE prt2_l;
1801 -- inner join, qual covering only top-level partitions
1802 EXPLAIN (COSTS OFF)
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;
1804 QUERY PLAN
1805 -------------------------------------------------------------
1806 Sort
1807 Sort Key: t1.a
1808 -> Append
1809 -> Hash Join
1810 Hash Cond: (t2_1.b = t1_1.a)
1811 -> Seq Scan on prt2_l_p1 t2_1
1812 -> Hash
1813 -> Seq Scan on prt1_l_p1 t1_1
1814 Filter: (b = 0)
1815 -> Hash Join
1816 Hash Cond: (t2_3.b = t1_3.a)
1817 -> Append
1818 -> Seq Scan on prt2_l_p2_p1 t2_3
1819 -> Seq Scan on prt2_l_p2_p2 t2_4
1820 -> Hash
1821 -> Append
1822 -> Seq Scan on prt1_l_p2_p1 t1_3
1823 Filter: (b = 0)
1824 -> Seq Scan on prt1_l_p2_p2 t1_4
1825 Filter: (b = 0)
1826 -> Hash Join
1827 Hash Cond: (t2_6.b = t1_5.a)
1828 -> Append
1829 -> Seq Scan on prt2_l_p3_p1 t2_6
1830 -> Seq Scan on prt2_l_p3_p2 t2_7
1831 -> Hash
1832 -> Seq Scan on prt1_l_p3_p1 t1_5
1833 Filter: (b = 0)
1834 (28 rows)
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;
1837 a | c | b | c
1838 -----+------+-----+------
1839 0 | 0000 | 0 | 0000
1840 150 | 0002 | 150 | 0002
1841 300 | 0000 | 300 | 0000
1842 450 | 0002 | 450 | 0002
1843 (4 rows)
1845 -- inner join with partially-redundant join clauses
1846 EXPLAIN (COSTS OFF)
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;
1848 QUERY PLAN
1849 ------------------------------------------------------------------------------------
1850 Sort
1851 Sort Key: t1.a
1852 -> Append
1853 -> Hash Join
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
1856 -> Hash
1857 -> Seq Scan on prt2_l_p1 t2_1
1858 Filter: (a = b)
1859 -> Hash Join
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
1862 -> Hash
1863 -> Seq Scan on prt2_l_p2_p1 t2_2
1864 Filter: (a = b)
1865 -> Hash Join
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
1868 -> Hash
1869 -> Seq Scan on prt2_l_p2_p2 t2_3
1870 Filter: (a = b)
1871 -> Hash Join
1872 Hash Cond: ((t1_5.a = t2_5.a) AND ((t1_5.c)::text = (t2_5.c)::text))
1873 -> Append
1874 -> Seq Scan on prt1_l_p3_p1 t1_5
1875 -> Seq Scan on prt1_l_p3_p2 t1_6
1876 -> Hash
1877 -> Append
1878 -> Seq Scan on prt2_l_p3_p1 t2_5
1879 Filter: (a = b)
1880 -> Seq Scan on prt2_l_p3_p2 t2_6
1881 Filter: (a = b)
1882 (32 rows)
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;
1885 a | c | b | c
1886 ----+------+----+------
1887 0 | 0000 | 0 | 0000
1888 6 | 0002 | 6 | 0002
1889 12 | 0000 | 12 | 0000
1890 18 | 0002 | 18 | 0002
1891 24 | 0000 | 24 | 0000
1892 (5 rows)
1894 -- left join
1895 EXPLAIN (COSTS OFF)
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;
1897 QUERY PLAN
1898 ------------------------------------------------------------------------------------
1899 Sort
1900 Sort Key: t1.a, t2.b
1901 -> Append
1902 -> Hash Right Join
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
1905 -> Hash
1906 -> Seq Scan on prt1_l_p1 t1_1
1907 Filter: (b = 0)
1908 -> Hash Right Join
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
1911 -> Hash
1912 -> Seq Scan on prt1_l_p2_p1 t1_2
1913 Filter: (b = 0)
1914 -> Hash Right Join
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
1917 -> Hash
1918 -> Seq Scan on prt1_l_p2_p2 t1_3
1919 Filter: (b = 0)
1920 -> Hash Right Join
1921 Hash Cond: ((t2_5.b = t1_4.a) AND ((t2_5.c)::text = (t1_4.c)::text))
1922 -> Append
1923 -> Seq Scan on prt2_l_p3_p1 t2_5
1924 -> Seq Scan on prt2_l_p3_p2 t2_6
1925 -> Hash
1926 -> Seq Scan on prt1_l_p3_p1 t1_4
1927 Filter: (b = 0)
1928 (29 rows)
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;
1931 a | c | b | c
1932 -----+------+-----+------
1933 0 | 0000 | 0 | 0000
1934 50 | 0002 | |
1935 100 | 0000 | |
1936 150 | 0002 | 150 | 0002
1937 200 | 0000 | |
1938 250 | 0002 | |
1939 300 | 0000 | 300 | 0000
1940 350 | 0002 | |
1941 400 | 0000 | |
1942 450 | 0002 | 450 | 0002
1943 500 | 0000 | |
1944 550 | 0002 | |
1945 (12 rows)
1947 -- right join
1948 EXPLAIN (COSTS OFF)
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;
1950 QUERY PLAN
1951 ------------------------------------------------------------------------------------
1952 Sort
1953 Sort Key: t1.a, t2.b
1954 -> Append
1955 -> Hash Right Join
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
1958 -> Hash
1959 -> Seq Scan on prt2_l_p1 t2_1
1960 Filter: (a = 0)
1961 -> Hash Right Join
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
1964 -> Hash
1965 -> Seq Scan on prt2_l_p2_p1 t2_2
1966 Filter: (a = 0)
1967 -> Hash Right Join
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
1970 -> Hash
1971 -> Seq Scan on prt2_l_p2_p2 t2_3
1972 Filter: (a = 0)
1973 -> Hash Right Join
1974 Hash Cond: ((t1_5.a = t2_4.b) AND ((t1_5.c)::text = (t2_4.c)::text))
1975 -> Append
1976 -> Seq Scan on prt1_l_p3_p1 t1_5
1977 -> Seq Scan on prt1_l_p3_p2 t1_6
1978 -> Hash
1979 -> Seq Scan on prt2_l_p3_p1 t2_4
1980 Filter: (a = 0)
1981 (29 rows)
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;
1984 a | c | b | c
1985 -----+------+-----+------
1986 0 | 0000 | 0 | 0000
1987 150 | 0002 | 150 | 0002
1988 300 | 0000 | 300 | 0000
1989 450 | 0002 | 450 | 0002
1990 | | 75 | 0003
1991 | | 225 | 0001
1992 | | 375 | 0003
1993 | | 525 | 0001
1994 (8 rows)
1996 -- full join
1997 EXPLAIN (COSTS OFF)
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;
1999 QUERY PLAN
2000 ----------------------------------------------------------------------------------------------------
2001 Sort
2002 Sort Key: prt1_l.a, prt2_l.b
2003 -> Append
2004 -> Hash Full Join
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
2007 Filter: (b = 0)
2008 -> Hash
2009 -> Seq Scan on prt2_l_p1 prt2_l_1
2010 Filter: (a = 0)
2011 -> Hash Full Join
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
2014 Filter: (b = 0)
2015 -> Hash
2016 -> Seq Scan on prt2_l_p2_p1 prt2_l_2
2017 Filter: (a = 0)
2018 -> Hash Full Join
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
2021 Filter: (b = 0)
2022 -> Hash
2023 -> Seq Scan on prt2_l_p2_p2 prt2_l_3
2024 Filter: (a = 0)
2025 -> Hash Full Join
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
2028 Filter: (b = 0)
2029 -> Hash
2030 -> Seq Scan on prt2_l_p3_p1 prt2_l_4
2031 Filter: (a = 0)
2032 (31 rows)
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;
2035 a | c | b | c
2036 -----+------+-----+------
2037 0 | 0000 | 0 | 0000
2038 50 | 0002 | |
2039 100 | 0000 | |
2040 150 | 0002 | 150 | 0002
2041 200 | 0000 | |
2042 250 | 0002 | |
2043 300 | 0000 | 300 | 0000
2044 350 | 0002 | |
2045 400 | 0000 | |
2046 450 | 0002 | 450 | 0002
2047 500 | 0000 | |
2048 550 | 0002 | |
2049 | | 75 | 0003
2050 | | 225 | 0001
2051 | | 375 | 0003
2052 | | 525 | 0001
2053 (16 rows)
2055 -- lateral partitionwise join
2056 EXPLAIN (COSTS OFF)
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;
2060 QUERY PLAN
2061 -----------------------------------------------------------------------------------------------
2062 Sort
2063 Sort Key: t1.a
2064 -> Append
2065 -> Nested Loop Left Join
2066 -> Seq Scan on prt1_l_p1 t1_1
2067 Filter: (b = 0)
2068 -> Hash Join
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
2071 -> Hash
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
2076 Filter: (b = 0)
2077 -> Hash Join
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
2080 -> Hash
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
2085 Filter: (b = 0)
2086 -> Hash Join
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
2089 -> Hash
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
2094 Filter: (b = 0)
2095 -> Hash Join
2096 Hash Cond: ((t3_5.b = t2_5.a) AND ((t3_5.c)::text = (t2_5.c)::text))
2097 -> Append
2098 -> Seq Scan on prt2_l_p3_p1 t3_5
2099 -> Seq Scan on prt2_l_p3_p2 t3_6
2100 -> Hash
2101 -> Append
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))
2106 (44 rows)
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 | | | | |
2125 (12 rows)
2127 -- partitionwise join with lateral reference in sample scan
2128 EXPLAIN (COSTS OFF)
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;
2132 QUERY PLAN
2133 ----------------------------------------------------------------------------------------
2134 Append
2135 -> Nested Loop
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))
2140 -> Nested Loop
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))
2145 -> Nested Loop
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))
2150 -> Nested Loop
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))
2155 -> Nested Loop
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))
2160 (26 rows)
2162 -- partitionwise join with lateral reference in scan's restriction clauses
2163 EXPLAIN (COSTS OFF)
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
2167 WHERE s.t1b = s.a;
2168 QUERY PLAN
2169 ---------------------------------------------------------------------------------------------------------------
2170 Aggregate
2171 -> Append
2172 -> Nested Loop
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))
2176 -> Nested Loop
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))
2180 -> Nested Loop
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))
2184 -> Nested Loop
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))
2188 -> Nested Loop
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))
2192 (22 rows)
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
2197 WHERE s.t1b = s.a;
2198 count
2199 -------
2200 100
2201 (1 row)
2203 -- join with one side empty
2204 EXPLAIN (COSTS OFF)
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;
2206 QUERY PLAN
2207 -------------------------------------------------------------------------
2208 Hash Left Join
2209 Hash Cond: ((t2.b = a) AND (t2.a = b) AND ((t2.c)::text = (c)::text))
2210 -> Append
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
2216 -> Hash
2217 -> Result
2218 One-Time Filter: false
2219 (11 rows)
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.
2225 EXPLAIN (COSTS OFF)
2226 DELETE FROM prt1_l
2227 WHERE EXISTS (
2228 SELECT 1
2229 FROM int4_tbl,
2230 LATERAL (SELECT int4_tbl.f1 FROM int8_tbl LIMIT 2) ss
2231 WHERE prt1_l.c IS NULL);
2232 QUERY PLAN
2233 ----------------------------------------------------------
2234 Delete on prt1_l
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
2239 -> Append
2240 -> Seq Scan on prt1_l_p1 prt1_l_1
2241 Filter: (c IS NULL)
2242 -> Seq Scan on prt1_l_p3_p1 prt1_l_2
2243 Filter: (c IS NULL)
2244 -> Seq Scan on prt1_l_p3_p2 prt1_l_3
2245 Filter: (c IS NULL)
2246 -> Materialize
2247 -> Nested Loop
2248 -> Seq Scan on int4_tbl
2249 -> Subquery Scan on ss
2250 -> Limit
2251 -> Seq Scan on int8_tbl
2252 (18 rows)
2254 --
2255 -- negative testcases
2256 --
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;
2261 ANALYZE prt1_n;
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;
2266 ANALYZE prt2_n;
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;
2272 ANALYZE prt3_n;
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;
2278 ANALYZE prt4_n;
2279 -- partitionwise join can not be applied if the partition ranges differ
2280 EXPLAIN (COSTS OFF)
2281 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
2282 QUERY PLAN
2283 ----------------------------------------------
2284 Hash Join
2285 Hash Cond: (t1.a = t2.a)
2286 -> Append
2287 -> Seq Scan on prt1_p1 t1_1
2288 -> Seq Scan on prt1_p2 t1_2
2289 -> Seq Scan on prt1_p3 t1_3
2290 -> Hash
2291 -> Append
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
2295 (11 rows)
2297 EXPLAIN (COSTS OFF)
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;
2299 QUERY PLAN
2300 --------------------------------------------------------
2301 Hash Join
2302 Hash Cond: (t2.a = t1.a)
2303 -> Append
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
2307 -> Hash
2308 -> Append
2309 -> Hash Join
2310 Hash Cond: (t1_1.a = t3_1.b)
2311 -> Seq Scan on prt1_p1 t1_1
2312 -> Hash
2313 -> Seq Scan on prt2_p1 t3_1
2314 -> Hash Join
2315 Hash Cond: (t1_2.a = t3_2.b)
2316 -> Seq Scan on prt1_p2 t1_2
2317 -> Hash
2318 -> Seq Scan on prt2_p2 t3_2
2319 -> Hash Join
2320 Hash Cond: (t1_3.a = t3_3.b)
2321 -> Seq Scan on prt1_p3 t1_3
2322 -> Hash
2323 -> Seq Scan on prt2_p3 t3_3
2324 (23 rows)
2326 -- partitionwise join can not be applied if there are no equi-join conditions
2327 -- between partition keys
2328 EXPLAIN (COSTS OFF)
2329 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON (t1.a < t2.b);
2330 QUERY PLAN
2331 ---------------------------------------------------------
2332 Nested Loop Left Join
2333 -> Append
2334 -> Seq Scan on prt1_p1 t1_1
2335 -> Seq Scan on prt1_p2 t1_2
2336 -> Seq Scan on prt1_p3 t1_3
2337 -> Append
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)
2344 (12 rows)
2346 -- equi-join with join condition on partial keys does not qualify for
2347 -- partitionwise join
2348 EXPLAIN (COSTS OFF)
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;
2350 QUERY PLAN
2351 ----------------------------------------------
2352 Hash Join
2353 Hash Cond: (((t2.b + t2.a) / 2) = t1.a)
2354 -> Append
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
2358 -> Hash
2359 -> Append
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
2363 (11 rows)
2365 -- equi-join between out-of-order partition key columns does not qualify for
2366 -- partitionwise join
2367 EXPLAIN (COSTS OFF)
2368 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.a = t2.b;
2369 QUERY PLAN
2370 ----------------------------------------------
2371 Hash Left Join
2372 Hash Cond: (t1.a = t2.b)
2373 -> Append
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
2377 -> Hash
2378 -> Append
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
2382 (11 rows)
2384 -- equi-join between non-key columns does not qualify for partitionwise join
2385 EXPLAIN (COSTS OFF)
2386 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.c = t2.c;
2387 QUERY PLAN
2388 ----------------------------------------------
2389 Hash Left Join
2390 Hash Cond: (t1.c = t2.c)
2391 -> Append
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
2395 -> Hash
2396 -> Append
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
2400 (11 rows)
2402 -- partitionwise join can not be applied for a join between list and range
2403 -- partitioned tables
2404 EXPLAIN (COSTS OFF)
2405 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c);
2406 QUERY PLAN
2407 ----------------------------------------------
2408 Hash Right Join
2409 Hash Cond: (t2.c = (t1.c)::text)
2410 -> Append
2411 -> Seq Scan on prt2_n_p1 t2_1
2412 -> Seq Scan on prt2_n_p2 t2_2
2413 -> Hash
2414 -> Append
2415 -> Seq Scan on prt1_n_p1 t1_1
2416 -> Seq Scan on prt1_n_p2 t1_2
2417 (9 rows)
2419 -- partitionwise join can not be applied between tables with different
2420 -- partition lists
2421 EXPLAIN (COSTS OFF)
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);
2423 QUERY PLAN
2424 ----------------------------------------------------------
2425 Hash Join
2426 Hash Cond: (t2.c = (t1.c)::text)
2427 -> Append
2428 -> Seq Scan on prt2_n_p1 t2_1
2429 -> Seq Scan on prt2_n_p2 t2_2
2430 -> Hash
2431 -> Hash Join
2432 Hash Cond: (t3.c = (t1.c)::text)
2433 -> Append
2434 -> Seq Scan on plt1_p1 t3_1
2435 -> Seq Scan on plt1_p2 t3_2
2436 -> Seq Scan on plt1_p3 t3_3
2437 -> Hash
2438 -> Append
2439 -> Seq Scan on prt1_n_p1 t1_1
2440 -> Seq Scan on prt1_n_p2 t1_2
2441 (16 rows)
2443 -- partitionwise join can not be applied for a join between key column and
2444 -- non-key column
2445 EXPLAIN (COSTS OFF)
2446 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
2447 QUERY PLAN
2448 ----------------------------------------------
2449 Hash Full Join
2450 Hash Cond: ((t2.c)::text = (t1.c)::text)
2451 -> Append
2452 -> Seq Scan on prt1_p1 t2_1
2453 -> Seq Scan on prt1_p2 t2_2
2454 -> Seq Scan on prt1_p3 t2_3
2455 -> Hash
2456 -> Append
2457 -> Seq Scan on prt1_n_p1 t1_1
2458 -> Seq Scan on prt1_n_p2 t1_2
2459 (10 rows)
2461 --
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)
2464 --
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);
2481 analyze prtx1;
2482 analyze prtx2;
2483 explain (costs off)
2484 select * from prtx1
2485 where not exists (select 1 from prtx2
2486 where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
2487 and a<20 and c=120;
2488 QUERY PLAN
2489 -------------------------------------------------------------
2490 Append
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)
2497 -> BitmapAnd
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)
2508 -> BitmapAnd
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)
2513 (23 rows)
2515 select * from prtx1
2516 where not exists (select 1 from prtx2
2517 where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
2518 and a<20 and c=120;
2519 a | b | c
2520 ---+-----+-----
2521 1 | 120 | 120
2522 (1 row)
2524 explain (costs off)
2525 select * from prtx1
2526 where not exists (select 1 from prtx2
2527 where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
2528 and a<20 and c=91;
2529 QUERY PLAN
2530 -----------------------------------------------------------------
2531 Append
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)
2538 -> BitmapOr
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)
2549 -> BitmapOr
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)
2554 (23 rows)
2556 select * from prtx1
2557 where not exists (select 1 from prtx2
2558 where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
2559 and a<20 and c=91;
2560 a | b | c
2561 ---+----+----
2562 2 | 91 | 91
2563 (1 row)
2565 --
2566 -- Test advanced partition-matching algorithm for partitioned join
2567 --
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;
2575 ANALYZE prt1_adv;
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;
2584 ANALYZE prt2_adv;
2585 -- inner join
2586 EXPLAIN (COSTS OFF)
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;
2588 QUERY PLAN
2589 ------------------------------------------------------
2590 Sort
2591 Sort Key: t1.a
2592 -> Append
2593 -> Hash Join
2594 Hash Cond: (t2_1.b = t1_1.a)
2595 -> Seq Scan on prt2_adv_p1 t2_1
2596 -> Hash
2597 -> Seq Scan on prt1_adv_p1 t1_1
2598 Filter: (b = 0)
2599 -> Hash Join
2600 Hash Cond: (t2_2.b = t1_2.a)
2601 -> Seq Scan on prt2_adv_p2 t2_2
2602 -> Hash
2603 -> Seq Scan on prt1_adv_p2 t1_2
2604 Filter: (b = 0)
2605 -> Hash Join
2606 Hash Cond: (t2_3.b = t1_3.a)
2607 -> Seq Scan on prt2_adv_p3 t2_3
2608 -> Hash
2609 -> Seq Scan on prt1_adv_p3 t1_3
2610 Filter: (b = 0)
2611 (21 rows)
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;
2614 a | c | b | c
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
2624 (8 rows)
2626 -- semi join
2627 EXPLAIN (COSTS OFF)
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;
2629 QUERY PLAN
2630 ------------------------------------------------------
2631 Sort
2632 Sort Key: t1.a
2633 -> Append
2634 -> Hash Right Semi Join
2635 Hash Cond: (t2_1.b = t1_1.a)
2636 -> Seq Scan on prt2_adv_p1 t2_1
2637 -> Hash
2638 -> Seq Scan on prt1_adv_p1 t1_1
2639 Filter: (b = 0)
2640 -> Hash Right Semi Join
2641 Hash Cond: (t2_2.b = t1_2.a)
2642 -> Seq Scan on prt2_adv_p2 t2_2
2643 -> Hash
2644 -> Seq Scan on prt1_adv_p2 t1_2
2645 Filter: (b = 0)
2646 -> Hash Right Semi Join
2647 Hash Cond: (t2_3.b = t1_3.a)
2648 -> Seq Scan on prt2_adv_p3 t2_3
2649 -> Hash
2650 -> Seq Scan on prt1_adv_p3 t1_3
2651 Filter: (b = 0)
2652 (21 rows)
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;
2655 a | b | c
2656 -----+---+------
2657 100 | 0 | 0100
2658 125 | 0 | 0125
2659 200 | 0 | 0200
2660 225 | 0 | 0225
2661 250 | 0 | 0250
2662 275 | 0 | 0275
2663 350 | 0 | 0350
2664 375 | 0 | 0375
2665 (8 rows)
2667 -- left join
2668 EXPLAIN (COSTS OFF)
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;
2670 QUERY PLAN
2671 ------------------------------------------------------
2672 Sort
2673 Sort Key: t1.a, t2.b
2674 -> Append
2675 -> Hash Right Join
2676 Hash Cond: (t2_1.b = t1_1.a)
2677 -> Seq Scan on prt2_adv_p1 t2_1
2678 -> Hash
2679 -> Seq Scan on prt1_adv_p1 t1_1
2680 Filter: (b = 0)
2681 -> Hash Right Join
2682 Hash Cond: (t2_2.b = t1_2.a)
2683 -> Seq Scan on prt2_adv_p2 t2_2
2684 -> Hash
2685 -> Seq Scan on prt1_adv_p2 t1_2
2686 Filter: (b = 0)
2687 -> Hash Right Join
2688 Hash Cond: (t2_3.b = t1_3.a)
2689 -> Seq Scan on prt2_adv_p3 t2_3
2690 -> Hash
2691 -> Seq Scan on prt1_adv_p3 t1_3
2692 Filter: (b = 0)
2693 (21 rows)
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;
2696 a | c | b | c
2697 -----+------+-----+------
2698 100 | 0100 | 100 | 0100
2699 125 | 0125 | 125 | 0125
2700 150 | 0150 | |
2701 175 | 0175 | |
2702 200 | 0200 | 200 | 0200
2703 225 | 0225 | 225 | 0225
2704 250 | 0250 | 250 | 0250
2705 275 | 0275 | 275 | 0275
2706 300 | 0300 | |
2707 325 | 0325 | |
2708 350 | 0350 | 350 | 0350
2709 375 | 0375 | 375 | 0375
2710 (12 rows)
2712 -- anti join
2713 EXPLAIN (COSTS OFF)
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;
2715 QUERY PLAN
2716 ------------------------------------------------------
2717 Sort
2718 Sort Key: t1.a
2719 -> Append
2720 -> Hash Right Anti Join
2721 Hash Cond: (t2_1.b = t1_1.a)
2722 -> Seq Scan on prt2_adv_p1 t2_1
2723 -> Hash
2724 -> Seq Scan on prt1_adv_p1 t1_1
2725 Filter: (b = 0)
2726 -> Hash Right Anti Join
2727 Hash Cond: (t2_2.b = t1_2.a)
2728 -> Seq Scan on prt2_adv_p2 t2_2
2729 -> Hash
2730 -> Seq Scan on prt1_adv_p2 t1_2
2731 Filter: (b = 0)
2732 -> Hash Right Anti Join
2733 Hash Cond: (t2_3.b = t1_3.a)
2734 -> Seq Scan on prt2_adv_p3 t2_3
2735 -> Hash
2736 -> Seq Scan on prt1_adv_p3 t1_3
2737 Filter: (b = 0)
2738 (21 rows)
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;
2741 a | b | c
2742 -----+---+------
2743 150 | 0 | 0150
2744 175 | 0 | 0175
2745 300 | 0 | 0300
2746 325 | 0 | 0325
2747 (4 rows)
2749 -- full join
2750 EXPLAIN (COSTS OFF)
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;
2752 QUERY PLAN
2753 --------------------------------------------------------------------------
2754 Sort
2755 Sort Key: prt1_adv.a, prt2_adv.b
2756 -> Append
2757 -> Hash Full Join
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
2761 Filter: (b = 0)
2762 -> Hash
2763 -> Seq Scan on prt2_adv_p1 prt2_adv_1
2764 Filter: (a = 0)
2765 -> Hash Full Join
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
2769 Filter: (b = 0)
2770 -> Hash
2771 -> Seq Scan on prt2_adv_p2 prt2_adv_2
2772 Filter: (a = 0)
2773 -> Hash Full Join
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
2777 Filter: (a = 0)
2778 -> Hash
2779 -> Seq Scan on prt1_adv_p3 prt1_adv_3
2780 Filter: (b = 0)
2781 (27 rows)
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;
2784 a | c | b | c
2785 -----+------+-----+------
2786 175 | 0175 | |
2787 | | 425 | 0425
2788 (2 rows)
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;
2793 ANALYZE prt2_adv;
2794 -- inner join
2795 EXPLAIN (COSTS OFF)
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;
2797 QUERY PLAN
2798 ------------------------------------------------------
2799 Sort
2800 Sort Key: t1.a
2801 -> Append
2802 -> Hash Join
2803 Hash Cond: (t2_1.b = t1_1.a)
2804 -> Seq Scan on prt2_adv_p1 t2_1
2805 -> Hash
2806 -> Seq Scan on prt1_adv_p1 t1_1
2807 Filter: (b = 0)
2808 -> Hash Join
2809 Hash Cond: (t2_2.b = t1_2.a)
2810 -> Seq Scan on prt2_adv_p2 t2_2
2811 -> Hash
2812 -> Seq Scan on prt1_adv_p2 t1_2
2813 Filter: (b = 0)
2814 -> Hash Join
2815 Hash Cond: (t2_3.b = t1_3.a)
2816 -> Seq Scan on prt2_adv_p3 t2_3
2817 -> Hash
2818 -> Seq Scan on prt1_adv_p3 t1_3
2819 Filter: (b = 0)
2820 (21 rows)
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;
2823 a | c | b | c
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
2833 (8 rows)
2835 -- semi join
2836 EXPLAIN (COSTS OFF)
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;
2838 QUERY PLAN
2839 ------------------------------------------------------
2840 Sort
2841 Sort Key: t1.a
2842 -> Append
2843 -> Hash Right Semi Join
2844 Hash Cond: (t2_1.b = t1_1.a)
2845 -> Seq Scan on prt2_adv_p1 t2_1
2846 -> Hash
2847 -> Seq Scan on prt1_adv_p1 t1_1
2848 Filter: (b = 0)
2849 -> Hash Right Semi Join
2850 Hash Cond: (t2_2.b = t1_2.a)
2851 -> Seq Scan on prt2_adv_p2 t2_2
2852 -> Hash
2853 -> Seq Scan on prt1_adv_p2 t1_2
2854 Filter: (b = 0)
2855 -> Hash Right Semi Join
2856 Hash Cond: (t2_3.b = t1_3.a)
2857 -> Seq Scan on prt2_adv_p3 t2_3
2858 -> Hash
2859 -> Seq Scan on prt1_adv_p3 t1_3
2860 Filter: (b = 0)
2861 (21 rows)
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;
2864 a | b | c
2865 -----+---+------
2866 100 | 0 | 0100
2867 125 | 0 | 0125
2868 200 | 0 | 0200
2869 225 | 0 | 0225
2870 250 | 0 | 0250
2871 275 | 0 | 0275
2872 350 | 0 | 0350
2873 375 | 0 | 0375
2874 (8 rows)
2876 -- left join
2877 EXPLAIN (COSTS OFF)
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;
2879 QUERY PLAN
2880 ------------------------------------------------------
2881 Sort
2882 Sort Key: t1.a, t2.b
2883 -> Append
2884 -> Hash Right Join
2885 Hash Cond: (t2_1.b = t1_1.a)
2886 -> Seq Scan on prt2_adv_p1 t2_1
2887 -> Hash
2888 -> Seq Scan on prt1_adv_p1 t1_1
2889 Filter: (b = 0)
2890 -> Hash Right Join
2891 Hash Cond: (t2_2.b = t1_2.a)
2892 -> Seq Scan on prt2_adv_p2 t2_2
2893 -> Hash
2894 -> Seq Scan on prt1_adv_p2 t1_2
2895 Filter: (b = 0)
2896 -> Hash Right Join
2897 Hash Cond: (t2_3.b = t1_3.a)
2898 -> Seq Scan on prt2_adv_p3 t2_3
2899 -> Hash
2900 -> Seq Scan on prt1_adv_p3 t1_3
2901 Filter: (b = 0)
2902 (21 rows)
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;
2905 a | c | b | c
2906 -----+------+-----+------
2907 100 | 0100 | 100 | 0100
2908 125 | 0125 | 125 | 0125
2909 150 | 0150 | |
2910 175 | 0175 | |
2911 200 | 0200 | 200 | 0200
2912 225 | 0225 | 225 | 0225
2913 250 | 0250 | 250 | 0250
2914 275 | 0275 | 275 | 0275
2915 300 | 0300 | |
2916 325 | 0325 | |
2917 350 | 0350 | 350 | 0350
2918 375 | 0375 | 375 | 0375
2919 (12 rows)
2921 -- left join; currently we can't do partitioned join if there are no matched
2922 -- partitions on the nullable side
2923 EXPLAIN (COSTS OFF)
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;
2925 QUERY PLAN
2926 ---------------------------------------------------------
2927 Sort
2928 Sort Key: t1.b, t2.a
2929 -> Hash Right Join
2930 Hash Cond: (t2.a = t1.b)
2931 -> Append
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
2935 -> Hash
2936 -> Append
2937 -> Seq Scan on prt2_adv_p1 t1_1
2938 Filter: (a = 0)
2939 -> Seq Scan on prt2_adv_p2 t1_2
2940 Filter: (a = 0)
2941 -> Seq Scan on prt2_adv_p3 t1_3
2942 Filter: (a = 0)
2943 -> Seq Scan on prt2_adv_extra t1_4
2944 Filter: (a = 0)
2945 (18 rows)
2947 -- anti join
2948 EXPLAIN (COSTS OFF)
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;
2950 QUERY PLAN
2951 ------------------------------------------------------
2952 Sort
2953 Sort Key: t1.a
2954 -> Append
2955 -> Hash Right Anti Join
2956 Hash Cond: (t2_1.b = t1_1.a)
2957 -> Seq Scan on prt2_adv_p1 t2_1
2958 -> Hash
2959 -> Seq Scan on prt1_adv_p1 t1_1
2960 Filter: (b = 0)
2961 -> Hash Right Anti Join
2962 Hash Cond: (t2_2.b = t1_2.a)
2963 -> Seq Scan on prt2_adv_p2 t2_2
2964 -> Hash
2965 -> Seq Scan on prt1_adv_p2 t1_2
2966 Filter: (b = 0)
2967 -> Hash Right Anti Join
2968 Hash Cond: (t2_3.b = t1_3.a)
2969 -> Seq Scan on prt2_adv_p3 t2_3
2970 -> Hash
2971 -> Seq Scan on prt1_adv_p3 t1_3
2972 Filter: (b = 0)
2973 (21 rows)
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;
2976 a | b | c
2977 -----+---+------
2978 150 | 0 | 0150
2979 175 | 0 | 0175
2980 300 | 0 | 0300
2981 325 | 0 | 0325
2982 (4 rows)
2984 -- anti join; currently we can't do partitioned join if there are no matched
2985 -- partitions on the nullable side
2986 EXPLAIN (COSTS OFF)
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;
2988 QUERY PLAN
2989 ---------------------------------------------------------
2990 Sort
2991 Sort Key: t1.b
2992 -> Hash Right Anti Join
2993 Hash Cond: (t2.a = t1.b)
2994 -> Append
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
2998 -> Hash
2999 -> Append
3000 -> Seq Scan on prt2_adv_p1 t1_1
3001 Filter: (a = 0)
3002 -> Seq Scan on prt2_adv_p2 t1_2
3003 Filter: (a = 0)
3004 -> Seq Scan on prt2_adv_p3 t1_3
3005 Filter: (a = 0)
3006 -> Seq Scan on prt2_adv_extra t1_4
3007 Filter: (a = 0)
3008 (18 rows)
3010 -- full join; currently we can't do partitioned join if there are no matched
3011 -- partitions on the nullable side
3012 EXPLAIN (COSTS OFF)
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;
3014 QUERY PLAN
3015 ----------------------------------------------------------------
3016 Sort
3017 Sort Key: prt1_adv.a, prt2_adv.b
3018 -> Hash Full Join
3019 Hash Cond: (prt2_adv.b = prt1_adv.a)
3020 Filter: (((175) = prt1_adv.a) OR ((425) = prt2_adv.b))
3021 -> Append
3022 -> Seq Scan on prt2_adv_p1 prt2_adv_1
3023 Filter: (a = 0)
3024 -> Seq Scan on prt2_adv_p2 prt2_adv_2
3025 Filter: (a = 0)
3026 -> Seq Scan on prt2_adv_p3 prt2_adv_3
3027 Filter: (a = 0)
3028 -> Seq Scan on prt2_adv_extra prt2_adv_4
3029 Filter: (a = 0)
3030 -> Hash
3031 -> Append
3032 -> Seq Scan on prt1_adv_p1 prt1_adv_1
3033 Filter: (b = 0)
3034 -> Seq Scan on prt1_adv_p2 prt1_adv_2
3035 Filter: (b = 0)
3036 -> Seq Scan on prt1_adv_p3 prt1_adv_3
3037 Filter: (b = 0)
3038 (22 rows)
3040 -- 3-way join where not every pair of relations can do partitioned join
3041 EXPLAIN (COSTS OFF)
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;
3043 QUERY PLAN
3044 --------------------------------------------------------------------------------
3045 Sort
3046 Sort Key: t1.b, t2.a
3047 -> Append
3048 -> Nested Loop Left Join
3049 -> Nested Loop
3050 -> Seq Scan on prt2_adv_p1 t1_1
3051 Filter: (a = 0)
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)
3056 -> Hash Right Join
3057 Hash Cond: (t2_2.a = t1_2.b)
3058 -> Seq Scan on prt1_adv_p2 t2_2
3059 -> Hash
3060 -> Hash Join
3061 Hash Cond: (t3_2.a = t1_2.b)
3062 -> Seq Scan on prt1_adv_p2 t3_2
3063 -> Hash
3064 -> Seq Scan on prt2_adv_p2 t1_2
3065 Filter: (a = 0)
3066 -> Hash Right Join
3067 Hash Cond: (t2_3.a = t1_3.b)
3068 -> Seq Scan on prt1_adv_p3 t2_3
3069 -> Hash
3070 -> Hash Join
3071 Hash Cond: (t3_3.a = t1_3.b)
3072 -> Seq Scan on prt1_adv_p3 t3_3
3073 -> Hash
3074 -> Seq Scan on prt2_adv_p3 t1_3
3075 Filter: (a = 0)
3076 (31 rows)
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
3089 (8 rows)
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;
3099 ANALYZE prt2_adv;
3100 -- inner join
3101 EXPLAIN (COSTS OFF)
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;
3103 QUERY PLAN
3104 ------------------------------------------------------
3105 Sort
3106 Sort Key: t1.a
3107 -> Hash Join
3108 Hash Cond: (t2.b = t1.a)
3109 -> Append
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
3114 -> Hash
3115 -> Append
3116 -> Seq Scan on prt1_adv_p1 t1_1
3117 Filter: (b = 0)
3118 -> Seq Scan on prt1_adv_p2 t1_2
3119 Filter: (b = 0)
3120 -> Seq Scan on prt1_adv_p3 t1_3
3121 Filter: (b = 0)
3122 (17 rows)
3124 -- semi join
3125 EXPLAIN (COSTS OFF)
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;
3127 QUERY PLAN
3128 ------------------------------------------------------
3129 Sort
3130 Sort Key: t1.a
3131 -> Hash Right Semi Join
3132 Hash Cond: (t2.b = t1.a)
3133 -> Append
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
3138 -> Hash
3139 -> Append
3140 -> Seq Scan on prt1_adv_p1 t1_1
3141 Filter: (b = 0)
3142 -> Seq Scan on prt1_adv_p2 t1_2
3143 Filter: (b = 0)
3144 -> Seq Scan on prt1_adv_p3 t1_3
3145 Filter: (b = 0)
3146 (17 rows)
3148 -- left join
3149 EXPLAIN (COSTS OFF)
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;
3151 QUERY PLAN
3152 ------------------------------------------------------
3153 Sort
3154 Sort Key: t1.a, t2.b
3155 -> Hash Right Join
3156 Hash Cond: (t2.b = t1.a)
3157 -> Append
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
3162 -> Hash
3163 -> Append
3164 -> Seq Scan on prt1_adv_p1 t1_1
3165 Filter: (b = 0)
3166 -> Seq Scan on prt1_adv_p2 t1_2
3167 Filter: (b = 0)
3168 -> Seq Scan on prt1_adv_p3 t1_3
3169 Filter: (b = 0)
3170 (17 rows)
3172 -- anti join
3173 EXPLAIN (COSTS OFF)
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;
3175 QUERY PLAN
3176 ------------------------------------------------------
3177 Sort
3178 Sort Key: t1.a
3179 -> Hash Right Anti Join
3180 Hash Cond: (t2.b = t1.a)
3181 -> Append
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
3186 -> Hash
3187 -> Append
3188 -> Seq Scan on prt1_adv_p1 t1_1
3189 Filter: (b = 0)
3190 -> Seq Scan on prt1_adv_p2 t1_2
3191 Filter: (b = 0)
3192 -> Seq Scan on prt1_adv_p3 t1_3
3193 Filter: (b = 0)
3194 (17 rows)
3196 -- full join
3197 EXPLAIN (COSTS OFF)
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;
3199 QUERY PLAN
3200 ----------------------------------------------------------------
3201 Sort
3202 Sort Key: prt1_adv.a, prt2_adv.b
3203 -> Hash Full Join
3204 Hash Cond: (prt2_adv.b = prt1_adv.a)
3205 Filter: (((175) = prt1_adv.a) OR ((425) = prt2_adv.b))
3206 -> Append
3207 -> Seq Scan on prt2_adv_p1 prt2_adv_1
3208 Filter: (a = 0)
3209 -> Seq Scan on prt2_adv_p2 prt2_adv_2
3210 Filter: (a = 0)
3211 -> Seq Scan on prt2_adv_p3_1 prt2_adv_3
3212 Filter: (a = 0)
3213 -> Seq Scan on prt2_adv_p3_2 prt2_adv_4
3214 Filter: (a = 0)
3215 -> Hash
3216 -> Append
3217 -> Seq Scan on prt1_adv_p1 prt1_adv_1
3218 Filter: (b = 0)
3219 -> Seq Scan on prt1_adv_p2 prt1_adv_2
3220 Filter: (b = 0)
3221 -> Seq Scan on prt1_adv_p3 prt1_adv_3
3222 Filter: (b = 0)
3223 (22 rows)
3225 DROP TABLE prt2_adv_p3_1;
3226 DROP TABLE prt2_adv_p3_2;
3227 ANALYZE prt2_adv;
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;
3233 ANALYZE prt1_adv;
3234 -- We can do partitioned join even if only one of relations has the default
3235 -- partition
3236 EXPLAIN (COSTS OFF)
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;
3238 QUERY PLAN
3239 ------------------------------------------------------
3240 Sort
3241 Sort Key: t1.a
3242 -> Append
3243 -> Hash Join
3244 Hash Cond: (t2_1.b = t1_2.a)
3245 -> Seq Scan on prt2_adv_p1 t2_1
3246 -> Hash
3247 -> Seq Scan on prt1_adv_p1 t1_2
3248 Filter: (b = 0)
3249 -> Hash Join
3250 Hash Cond: (t2_2.b = t1_1.a)
3251 -> Seq Scan on prt2_adv_p2 t2_2
3252 -> Hash
3253 -> Seq Scan on prt1_adv_p2 t1_1
3254 Filter: (b = 0)
3255 (15 rows)
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;
3258 a | c | b | c
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
3266 (6 rows)
3268 -- Restore prt1_adv_p3
3269 ALTER TABLE prt1_adv ATTACH PARTITION prt1_adv_p3 FOR VALUES FROM (300) TO (400);
3270 ANALYZE prt1_adv;
3271 -- Restore prt2_adv_p3
3272 ALTER TABLE prt2_adv ATTACH PARTITION prt2_adv_p3 FOR VALUES FROM (350) TO (500);
3273 ANALYZE prt2_adv;
3274 -- Partitioned join can't be applied because the default partition of prt1_adv
3275 -- matches prt2_adv_p1 and prt2_adv_p3
3276 EXPLAIN (COSTS OFF)
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;
3278 QUERY PLAN
3279 ------------------------------------------------------
3280 Sort
3281 Sort Key: t1.a
3282 -> Hash Join
3283 Hash Cond: (t2.b = t1.a)
3284 -> Append
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
3288 -> Hash
3289 -> Append
3290 -> Seq Scan on prt1_adv_p2 t1_1
3291 Filter: (b = 0)
3292 -> Seq Scan on prt1_adv_p3 t1_2
3293 Filter: (b = 0)
3294 -> Seq Scan on prt1_adv_p1 t1_3
3295 Filter: (b = 0)
3296 (16 rows)
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;
3301 ANALYZE prt2_adv;
3302 -- Partitioned join can't be applied because the default partition of prt1_adv
3303 -- matches prt2_adv_p1 and prt2_adv_p3
3304 EXPLAIN (COSTS OFF)
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;
3306 QUERY PLAN
3307 ------------------------------------------------------
3308 Sort
3309 Sort Key: t1.a
3310 -> Hash Join
3311 Hash Cond: (t2.b = t1.a)
3312 -> Append
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
3316 -> Hash
3317 -> Append
3318 -> Seq Scan on prt1_adv_p2 t1_1
3319 Filter: (b = 0)
3320 -> Seq Scan on prt1_adv_p3 t1_2
3321 Filter: (b = 0)
3322 -> Seq Scan on prt1_adv_p1 t1_3
3323 Filter: (b = 0)
3324 (16 rows)
3326 DROP TABLE prt1_adv_p3;
3327 ANALYZE prt1_adv;
3328 DROP TABLE prt2_adv_p3;
3329 ANALYZE prt2_adv;
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;
3335 ANALYZE prt3_adv;
3336 -- 3-way join to test the default partition of a join relation
3337 EXPLAIN (COSTS OFF)
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;
3339 QUERY PLAN
3340 ------------------------------------------------------------------
3341 Sort
3342 Sort Key: t1.a, t2.b, t3.a
3343 -> Append
3344 -> Hash Right Join
3345 Hash Cond: (t3_1.a = t1_1.a)
3346 -> Seq Scan on prt3_adv_p1 t3_1
3347 -> Hash
3348 -> Hash Right Join
3349 Hash Cond: (t2_2.b = t1_1.a)
3350 -> Seq Scan on prt2_adv_p2 t2_2
3351 -> Hash
3352 -> Seq Scan on prt1_adv_p2 t1_1
3353 Filter: (b = 0)
3354 -> Hash Right Join
3355 Hash Cond: (t3_2.a = t1_2.a)
3356 -> Seq Scan on prt3_adv_p2 t3_2
3357 -> Hash
3358 -> Hash Right Join
3359 Hash Cond: (t2_1.b = t1_2.a)
3360 -> Seq Scan on prt2_adv_p1 t2_1
3361 -> Hash
3362 -> Seq Scan on prt1_adv_p1 t1_2
3363 Filter: (b = 0)
3364 (23 rows)
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 | |
3371 150 | 0150 | | | |
3372 175 | 0175 | | | |
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
3377 (8 rows)
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;
3389 ANALYZE prt1_adv;
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;
3395 ANALYZE prt2_adv;
3396 EXPLAIN (COSTS OFF)
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;
3398 QUERY PLAN
3399 -----------------------------------------------------------
3400 Sort
3401 Sort Key: t1.a
3402 -> Append
3403 -> Hash Join
3404 Hash Cond: (t2_1.b = t1_1.a)
3405 -> Seq Scan on prt2_adv_p1 t2_1
3406 -> Hash
3407 -> Seq Scan on prt1_adv_p1 t1_1
3408 Filter: ((a < 300) AND (b = 0))
3409 -> Hash Join
3410 Hash Cond: (t2_2.b = t1_2.a)
3411 -> Seq Scan on prt2_adv_p2 t2_2
3412 -> Hash
3413 -> Seq Scan on prt1_adv_p2 t1_2
3414 Filter: ((a < 300) AND (b = 0))
3415 (15 rows)
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;
3418 a | c | b | c
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
3428 (8 rows)
3430 DROP TABLE prt1_adv_p3;
3431 CREATE TABLE prt1_adv_default PARTITION OF prt1_adv DEFAULT;
3432 ANALYZE prt1_adv;
3433 CREATE TABLE prt2_adv_default PARTITION OF prt2_adv DEFAULT;
3434 ANALYZE prt2_adv;
3435 EXPLAIN (COSTS OFF)
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;
3437 QUERY PLAN
3438 --------------------------------------------------------------------------
3439 Sort
3440 Sort Key: t1.a
3441 -> Append
3442 -> Hash Join
3443 Hash Cond: (t2_1.b = t1_1.a)
3444 -> Seq Scan on prt2_adv_p1 t2_1
3445 -> Hash
3446 -> Seq Scan on prt1_adv_p1 t1_1
3447 Filter: ((a >= 100) AND (a < 300) AND (b = 0))
3448 -> Hash Join
3449 Hash Cond: (t2_2.b = t1_2.a)
3450 -> Seq Scan on prt2_adv_p2 t2_2
3451 -> Hash
3452 -> Seq Scan on prt1_adv_p2 t1_2
3453 Filter: ((a >= 100) AND (a < 300) AND (b = 0))
3454 (15 rows)
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;
3457 a | c | b | c
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
3467 (8 rows)
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);
3477 ANALYZE plt1_adv;
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);
3483 ANALYZE plt2_adv;
3484 -- inner join
3485 EXPLAIN (COSTS OFF)
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;
3487 QUERY PLAN
3488 --------------------------------------------------------------------
3489 Sort
3490 Sort Key: t1.a
3491 -> Append
3492 -> Hash Join
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
3495 -> Hash
3496 -> Seq Scan on plt1_adv_p1 t1_1
3497 Filter: (b < 10)
3498 -> Hash Join
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
3501 -> Hash
3502 -> Seq Scan on plt1_adv_p2 t1_2
3503 Filter: (b < 10)
3504 -> Hash Join
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
3507 -> Hash
3508 -> Seq Scan on plt1_adv_p3 t1_3
3509 Filter: (b < 10)
3510 (21 rows)
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;
3513 a | c | a | c
3514 ---+------+---+------
3515 3 | 0003 | 3 | 0003
3516 4 | 0004 | 4 | 0004
3517 6 | 0006 | 6 | 0006
3518 9 | 0009 | 9 | 0009
3519 (4 rows)
3521 -- semi join
3522 EXPLAIN (COSTS OFF)
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;
3524 QUERY PLAN
3525 --------------------------------------------------------------------
3526 Sort
3527 Sort Key: t1.a
3528 -> Append
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
3532 -> Hash
3533 -> Seq Scan on plt1_adv_p1 t1_1
3534 Filter: (b < 10)
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
3538 -> Hash
3539 -> Seq Scan on plt1_adv_p2 t1_2
3540 Filter: (b < 10)
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
3544 -> Hash
3545 -> Seq Scan on plt1_adv_p3 t1_3
3546 Filter: (b < 10)
3547 (21 rows)
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;
3550 a | b | c
3551 ---+---+------
3552 3 | 3 | 0003
3553 4 | 4 | 0004
3554 6 | 6 | 0006
3555 9 | 9 | 0009
3556 (4 rows)
3558 -- left join
3559 EXPLAIN (COSTS OFF)
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;
3561 QUERY PLAN
3562 --------------------------------------------------------------------
3563 Sort
3564 Sort Key: t1.a
3565 -> Append
3566 -> Hash Right Join
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
3569 -> Hash
3570 -> Seq Scan on plt1_adv_p1 t1_1
3571 Filter: (b < 10)
3572 -> Hash Right Join
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
3575 -> Hash
3576 -> Seq Scan on plt1_adv_p2 t1_2
3577 Filter: (b < 10)
3578 -> Hash Right Join
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
3581 -> Hash
3582 -> Seq Scan on plt1_adv_p3 t1_3
3583 Filter: (b < 10)
3584 (21 rows)
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;
3587 a | c | a | c
3588 ---+------+---+------
3589 1 | 0001 | |
3590 3 | 0003 | 3 | 0003
3591 4 | 0004 | 4 | 0004
3592 6 | 0006 | 6 | 0006
3593 8 | 0008 | |
3594 9 | 0009 | 9 | 0009
3595 (6 rows)
3597 -- anti join
3598 EXPLAIN (COSTS OFF)
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;
3600 QUERY PLAN
3601 --------------------------------------------------------------------
3602 Sort
3603 Sort Key: t1.a
3604 -> Append
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
3608 -> Hash
3609 -> Seq Scan on plt1_adv_p1 t1_1
3610 Filter: (b < 10)
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
3614 -> Hash
3615 -> Seq Scan on plt1_adv_p2 t1_2
3616 Filter: (b < 10)
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
3620 -> Hash
3621 -> Seq Scan on plt1_adv_p3 t1_3
3622 Filter: (b < 10)
3623 (21 rows)
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;
3626 a | b | c
3627 ---+---+------
3628 1 | 1 | 0001
3629 8 | 8 | 0008
3630 (2 rows)
3632 -- full join
3633 EXPLAIN (COSTS OFF)
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;
3635 QUERY PLAN
3636 -----------------------------------------------------------------------------------
3637 Sort
3638 Sort Key: t1.a, t2.a
3639 -> Append
3640 -> Hash Full Join
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
3644 -> Hash
3645 -> Seq Scan on plt2_adv_p1 t2_1
3646 -> Hash Full Join
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
3650 -> Hash
3651 -> Seq Scan on plt2_adv_p2 t2_2
3652 -> Hash Full Join
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
3656 -> Hash
3657 -> Seq Scan on plt2_adv_p3 t2_3
3658 (21 rows)
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;
3661 a | c | a | c
3662 ---+------+---+------
3663 1 | 0001 | |
3664 3 | 0003 | 3 | 0003
3665 4 | 0004 | 4 | 0004
3666 6 | 0006 | 6 | 0006
3667 8 | 0008 | |
3668 9 | 0009 | 9 | 0009
3669 | | 2 | 0002
3670 | | 7 | 0007
3671 (8 rows)
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');
3676 ANALYZE plt2_adv;
3677 -- inner join
3678 EXPLAIN (COSTS OFF)
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;
3680 QUERY PLAN
3681 --------------------------------------------------------------------
3682 Sort
3683 Sort Key: t1.a
3684 -> Append
3685 -> Hash Join
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
3688 -> Hash
3689 -> Seq Scan on plt1_adv_p1 t1_1
3690 Filter: (b < 10)
3691 -> Hash Join
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
3694 -> Hash
3695 -> Seq Scan on plt1_adv_p2 t1_2
3696 Filter: (b < 10)
3697 -> Hash Join
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
3700 -> Hash
3701 -> Seq Scan on plt1_adv_p3 t1_3
3702 Filter: (b < 10)
3703 (21 rows)
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;
3706 a | c | a | c
3707 ---+------+---+------
3708 3 | 0003 | 3 | 0003
3709 4 | 0004 | 4 | 0004
3710 6 | 0006 | 6 | 0006
3711 9 | 0009 | 9 | 0009
3712 (4 rows)
3714 -- semi join
3715 EXPLAIN (COSTS OFF)
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;
3717 QUERY PLAN
3718 --------------------------------------------------------------------
3719 Sort
3720 Sort Key: t1.a
3721 -> Append
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
3725 -> Hash
3726 -> Seq Scan on plt1_adv_p1 t1_1
3727 Filter: (b < 10)
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
3731 -> Hash
3732 -> Seq Scan on plt1_adv_p2 t1_2
3733 Filter: (b < 10)
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
3737 -> Hash
3738 -> Seq Scan on plt1_adv_p3 t1_3
3739 Filter: (b < 10)
3740 (21 rows)
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;
3743 a | b | c
3744 ---+---+------
3745 3 | 3 | 0003
3746 4 | 4 | 0004
3747 6 | 6 | 0006
3748 9 | 9 | 0009
3749 (4 rows)
3751 -- left join
3752 EXPLAIN (COSTS OFF)
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;
3754 QUERY PLAN
3755 --------------------------------------------------------------------
3756 Sort
3757 Sort Key: t1.a
3758 -> Append
3759 -> Hash Right Join
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
3762 -> Hash
3763 -> Seq Scan on plt1_adv_p1 t1_1
3764 Filter: (b < 10)
3765 -> Hash Right Join
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
3768 -> Hash
3769 -> Seq Scan on plt1_adv_p2 t1_2
3770 Filter: (b < 10)
3771 -> Hash Right Join
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
3774 -> Hash
3775 -> Seq Scan on plt1_adv_p3 t1_3
3776 Filter: (b < 10)
3777 (21 rows)
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;
3780 a | c | a | c
3781 ---+------+---+------
3782 1 | 0001 | |
3783 3 | 0003 | 3 | 0003
3784 4 | 0004 | 4 | 0004
3785 6 | 0006 | 6 | 0006
3786 8 | 0008 | |
3787 9 | 0009 | 9 | 0009
3788 (6 rows)
3790 -- left join; currently we can't do partitioned join if there are no matched
3791 -- partitions on the nullable side
3792 EXPLAIN (COSTS OFF)
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;
3794 QUERY PLAN
3795 ---------------------------------------------------------
3796 Sort
3797 Sort Key: t1.a
3798 -> Hash Right Join
3799 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3800 -> Append
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
3804 -> Hash
3805 -> Append
3806 -> Seq Scan on plt2_adv_extra t1_1
3807 Filter: (b < 10)
3808 -> Seq Scan on plt2_adv_p1 t1_2
3809 Filter: (b < 10)
3810 -> Seq Scan on plt2_adv_p2 t1_3
3811 Filter: (b < 10)
3812 -> Seq Scan on plt2_adv_p3 t1_4
3813 Filter: (b < 10)
3814 (18 rows)
3816 -- anti join
3817 EXPLAIN (COSTS OFF)
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;
3819 QUERY PLAN
3820 --------------------------------------------------------------------
3821 Sort
3822 Sort Key: t1.a
3823 -> Append
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
3827 -> Hash
3828 -> Seq Scan on plt1_adv_p1 t1_1
3829 Filter: (b < 10)
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
3833 -> Hash
3834 -> Seq Scan on plt1_adv_p2 t1_2
3835 Filter: (b < 10)
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
3839 -> Hash
3840 -> Seq Scan on plt1_adv_p3 t1_3
3841 Filter: (b < 10)
3842 (21 rows)
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;
3845 a | b | c
3846 ---+---+------
3847 1 | 1 | 0001
3848 8 | 8 | 0008
3849 (2 rows)
3851 -- anti join; currently we can't do partitioned join if there are no matched
3852 -- partitions on the nullable side
3853 EXPLAIN (COSTS OFF)
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;
3855 QUERY PLAN
3856 ---------------------------------------------------------
3857 Sort
3858 Sort Key: t1.a
3859 -> Hash Right Anti Join
3860 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3861 -> Append
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
3865 -> Hash
3866 -> Append
3867 -> Seq Scan on plt2_adv_extra t1_1
3868 Filter: (b < 10)
3869 -> Seq Scan on plt2_adv_p1 t1_2
3870 Filter: (b < 10)
3871 -> Seq Scan on plt2_adv_p2 t1_3
3872 Filter: (b < 10)
3873 -> Seq Scan on plt2_adv_p3 t1_4
3874 Filter: (b < 10)
3875 (18 rows)
3877 -- full join; currently we can't do partitioned join if there are no matched
3878 -- partitions on the nullable side
3879 EXPLAIN (COSTS OFF)
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;
3881 QUERY PLAN
3882 -------------------------------------------------------------------------
3883 Sort
3884 Sort Key: t1.a, t2.a
3885 -> Hash Full Join
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))
3888 -> Append
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
3893 -> Hash
3894 -> Append
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
3898 (15 rows)
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);
3908 ANALYZE plt2_adv;
3909 -- inner join
3910 EXPLAIN (COSTS OFF)
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;
3912 QUERY PLAN
3913 ------------------------------------------------------
3914 Sort
3915 Sort Key: t1.a
3916 -> Hash Join
3917 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3918 -> Append
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
3923 -> Hash
3924 -> Append
3925 -> Seq Scan on plt1_adv_p1 t1_1
3926 Filter: (b < 10)
3927 -> Seq Scan on plt1_adv_p2 t1_2
3928 Filter: (b < 10)
3929 -> Seq Scan on plt1_adv_p3 t1_3
3930 Filter: (b < 10)
3931 (17 rows)
3933 -- semi join
3934 EXPLAIN (COSTS OFF)
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;
3936 QUERY PLAN
3937 ------------------------------------------------------
3938 Sort
3939 Sort Key: t1.a
3940 -> Hash Right Semi Join
3941 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3942 -> Append
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
3947 -> Hash
3948 -> Append
3949 -> Seq Scan on plt1_adv_p1 t1_1
3950 Filter: (b < 10)
3951 -> Seq Scan on plt1_adv_p2 t1_2
3952 Filter: (b < 10)
3953 -> Seq Scan on plt1_adv_p3 t1_3
3954 Filter: (b < 10)
3955 (17 rows)
3957 -- left join
3958 EXPLAIN (COSTS OFF)
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;
3960 QUERY PLAN
3961 ------------------------------------------------------
3962 Sort
3963 Sort Key: t1.a
3964 -> Hash Right Join
3965 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3966 -> Append
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
3971 -> Hash
3972 -> Append
3973 -> Seq Scan on plt1_adv_p1 t1_1
3974 Filter: (b < 10)
3975 -> Seq Scan on plt1_adv_p2 t1_2
3976 Filter: (b < 10)
3977 -> Seq Scan on plt1_adv_p3 t1_3
3978 Filter: (b < 10)
3979 (17 rows)
3981 -- anti join
3982 EXPLAIN (COSTS OFF)
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;
3984 QUERY PLAN
3985 ------------------------------------------------------
3986 Sort
3987 Sort Key: t1.a
3988 -> Hash Right Anti Join
3989 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3990 -> Append
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
3995 -> Hash
3996 -> Append
3997 -> Seq Scan on plt1_adv_p1 t1_1
3998 Filter: (b < 10)
3999 -> Seq Scan on plt1_adv_p2 t1_2
4000 Filter: (b < 10)
4001 -> Seq Scan on plt1_adv_p3 t1_3
4002 Filter: (b < 10)
4003 (17 rows)
4005 -- full join
4006 EXPLAIN (COSTS OFF)
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;
4008 QUERY PLAN
4009 -------------------------------------------------------------------------
4010 Sort
4011 Sort Key: t1.a, t2.a
4012 -> Hash Full Join
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))
4015 -> Append
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
4020 -> Hash
4021 -> Append
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
4025 (15 rows)
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);
4037 ANALYZE plt1_adv;
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);
4043 ANALYZE plt2_adv;
4044 -- inner join
4045 EXPLAIN (COSTS OFF)
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;
4047 QUERY PLAN
4048 --------------------------------------------------------------------
4049 Sort
4050 Sort Key: t1.a
4051 -> Append
4052 -> Hash Join
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
4055 -> Hash
4056 -> Seq Scan on plt1_adv_p1_null t1_1
4057 Filter: (b < 10)
4058 -> Hash Join
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
4061 -> Hash
4062 -> Seq Scan on plt1_adv_p2 t1_2
4063 Filter: (b < 10)
4064 -> Hash Join
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
4067 -> Hash
4068 -> Seq Scan on plt1_adv_p3 t1_3
4069 Filter: (b < 10)
4070 (21 rows)
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;
4073 a | c | a | c
4074 ---+------+---+------
4075 3 | 0003 | 3 | 0003
4076 4 | 0004 | 4 | 0004
4077 6 | 0006 | 6 | 0006
4078 9 | 0009 | 9 | 0009
4079 (4 rows)
4081 -- semi join
4082 EXPLAIN (COSTS OFF)
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;
4084 QUERY PLAN
4085 --------------------------------------------------------------------
4086 Sort
4087 Sort Key: t1.a
4088 -> Append
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
4092 -> Hash
4093 -> Seq Scan on plt1_adv_p1_null t1_1
4094 Filter: (b < 10)
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
4098 -> Hash
4099 -> Seq Scan on plt1_adv_p2 t1_2
4100 Filter: (b < 10)
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
4104 -> Hash
4105 -> Seq Scan on plt1_adv_p3 t1_3
4106 Filter: (b < 10)
4107 (21 rows)
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;
4110 a | b | c
4111 ---+---+------
4112 3 | 3 | 0003
4113 4 | 4 | 0004
4114 6 | 6 | 0006
4115 9 | 9 | 0009
4116 (4 rows)
4118 -- left join
4119 EXPLAIN (COSTS OFF)
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;
4121 QUERY PLAN
4122 --------------------------------------------------------------------
4123 Sort
4124 Sort Key: t1.a
4125 -> Append
4126 -> Hash Right Join
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
4129 -> Hash
4130 -> Seq Scan on plt1_adv_p1_null t1_1
4131 Filter: (b < 10)
4132 -> Hash Right Join
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
4135 -> Hash
4136 -> Seq Scan on plt1_adv_p2 t1_2
4137 Filter: (b < 10)
4138 -> Hash Right Join
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
4141 -> Hash
4142 -> Seq Scan on plt1_adv_p3 t1_3
4143 Filter: (b < 10)
4144 (21 rows)
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;
4147 a | c | a | c
4148 ----+------+---+------
4149 -1 | | |
4150 1 | 0001 | |
4151 3 | 0003 | 3 | 0003
4152 4 | 0004 | 4 | 0004
4153 6 | 0006 | 6 | 0006
4154 8 | 0008 | |
4155 9 | 0009 | 9 | 0009
4156 (7 rows)
4158 -- anti join
4159 EXPLAIN (COSTS OFF)
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;
4161 QUERY PLAN
4162 --------------------------------------------------------------------
4163 Sort
4164 Sort Key: t1.a
4165 -> Append
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
4169 -> Hash
4170 -> Seq Scan on plt1_adv_p1_null t1_1
4171 Filter: (b < 10)
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
4175 -> Hash
4176 -> Seq Scan on plt1_adv_p2 t1_2
4177 Filter: (b < 10)
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
4181 -> Hash
4182 -> Seq Scan on plt1_adv_p3 t1_3
4183 Filter: (b < 10)
4184 (21 rows)
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;
4187 a | b | c
4188 ----+----+------
4189 -1 | -1 |
4190 1 | 1 | 0001
4191 8 | 8 | 0008
4192 (3 rows)
4194 -- full join
4195 EXPLAIN (COSTS OFF)
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;
4197 QUERY PLAN
4198 -----------------------------------------------------------------------------------
4199 Sort
4200 Sort Key: t1.a, t2.a
4201 -> Append
4202 -> Hash Full Join
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
4206 -> Hash
4207 -> Seq Scan on plt2_adv_p1 t2_1
4208 -> Hash Full Join
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
4212 -> Hash
4213 -> Seq Scan on plt2_adv_p2 t2_2
4214 -> Hash Full Join
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
4218 -> Hash
4219 -> Seq Scan on plt1_adv_p3 t1_3
4220 (21 rows)
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;
4223 a | c | a | c
4224 ----+------+----+------
4225 -1 | | |
4226 1 | 0001 | |
4227 3 | 0003 | 3 | 0003
4228 4 | 0004 | 4 | 0004
4229 6 | 0006 | 6 | 0006
4230 8 | 0008 | |
4231 9 | 0009 | 9 | 0009
4232 | | -1 |
4233 | | 2 | 0002
4234 | | 7 | 0007
4235 (10 rows)
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
4241 -- key values
4242 CREATE TABLE plt1_adv_extra PARTITION OF plt1_adv FOR VALUES IN (NULL);
4243 INSERT INTO plt1_adv VALUES (-1, -1, NULL);
4244 ANALYZE plt1_adv;
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');
4248 ANALYZE plt2_adv;
4249 -- inner join
4250 EXPLAIN (COSTS OFF)
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;
4252 QUERY PLAN
4253 --------------------------------------------------------------------
4254 Sort
4255 Sort Key: t1.a
4256 -> Append
4257 -> Hash Join
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
4260 -> Hash
4261 -> Seq Scan on plt1_adv_p1 t1_1
4262 Filter: (b < 10)
4263 -> Hash Join
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
4266 -> Hash
4267 -> Seq Scan on plt1_adv_p2 t1_2
4268 Filter: (b < 10)
4269 -> Hash Join
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
4272 -> Hash
4273 -> Seq Scan on plt1_adv_p3 t1_3
4274 Filter: (b < 10)
4275 (21 rows)
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;
4278 a | c | a | c
4279 ---+------+---+------
4280 3 | 0003 | 3 | 0003
4281 4 | 0004 | 4 | 0004
4282 6 | 0006 | 6 | 0006
4283 9 | 0009 | 9 | 0009
4284 (4 rows)
4286 -- left join; currently we can't do partitioned join if there are no matched
4287 -- partitions on the nullable side
4288 EXPLAIN (COSTS OFF)
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;
4290 QUERY PLAN
4291 ---------------------------------------------------------
4292 Sort
4293 Sort Key: t1.a
4294 -> Hash Right Join
4295 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4296 -> Append
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
4300 -> Hash
4301 -> Append
4302 -> Seq Scan on plt1_adv_p1 t1_1
4303 Filter: (b < 10)
4304 -> Seq Scan on plt1_adv_p2 t1_2
4305 Filter: (b < 10)
4306 -> Seq Scan on plt1_adv_p3 t1_3
4307 Filter: (b < 10)
4308 -> Seq Scan on plt1_adv_extra t1_4
4309 Filter: (b < 10)
4310 (18 rows)
4312 -- full join; currently we can't do partitioned join if there are no matched
4313 -- partitions on the nullable side
4314 EXPLAIN (COSTS OFF)
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;
4316 QUERY PLAN
4317 -------------------------------------------------------------------------
4318 Sort
4319 Sort Key: t1.a, t2.a
4320 -> Hash Full Join
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))
4323 -> Append
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
4328 -> Hash
4329 -> Append
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
4333 (15 rows)
4335 -- Add to plt2_adv the extra NULL partition containing only NULL values as the
4336 -- key values
4337 CREATE TABLE plt2_adv_extra PARTITION OF plt2_adv FOR VALUES IN (NULL);
4338 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4339 ANALYZE plt2_adv;
4340 -- inner join
4341 EXPLAIN (COSTS OFF)
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;
4343 QUERY PLAN
4344 --------------------------------------------------------------------
4345 Sort
4346 Sort Key: t1.a
4347 -> Append
4348 -> Hash Join
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
4351 -> Hash
4352 -> Seq Scan on plt1_adv_p1 t1_1
4353 Filter: (b < 10)
4354 -> Hash Join
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
4357 -> Hash
4358 -> Seq Scan on plt1_adv_p2 t1_2
4359 Filter: (b < 10)
4360 -> Hash Join
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
4363 -> Hash
4364 -> Seq Scan on plt1_adv_p3 t1_3
4365 Filter: (b < 10)
4366 (21 rows)
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;
4369 a | c | a | c
4370 ---+------+---+------
4371 3 | 0003 | 3 | 0003
4372 4 | 0004 | 4 | 0004
4373 6 | 0006 | 6 | 0006
4374 9 | 0009 | 9 | 0009
4375 (4 rows)
4377 -- left join
4378 EXPLAIN (COSTS OFF)
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;
4380 QUERY PLAN
4381 ----------------------------------------------------------------------
4382 Sort
4383 Sort Key: t1.a
4384 -> Append
4385 -> Hash Right Join
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
4388 -> Hash
4389 -> Seq Scan on plt1_adv_p1 t1_1
4390 Filter: (b < 10)
4391 -> Hash Right Join
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
4394 -> Hash
4395 -> Seq Scan on plt1_adv_p2 t1_2
4396 Filter: (b < 10)
4397 -> Hash Right Join
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
4400 -> Hash
4401 -> Seq Scan on plt1_adv_p3 t1_3
4402 Filter: (b < 10)
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
4406 Filter: (b < 10)
4407 -> Seq Scan on plt2_adv_extra t2_4
4408 (26 rows)
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;
4411 a | c | a | c
4412 ----+------+---+------
4413 -1 | | |
4414 1 | 0001 | |
4415 3 | 0003 | 3 | 0003
4416 4 | 0004 | 4 | 0004
4417 6 | 0006 | 6 | 0006
4418 8 | 0008 | |
4419 9 | 0009 | 9 | 0009
4420 (7 rows)
4422 -- full join
4423 EXPLAIN (COSTS OFF)
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;
4425 QUERY PLAN
4426 -----------------------------------------------------------------------------------
4427 Sort
4428 Sort Key: t1.a, t2.a
4429 -> Append
4430 -> Hash Full Join
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
4434 -> Hash
4435 -> Seq Scan on plt2_adv_p1 t2_1
4436 -> Hash Full Join
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
4440 -> Hash
4441 -> Seq Scan on plt2_adv_p2 t2_2
4442 -> Hash Full Join
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
4446 -> Hash
4447 -> Seq Scan on plt2_adv_p3 t2_3
4448 -> Hash Full Join
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
4452 -> Hash
4453 -> Seq Scan on plt2_adv_extra t2_4
4454 (27 rows)
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;
4457 a | c | a | c
4458 ----+------+----+------
4459 -1 | | |
4460 1 | 0001 | |
4461 3 | 0003 | 3 | 0003
4462 4 | 0004 | 4 | 0004
4463 6 | 0006 | 6 | 0006
4464 8 | 0008 | |
4465 9 | 0009 | 9 | 0009
4466 | | -1 |
4467 | | 2 | 0002
4468 | | 7 | 0007
4469 (10 rows)
4471 -- 3-way join to test the NULL partition of a join relation
4472 EXPLAIN (COSTS OFF)
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;
4474 QUERY PLAN
4475 --------------------------------------------------------------------------------
4476 Sort
4477 Sort Key: t1.a
4478 -> Append
4479 -> Hash Right Join
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
4482 -> Hash
4483 -> Hash Right Join
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
4486 -> Hash
4487 -> Seq Scan on plt1_adv_p1 t1_1
4488 Filter: (b < 10)
4489 -> Hash Right Join
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
4492 -> Hash
4493 -> Hash Right Join
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
4496 -> Hash
4497 -> Seq Scan on plt1_adv_p2 t1_2
4498 Filter: (b < 10)
4499 -> Hash Right Join
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
4502 -> Hash
4503 -> Hash Right Join
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
4506 -> Hash
4507 -> Seq Scan on plt1_adv_p3 t1_3
4508 Filter: (b < 10)
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
4514 Filter: (b < 10)
4515 -> Seq Scan on plt2_adv_extra t2_4
4516 -> Seq Scan on plt1_adv_extra t3_4
4517 (41 rows)
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 ----+------+---+------+---+------
4522 -1 | | | | |
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
4529 (7 rows)
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;
4538 ANALYZE plt1_adv;
4539 DROP TABLE plt2_adv_p3;
4540 ANALYZE plt2_adv;
4541 -- We can do partitioned join even if only one of relations has the default
4542 -- partition
4543 EXPLAIN (COSTS OFF)
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;
4545 QUERY PLAN
4546 --------------------------------------------------------------------
4547 Sort
4548 Sort Key: t1.a
4549 -> Append
4550 -> Hash Join
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
4553 -> Hash
4554 -> Seq Scan on plt1_adv_p1 t1_2
4555 Filter: (b < 10)
4556 -> Hash Join
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
4559 -> Hash
4560 -> Seq Scan on plt1_adv_p2 t1_1
4561 Filter: (b < 10)
4562 (15 rows)
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;
4565 a | c | a | c
4566 ---+------+---+------
4567 3 | 0003 | 3 | 0003
4568 4 | 0004 | 4 | 0004
4569 6 | 0006 | 6 | 0006
4570 (3 rows)
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
4574 -- the key values
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);
4577 ANALYZE plt2_adv;
4578 -- Partitioned join can't be applied because the default partition of plt1_adv
4579 -- matches plt2_adv_p1 and plt2_adv_p2_ext
4580 EXPLAIN (COSTS OFF)
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;
4582 QUERY PLAN
4583 ------------------------------------------------------
4584 Sort
4585 Sort Key: t1.a
4586 -> Hash Join
4587 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4588 -> Append
4589 -> Seq Scan on plt2_adv_p1 t2_1
4590 -> Seq Scan on plt2_adv_p2_ext t2_2
4591 -> Hash
4592 -> Append
4593 -> Seq Scan on plt1_adv_p2 t1_1
4594 Filter: (b < 10)
4595 -> Seq Scan on plt1_adv_p1 t1_2
4596 Filter: (b < 10)
4597 (13 rows)
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;
4602 ANALYZE plt2_adv;
4603 -- Partitioned join can't be applied because the default partition of plt1_adv
4604 -- matches plt2_adv_p1 and plt2_adv_p2_ext
4605 EXPLAIN (COSTS OFF)
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;
4607 QUERY PLAN
4608 ------------------------------------------------------
4609 Sort
4610 Sort Key: t1.a
4611 -> Hash Join
4612 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4613 -> Append
4614 -> Seq Scan on plt2_adv_p1 t2_1
4615 -> Seq Scan on plt2_adv_p2_ext t2_2
4616 -> Hash
4617 -> Append
4618 -> Seq Scan on plt1_adv_p2 t1_1
4619 Filter: (b < 10)
4620 -> Seq Scan on plt1_adv_p1 t1_2
4621 Filter: (b < 10)
4622 (13 rows)
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');
4627 ANALYZE plt2_adv;
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);
4632 ANALYZE plt3_adv;
4633 -- 3-way join to test the default partition of a join relation
4634 EXPLAIN (COSTS OFF)
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;
4636 QUERY PLAN
4637 --------------------------------------------------------------------------------
4638 Sort
4639 Sort Key: t1.a
4640 -> Append
4641 -> Hash Right Join
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
4644 -> Hash
4645 -> Hash Right Join
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
4648 -> Hash
4649 -> Seq Scan on plt1_adv_p2 t1_1
4650 Filter: (b < 10)
4651 -> Hash Right Join
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
4654 -> Hash
4655 -> Hash Right Join
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
4658 -> Hash
4659 -> Seq Scan on plt1_adv_p1 t1_2
4660 Filter: (b < 10)
4661 (23 rows)
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 ---+------+---+------+---+------
4666 1 | 0001 | | | |
4667 3 | 0003 | 3 | 0003 | |
4668 4 | 0004 | 4 | 0004 | 4 | 0004
4669 6 | 0006 | 6 | 0006 | 6 | 0006
4670 (4 rows)
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);
4679 ANALYZE plt2_adv;
4680 EXPLAIN (COSTS OFF)
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;
4682 QUERY PLAN
4683 --------------------------------------------------------------------
4684 Sort
4685 Sort Key: t1.a
4686 -> Append
4687 -> Hash Join
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
4690 -> Hash
4691 -> Seq Scan on plt1_adv_p1 t1_2
4692 Filter: (b < 10)
4693 -> Hash Join
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
4696 -> Hash
4697 -> Seq Scan on plt1_adv_p2 t1_1
4698 Filter: (b < 10)
4699 (15 rows)
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;
4702 a | c | a | c
4703 ---+------+---+------
4704 1 | 0001 | 1 | 0001
4705 3 | 0003 | 3 | 0003
4706 4 | 0004 | 4 | 0004
4707 6 | 0006 | 6 | 0006
4708 (4 rows)
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);
4714 ANALYZE plt2_adv;
4715 EXPLAIN (COSTS OFF)
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;
4717 QUERY PLAN
4718 ------------------------------------------------------
4719 Sort
4720 Sort Key: t1.a
4721 -> Hash Join
4722 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4723 -> Seq Scan on plt2_adv_p2 t2
4724 -> Hash
4725 -> Seq Scan on plt1_adv_p2 t1
4726 Filter: (b < 10)
4727 (8 rows)
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;
4730 a | c | a | c
4731 ---+------+---+------
4732 4 | 0004 | 4 | 0004
4733 6 | 0006 | 6 | 0006
4734 (2 rows)
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);
4747 ANALYZE plt1_adv;
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);
4755 ANALYZE plt2_adv;
4756 EXPLAIN (COSTS OFF)
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;
4758 QUERY PLAN
4759 -----------------------------------------------------------------------------------------
4760 Sort
4761 Sort Key: t1.a
4762 -> Append
4763 -> Hash Join
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
4766 -> Hash
4767 -> Seq Scan on plt1_adv_p3 t1_1
4768 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4769 -> Hash Join
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
4772 -> Hash
4773 -> Seq Scan on plt1_adv_p4 t1_2
4774 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4775 (15 rows)
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;
4778 a | c | a | c
4779 ---+------+---+------
4780 3 | 0003 | 3 | 0003
4781 4 | 0004 | 4 | 0004
4782 5 | 0005 | 5 | 0005
4783 (3 rows)
4785 EXPLAIN (COSTS OFF)
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;
4787 QUERY PLAN
4788 --------------------------------------------------------
4789 Sort
4790 Sort Key: t1.a
4791 -> Hash Right Join
4792 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4793 -> Seq Scan on plt2_adv_p4 t2
4794 -> Hash
4795 -> Seq Scan on plt1_adv_p4 t1
4796 Filter: ((c IS NULL) AND (b < 10))
4797 (8 rows)
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;
4800 a | c | a | c
4801 ----+---+---+---
4802 -1 | | |
4803 (1 row)
4805 CREATE TABLE plt1_adv_default PARTITION OF plt1_adv DEFAULT;
4806 ANALYZE plt1_adv;
4807 CREATE TABLE plt2_adv_default PARTITION OF plt2_adv DEFAULT;
4808 ANALYZE plt2_adv;
4809 EXPLAIN (COSTS OFF)
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;
4811 QUERY PLAN
4812 -----------------------------------------------------------------------------------------
4813 Sort
4814 Sort Key: t1.a
4815 -> Append
4816 -> Hash Join
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
4819 -> Hash
4820 -> Seq Scan on plt1_adv_p3 t1_1
4821 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4822 -> Hash Join
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
4825 -> Hash
4826 -> Seq Scan on plt1_adv_p4 t1_2
4827 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4828 (15 rows)
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;
4831 a | c | a | c
4832 ---+------+---+------
4833 3 | 0003 | 3 | 0003
4834 4 | 0004 | 4 | 0004
4835 5 | 0005 | 5 | 0005
4836 (3 rows)
4838 EXPLAIN (COSTS OFF)
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;
4840 QUERY PLAN
4841 --------------------------------------------------------
4842 Sort
4843 Sort Key: t1.a
4844 -> Hash Right Join
4845 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4846 -> Seq Scan on plt2_adv_p4 t2
4847 -> Hash
4848 -> Seq Scan on plt1_adv_p4 t1
4849 Filter: ((c IS NULL) AND (b < 10))
4850 (8 rows)
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;
4853 a | c | a | c
4854 ----+---+---+---
4855 -1 | | |
4856 (1 row)
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;
4865 ANALYZE plt1_adv;
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);
4870 ANALYZE plt2_adv;
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);
4875 ANALYZE plt3_adv;
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
4880 EXPLAIN (COSTS OFF)
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;
4882 QUERY PLAN
4883 -----------------------------------------------------------------------------------------------
4884 Sort
4885 Sort Key: t1.c, t1.a, t2.a, t3.a
4886 -> Append
4887 -> Hash Full Join
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))
4890 -> Hash Left Join
4891 Hash Cond: (t1_1.c = t2_1.c)
4892 -> Seq Scan on plt1_adv_p1 t1_1
4893 -> Hash
4894 -> Seq Scan on plt2_adv_p1 t2_1
4895 -> Hash
4896 -> Seq Scan on plt3_adv_p1 t3_1
4897 -> Hash Full Join
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))
4900 -> Hash Left Join
4901 Hash Cond: (t1_2.c = t2_2.c)
4902 -> Seq Scan on plt1_adv_p2 t1_2
4903 -> Hash
4904 -> Seq Scan on plt2_adv_p2 t2_2
4905 -> Hash
4906 -> Seq Scan on plt3_adv_p2 t3_2
4907 (23 rows)
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 ----+------+----+------+----+------
4912 0 | 0000 | | | |
4913 5 | 0000 | | | |
4914 10 | 0000 | | | |
4915 15 | 0000 | | | |
4916 20 | 0000 | | | |
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 | |
4967 (55 rows)
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);
4984 ANALYZE alpha;
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);
5000 ANALYZE beta;
5001 EXPLAIN (COSTS OFF)
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;
5003 QUERY PLAN
5004 --------------------------------------------------------------------
5005 Sort
5006 Sort Key: t1.a, t1.b
5007 -> Append
5008 -> Hash Join
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))
5012 -> Hash
5013 -> Seq Scan on beta_neg_p1 t2_1
5014 -> Hash Join
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
5017 -> Hash
5018 -> Seq Scan on alpha_neg_p2 t1_2
5019 Filter: ((b >= 125) AND (b < 225))
5020 -> Hash Join
5021 Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
5022 -> Append
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
5026 -> Hash
5027 -> Append
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))
5034 (29 rows)
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
5079 (40 rows)
5081 EXPLAIN (COSTS OFF)
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;
5083 QUERY PLAN
5084 --------------------------------------------------------------------------------------------------------------------------------------
5085 Sort
5086 Sort Key: t1.a, t1.b, t2.b
5087 -> Append
5088 -> Hash Join
5089 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
5090 -> Append
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))))
5095 -> Hash
5096 -> Append
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)))
5101 -> Nested Loop
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)))
5107 -> Nested Loop
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)))
5113 (28 rows)
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
5134 (16 rows)
5136 EXPLAIN (COSTS OFF)
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;
5138 QUERY PLAN
5139 --------------------------------------------------------------------------------------------------------------------------------
5140 Sort
5141 Sort Key: t1.a, t1.b
5142 -> Append
5143 -> Hash Join
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))))
5147 -> Hash
5148 -> Seq Scan on beta_neg_p1 t2_1
5149 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5150 -> Hash Join
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))))
5154 -> Hash
5155 -> Seq Scan on beta_neg_p2 t2_2
5156 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5157 -> Nested Loop
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)))
5163 -> Nested Loop
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)))
5169 (29 rows)
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
5182 (8 rows)
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');
5188 -- insert data
5189 INSERT INTO fract_t (id) (SELECT generate_series(0, 1999));
5190 ANALYZE fract_t;
5191 -- verify plan; nested index only scans
5192 SET max_parallel_workers_per_gather = 0;
5193 SET enable_partitionwise_join = on;
5194 EXPLAIN (COSTS OFF)
5195 SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
5196 QUERY PLAN
5197 -----------------------------------------------------------------------
5198 Limit
5199 -> Merge Append
5200 Sort Key: x.id
5201 -> Merge Left Join
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
5205 -> Merge Left Join
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
5209 (11 rows)
5211 EXPLAIN (COSTS OFF)
5212 SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10;
5213 QUERY PLAN
5214 --------------------------------------------------------------------------------
5215 Limit
5216 -> Merge Append
5217 Sort Key: x.id DESC
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)
5226 (11 rows)
5228 -- cleanup
5229 DROP TABLE fract_t;
5230 RESET max_parallel_workers_per_gather;
5231 RESET enable_partitionwise_join;