| blob | 320b7273e745dfcee8a1bb2afe3b99f9c209fe4a |
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 -- left outer join, 3-way
66 EXPLAIN (COSTS OFF)
67 SELECT COUNT(*) FROM prt1 t1
68 LEFT JOIN prt1 t2 ON t1.a = t2.a
69 LEFT JOIN prt1 t3 ON t2.a = t3.a;
70 QUERY PLAN
71 --------------------------------------------------------
72 Aggregate
73 -> Append
74 -> Hash Left Join
75 Hash Cond: (t2_1.a = t3_1.a)
76 -> Hash Left Join
77 Hash Cond: (t1_1.a = t2_1.a)
78 -> Seq Scan on prt1_p1 t1_1
79 -> Hash
80 -> Seq Scan on prt1_p1 t2_1
81 -> Hash
82 -> Seq Scan on prt1_p1 t3_1
83 -> Hash Left Join
84 Hash Cond: (t2_2.a = t3_2.a)
85 -> Hash Left Join
86 Hash Cond: (t1_2.a = t2_2.a)
87 -> Seq Scan on prt1_p2 t1_2
88 -> Hash
89 -> Seq Scan on prt1_p2 t2_2
90 -> Hash
91 -> Seq Scan on prt1_p2 t3_2
92 -> Hash Left Join
93 Hash Cond: (t2_3.a = t3_3.a)
94 -> Hash Left Join
95 Hash Cond: (t1_3.a = t2_3.a)
96 -> Seq Scan on prt1_p3 t1_3
97 -> Hash
98 -> Seq Scan on prt1_p3 t2_3
99 -> Hash
100 -> Seq Scan on prt1_p3 t3_3
101 (29 rows)
103 SELECT COUNT(*) FROM prt1 t1
104 LEFT JOIN prt1 t2 ON t1.a = t2.a
105 LEFT JOIN prt1 t3 ON t2.a = t3.a;
106 count
107 -------
108 300
109 (1 row)
111 -- left outer join, with whole-row reference; partitionwise join does not apply
112 EXPLAIN (COSTS OFF)
113 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;
114 QUERY PLAN
115 --------------------------------------------------
116 Sort
117 Sort Key: t1.a, t2.b
118 -> Hash Right Join
119 Hash Cond: (t2.b = t1.a)
120 -> Append
121 -> Seq Scan on prt2_p1 t2_1
122 -> Seq Scan on prt2_p2 t2_2
123 -> Seq Scan on prt2_p3 t2_3
124 -> Hash
125 -> Append
126 -> Seq Scan on prt1_p1 t1_1
127 Filter: (b = 0)
128 -> Seq Scan on prt1_p2 t1_2
129 Filter: (b = 0)
130 -> Seq Scan on prt1_p3 t1_3
131 Filter: (b = 0)
132 (16 rows)
134 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;
135 t1 | t2
136 --------------+--------------
137 (0,0,0000) | (0,0,0000)
138 (50,0,0050) |
139 (100,0,0100) |
140 (150,0,0150) | (0,150,0150)
141 (200,0,0200) |
142 (250,0,0250) |
143 (300,0,0300) | (0,300,0300)
144 (350,0,0350) |
145 (400,0,0400) |
146 (450,0,0450) | (0,450,0450)
147 (500,0,0500) |
148 (550,0,0550) |
149 (12 rows)
151 -- right outer join
152 EXPLAIN (COSTS OFF)
153 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;
154 QUERY PLAN
155 ---------------------------------------------------------------
156 Sort
157 Sort Key: t1.a, t2.b
158 -> Append
159 -> Hash Right Join
160 Hash Cond: (t1_1.a = t2_1.b)
161 -> Seq Scan on prt1_p1 t1_1
162 -> Hash
163 -> Seq Scan on prt2_p1 t2_1
164 Filter: (a = 0)
165 -> Hash Right Join
166 Hash Cond: (t1_2.a = t2_2.b)
167 -> Seq Scan on prt1_p2 t1_2
168 -> Hash
169 -> Seq Scan on prt2_p2 t2_2
170 Filter: (a = 0)
171 -> Nested Loop Left Join
172 -> Seq Scan on prt2_p3 t2_3
173 Filter: (a = 0)
174 -> Index Scan using iprt1_p3_a on prt1_p3 t1_3
175 Index Cond: (a = t2_3.b)
176 (20 rows)
178 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;
179 a | c | b | c
180 -----+------+-----+------
181 0 | 0000 | 0 | 0000
182 150 | 0150 | 150 | 0150
183 300 | 0300 | 300 | 0300
184 450 | 0450 | 450 | 0450
185 | | 75 | 0075
186 | | 225 | 0225
187 | | 375 | 0375
188 | | 525 | 0525
189 (8 rows)
191 -- full outer join, with placeholder vars
192 EXPLAIN (COSTS OFF)
193 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;
194 QUERY PLAN
195 ----------------------------------------------------------------
196 Sort
197 Sort Key: prt1.a, prt2.b
198 -> Append
199 -> Hash Full Join
200 Hash Cond: (prt1_1.a = prt2_1.b)
201 Filter: (((50) = prt1_1.a) OR ((75) = prt2_1.b))
202 -> Seq Scan on prt1_p1 prt1_1
203 Filter: (b = 0)
204 -> Hash
205 -> Seq Scan on prt2_p1 prt2_1
206 Filter: (a = 0)
207 -> Hash Full Join
208 Hash Cond: (prt1_2.a = prt2_2.b)
209 Filter: (((50) = prt1_2.a) OR ((75) = prt2_2.b))
210 -> Seq Scan on prt1_p2 prt1_2
211 Filter: (b = 0)
212 -> Hash
213 -> Seq Scan on prt2_p2 prt2_2
214 Filter: (a = 0)
215 -> Hash Full Join
216 Hash Cond: (prt1_3.a = prt2_3.b)
217 Filter: (((50) = prt1_3.a) OR ((75) = prt2_3.b))
218 -> Seq Scan on prt1_p3 prt1_3
219 Filter: (b = 0)
220 -> Hash
221 -> Seq Scan on prt2_p3 prt2_3
222 Filter: (a = 0)
223 (27 rows)
225 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;
226 a | c | b | c
227 ----+------+----+------
228 50 | 0050 | |
229 | | 75 | 0075
230 (2 rows)
232 -- Join with pruned partitions from joining relations
233 EXPLAIN (COSTS OFF)
234 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;
235 QUERY PLAN
236 -----------------------------------------------------
237 Sort
238 Sort Key: t1.a
239 -> Hash Join
240 Hash Cond: (t2.b = t1.a)
241 -> Seq Scan on prt2_p2 t2
242 Filter: (b > 250)
243 -> Hash
244 -> Seq Scan on prt1_p2 t1
245 Filter: ((a < 450) AND (b = 0))
246 (9 rows)
248 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;
249 a | c | b | c
250 -----+------+-----+------
251 300 | 0300 | 300 | 0300
252 (1 row)
254 -- Currently we can't do partitioned join if nullable-side partitions are pruned
255 EXPLAIN (COSTS OFF)
256 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;
257 QUERY PLAN
258 -----------------------------------------------------------
259 Sort
260 Sort Key: prt1.a, prt2.b
261 -> Hash Right Join
262 Hash Cond: (prt2.b = prt1.a)
263 -> Append
264 -> Seq Scan on prt2_p2 prt2_1
265 Filter: (b > 250)
266 -> Seq Scan on prt2_p3 prt2_2
267 Filter: (b > 250)
268 -> Hash
269 -> Append
270 -> Seq Scan on prt1_p1 prt1_1
271 Filter: ((a < 450) AND (b = 0))
272 -> Seq Scan on prt1_p2 prt1_2
273 Filter: ((a < 450) AND (b = 0))
274 (15 rows)
276 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;
277 a | c | b | c
278 -----+------+-----+------
279 0 | 0000 | |
280 50 | 0050 | |
281 100 | 0100 | |
282 150 | 0150 | |
283 200 | 0200 | |
284 250 | 0250 | |
285 300 | 0300 | 300 | 0300
286 350 | 0350 | |
287 400 | 0400 | |
288 (9 rows)
290 -- Currently we can't do partitioned join if nullable-side partitions are pruned
291 EXPLAIN (COSTS OFF)
292 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;
293 QUERY PLAN
294 ----------------------------------------------------
295 Sort
296 Sort Key: prt1.a, prt2.b
297 -> Hash Full Join
298 Hash Cond: (prt1.a = prt2.b)
299 Filter: ((prt1.b = 0) OR (prt2.a = 0))
300 -> Append
301 -> Seq Scan on prt1_p1 prt1_1
302 Filter: (a < 450)
303 -> Seq Scan on prt1_p2 prt1_2
304 Filter: (a < 450)
305 -> Hash
306 -> Append
307 -> Seq Scan on prt2_p2 prt2_1
308 Filter: (b > 250)
309 -> Seq Scan on prt2_p3 prt2_2
310 Filter: (b > 250)
311 (16 rows)
313 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;
314 a | c | b | c
315 -----+------+-----+------
316 0 | 0000 | |
317 50 | 0050 | |
318 100 | 0100 | |
319 150 | 0150 | |
320 200 | 0200 | |
321 250 | 0250 | |
322 300 | 0300 | 300 | 0300
323 350 | 0350 | |
324 400 | 0400 | |
325 | | 375 | 0375
326 | | 450 | 0450
327 | | 525 | 0525
328 (12 rows)
330 -- Semi-join
331 EXPLAIN (COSTS OFF)
332 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;
333 QUERY PLAN
334 --------------------------------------------------
335 Sort
336 Sort Key: t1.a
337 -> Append
338 -> Hash Semi Join
339 Hash Cond: (t1_1.a = t2_1.b)
340 -> Seq Scan on prt1_p1 t1_1
341 Filter: (b = 0)
342 -> Hash
343 -> Seq Scan on prt2_p1 t2_1
344 Filter: (a = 0)
345 -> Hash Semi Join
346 Hash Cond: (t1_2.a = t2_2.b)
347 -> Seq Scan on prt1_p2 t1_2
348 Filter: (b = 0)
349 -> Hash
350 -> Seq Scan on prt2_p2 t2_2
351 Filter: (a = 0)
352 -> Nested Loop Semi Join
353 Join Filter: (t1_3.a = t2_3.b)
354 -> Seq Scan on prt1_p3 t1_3
355 Filter: (b = 0)
356 -> Materialize
357 -> Seq Scan on prt2_p3 t2_3
358 Filter: (a = 0)
359 (24 rows)
361 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;
362 a | b | c
363 -----+---+------
364 0 | 0 | 0000
365 150 | 0 | 0150
366 300 | 0 | 0300
367 450 | 0 | 0450
368 (4 rows)
370 -- Anti-join with aggregates
371 EXPLAIN (COSTS OFF)
372 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);
373 QUERY PLAN
374 --------------------------------------------------
375 Aggregate
376 -> Append
377 -> Hash Anti Join
378 Hash Cond: (t1_1.a = t2_1.b)
379 -> Seq Scan on prt1_p1 t1_1
380 -> Hash
381 -> Seq Scan on prt2_p1 t2_1
382 -> Hash Anti Join
383 Hash Cond: (t1_2.a = t2_2.b)
384 -> Seq Scan on prt1_p2 t1_2
385 -> Hash
386 -> Seq Scan on prt2_p2 t2_2
387 -> Hash Anti Join
388 Hash Cond: (t1_3.a = t2_3.b)
389 -> Seq Scan on prt1_p3 t1_3
390 -> Hash
391 -> Seq Scan on prt2_p3 t2_3
392 (17 rows)
394 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);
395 sum | avg | sum | avg
396 -------+----------------------+------+---------------------
397 60000 | 300.0000000000000000 | 2400 | 12.0000000000000000
398 (1 row)
400 -- lateral reference
401 EXPLAIN (COSTS OFF)
402 SELECT * FROM prt1 t1 LEFT JOIN LATERAL
403 (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
404 ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a;
405 QUERY PLAN
406 --------------------------------------------------------------------------
407 Sort
408 Sort Key: t1.a
409 -> Append
410 -> Nested Loop Left Join
411 -> Seq Scan on prt1_p1 t1_1
412 Filter: (b = 0)
413 -> Nested Loop
414 -> Index Only Scan using iprt1_p1_a on prt1_p1 t2_1
415 Index Cond: (a = t1_1.a)
416 -> Index Scan using iprt2_p1_b on prt2_p1 t3_1
417 Index Cond: (b = t2_1.a)
418 -> Nested Loop Left Join
419 -> Seq Scan on prt1_p2 t1_2
420 Filter: (b = 0)
421 -> Nested Loop
422 -> Index Only Scan using iprt1_p2_a on prt1_p2 t2_2
423 Index Cond: (a = t1_2.a)
424 -> Index Scan using iprt2_p2_b on prt2_p2 t3_2
425 Index Cond: (b = t2_2.a)
426 -> Nested Loop Left Join
427 -> Seq Scan on prt1_p3 t1_3
428 Filter: (b = 0)
429 -> Nested Loop
430 -> Index Only Scan using iprt1_p3_a on prt1_p3 t2_3
431 Index Cond: (a = t1_3.a)
432 -> Index Scan using iprt2_p3_b on prt2_p3 t3_3
433 Index Cond: (b = t2_3.a)
434 (27 rows)
436 SELECT * FROM prt1 t1 LEFT JOIN LATERAL
437 (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
438 ON t1.a = ss.t2a WHERE t1.b = 0 ORDER BY t1.a;
439 a | b | c | t2a | t3a | least
440 -----+---+------+-----+-----+-------
441 0 | 0 | 0000 | 0 | 0 | 0
442 50 | 0 | 0050 | | |
443 100 | 0 | 0100 | | |
444 150 | 0 | 0150 | 150 | 0 | 150
445 200 | 0 | 0200 | | |
446 250 | 0 | 0250 | | |
447 300 | 0 | 0300 | 300 | 0 | 300
448 350 | 0 | 0350 | | |
449 400 | 0 | 0400 | | |
450 450 | 0 | 0450 | 450 | 0 | 450
451 500 | 0 | 0500 | | |
452 550 | 0 | 0550 | | |
453 (12 rows)
455 EXPLAIN (COSTS OFF)
456 SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
457 (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
458 ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a;
459 QUERY PLAN
460 --------------------------------------------------------------
461 Sort
462 Sort Key: t1.a
463 -> Hash Left Join
464 Hash Cond: ((t1.c)::text = (t2.c)::text)
465 Filter: ((t1.b + COALESCE(t2.b, 0)) = 0)
466 -> Append
467 -> Seq Scan on prt1_p1 t1_1
468 -> Seq Scan on prt1_p2 t1_2
469 -> Seq Scan on prt1_p3 t1_3
470 -> Hash
471 -> Append
472 -> Hash Join
473 Hash Cond: (t2_1.a = t3_1.b)
474 -> Seq Scan on prt1_p1 t2_1
475 -> Hash
476 -> Seq Scan on prt2_p1 t3_1
477 -> Hash Join
478 Hash Cond: (t2_2.a = t3_2.b)
479 -> Seq Scan on prt1_p2 t2_2
480 -> Hash
481 -> Seq Scan on prt2_p2 t3_2
482 -> Hash Join
483 Hash Cond: (t2_3.a = t3_3.b)
484 -> Seq Scan on prt1_p3 t2_3
485 -> Hash
486 -> Seq Scan on prt2_p3 t3_3
487 (26 rows)
489 SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
490 (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
491 ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a;
492 a | t2a | t2c
493 -----+-----+------
494 0 | 0 | 0000
495 50 | |
496 100 | |
497 150 | 150 | 0150
498 200 | |
499 250 | |
500 300 | 300 | 0300
501 350 | |
502 400 | |
503 450 | 450 | 0450
504 500 | |
505 550 | |
506 (12 rows)
508 SET max_parallel_workers_per_gather = 0;
509 -- If there are lateral references to the other relation in sample scan,
510 -- we cannot generate a partitionwise join.
511 EXPLAIN (COSTS OFF)
512 SELECT * FROM prt1 t1 JOIN LATERAL
513 (SELECT * FROM prt1 t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
514 ON t1.a = s.a;
515 QUERY PLAN
516 ---------------------------------------------------------
517 Nested Loop
518 -> Append
519 -> Seq Scan on prt1_p1 t1_1
520 -> Seq Scan on prt1_p2 t1_2
521 -> Seq Scan on prt1_p3 t1_3
522 -> Append
523 -> Sample Scan on prt1_p1 t2_1
524 Sampling: system (t1.a) REPEATABLE (t1.b)
525 Filter: (t1.a = a)
526 -> Sample Scan on prt1_p2 t2_2
527 Sampling: system (t1.a) REPEATABLE (t1.b)
528 Filter: (t1.a = a)
529 -> Sample Scan on prt1_p3 t2_3
530 Sampling: system (t1.a) REPEATABLE (t1.b)
531 Filter: (t1.a = a)
532 (15 rows)
534 -- If there are lateral references to the other relation in scan's restriction
535 -- clauses, we cannot generate a partitionwise join.
536 EXPLAIN (COSTS OFF)
537 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
538 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
539 ON t1.a = s.b WHERE s.t1b = s.a;
540 QUERY PLAN
541 ---------------------------------------------------------------
542 Aggregate
543 -> Nested Loop
544 -> Append
545 -> Seq Scan on prt1_p1 t1_1
546 -> Seq Scan on prt1_p2 t1_2
547 -> Seq Scan on prt1_p3 t1_3
548 -> Append
549 -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
550 Index Cond: (b = t1.a)
551 Filter: (t1.b = a)
552 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
553 Index Cond: (b = t1.a)
554 Filter: (t1.b = a)
555 -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
556 Index Cond: (b = t1.a)
557 Filter: (t1.b = a)
558 (16 rows)
560 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
561 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
562 ON t1.a = s.b WHERE s.t1b = s.a;
563 count
564 -------
565 100
566 (1 row)
568 EXPLAIN (COSTS OFF)
569 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
570 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
571 ON t1.a = s.b WHERE s.t1b = s.b;
572 QUERY PLAN
573 --------------------------------------------------------------------
574 Aggregate
575 -> Nested Loop
576 -> Append
577 -> Seq Scan on prt1_p1 t1_1
578 -> Seq Scan on prt1_p2 t1_2
579 -> Seq Scan on prt1_p3 t1_3
580 -> Append
581 -> Index Only Scan using iprt2_p1_b on prt2_p1 t2_1
582 Index Cond: (b = t1.a)
583 Filter: (b = t1.b)
584 -> Index Only Scan using iprt2_p2_b on prt2_p2 t2_2
585 Index Cond: (b = t1.a)
586 Filter: (b = t1.b)
587 -> Index Only Scan using iprt2_p3_b on prt2_p3 t2_3
588 Index Cond: (b = t1.a)
589 Filter: (b = t1.b)
590 (16 rows)
592 SELECT count(*) FROM prt1 t1 LEFT JOIN LATERAL
593 (SELECT t1.b AS t1b, t2.* FROM prt2 t2) s
594 ON t1.a = s.b WHERE s.t1b = s.b;
595 count
596 -------
597 5
598 (1 row)
600 RESET max_parallel_workers_per_gather;
601 -- bug with inadequate sort key representation
602 SET enable_partitionwise_aggregate TO true;
603 SET enable_hashjoin TO false;
604 EXPLAIN (COSTS OFF)
605 SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
606 WHERE a BETWEEN 490 AND 510
607 GROUP BY 1, 2 ORDER BY 1, 2;
608 QUERY PLAN
609 -----------------------------------------------------------------------------------------------------------------
610 Group
611 Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
612 -> Merge Append
613 Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
614 -> Group
615 Group Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
616 -> Sort
617 Sort Key: (COALESCE(prt1.a, p2.a)), (COALESCE(prt1.b, p2.b))
618 -> Merge Full Join
619 Merge Cond: ((prt1.a = p2.a) AND (prt1.b = p2.b))
620 Filter: ((COALESCE(prt1.a, p2.a) >= 490) AND (COALESCE(prt1.a, p2.a) <= 510))
621 -> Sort
622 Sort Key: prt1.a, prt1.b
623 -> Seq Scan on prt1_p1 prt1
624 -> Sort
625 Sort Key: p2.a, p2.b
626 -> Seq Scan on prt2_p1 p2
627 -> Group
628 Group Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
629 -> Sort
630 Sort Key: (COALESCE(prt1_1.a, p2_1.a)), (COALESCE(prt1_1.b, p2_1.b))
631 -> Merge Full Join
632 Merge Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
633 Filter: ((COALESCE(prt1_1.a, p2_1.a) >= 490) AND (COALESCE(prt1_1.a, p2_1.a) <= 510))
634 -> Sort
635 Sort Key: prt1_1.a, prt1_1.b
636 -> Seq Scan on prt1_p2 prt1_1
637 -> Sort
638 Sort Key: p2_1.a, p2_1.b
639 -> Seq Scan on prt2_p2 p2_1
640 -> Group
641 Group Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
642 -> Sort
643 Sort Key: (COALESCE(prt1_2.a, p2_2.a)), (COALESCE(prt1_2.b, p2_2.b))
644 -> Merge Full Join
645 Merge Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
646 Filter: ((COALESCE(prt1_2.a, p2_2.a) >= 490) AND (COALESCE(prt1_2.a, p2_2.a) <= 510))
647 -> Sort
648 Sort Key: prt1_2.a, prt1_2.b
649 -> Seq Scan on prt1_p3 prt1_2
650 -> Sort
651 Sort Key: p2_2.a, p2_2.b
652 -> Seq Scan on prt2_p3 p2_2
653 (43 rows)
655 SELECT a, b FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b)
656 WHERE a BETWEEN 490 AND 510
657 GROUP BY 1, 2 ORDER BY 1, 2;
658 a | b
659 -----+----
660 490 | 15
661 492 | 17
662 494 | 19
663 495 | 20
664 496 | 21
665 498 | 23
666 500 | 0
667 501 | 1
668 502 | 2
669 504 | 4
670 506 | 6
671 507 | 7
672 508 | 8
673 510 | 10
674 (14 rows)
676 RESET enable_partitionwise_aggregate;
677 RESET enable_hashjoin;
678 --
679 -- partitioned by expression
680 --
681 CREATE TABLE prt1_e (a int, b int, c int) PARTITION BY RANGE(((a + b)/2));
682 CREATE TABLE prt1_e_p1 PARTITION OF prt1_e FOR VALUES FROM (0) TO (250);
683 CREATE TABLE prt1_e_p2 PARTITION OF prt1_e FOR VALUES FROM (250) TO (500);
684 CREATE TABLE prt1_e_p3 PARTITION OF prt1_e FOR VALUES FROM (500) TO (600);
685 INSERT INTO prt1_e SELECT i, i, i % 25 FROM generate_series(0, 599, 2) i;
686 CREATE INDEX iprt1_e_p1_ab2 on prt1_e_p1(((a+b)/2));
687 CREATE INDEX iprt1_e_p2_ab2 on prt1_e_p2(((a+b)/2));
688 CREATE INDEX iprt1_e_p3_ab2 on prt1_e_p3(((a+b)/2));
689 ANALYZE prt1_e;
690 CREATE TABLE prt2_e (a int, b int, c int) PARTITION BY RANGE(((b + a)/2));
691 CREATE TABLE prt2_e_p1 PARTITION OF prt2_e FOR VALUES FROM (0) TO (250);
692 CREATE TABLE prt2_e_p2 PARTITION OF prt2_e FOR VALUES FROM (250) TO (500);
693 CREATE TABLE prt2_e_p3 PARTITION OF prt2_e FOR VALUES FROM (500) TO (600);
694 INSERT INTO prt2_e SELECT i, i, i % 25 FROM generate_series(0, 599, 3) i;
695 ANALYZE prt2_e;
696 EXPLAIN (COSTS OFF)
697 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;
698 QUERY PLAN
699 ------------------------------------------------------------------------------
700 Sort
701 Sort Key: t1.a, t2.b
702 -> Append
703 -> Hash Join
704 Hash Cond: (((t2_1.b + t2_1.a) / 2) = ((t1_1.a + t1_1.b) / 2))
705 -> Seq Scan on prt2_e_p1 t2_1
706 -> Hash
707 -> Seq Scan on prt1_e_p1 t1_1
708 Filter: (c = 0)
709 -> Hash Join
710 Hash Cond: (((t2_2.b + t2_2.a) / 2) = ((t1_2.a + t1_2.b) / 2))
711 -> Seq Scan on prt2_e_p2 t2_2
712 -> Hash
713 -> Seq Scan on prt1_e_p2 t1_2
714 Filter: (c = 0)
715 -> Hash Join
716 Hash Cond: (((t2_3.b + t2_3.a) / 2) = ((t1_3.a + t1_3.b) / 2))
717 -> Seq Scan on prt2_e_p3 t2_3
718 -> Hash
719 -> Seq Scan on prt1_e_p3 t1_3
720 Filter: (c = 0)
721 (21 rows)
723 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;
724 a | c | b | c
725 -----+---+-----+---
726 0 | 0 | 0 | 0
727 150 | 0 | 150 | 0
728 300 | 0 | 300 | 0
729 450 | 0 | 450 | 0
730 (4 rows)
732 --
733 -- N-way join
734 --
735 EXPLAIN (COSTS OFF)
736 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;
737 QUERY PLAN
738 ---------------------------------------------------------------------
739 Sort
740 Sort Key: t1.a
741 -> Append
742 -> Nested Loop
743 Join Filter: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
744 -> Hash Join
745 Hash Cond: (t2_1.b = t1_1.a)
746 -> Seq Scan on prt2_p1 t2_1
747 -> Hash
748 -> Seq Scan on prt1_p1 t1_1
749 Filter: (b = 0)
750 -> Index Scan using iprt1_e_p1_ab2 on prt1_e_p1 t3_1
751 Index Cond: (((a + b) / 2) = t2_1.b)
752 -> Nested Loop
753 Join Filter: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
754 -> Hash Join
755 Hash Cond: (t2_2.b = t1_2.a)
756 -> Seq Scan on prt2_p2 t2_2
757 -> Hash
758 -> Seq Scan on prt1_p2 t1_2
759 Filter: (b = 0)
760 -> Index Scan using iprt1_e_p2_ab2 on prt1_e_p2 t3_2
761 Index Cond: (((a + b) / 2) = t2_2.b)
762 -> Nested Loop
763 Join Filter: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
764 -> Hash Join
765 Hash Cond: (t2_3.b = t1_3.a)
766 -> Seq Scan on prt2_p3 t2_3
767 -> Hash
768 -> Seq Scan on prt1_p3 t1_3
769 Filter: (b = 0)
770 -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t3_3
771 Index Cond: (((a + b) / 2) = t2_3.b)
772 (33 rows)
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 a | c | b | c | ?column? | c
776 -----+------+-----+------+----------+---
777 0 | 0000 | 0 | 0000 | 0 | 0
778 150 | 0150 | 150 | 0150 | 300 | 0
779 300 | 0300 | 300 | 0300 | 600 | 0
780 450 | 0450 | 450 | 0450 | 900 | 0
781 (4 rows)
783 EXPLAIN (COSTS OFF)
784 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;
785 QUERY PLAN
786 --------------------------------------------------------------
787 Sort
788 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
789 -> Append
790 -> Hash Right Join
791 Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
792 -> Seq Scan on prt1_e_p1 t3_1
793 -> Hash
794 -> Hash Right Join
795 Hash Cond: (t2_1.b = t1_1.a)
796 -> Seq Scan on prt2_p1 t2_1
797 -> Hash
798 -> Seq Scan on prt1_p1 t1_1
799 Filter: (b = 0)
800 -> Hash Right Join
801 Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
802 -> Seq Scan on prt1_e_p2 t3_2
803 -> Hash
804 -> Hash Right Join
805 Hash Cond: (t2_2.b = t1_2.a)
806 -> Seq Scan on prt2_p2 t2_2
807 -> Hash
808 -> Seq Scan on prt1_p2 t1_2
809 Filter: (b = 0)
810 -> Hash Right Join
811 Hash Cond: (((t3_3.a + t3_3.b) / 2) = t1_3.a)
812 -> Seq Scan on prt1_e_p3 t3_3
813 -> Hash
814 -> Hash Right Join
815 Hash Cond: (t2_3.b = t1_3.a)
816 -> Seq Scan on prt2_p3 t2_3
817 -> Hash
818 -> Seq Scan on prt1_p3 t1_3
819 Filter: (b = 0)
820 (33 rows)
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 a | c | b | c | ?column? | c
824 -----+------+-----+------+----------+---
825 0 | 0000 | 0 | 0000 | 0 | 0
826 50 | 0050 | | | 100 | 0
827 100 | 0100 | | | 200 | 0
828 150 | 0150 | 150 | 0150 | 300 | 0
829 200 | 0200 | | | 400 | 0
830 250 | 0250 | | | 500 | 0
831 300 | 0300 | 300 | 0300 | 600 | 0
832 350 | 0350 | | | 700 | 0
833 400 | 0400 | | | 800 | 0
834 450 | 0450 | 450 | 0450 | 900 | 0
835 500 | 0500 | | | 1000 | 0
836 550 | 0550 | | | 1100 | 0
837 (12 rows)
839 EXPLAIN (COSTS OFF)
840 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;
841 QUERY PLAN
842 -------------------------------------------------------------------
843 Sort
844 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
845 -> Append
846 -> Nested Loop Left Join
847 -> Hash Right Join
848 Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
849 -> Seq Scan on prt1_p1 t1_1
850 -> Hash
851 -> Seq Scan on prt1_e_p1 t3_1
852 Filter: (c = 0)
853 -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
854 Index Cond: (b = t1_1.a)
855 -> Nested Loop Left Join
856 -> Hash Right Join
857 Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
858 -> Seq Scan on prt1_p2 t1_2
859 -> Hash
860 -> Seq Scan on prt1_e_p2 t3_2
861 Filter: (c = 0)
862 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
863 Index Cond: (b = t1_2.a)
864 -> Nested Loop Left Join
865 -> Hash Right Join
866 Hash Cond: (t1_3.a = ((t3_3.a + t3_3.b) / 2))
867 -> Seq Scan on prt1_p3 t1_3
868 -> Hash
869 -> Seq Scan on prt1_e_p3 t3_3
870 Filter: (c = 0)
871 -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
872 Index Cond: (b = t1_3.a)
873 (30 rows)
875 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;
876 a | c | b | c | ?column? | c
877 -----+------+-----+------+----------+---
878 0 | 0000 | 0 | 0000 | 0 | 0
879 50 | 0050 | | | 100 | 0
880 100 | 0100 | | | 200 | 0
881 150 | 0150 | 150 | 0150 | 300 | 0
882 200 | 0200 | | | 400 | 0
883 250 | 0250 | | | 500 | 0
884 300 | 0300 | 300 | 0300 | 600 | 0
885 350 | 0350 | | | 700 | 0
886 400 | 0400 | | | 800 | 0
887 450 | 0450 | 450 | 0450 | 900 | 0
888 500 | 0500 | | | 1000 | 0
889 550 | 0550 | | | 1100 | 0
890 (12 rows)
892 --
893 -- 3-way full join
894 --
895 EXPLAIN (COSTS OFF)
896 SELECT COUNT(*) FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b) FULL JOIN prt2 p3(b,a,c) USING (a, b)
897 WHERE a BETWEEN 490 AND 510;
898 QUERY PLAN
899 -----------------------------------------------------------------------------------------------------------------------------------------
900 Aggregate
901 -> Append
902 -> Hash Full Join
903 Hash Cond: ((COALESCE(prt1_1.a, p2_1.a) = p3_1.a) AND (COALESCE(prt1_1.b, p2_1.b) = p3_1.b))
904 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))
905 -> Hash Full Join
906 Hash Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
907 -> Seq Scan on prt1_p1 prt1_1
908 -> Hash
909 -> Seq Scan on prt2_p1 p2_1
910 -> Hash
911 -> Seq Scan on prt2_p1 p3_1
912 -> Hash Full Join
913 Hash Cond: ((COALESCE(prt1_2.a, p2_2.a) = p3_2.a) AND (COALESCE(prt1_2.b, p2_2.b) = p3_2.b))
914 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))
915 -> Hash Full Join
916 Hash Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
917 -> Seq Scan on prt1_p2 prt1_2
918 -> Hash
919 -> Seq Scan on prt2_p2 p2_2
920 -> Hash
921 -> Seq Scan on prt2_p2 p3_2
922 -> Hash Full Join
923 Hash Cond: ((COALESCE(prt1_3.a, p2_3.a) = p3_3.a) AND (COALESCE(prt1_3.b, p2_3.b) = p3_3.b))
924 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))
925 -> Hash Full Join
926 Hash Cond: ((prt1_3.a = p2_3.a) AND (prt1_3.b = p2_3.b))
927 -> Seq Scan on prt1_p3 prt1_3
928 -> Hash
929 -> Seq Scan on prt2_p3 p2_3
930 -> Hash
931 -> Seq Scan on prt2_p3 p3_3
932 (32 rows)
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 count
937 -------
938 14
939 (1 row)
941 --
942 -- 4-way full join
943 --
944 EXPLAIN (COSTS OFF)
945 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)
946 WHERE a BETWEEN 490 AND 510;
947 QUERY PLAN
948 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
949 Aggregate
950 -> Append
951 -> Hash Full Join
952 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))
953 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))
954 -> Hash Full Join
955 Hash Cond: ((COALESCE(prt1_1.a, p2_1.a) = p3_1.a) AND (COALESCE(prt1_1.b, p2_1.b) = p3_1.b))
956 -> Hash Full Join
957 Hash Cond: ((prt1_1.a = p2_1.a) AND (prt1_1.b = p2_1.b))
958 -> Seq Scan on prt1_p1 prt1_1
959 -> Hash
960 -> Seq Scan on prt2_p1 p2_1
961 -> Hash
962 -> Seq Scan on prt2_p1 p3_1
963 -> Hash
964 -> Seq Scan on prt1_p1 p4_1
965 -> Hash Full Join
966 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))
967 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))
968 -> Hash Full Join
969 Hash Cond: ((COALESCE(prt1_2.a, p2_2.a) = p3_2.a) AND (COALESCE(prt1_2.b, p2_2.b) = p3_2.b))
970 -> Hash Full Join
971 Hash Cond: ((prt1_2.a = p2_2.a) AND (prt1_2.b = p2_2.b))
972 -> Seq Scan on prt1_p2 prt1_2
973 -> Hash
974 -> Seq Scan on prt2_p2 p2_2
975 -> Hash
976 -> Seq Scan on prt2_p2 p3_2
977 -> Hash
978 -> Seq Scan on prt1_p2 p4_2
979 -> Hash Full Join
980 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))
981 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))
982 -> Hash Full Join
983 Hash Cond: ((COALESCE(prt1_3.a, p2_3.a) = p3_3.a) AND (COALESCE(prt1_3.b, p2_3.b) = p3_3.b))
984 -> Hash Full Join
985 Hash Cond: ((prt1_3.a = p2_3.a) AND (prt1_3.b = p2_3.b))
986 -> Seq Scan on prt1_p3 prt1_3
987 -> Hash
988 -> Seq Scan on prt2_p3 p2_3
989 -> Hash
990 -> Seq Scan on prt2_p3 p3_3
991 -> Hash
992 -> Seq Scan on prt1_p3 p4_3
993 (44 rows)
995 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)
996 WHERE a BETWEEN 490 AND 510;
997 count
998 -------
999 14
1000 (1 row)
1002 -- Cases with non-nullable expressions in subquery results;
1003 -- make sure these go to null as expected
1004 EXPLAIN (COSTS OFF)
1005 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;
1006 QUERY PLAN
1007 ------------------------------------------------------------------------------------------------------------
1008 Sort
1009 Sort Key: prt1.a, prt2.b, ((prt1_e.a + prt1_e.b))
1010 -> Append
1011 -> Hash Full Join
1012 Hash Cond: (prt1_1.a = ((prt1_e_1.a + prt1_e_1.b) / 2))
1013 Filter: ((prt1_1.a = (50)) OR (prt2_1.b = (75)) OR (((prt1_e_1.a + prt1_e_1.b) / 2) = (50)))
1014 -> Hash Full Join
1015 Hash Cond: (prt1_1.a = prt2_1.b)
1016 -> Seq Scan on prt1_p1 prt1_1
1017 Filter: (b = 0)
1018 -> Hash
1019 -> Seq Scan on prt2_p1 prt2_1
1020 Filter: (a = 0)
1021 -> Hash
1022 -> Seq Scan on prt1_e_p1 prt1_e_1
1023 Filter: (c = 0)
1024 -> Hash Full Join
1025 Hash Cond: (prt1_2.a = ((prt1_e_2.a + prt1_e_2.b) / 2))
1026 Filter: ((prt1_2.a = (50)) OR (prt2_2.b = (75)) OR (((prt1_e_2.a + prt1_e_2.b) / 2) = (50)))
1027 -> Hash Full Join
1028 Hash Cond: (prt1_2.a = prt2_2.b)
1029 -> Seq Scan on prt1_p2 prt1_2
1030 Filter: (b = 0)
1031 -> Hash
1032 -> Seq Scan on prt2_p2 prt2_2
1033 Filter: (a = 0)
1034 -> Hash
1035 -> Seq Scan on prt1_e_p2 prt1_e_2
1036 Filter: (c = 0)
1037 -> Hash Full Join
1038 Hash Cond: (prt1_3.a = ((prt1_e_3.a + prt1_e_3.b) / 2))
1039 Filter: ((prt1_3.a = (50)) OR (prt2_3.b = (75)) OR (((prt1_e_3.a + prt1_e_3.b) / 2) = (50)))
1040 -> Hash Full Join
1041 Hash Cond: (prt1_3.a = prt2_3.b)
1042 -> Seq Scan on prt1_p3 prt1_3
1043 Filter: (b = 0)
1044 -> Hash
1045 -> Seq Scan on prt2_p3 prt2_3
1046 Filter: (a = 0)
1047 -> Hash
1048 -> Seq Scan on prt1_e_p3 prt1_e_3
1049 Filter: (c = 0)
1050 (42 rows)
1052 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;
1053 a | phv | b | phv | ?column? | phv
1054 ----+-----+----+-----+----------+-----
1055 50 | 50 | | | 100 | 50
1056 | | 75 | 75 | |
1057 (2 rows)
1059 -- Semi-join
1060 EXPLAIN (COSTS OFF)
1061 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;
1062 QUERY PLAN
1063 ---------------------------------------------------------------------------------
1064 Sort
1065 Sort Key: t1.a
1066 -> Append
1067 -> Nested Loop
1068 Join Filter: (t1_2.a = t1_5.b)
1069 -> HashAggregate
1070 Group Key: t1_5.b
1071 -> Hash Join
1072 Hash Cond: (((t2_1.a + t2_1.b) / 2) = t1_5.b)
1073 -> Seq Scan on prt1_e_p1 t2_1
1074 -> Hash
1075 -> Seq Scan on prt2_p1 t1_5
1076 Filter: (a = 0)
1077 -> Index Scan using iprt1_p1_a on prt1_p1 t1_2
1078 Index Cond: (a = ((t2_1.a + t2_1.b) / 2))
1079 Filter: (b = 0)
1080 -> Nested Loop
1081 Join Filter: (t1_3.a = t1_6.b)
1082 -> HashAggregate
1083 Group Key: t1_6.b
1084 -> Hash Join
1085 Hash Cond: (((t2_2.a + t2_2.b) / 2) = t1_6.b)
1086 -> Seq Scan on prt1_e_p2 t2_2
1087 -> Hash
1088 -> Seq Scan on prt2_p2 t1_6
1089 Filter: (a = 0)
1090 -> Index Scan using iprt1_p2_a on prt1_p2 t1_3
1091 Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
1092 Filter: (b = 0)
1093 -> Nested Loop
1094 Join Filter: (t1_4.a = t1_7.b)
1095 -> HashAggregate
1096 Group Key: t1_7.b
1097 -> Nested Loop
1098 -> Seq Scan on prt2_p3 t1_7
1099 Filter: (a = 0)
1100 -> Index Scan using iprt1_e_p3_ab2 on prt1_e_p3 t2_3
1101 Index Cond: (((a + b) / 2) = t1_7.b)
1102 -> Index Scan using iprt1_p3_a on prt1_p3 t1_4
1103 Index Cond: (a = ((t2_3.a + t2_3.b) / 2))
1104 Filter: (b = 0)
1105 (41 rows)
1107 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;
1108 a | b | c
1109 -----+---+------
1110 0 | 0 | 0000
1111 150 | 0 | 0150
1112 300 | 0 | 0300
1113 450 | 0 | 0450
1114 (4 rows)
1116 EXPLAIN (COSTS OFF)
1117 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;
1118 QUERY PLAN
1119 ---------------------------------------------------------------------------
1120 Sort
1121 Sort Key: t1.a
1122 -> Append
1123 -> Nested Loop
1124 -> HashAggregate
1125 Group Key: t1_6.b
1126 -> Hash Semi Join
1127 Hash Cond: (t1_6.b = ((t1_9.a + t1_9.b) / 2))
1128 -> Seq Scan on prt2_p1 t1_6
1129 -> Hash
1130 -> Seq Scan on prt1_e_p1 t1_9
1131 Filter: (c = 0)
1132 -> Index Scan using iprt1_p1_a on prt1_p1 t1_3
1133 Index Cond: (a = t1_6.b)
1134 Filter: (b = 0)
1135 -> Nested Loop
1136 -> HashAggregate
1137 Group Key: t1_7.b
1138 -> Hash Semi Join
1139 Hash Cond: (t1_7.b = ((t1_10.a + t1_10.b) / 2))
1140 -> Seq Scan on prt2_p2 t1_7
1141 -> Hash
1142 -> Seq Scan on prt1_e_p2 t1_10
1143 Filter: (c = 0)
1144 -> Index Scan using iprt1_p2_a on prt1_p2 t1_4
1145 Index Cond: (a = t1_7.b)
1146 Filter: (b = 0)
1147 -> Nested Loop
1148 -> HashAggregate
1149 Group Key: t1_8.b
1150 -> Hash Semi Join
1151 Hash Cond: (t1_8.b = ((t1_11.a + t1_11.b) / 2))
1152 -> Seq Scan on prt2_p3 t1_8
1153 -> Hash
1154 -> Seq Scan on prt1_e_p3 t1_11
1155 Filter: (c = 0)
1156 -> Index Scan using iprt1_p3_a on prt1_p3 t1_5
1157 Index Cond: (a = t1_8.b)
1158 Filter: (b = 0)
1159 (39 rows)
1161 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;
1162 a | b | c
1163 -----+---+------
1164 0 | 0 | 0000
1165 150 | 0 | 0150
1166 300 | 0 | 0300
1167 450 | 0 | 0450
1168 (4 rows)
1170 -- test merge joins
1171 SET enable_hashjoin TO off;
1172 SET enable_nestloop TO off;
1173 EXPLAIN (COSTS OFF)
1174 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;
1175 QUERY PLAN
1176 ------------------------------------------------------------------
1177 Merge Append
1178 Sort Key: t1.a
1179 -> Merge Semi Join
1180 Merge Cond: (t1_3.a = t1_6.b)
1181 -> Sort
1182 Sort Key: t1_3.a
1183 -> Seq Scan on prt1_p1 t1_3
1184 Filter: (b = 0)
1185 -> Merge Semi Join
1186 Merge Cond: (t1_6.b = (((t1_9.a + t1_9.b) / 2)))
1187 -> Sort
1188 Sort Key: t1_6.b
1189 -> Seq Scan on prt2_p1 t1_6
1190 -> Sort
1191 Sort Key: (((t1_9.a + t1_9.b) / 2))
1192 -> Seq Scan on prt1_e_p1 t1_9
1193 Filter: (c = 0)
1194 -> Merge Semi Join
1195 Merge Cond: (t1_4.a = t1_7.b)
1196 -> Sort
1197 Sort Key: t1_4.a
1198 -> Seq Scan on prt1_p2 t1_4
1199 Filter: (b = 0)
1200 -> Merge Semi Join
1201 Merge Cond: (t1_7.b = (((t1_10.a + t1_10.b) / 2)))
1202 -> Sort
1203 Sort Key: t1_7.b
1204 -> Seq Scan on prt2_p2 t1_7
1205 -> Sort
1206 Sort Key: (((t1_10.a + t1_10.b) / 2))
1207 -> Seq Scan on prt1_e_p2 t1_10
1208 Filter: (c = 0)
1209 -> Merge Semi Join
1210 Merge Cond: (t1_5.a = t1_8.b)
1211 -> Sort
1212 Sort Key: t1_5.a
1213 -> Seq Scan on prt1_p3 t1_5
1214 Filter: (b = 0)
1215 -> Merge Semi Join
1216 Merge Cond: (t1_8.b = (((t1_11.a + t1_11.b) / 2)))
1217 -> Sort
1218 Sort Key: t1_8.b
1219 -> Seq Scan on prt2_p3 t1_8
1220 -> Sort
1221 Sort Key: (((t1_11.a + t1_11.b) / 2))
1222 -> Seq Scan on prt1_e_p3 t1_11
1223 Filter: (c = 0)
1224 (47 rows)
1226 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;
1227 a | b | c
1228 -----+---+------
1229 0 | 0 | 0000
1230 150 | 0 | 0150
1231 300 | 0 | 0300
1232 450 | 0 | 0450
1233 (4 rows)
1235 EXPLAIN (COSTS OFF)
1236 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;
1237 QUERY PLAN
1238 ----------------------------------------------------------------------------
1239 Sort
1240 Sort Key: t1.a, t2.b, ((t3.a + t3.b))
1241 -> Append
1242 -> Merge Left Join
1243 Merge Cond: (t1_1.a = t2_1.b)
1244 -> Sort
1245 Sort Key: t1_1.a
1246 -> Merge Left Join
1247 Merge Cond: ((((t3_1.a + t3_1.b) / 2)) = t1_1.a)
1248 -> Sort
1249 Sort Key: (((t3_1.a + t3_1.b) / 2))
1250 -> Seq Scan on prt1_e_p1 t3_1
1251 Filter: (c = 0)
1252 -> Sort
1253 Sort Key: t1_1.a
1254 -> Seq Scan on prt1_p1 t1_1
1255 -> Sort
1256 Sort Key: t2_1.b
1257 -> Seq Scan on prt2_p1 t2_1
1258 -> Merge Left Join
1259 Merge Cond: (t1_2.a = t2_2.b)
1260 -> Sort
1261 Sort Key: t1_2.a
1262 -> Merge Left Join
1263 Merge Cond: ((((t3_2.a + t3_2.b) / 2)) = t1_2.a)
1264 -> Sort
1265 Sort Key: (((t3_2.a + t3_2.b) / 2))
1266 -> Seq Scan on prt1_e_p2 t3_2
1267 Filter: (c = 0)
1268 -> Sort
1269 Sort Key: t1_2.a
1270 -> Seq Scan on prt1_p2 t1_2
1271 -> Sort
1272 Sort Key: t2_2.b
1273 -> Seq Scan on prt2_p2 t2_2
1274 -> Merge Left Join
1275 Merge Cond: (t1_3.a = t2_3.b)
1276 -> Sort
1277 Sort Key: t1_3.a
1278 -> Merge Left Join
1279 Merge Cond: ((((t3_3.a + t3_3.b) / 2)) = t1_3.a)
1280 -> Sort
1281 Sort Key: (((t3_3.a + t3_3.b) / 2))
1282 -> Seq Scan on prt1_e_p3 t3_3
1283 Filter: (c = 0)
1284 -> Sort
1285 Sort Key: t1_3.a
1286 -> Seq Scan on prt1_p3 t1_3
1287 -> Sort
1288 Sort Key: t2_3.b
1289 -> Seq Scan on prt2_p3 t2_3
1290 (51 rows)
1292 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;
1293 a | c | b | c | ?column? | c
1294 -----+------+-----+------+----------+---
1295 0 | 0000 | 0 | 0000 | 0 | 0
1296 50 | 0050 | | | 100 | 0
1297 100 | 0100 | | | 200 | 0
1298 150 | 0150 | 150 | 0150 | 300 | 0
1299 200 | 0200 | | | 400 | 0
1300 250 | 0250 | | | 500 | 0
1301 300 | 0300 | 300 | 0300 | 600 | 0
1302 350 | 0350 | | | 700 | 0
1303 400 | 0400 | | | 800 | 0
1304 450 | 0450 | 450 | 0450 | 900 | 0
1305 500 | 0500 | | | 1000 | 0
1306 550 | 0550 | | | 1100 | 0
1307 (12 rows)
1309 -- MergeAppend on nullable column
1310 -- This should generate a partitionwise join, but currently fails to
1311 EXPLAIN (COSTS OFF)
1312 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;
1313 QUERY PLAN
1314 -----------------------------------------------------------
1315 Incremental Sort
1316 Sort Key: prt1.a, prt2.b
1317 Presorted Key: prt1.a
1318 -> Merge Left Join
1319 Merge Cond: (prt1.a = prt2.b)
1320 -> Sort
1321 Sort Key: prt1.a
1322 -> Append
1323 -> Seq Scan on prt1_p1 prt1_1
1324 Filter: ((a < 450) AND (b = 0))
1325 -> Seq Scan on prt1_p2 prt1_2
1326 Filter: ((a < 450) AND (b = 0))
1327 -> Sort
1328 Sort Key: prt2.b
1329 -> Append
1330 -> Seq Scan on prt2_p2 prt2_1
1331 Filter: (b > 250)
1332 -> Seq Scan on prt2_p3 prt2_2
1333 Filter: (b > 250)
1334 (19 rows)
1336 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;
1337 a | b
1338 -----+-----
1339 0 |
1340 50 |
1341 100 |
1342 150 |
1343 200 |
1344 250 |
1345 300 | 300
1346 350 |
1347 400 |
1348 (9 rows)
1350 -- merge join when expression with whole-row reference needs to be sorted;
1351 -- partitionwise join does not apply
1352 EXPLAIN (COSTS OFF)
1353 SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
1354 QUERY PLAN
1355 -----------------------------------------------------------------------------------------
1356 Merge Join
1357 Merge Cond: ((t1.a = t2.b) AND (((((t1.*)::prt1))::text) = ((((t2.*)::prt2))::text)))
1358 -> Sort
1359 Sort Key: t1.a, ((((t1.*)::prt1))::text)
1360 -> Result
1361 -> Append
1362 -> Seq Scan on prt1_p1 t1_1
1363 -> Seq Scan on prt1_p2 t1_2
1364 -> Seq Scan on prt1_p3 t1_3
1365 -> Sort
1366 Sort Key: t2.b, ((((t2.*)::prt2))::text)
1367 -> Result
1368 -> Append
1369 -> Seq Scan on prt2_p1 t2_1
1370 -> Seq Scan on prt2_p2 t2_2
1371 -> Seq Scan on prt2_p3 t2_3
1372 (16 rows)
1374 SELECT t1.a, t2.b FROM prt1 t1, prt2 t2 WHERE t1::text = t2::text AND t1.a = t2.b ORDER BY t1.a;
1375 a | b
1376 ----+----
1377 0 | 0
1378 6 | 6
1379 12 | 12
1380 18 | 18
1381 24 | 24
1382 (5 rows)
1384 RESET enable_hashjoin;
1385 RESET enable_nestloop;
1386 --
1387 -- partitioned by multiple columns
1388 --
1389 CREATE TABLE prt1_m (a int, b int, c int) PARTITION BY RANGE(a, ((a + b)/2));
1390 CREATE TABLE prt1_m_p1 PARTITION OF prt1_m FOR VALUES FROM (0, 0) TO (250, 250);
1391 CREATE TABLE prt1_m_p2 PARTITION OF prt1_m FOR VALUES FROM (250, 250) TO (500, 500);
1392 CREATE TABLE prt1_m_p3 PARTITION OF prt1_m FOR VALUES FROM (500, 500) TO (600, 600);
1393 INSERT INTO prt1_m SELECT i, i, i % 25 FROM generate_series(0, 599, 2) i;
1394 ANALYZE prt1_m;
1395 CREATE TABLE prt2_m (a int, b int, c int) PARTITION BY RANGE(((b + a)/2), b);
1396 CREATE TABLE prt2_m_p1 PARTITION OF prt2_m FOR VALUES FROM (0, 0) TO (250, 250);
1397 CREATE TABLE prt2_m_p2 PARTITION OF prt2_m FOR VALUES FROM (250, 250) TO (500, 500);
1398 CREATE TABLE prt2_m_p3 PARTITION OF prt2_m FOR VALUES FROM (500, 500) TO (600, 600);
1399 INSERT INTO prt2_m SELECT i, i, i % 25 FROM generate_series(0, 599, 3) i;
1400 ANALYZE prt2_m;
1401 EXPLAIN (COSTS OFF)
1402 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;
1403 QUERY PLAN
1404 ------------------------------------------------------------------------------------------------------------------------------
1405 Sort
1406 Sort Key: prt1_m.a, prt2_m.b
1407 -> Append
1408 -> Hash Full Join
1409 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))
1410 -> Seq Scan on prt1_m_p1 prt1_m_1
1411 Filter: (c = 0)
1412 -> Hash
1413 -> Seq Scan on prt2_m_p1 prt2_m_1
1414 Filter: (c = 0)
1415 -> Hash Full Join
1416 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))
1417 -> Seq Scan on prt1_m_p2 prt1_m_2
1418 Filter: (c = 0)
1419 -> Hash
1420 -> Seq Scan on prt2_m_p2 prt2_m_2
1421 Filter: (c = 0)
1422 -> Hash Full Join
1423 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))
1424 -> Seq Scan on prt1_m_p3 prt1_m_3
1425 Filter: (c = 0)
1426 -> Hash
1427 -> Seq Scan on prt2_m_p3 prt2_m_3
1428 Filter: (c = 0)
1429 (24 rows)
1431 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;
1432 a | c | b | c
1433 -----+---+-----+---
1434 0 | 0 | 0 | 0
1435 50 | 0 | |
1436 100 | 0 | |
1437 150 | 0 | 150 | 0
1438 200 | 0 | |
1439 250 | 0 | |
1440 300 | 0 | 300 | 0
1441 350 | 0 | |
1442 400 | 0 | |
1443 450 | 0 | 450 | 0
1444 500 | 0 | |
1445 550 | 0 | |
1446 | | 75 | 0
1447 | | 225 | 0
1448 | | 375 | 0
1449 | | 525 | 0
1450 (16 rows)
1452 --
1453 -- tests for list partitioned tables.
1454 --
1455 CREATE TABLE plt1 (a int, b int, c text) PARTITION BY LIST(c);
1456 CREATE TABLE plt1_p1 PARTITION OF plt1 FOR VALUES IN ('0000', '0003', '0004', '0010');
1457 CREATE TABLE plt1_p2 PARTITION OF plt1 FOR VALUES IN ('0001', '0005', '0002', '0009');
1458 CREATE TABLE plt1_p3 PARTITION OF plt1 FOR VALUES IN ('0006', '0007', '0008', '0011');
1459 INSERT INTO plt1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
1460 ANALYZE plt1;
1461 CREATE TABLE plt2 (a int, b int, c text) PARTITION BY LIST(c);
1462 CREATE TABLE plt2_p1 PARTITION OF plt2 FOR VALUES IN ('0000', '0003', '0004', '0010');
1463 CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0001', '0005', '0002', '0009');
1464 CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0011');
1465 INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
1466 ANALYZE plt2;
1467 --
1468 -- list partitioned by expression
1469 --
1470 CREATE TABLE plt1_e (a int, b int, c text) PARTITION BY LIST(ltrim(c, 'A'));
1471 CREATE TABLE plt1_e_p1 PARTITION OF plt1_e FOR VALUES IN ('0000', '0003', '0004', '0010');
1472 CREATE TABLE plt1_e_p2 PARTITION OF plt1_e FOR VALUES IN ('0001', '0005', '0002', '0009');
1473 CREATE TABLE plt1_e_p3 PARTITION OF plt1_e FOR VALUES IN ('0006', '0007', '0008', '0011');
1474 INSERT INTO plt1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
1475 ANALYZE plt1_e;
1476 -- test partition matching with N-way join
1477 EXPLAIN (COSTS OFF)
1478 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;
1479 QUERY PLAN
1480 --------------------------------------------------------------------------------
1481 GroupAggregate
1482 Group Key: t1.c, t3.c
1483 -> Sort
1484 Sort Key: t1.c, t3.c
1485 -> Append
1486 -> Hash Join
1487 Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
1488 -> Hash Join
1489 Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1490 -> Seq Scan on plt1_p1 t1_1
1491 -> Hash
1492 -> Seq Scan on plt2_p1 t2_1
1493 -> Hash
1494 -> Seq Scan on plt1_e_p1 t3_1
1495 -> Hash Join
1496 Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
1497 -> Hash Join
1498 Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1499 -> Seq Scan on plt1_p2 t1_2
1500 -> Hash
1501 -> Seq Scan on plt2_p2 t2_2
1502 -> Hash
1503 -> Seq Scan on plt1_e_p2 t3_2
1504 -> Hash Join
1505 Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
1506 -> Hash Join
1507 Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
1508 -> Seq Scan on plt1_p3 t1_3
1509 -> Hash
1510 -> Seq Scan on plt2_p3 t2_3
1511 -> Hash
1512 -> Seq Scan on plt1_e_p3 t3_3
1513 (32 rows)
1515 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;
1516 avg | avg | avg | c | c | c
1517 ----------------------+----------------------+-----------------------+------+------+-------
1518 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
1519 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1520 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
1521 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
1522 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1523 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
1524 324.0000000000000000 | 324.0000000000000000 | 648.0000000000000000 | 0006 | 0006 | A0006
1525 375.0000000000000000 | 375.0000000000000000 | 748.0000000000000000 | 0007 | 0007 | A0007
1526 423.0000000000000000 | 423.0000000000000000 | 848.0000000000000000 | 0008 | 0008 | A0008
1527 474.0000000000000000 | 474.0000000000000000 | 948.0000000000000000 | 0009 | 0009 | A0009
1528 525.0000000000000000 | 525.0000000000000000 | 1048.0000000000000000 | 0010 | 0010 | A0010
1529 573.0000000000000000 | 573.0000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
1530 (12 rows)
1532 -- joins where one of the relations is proven empty
1533 EXPLAIN (COSTS OFF)
1534 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;
1535 QUERY PLAN
1536 --------------------------
1537 Result
1538 One-Time Filter: false
1539 (2 rows)
1541 EXPLAIN (COSTS OFF)
1542 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;
1543 QUERY PLAN
1544 --------------------------
1545 Result
1546 One-Time Filter: false
1547 (2 rows)
1549 EXPLAIN (COSTS OFF)
1550 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;
1551 QUERY PLAN
1552 --------------------------------------------------
1553 Hash Left Join
1554 Hash Cond: (t2.b = a)
1555 -> Append
1556 -> Hash Join
1557 Hash Cond: (t3_1.a = t2_1.b)
1558 -> Seq Scan on prt1_p1 t3_1
1559 -> Hash
1560 -> Seq Scan on prt2_p1 t2_1
1561 -> Hash Join
1562 Hash Cond: (t3_2.a = t2_2.b)
1563 -> Seq Scan on prt1_p2 t3_2
1564 -> Hash
1565 -> Seq Scan on prt2_p2 t2_2
1566 -> Hash Join
1567 Hash Cond: (t3_3.a = t2_3.b)
1568 -> Seq Scan on prt1_p3 t3_3
1569 -> Hash
1570 -> Seq Scan on prt2_p3 t2_3
1571 -> Hash
1572 -> Result
1573 One-Time Filter: false
1574 (21 rows)
1576 EXPLAIN (COSTS OFF)
1577 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;
1578 QUERY PLAN
1579 --------------------------------------------
1580 Sort
1581 Sort Key: a, t2.b
1582 -> Hash Left Join
1583 Hash Cond: (t2.b = a)
1584 -> Append
1585 -> Seq Scan on prt2_p1 t2_1
1586 Filter: (a = 0)
1587 -> Seq Scan on prt2_p2 t2_2
1588 Filter: (a = 0)
1589 -> Seq Scan on prt2_p3 t2_3
1590 Filter: (a = 0)
1591 -> Hash
1592 -> Result
1593 One-Time Filter: false
1594 (14 rows)
1596 --
1597 -- tests for hash partitioned tables.
1598 --
1599 CREATE TABLE pht1 (a int, b int, c text) PARTITION BY HASH(c);
1600 CREATE TABLE pht1_p1 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
1601 CREATE TABLE pht1_p2 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
1602 CREATE TABLE pht1_p3 PARTITION OF pht1 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
1603 INSERT INTO pht1 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
1604 ANALYZE pht1;
1605 CREATE TABLE pht2 (a int, b int, c text) PARTITION BY HASH(c);
1606 CREATE TABLE pht2_p1 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
1607 CREATE TABLE pht2_p2 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
1608 CREATE TABLE pht2_p3 PARTITION OF pht2 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
1609 INSERT INTO pht2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
1610 ANALYZE pht2;
1611 --
1612 -- hash partitioned by expression
1613 --
1614 CREATE TABLE pht1_e (a int, b int, c text) PARTITION BY HASH(ltrim(c, 'A'));
1615 CREATE TABLE pht1_e_p1 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 0);
1616 CREATE TABLE pht1_e_p2 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 1);
1617 CREATE TABLE pht1_e_p3 PARTITION OF pht1_e FOR VALUES WITH (MODULUS 3, REMAINDER 2);
1618 INSERT INTO pht1_e SELECT i, i, 'A' || to_char(i/50, 'FM0000') FROM generate_series(0, 299, 2) i;
1619 ANALYZE pht1_e;
1620 -- test partition matching with N-way join
1621 EXPLAIN (COSTS OFF)
1622 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;
1623 QUERY PLAN
1624 --------------------------------------------------------------------------------
1625 GroupAggregate
1626 Group Key: t1.c, t3.c
1627 -> Sort
1628 Sort Key: t1.c, t3.c
1629 -> Append
1630 -> Hash Join
1631 Hash Cond: (t1_1.c = ltrim(t3_1.c, 'A'::text))
1632 -> Hash Join
1633 Hash Cond: ((t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
1634 -> Seq Scan on pht1_p1 t1_1
1635 -> Hash
1636 -> Seq Scan on pht2_p1 t2_1
1637 -> Hash
1638 -> Seq Scan on pht1_e_p1 t3_1
1639 -> Hash Join
1640 Hash Cond: (t1_2.c = ltrim(t3_2.c, 'A'::text))
1641 -> Hash Join
1642 Hash Cond: ((t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
1643 -> Seq Scan on pht1_p2 t1_2
1644 -> Hash
1645 -> Seq Scan on pht2_p2 t2_2
1646 -> Hash
1647 -> Seq Scan on pht1_e_p2 t3_2
1648 -> Hash Join
1649 Hash Cond: (t1_3.c = ltrim(t3_3.c, 'A'::text))
1650 -> Hash Join
1651 Hash Cond: ((t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
1652 -> Seq Scan on pht1_p3 t1_3
1653 -> Hash
1654 -> Seq Scan on pht2_p3 t2_3
1655 -> Hash
1656 -> Seq Scan on pht1_e_p3 t3_3
1657 (32 rows)
1659 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;
1660 avg | avg | avg | c | c | c
1661 ----------------------+----------------------+----------------------+------+------+-------
1662 24.0000000000000000 | 24.0000000000000000 | 48.0000000000000000 | 0000 | 0000 | A0000
1663 75.0000000000000000 | 75.0000000000000000 | 148.0000000000000000 | 0001 | 0001 | A0001
1664 123.0000000000000000 | 123.0000000000000000 | 248.0000000000000000 | 0002 | 0002 | A0002
1665 174.0000000000000000 | 174.0000000000000000 | 348.0000000000000000 | 0003 | 0003 | A0003
1666 225.0000000000000000 | 225.0000000000000000 | 448.0000000000000000 | 0004 | 0004 | A0004
1667 273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 | 0005 | A0005
1668 (6 rows)
1670 -- test default partition behavior for range
1671 ALTER TABLE prt1 DETACH PARTITION prt1_p3;
1672 ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
1673 ANALYZE prt1;
1674 ALTER TABLE prt2 DETACH PARTITION prt2_p3;
1675 ALTER TABLE prt2 ATTACH PARTITION prt2_p3 DEFAULT;
1676 ANALYZE prt2;
1677 EXPLAIN (COSTS OFF)
1678 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;
1679 QUERY PLAN
1680 --------------------------------------------------
1681 Sort
1682 Sort Key: t1.a
1683 -> Append
1684 -> Hash Join
1685 Hash Cond: (t2_1.b = t1_1.a)
1686 -> Seq Scan on prt2_p1 t2_1
1687 -> Hash
1688 -> Seq Scan on prt1_p1 t1_1
1689 Filter: (b = 0)
1690 -> Hash Join
1691 Hash Cond: (t2_2.b = t1_2.a)
1692 -> Seq Scan on prt2_p2 t2_2
1693 -> Hash
1694 -> Seq Scan on prt1_p2 t1_2
1695 Filter: (b = 0)
1696 -> Hash Join
1697 Hash Cond: (t2_3.b = t1_3.a)
1698 -> Seq Scan on prt2_p3 t2_3
1699 -> Hash
1700 -> Seq Scan on prt1_p3 t1_3
1701 Filter: (b = 0)
1702 (21 rows)
1704 -- test default partition behavior for list
1705 ALTER TABLE plt1 DETACH PARTITION plt1_p3;
1706 ALTER TABLE plt1 ATTACH PARTITION plt1_p3 DEFAULT;
1707 ANALYZE plt1;
1708 ALTER TABLE plt2 DETACH PARTITION plt2_p3;
1709 ALTER TABLE plt2 ATTACH PARTITION plt2_p3 DEFAULT;
1710 ANALYZE plt2;
1711 EXPLAIN (COSTS OFF)
1712 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;
1713 QUERY PLAN
1714 --------------------------------------------------------
1715 Sort
1716 Sort Key: t1.c
1717 -> HashAggregate
1718 Group Key: t1.c
1719 -> Append
1720 -> Hash Join
1721 Hash Cond: (t2_1.c = t1_1.c)
1722 -> Seq Scan on plt2_p1 t2_1
1723 -> Hash
1724 -> Seq Scan on plt1_p1 t1_1
1725 Filter: ((a % 25) = 0)
1726 -> Hash Join
1727 Hash Cond: (t2_2.c = t1_2.c)
1728 -> Seq Scan on plt2_p2 t2_2
1729 -> Hash
1730 -> Seq Scan on plt1_p2 t1_2
1731 Filter: ((a % 25) = 0)
1732 -> Hash Join
1733 Hash Cond: (t2_3.c = t1_3.c)
1734 -> Seq Scan on plt2_p3 t2_3
1735 -> Hash
1736 -> Seq Scan on plt1_p3 t1_3
1737 Filter: ((a % 25) = 0)
1738 (23 rows)
1740 --
1741 -- multiple levels of partitioning
1742 --
1743 CREATE TABLE prt1_l (a int, b int, c varchar) PARTITION BY RANGE(a);
1744 CREATE TABLE prt1_l_p1 PARTITION OF prt1_l FOR VALUES FROM (0) TO (250);
1745 CREATE TABLE prt1_l_p2 PARTITION OF prt1_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c);
1746 CREATE TABLE prt1_l_p2_p1 PARTITION OF prt1_l_p2 FOR VALUES IN ('0000', '0001');
1747 CREATE TABLE prt1_l_p2_p2 PARTITION OF prt1_l_p2 FOR VALUES IN ('0002', '0003');
1748 CREATE TABLE prt1_l_p3 PARTITION OF prt1_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (b);
1749 CREATE TABLE prt1_l_p3_p1 PARTITION OF prt1_l_p3 FOR VALUES FROM (0) TO (13);
1750 CREATE TABLE prt1_l_p3_p2 PARTITION OF prt1_l_p3 FOR VALUES FROM (13) TO (25);
1751 INSERT INTO prt1_l SELECT i, i % 25, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 2) i;
1752 ANALYZE prt1_l;
1753 CREATE TABLE prt2_l (a int, b int, c varchar) PARTITION BY RANGE(b);
1754 CREATE TABLE prt2_l_p1 PARTITION OF prt2_l FOR VALUES FROM (0) TO (250);
1755 CREATE TABLE prt2_l_p2 PARTITION OF prt2_l FOR VALUES FROM (250) TO (500) PARTITION BY LIST (c);
1756 CREATE TABLE prt2_l_p2_p1 PARTITION OF prt2_l_p2 FOR VALUES IN ('0000', '0001');
1757 CREATE TABLE prt2_l_p2_p2 PARTITION OF prt2_l_p2 FOR VALUES IN ('0002', '0003');
1758 CREATE TABLE prt2_l_p3 PARTITION OF prt2_l FOR VALUES FROM (500) TO (600) PARTITION BY RANGE (a);
1759 CREATE TABLE prt2_l_p3_p1 PARTITION OF prt2_l_p3 FOR VALUES FROM (0) TO (13);
1760 CREATE TABLE prt2_l_p3_p2 PARTITION OF prt2_l_p3 FOR VALUES FROM (13) TO (25);
1761 INSERT INTO prt2_l SELECT i % 25, i, to_char(i % 4, 'FM0000') FROM generate_series(0, 599, 3) i;
1762 ANALYZE prt2_l;
1763 -- inner join, qual covering only top-level partitions
1764 EXPLAIN (COSTS OFF)
1765 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;
1766 QUERY PLAN
1767 -------------------------------------------------------------
1768 Sort
1769 Sort Key: t1.a
1770 -> Append
1771 -> Hash Join
1772 Hash Cond: (t2_1.b = t1_1.a)
1773 -> Seq Scan on prt2_l_p1 t2_1
1774 -> Hash
1775 -> Seq Scan on prt1_l_p1 t1_1
1776 Filter: (b = 0)
1777 -> Hash Join
1778 Hash Cond: (t2_3.b = t1_3.a)
1779 -> Append
1780 -> Seq Scan on prt2_l_p2_p1 t2_3
1781 -> Seq Scan on prt2_l_p2_p2 t2_4
1782 -> Hash
1783 -> Append
1784 -> Seq Scan on prt1_l_p2_p1 t1_3
1785 Filter: (b = 0)
1786 -> Seq Scan on prt1_l_p2_p2 t1_4
1787 Filter: (b = 0)
1788 -> Hash Join
1789 Hash Cond: (t2_6.b = t1_5.a)
1790 -> Append
1791 -> Seq Scan on prt2_l_p3_p1 t2_6
1792 -> Seq Scan on prt2_l_p3_p2 t2_7
1793 -> Hash
1794 -> Seq Scan on prt1_l_p3_p1 t1_5
1795 Filter: (b = 0)
1796 (28 rows)
1798 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;
1799 a | c | b | c
1800 -----+------+-----+------
1801 0 | 0000 | 0 | 0000
1802 150 | 0002 | 150 | 0002
1803 300 | 0000 | 300 | 0000
1804 450 | 0002 | 450 | 0002
1805 (4 rows)
1807 -- left join
1808 EXPLAIN (COSTS OFF)
1809 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;
1810 QUERY PLAN
1811 ------------------------------------------------------------------------------------
1812 Sort
1813 Sort Key: t1.a, t2.b
1814 -> Append
1815 -> Hash Right Join
1816 Hash Cond: ((t2_1.b = t1_1.a) AND ((t2_1.c)::text = (t1_1.c)::text))
1817 -> Seq Scan on prt2_l_p1 t2_1
1818 -> Hash
1819 -> Seq Scan on prt1_l_p1 t1_1
1820 Filter: (b = 0)
1821 -> Hash Right Join
1822 Hash Cond: ((t2_2.b = t1_2.a) AND ((t2_2.c)::text = (t1_2.c)::text))
1823 -> Seq Scan on prt2_l_p2_p1 t2_2
1824 -> Hash
1825 -> Seq Scan on prt1_l_p2_p1 t1_2
1826 Filter: (b = 0)
1827 -> Hash Right Join
1828 Hash Cond: ((t2_3.b = t1_3.a) AND ((t2_3.c)::text = (t1_3.c)::text))
1829 -> Seq Scan on prt2_l_p2_p2 t2_3
1830 -> Hash
1831 -> Seq Scan on prt1_l_p2_p2 t1_3
1832 Filter: (b = 0)
1833 -> Hash Right Join
1834 Hash Cond: ((t2_5.b = t1_4.a) AND ((t2_5.c)::text = (t1_4.c)::text))
1835 -> Append
1836 -> Seq Scan on prt2_l_p3_p1 t2_5
1837 -> Seq Scan on prt2_l_p3_p2 t2_6
1838 -> Hash
1839 -> Seq Scan on prt1_l_p3_p1 t1_4
1840 Filter: (b = 0)
1841 (29 rows)
1843 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;
1844 a | c | b | c
1845 -----+------+-----+------
1846 0 | 0000 | 0 | 0000
1847 50 | 0002 | |
1848 100 | 0000 | |
1849 150 | 0002 | 150 | 0002
1850 200 | 0000 | |
1851 250 | 0002 | |
1852 300 | 0000 | 300 | 0000
1853 350 | 0002 | |
1854 400 | 0000 | |
1855 450 | 0002 | 450 | 0002
1856 500 | 0000 | |
1857 550 | 0002 | |
1858 (12 rows)
1860 -- right join
1861 EXPLAIN (COSTS OFF)
1862 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;
1863 QUERY PLAN
1864 ------------------------------------------------------------------------------------
1865 Sort
1866 Sort Key: t1.a, t2.b
1867 -> Append
1868 -> Hash Right Join
1869 Hash Cond: ((t1_1.a = t2_1.b) AND ((t1_1.c)::text = (t2_1.c)::text))
1870 -> Seq Scan on prt1_l_p1 t1_1
1871 -> Hash
1872 -> Seq Scan on prt2_l_p1 t2_1
1873 Filter: (a = 0)
1874 -> Hash Right Join
1875 Hash Cond: ((t1_2.a = t2_2.b) AND ((t1_2.c)::text = (t2_2.c)::text))
1876 -> Seq Scan on prt1_l_p2_p1 t1_2
1877 -> Hash
1878 -> Seq Scan on prt2_l_p2_p1 t2_2
1879 Filter: (a = 0)
1880 -> Hash Right Join
1881 Hash Cond: ((t1_3.a = t2_3.b) AND ((t1_3.c)::text = (t2_3.c)::text))
1882 -> Seq Scan on prt1_l_p2_p2 t1_3
1883 -> Hash
1884 -> Seq Scan on prt2_l_p2_p2 t2_3
1885 Filter: (a = 0)
1886 -> Hash Right Join
1887 Hash Cond: ((t1_5.a = t2_4.b) AND ((t1_5.c)::text = (t2_4.c)::text))
1888 -> Append
1889 -> Seq Scan on prt1_l_p3_p1 t1_5
1890 -> Seq Scan on prt1_l_p3_p2 t1_6
1891 -> Hash
1892 -> Seq Scan on prt2_l_p3_p1 t2_4
1893 Filter: (a = 0)
1894 (29 rows)
1896 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;
1897 a | c | b | c
1898 -----+------+-----+------
1899 0 | 0000 | 0 | 0000
1900 150 | 0002 | 150 | 0002
1901 300 | 0000 | 300 | 0000
1902 450 | 0002 | 450 | 0002
1903 | | 75 | 0003
1904 | | 225 | 0001
1905 | | 375 | 0003
1906 | | 525 | 0001
1907 (8 rows)
1909 -- full join
1910 EXPLAIN (COSTS OFF)
1911 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;
1912 QUERY PLAN
1913 ----------------------------------------------------------------------------------------------------
1914 Sort
1915 Sort Key: prt1_l.a, prt2_l.b
1916 -> Append
1917 -> Hash Full Join
1918 Hash Cond: ((prt1_l_1.a = prt2_l_1.b) AND ((prt1_l_1.c)::text = (prt2_l_1.c)::text))
1919 -> Seq Scan on prt1_l_p1 prt1_l_1
1920 Filter: (b = 0)
1921 -> Hash
1922 -> Seq Scan on prt2_l_p1 prt2_l_1
1923 Filter: (a = 0)
1924 -> Hash Full Join
1925 Hash Cond: ((prt1_l_2.a = prt2_l_2.b) AND ((prt1_l_2.c)::text = (prt2_l_2.c)::text))
1926 -> Seq Scan on prt1_l_p2_p1 prt1_l_2
1927 Filter: (b = 0)
1928 -> Hash
1929 -> Seq Scan on prt2_l_p2_p1 prt2_l_2
1930 Filter: (a = 0)
1931 -> Hash Full Join
1932 Hash Cond: ((prt1_l_3.a = prt2_l_3.b) AND ((prt1_l_3.c)::text = (prt2_l_3.c)::text))
1933 -> Seq Scan on prt1_l_p2_p2 prt1_l_3
1934 Filter: (b = 0)
1935 -> Hash
1936 -> Seq Scan on prt2_l_p2_p2 prt2_l_3
1937 Filter: (a = 0)
1938 -> Hash Full Join
1939 Hash Cond: ((prt1_l_4.a = prt2_l_4.b) AND ((prt1_l_4.c)::text = (prt2_l_4.c)::text))
1940 -> Seq Scan on prt1_l_p3_p1 prt1_l_4
1941 Filter: (b = 0)
1942 -> Hash
1943 -> Seq Scan on prt2_l_p3_p1 prt2_l_4
1944 Filter: (a = 0)
1945 (31 rows)
1947 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;
1948 a | c | b | c
1949 -----+------+-----+------
1950 0 | 0000 | 0 | 0000
1951 50 | 0002 | |
1952 100 | 0000 | |
1953 150 | 0002 | 150 | 0002
1954 200 | 0000 | |
1955 250 | 0002 | |
1956 300 | 0000 | 300 | 0000
1957 350 | 0002 | |
1958 400 | 0000 | |
1959 450 | 0002 | 450 | 0002
1960 500 | 0000 | |
1961 550 | 0002 | |
1962 | | 75 | 0003
1963 | | 225 | 0001
1964 | | 375 | 0003
1965 | | 525 | 0001
1966 (16 rows)
1968 -- lateral partitionwise join
1969 EXPLAIN (COSTS OFF)
1970 SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
1971 (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
1972 ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a;
1973 QUERY PLAN
1974 -----------------------------------------------------------------------------------------------
1975 Sort
1976 Sort Key: t1.a
1977 -> Append
1978 -> Nested Loop Left Join
1979 -> Seq Scan on prt1_l_p1 t1_1
1980 Filter: (b = 0)
1981 -> Hash Join
1982 Hash Cond: ((t3_1.b = t2_1.a) AND ((t3_1.c)::text = (t2_1.c)::text))
1983 -> Seq Scan on prt2_l_p1 t3_1
1984 -> Hash
1985 -> Seq Scan on prt1_l_p1 t2_1
1986 Filter: ((t1_1.a = a) AND ((t1_1.c)::text = (c)::text))
1987 -> Nested Loop Left Join
1988 -> Seq Scan on prt1_l_p2_p1 t1_2
1989 Filter: (b = 0)
1990 -> Hash Join
1991 Hash Cond: ((t3_2.b = t2_2.a) AND ((t3_2.c)::text = (t2_2.c)::text))
1992 -> Seq Scan on prt2_l_p2_p1 t3_2
1993 -> Hash
1994 -> Seq Scan on prt1_l_p2_p1 t2_2
1995 Filter: ((t1_2.a = a) AND ((t1_2.c)::text = (c)::text))
1996 -> Nested Loop Left Join
1997 -> Seq Scan on prt1_l_p2_p2 t1_3
1998 Filter: (b = 0)
1999 -> Hash Join
2000 Hash Cond: ((t3_3.b = t2_3.a) AND ((t3_3.c)::text = (t2_3.c)::text))
2001 -> Seq Scan on prt2_l_p2_p2 t3_3
2002 -> Hash
2003 -> Seq Scan on prt1_l_p2_p2 t2_3
2004 Filter: ((t1_3.a = a) AND ((t1_3.c)::text = (c)::text))
2005 -> Nested Loop Left Join
2006 -> Seq Scan on prt1_l_p3_p1 t1_4
2007 Filter: (b = 0)
2008 -> Hash Join
2009 Hash Cond: ((t3_5.b = t2_5.a) AND ((t3_5.c)::text = (t2_5.c)::text))
2010 -> Append
2011 -> Seq Scan on prt2_l_p3_p1 t3_5
2012 -> Seq Scan on prt2_l_p3_p2 t3_6
2013 -> Hash
2014 -> Append
2015 -> Seq Scan on prt1_l_p3_p1 t2_5
2016 Filter: ((t1_4.a = a) AND ((t1_4.c)::text = (c)::text))
2017 -> Seq Scan on prt1_l_p3_p2 t2_6
2018 Filter: ((t1_4.a = a) AND ((t1_4.c)::text = (c)::text))
2019 (44 rows)
2021 SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
2022 (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
2023 ON t1.a = ss.t2a AND t1.c = ss.t2c WHERE t1.b = 0 ORDER BY t1.a;
2024 a | b | c | t2a | t2c | t2b | t3b | least
2025 -----+---+------+-----+------+-----+-----+-------
2026 0 | 0 | 0000 | 0 | 0000 | 0 | 0 | 0
2027 50 | 0 | 0002 | | | | |
2028 100 | 0 | 0000 | | | | |
2029 150 | 0 | 0002 | 150 | 0002 | 0 | 150 | 150
2030 200 | 0 | 0000 | | | | |
2031 250 | 0 | 0002 | | | | |
2032 300 | 0 | 0000 | 300 | 0000 | 0 | 300 | 300
2033 350 | 0 | 0002 | | | | |
2034 400 | 0 | 0000 | | | | |
2035 450 | 0 | 0002 | 450 | 0002 | 0 | 450 | 450
2036 500 | 0 | 0000 | | | | |
2037 550 | 0 | 0002 | | | | |
2038 (12 rows)
2040 SET max_parallel_workers_per_gather = 0;
2041 -- If there are lateral references to the other relation in sample scan,
2042 -- we cannot generate a partitionwise join.
2043 EXPLAIN (COSTS OFF)
2044 SELECT * FROM prt1_l t1 JOIN LATERAL
2045 (SELECT * FROM prt1_l t2 TABLESAMPLE SYSTEM (t1.a) REPEATABLE(t1.b)) s
2046 ON t1.a = s.a AND t1.b = s.b AND t1.c = s.c;
2047 QUERY PLAN
2048 ----------------------------------------------------------------------------------
2049 Nested Loop
2050 -> Append
2051 -> Seq Scan on prt1_l_p1 t1_1
2052 -> Seq Scan on prt1_l_p2_p1 t1_2
2053 -> Seq Scan on prt1_l_p2_p2 t1_3
2054 -> Seq Scan on prt1_l_p3_p1 t1_4
2055 -> Seq Scan on prt1_l_p3_p2 t1_5
2056 -> Append
2057 -> Sample Scan on prt1_l_p1 t2_1
2058 Sampling: system (t1.a) REPEATABLE (t1.b)
2059 Filter: ((t1.a = a) AND (t1.b = b) AND ((t1.c)::text = (c)::text))
2060 -> Sample Scan on prt1_l_p2_p1 t2_2
2061 Sampling: system (t1.a) REPEATABLE (t1.b)
2062 Filter: ((t1.a = a) AND (t1.b = b) AND ((t1.c)::text = (c)::text))
2063 -> Sample Scan on prt1_l_p2_p2 t2_3
2064 Sampling: system (t1.a) REPEATABLE (t1.b)
2065 Filter: ((t1.a = a) AND (t1.b = b) AND ((t1.c)::text = (c)::text))
2066 -> Sample Scan on prt1_l_p3_p1 t2_4
2067 Sampling: system (t1.a) REPEATABLE (t1.b)
2068 Filter: ((t1.a = a) AND (t1.b = b) AND ((t1.c)::text = (c)::text))
2069 -> Sample Scan on prt1_l_p3_p2 t2_5
2070 Sampling: system (t1.a) REPEATABLE (t1.b)
2071 Filter: ((t1.a = a) AND (t1.b = b) AND ((t1.c)::text = (c)::text))
2072 (23 rows)
2074 -- If there are lateral references to the other relation in scan's restriction
2075 -- clauses, we cannot generate a partitionwise join.
2076 EXPLAIN (COSTS OFF)
2077 SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL
2078 (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s
2079 ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c
2080 WHERE s.t1b = s.a;
2081 QUERY PLAN
2082 -------------------------------------------------------------------------------------------------------
2083 Aggregate
2084 -> Nested Loop
2085 -> Append
2086 -> Seq Scan on prt1_l_p1 t1_1
2087 -> Seq Scan on prt1_l_p2_p1 t1_2
2088 -> Seq Scan on prt1_l_p2_p2 t1_3
2089 -> Seq Scan on prt1_l_p3_p1 t1_4
2090 -> Seq Scan on prt1_l_p3_p2 t1_5
2091 -> Append
2092 -> Seq Scan on prt2_l_p1 t2_1
2093 Filter: ((a = t1.b) AND (t1.a = b) AND (t1.b = a) AND ((t1.c)::text = (c)::text))
2094 -> Seq Scan on prt2_l_p2_p1 t2_2
2095 Filter: ((a = t1.b) AND (t1.a = b) AND (t1.b = a) AND ((t1.c)::text = (c)::text))
2096 -> Seq Scan on prt2_l_p2_p2 t2_3
2097 Filter: ((a = t1.b) AND (t1.a = b) AND (t1.b = a) AND ((t1.c)::text = (c)::text))
2098 -> Seq Scan on prt2_l_p3_p1 t2_4
2099 Filter: ((a = t1.b) AND (t1.a = b) AND (t1.b = a) AND ((t1.c)::text = (c)::text))
2100 -> Seq Scan on prt2_l_p3_p2 t2_5
2101 Filter: ((a = t1.b) AND (t1.a = b) AND (t1.b = a) AND ((t1.c)::text = (c)::text))
2102 (19 rows)
2104 SELECT COUNT(*) FROM prt1_l t1 LEFT JOIN LATERAL
2105 (SELECT t1.b AS t1b, t2.* FROM prt2_l t2) s
2106 ON t1.a = s.b AND t1.b = s.a AND t1.c = s.c
2107 WHERE s.t1b = s.a;
2108 count
2109 -------
2110 100
2111 (1 row)
2113 RESET max_parallel_workers_per_gather;
2114 -- join with one side empty
2115 EXPLAIN (COSTS OFF)
2116 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;
2117 QUERY PLAN
2118 -------------------------------------------------------------------------
2119 Hash Left Join
2120 Hash Cond: ((t2.b = a) AND (t2.a = b) AND ((t2.c)::text = (c)::text))
2121 -> Append
2122 -> Seq Scan on prt2_l_p1 t2_1
2123 -> Seq Scan on prt2_l_p2_p1 t2_2
2124 -> Seq Scan on prt2_l_p2_p2 t2_3
2125 -> Seq Scan on prt2_l_p3_p1 t2_4
2126 -> Seq Scan on prt2_l_p3_p2 t2_5
2127 -> Hash
2128 -> Result
2129 One-Time Filter: false
2130 (11 rows)
2132 -- Test case to verify proper handling of subqueries in a partitioned delete.
2133 -- The weird-looking lateral join is just there to force creation of a
2134 -- nestloop parameter within the subquery, which exposes the problem if the
2135 -- planner fails to make multiple copies of the subquery as appropriate.
2136 EXPLAIN (COSTS OFF)
2137 DELETE FROM prt1_l
2138 WHERE EXISTS (
2139 SELECT 1
2140 FROM int4_tbl,
2141 LATERAL (SELECT int4_tbl.f1 FROM int8_tbl LIMIT 2) ss
2142 WHERE prt1_l.c IS NULL);
2143 QUERY PLAN
2144 ----------------------------------------------------------
2145 Delete on prt1_l
2146 Delete on prt1_l_p1 prt1_l_1
2147 Delete on prt1_l_p3_p1 prt1_l_2
2148 Delete on prt1_l_p3_p2 prt1_l_3
2149 -> Nested Loop Semi Join
2150 -> Append
2151 -> Seq Scan on prt1_l_p1 prt1_l_1
2152 Filter: (c IS NULL)
2153 -> Seq Scan on prt1_l_p3_p1 prt1_l_2
2154 Filter: (c IS NULL)
2155 -> Seq Scan on prt1_l_p3_p2 prt1_l_3
2156 Filter: (c IS NULL)
2157 -> Materialize
2158 -> Nested Loop
2159 -> Seq Scan on int4_tbl
2160 -> Subquery Scan on ss
2161 -> Limit
2162 -> Seq Scan on int8_tbl
2163 (18 rows)
2165 --
2166 -- negative testcases
2167 --
2168 CREATE TABLE prt1_n (a int, b int, c varchar) PARTITION BY RANGE(c);
2169 CREATE TABLE prt1_n_p1 PARTITION OF prt1_n FOR VALUES FROM ('0000') TO ('0250');
2170 CREATE TABLE prt1_n_p2 PARTITION OF prt1_n FOR VALUES FROM ('0250') TO ('0500');
2171 INSERT INTO prt1_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 499, 2) i;
2172 ANALYZE prt1_n;
2173 CREATE TABLE prt2_n (a int, b int, c text) PARTITION BY LIST(c);
2174 CREATE TABLE prt2_n_p1 PARTITION OF prt2_n FOR VALUES IN ('0000', '0003', '0004', '0010', '0006', '0007');
2175 CREATE TABLE prt2_n_p2 PARTITION OF prt2_n FOR VALUES IN ('0001', '0005', '0002', '0009', '0008', '0011');
2176 INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
2177 ANALYZE prt2_n;
2178 CREATE TABLE prt3_n (a int, b int, c text) PARTITION BY LIST(c);
2179 CREATE TABLE prt3_n_p1 PARTITION OF prt3_n FOR VALUES IN ('0000', '0004', '0006', '0007');
2180 CREATE TABLE prt3_n_p2 PARTITION OF prt3_n FOR VALUES IN ('0001', '0002', '0008', '0010');
2181 CREATE TABLE prt3_n_p3 PARTITION OF prt3_n FOR VALUES IN ('0003', '0005', '0009', '0011');
2182 INSERT INTO prt2_n SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 2) i;
2183 ANALYZE prt3_n;
2184 CREATE TABLE prt4_n (a int, b int, c text) PARTITION BY RANGE(a);
2185 CREATE TABLE prt4_n_p1 PARTITION OF prt4_n FOR VALUES FROM (0) TO (300);
2186 CREATE TABLE prt4_n_p2 PARTITION OF prt4_n FOR VALUES FROM (300) TO (500);
2187 CREATE TABLE prt4_n_p3 PARTITION OF prt4_n FOR VALUES FROM (500) TO (600);
2188 INSERT INTO prt4_n SELECT i, i, to_char(i, 'FM0000') FROM generate_series(0, 599, 2) i;
2189 ANALYZE prt4_n;
2190 -- partitionwise join can not be applied if the partition ranges differ
2191 EXPLAIN (COSTS OFF)
2192 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt4_n t2 WHERE t1.a = t2.a;
2193 QUERY PLAN
2194 ----------------------------------------------
2195 Hash Join
2196 Hash Cond: (t1.a = t2.a)
2197 -> Append
2198 -> Seq Scan on prt1_p1 t1_1
2199 -> Seq Scan on prt1_p2 t1_2
2200 -> Seq Scan on prt1_p3 t1_3
2201 -> Hash
2202 -> Append
2203 -> Seq Scan on prt4_n_p1 t2_1
2204 -> Seq Scan on prt4_n_p2 t2_2
2205 -> Seq Scan on prt4_n_p3 t2_3
2206 (11 rows)
2208 EXPLAIN (COSTS OFF)
2209 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;
2210 QUERY PLAN
2211 --------------------------------------------------------
2212 Hash Join
2213 Hash Cond: (t2.a = t1.a)
2214 -> Append
2215 -> Seq Scan on prt4_n_p1 t2_1
2216 -> Seq Scan on prt4_n_p2 t2_2
2217 -> Seq Scan on prt4_n_p3 t2_3
2218 -> Hash
2219 -> Append
2220 -> Hash Join
2221 Hash Cond: (t1_1.a = t3_1.b)
2222 -> Seq Scan on prt1_p1 t1_1
2223 -> Hash
2224 -> Seq Scan on prt2_p1 t3_1
2225 -> Hash Join
2226 Hash Cond: (t1_2.a = t3_2.b)
2227 -> Seq Scan on prt1_p2 t1_2
2228 -> Hash
2229 -> Seq Scan on prt2_p2 t3_2
2230 -> Hash Join
2231 Hash Cond: (t1_3.a = t3_3.b)
2232 -> Seq Scan on prt1_p3 t1_3
2233 -> Hash
2234 -> Seq Scan on prt2_p3 t3_3
2235 (23 rows)
2237 -- partitionwise join can not be applied if there are no equi-join conditions
2238 -- between partition keys
2239 EXPLAIN (COSTS OFF)
2240 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1 LEFT JOIN prt2 t2 ON (t1.a < t2.b);
2241 QUERY PLAN
2242 ---------------------------------------------------------
2243 Nested Loop Left Join
2244 -> Append
2245 -> Seq Scan on prt1_p1 t1_1
2246 -> Seq Scan on prt1_p2 t1_2
2247 -> Seq Scan on prt1_p3 t1_3
2248 -> Append
2249 -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
2250 Index Cond: (b > t1.a)
2251 -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
2252 Index Cond: (b > t1.a)
2253 -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
2254 Index Cond: (b > t1.a)
2255 (12 rows)
2257 -- equi-join with join condition on partial keys does not qualify for
2258 -- partitionwise join
2259 EXPLAIN (COSTS OFF)
2260 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1, prt2_m t2 WHERE t1.a = (t2.b + t2.a)/2;
2261 QUERY PLAN
2262 ----------------------------------------------
2263 Hash Join
2264 Hash Cond: (((t2.b + t2.a) / 2) = t1.a)
2265 -> Append
2266 -> Seq Scan on prt2_m_p1 t2_1
2267 -> Seq Scan on prt2_m_p2 t2_2
2268 -> Seq Scan on prt2_m_p3 t2_3
2269 -> Hash
2270 -> Append
2271 -> Seq Scan on prt1_m_p1 t1_1
2272 -> Seq Scan on prt1_m_p2 t1_2
2273 -> Seq Scan on prt1_m_p3 t1_3
2274 (11 rows)
2276 -- equi-join between out-of-order partition key columns does not qualify for
2277 -- partitionwise join
2278 EXPLAIN (COSTS OFF)
2279 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.a = t2.b;
2280 QUERY PLAN
2281 ----------------------------------------------
2282 Hash Left Join
2283 Hash Cond: (t1.a = t2.b)
2284 -> Append
2285 -> Seq Scan on prt1_m_p1 t1_1
2286 -> Seq Scan on prt1_m_p2 t1_2
2287 -> Seq Scan on prt1_m_p3 t1_3
2288 -> Hash
2289 -> Append
2290 -> Seq Scan on prt2_m_p1 t2_1
2291 -> Seq Scan on prt2_m_p2 t2_2
2292 -> Seq Scan on prt2_m_p3 t2_3
2293 (11 rows)
2295 -- equi-join between non-key columns does not qualify for partitionwise join
2296 EXPLAIN (COSTS OFF)
2297 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_m t1 LEFT JOIN prt2_m t2 ON t1.c = t2.c;
2298 QUERY PLAN
2299 ----------------------------------------------
2300 Hash Left Join
2301 Hash Cond: (t1.c = t2.c)
2302 -> Append
2303 -> Seq Scan on prt1_m_p1 t1_1
2304 -> Seq Scan on prt1_m_p2 t1_2
2305 -> Seq Scan on prt1_m_p3 t1_3
2306 -> Hash
2307 -> Append
2308 -> Seq Scan on prt2_m_p1 t2_1
2309 -> Seq Scan on prt2_m_p2 t2_2
2310 -> Seq Scan on prt2_m_p3 t2_3
2311 (11 rows)
2313 -- partitionwise join can not be applied for a join between list and range
2314 -- partitioned tables
2315 EXPLAIN (COSTS OFF)
2316 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 LEFT JOIN prt2_n t2 ON (t1.c = t2.c);
2317 QUERY PLAN
2318 ----------------------------------------------
2319 Hash Right Join
2320 Hash Cond: (t2.c = (t1.c)::text)
2321 -> Append
2322 -> Seq Scan on prt2_n_p1 t2_1
2323 -> Seq Scan on prt2_n_p2 t2_2
2324 -> Hash
2325 -> Append
2326 -> Seq Scan on prt1_n_p1 t1_1
2327 -> Seq Scan on prt1_n_p2 t1_2
2328 (9 rows)
2330 -- partitionwise join can not be applied between tables with different
2331 -- partition lists
2332 EXPLAIN (COSTS OFF)
2333 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);
2334 QUERY PLAN
2335 ----------------------------------------------------------
2336 Hash Join
2337 Hash Cond: (t2.c = (t1.c)::text)
2338 -> Append
2339 -> Seq Scan on prt2_n_p1 t2_1
2340 -> Seq Scan on prt2_n_p2 t2_2
2341 -> Hash
2342 -> Hash Join
2343 Hash Cond: (t3.c = (t1.c)::text)
2344 -> Append
2345 -> Seq Scan on plt1_p1 t3_1
2346 -> Seq Scan on plt1_p2 t3_2
2347 -> Seq Scan on plt1_p3 t3_3
2348 -> Hash
2349 -> Append
2350 -> Seq Scan on prt1_n_p1 t1_1
2351 -> Seq Scan on prt1_n_p2 t1_2
2352 (16 rows)
2354 -- partitionwise join can not be applied for a join between key column and
2355 -- non-key column
2356 EXPLAIN (COSTS OFF)
2357 SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c = t2.c);
2358 QUERY PLAN
2359 ----------------------------------------------
2360 Hash Full Join
2361 Hash Cond: ((t2.c)::text = (t1.c)::text)
2362 -> Append
2363 -> Seq Scan on prt1_p1 t2_1
2364 -> Seq Scan on prt1_p2 t2_2
2365 -> Seq Scan on prt1_p3 t2_3
2366 -> Hash
2367 -> Append
2368 -> Seq Scan on prt1_n_p1 t1_1
2369 -> Seq Scan on prt1_n_p2 t1_2
2370 (10 rows)
2372 --
2373 -- Test some other plan types in a partitionwise join (unfortunately,
2374 -- we need larger tables to get the planner to choose these plan types)
2375 --
2376 create temp table prtx1 (a integer, b integer, c integer)
2377 partition by range (a);
2378 create temp table prtx1_1 partition of prtx1 for values from (1) to (11);
2379 create temp table prtx1_2 partition of prtx1 for values from (11) to (21);
2380 create temp table prtx1_3 partition of prtx1 for values from (21) to (31);
2381 create temp table prtx2 (a integer, b integer, c integer)
2382 partition by range (a);
2383 create temp table prtx2_1 partition of prtx2 for values from (1) to (11);
2384 create temp table prtx2_2 partition of prtx2 for values from (11) to (21);
2385 create temp table prtx2_3 partition of prtx2 for values from (21) to (31);
2386 insert into prtx1 select 1 + i%30, i, i
2387 from generate_series(1,1000) i;
2388 insert into prtx2 select 1 + i%30, i, i
2389 from generate_series(1,500) i, generate_series(1,10) j;
2390 create index on prtx2 (b);
2391 create index on prtx2 (c);
2392 analyze prtx1;
2393 analyze prtx2;
2394 explain (costs off)
2395 select * from prtx1
2396 where not exists (select 1 from prtx2
2397 where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
2398 and a<20 and c=120;
2399 QUERY PLAN
2400 -------------------------------------------------------------
2401 Append
2402 -> Nested Loop Anti Join
2403 -> Seq Scan on prtx1_1
2404 Filter: ((a < 20) AND (c = 120))
2405 -> Bitmap Heap Scan on prtx2_1
2406 Recheck Cond: ((b = prtx1_1.b) AND (c = 123))
2407 Filter: (a = prtx1_1.a)
2408 -> BitmapAnd
2409 -> Bitmap Index Scan on prtx2_1_b_idx
2410 Index Cond: (b = prtx1_1.b)
2411 -> Bitmap Index Scan on prtx2_1_c_idx
2412 Index Cond: (c = 123)
2413 -> Nested Loop Anti Join
2414 -> Seq Scan on prtx1_2
2415 Filter: ((a < 20) AND (c = 120))
2416 -> Bitmap Heap Scan on prtx2_2
2417 Recheck Cond: ((b = prtx1_2.b) AND (c = 123))
2418 Filter: (a = prtx1_2.a)
2419 -> BitmapAnd
2420 -> Bitmap Index Scan on prtx2_2_b_idx
2421 Index Cond: (b = prtx1_2.b)
2422 -> Bitmap Index Scan on prtx2_2_c_idx
2423 Index Cond: (c = 123)
2424 (23 rows)
2426 select * from prtx1
2427 where not exists (select 1 from prtx2
2428 where prtx2.a=prtx1.a and prtx2.b=prtx1.b and prtx2.c=123)
2429 and a<20 and c=120;
2430 a | b | c
2431 ---+-----+-----
2432 1 | 120 | 120
2433 (1 row)
2435 explain (costs off)
2436 select * from prtx1
2437 where not exists (select 1 from prtx2
2438 where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
2439 and a<20 and c=91;
2440 QUERY PLAN
2441 -----------------------------------------------------------------
2442 Append
2443 -> Nested Loop Anti Join
2444 -> Seq Scan on prtx1_1
2445 Filter: ((a < 20) AND (c = 91))
2446 -> Bitmap Heap Scan on prtx2_1
2447 Recheck Cond: ((b = (prtx1_1.b + 1)) OR (c = 99))
2448 Filter: (a = prtx1_1.a)
2449 -> BitmapOr
2450 -> Bitmap Index Scan on prtx2_1_b_idx
2451 Index Cond: (b = (prtx1_1.b + 1))
2452 -> Bitmap Index Scan on prtx2_1_c_idx
2453 Index Cond: (c = 99)
2454 -> Nested Loop Anti Join
2455 -> Seq Scan on prtx1_2
2456 Filter: ((a < 20) AND (c = 91))
2457 -> Bitmap Heap Scan on prtx2_2
2458 Recheck Cond: ((b = (prtx1_2.b + 1)) OR (c = 99))
2459 Filter: (a = prtx1_2.a)
2460 -> BitmapOr
2461 -> Bitmap Index Scan on prtx2_2_b_idx
2462 Index Cond: (b = (prtx1_2.b + 1))
2463 -> Bitmap Index Scan on prtx2_2_c_idx
2464 Index Cond: (c = 99)
2465 (23 rows)
2467 select * from prtx1
2468 where not exists (select 1 from prtx2
2469 where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
2470 and a<20 and c=91;
2471 a | b | c
2472 ---+----+----
2473 2 | 91 | 91
2474 (1 row)
2476 --
2477 -- Test advanced partition-matching algorithm for partitioned join
2478 --
2479 -- Tests for range-partitioned tables
2480 CREATE TABLE prt1_adv (a int, b int, c varchar) PARTITION BY RANGE (a);
2481 CREATE TABLE prt1_adv_p1 PARTITION OF prt1_adv FOR VALUES FROM (100) TO (200);
2482 CREATE TABLE prt1_adv_p2 PARTITION OF prt1_adv FOR VALUES FROM (200) TO (300);
2483 CREATE TABLE prt1_adv_p3 PARTITION OF prt1_adv FOR VALUES FROM (300) TO (400);
2484 CREATE INDEX prt1_adv_a_idx ON prt1_adv (a);
2485 INSERT INTO prt1_adv SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
2486 ANALYZE prt1_adv;
2487 CREATE TABLE prt2_adv (a int, b int, c varchar) PARTITION BY RANGE (b);
2488 CREATE TABLE prt2_adv_p1 PARTITION OF prt2_adv FOR VALUES FROM (100) TO (150);
2489 CREATE TABLE prt2_adv_p2 PARTITION OF prt2_adv FOR VALUES FROM (200) TO (300);
2490 CREATE TABLE prt2_adv_p3 PARTITION OF prt2_adv FOR VALUES FROM (350) TO (500);
2491 CREATE INDEX prt2_adv_b_idx ON prt2_adv (b);
2492 INSERT INTO prt2_adv_p1 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(100, 149) i;
2493 INSERT INTO prt2_adv_p2 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(200, 299) i;
2494 INSERT INTO prt2_adv_p3 SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(350, 499) i;
2495 ANALYZE prt2_adv;
2496 -- inner join
2497 EXPLAIN (COSTS OFF)
2498 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;
2499 QUERY PLAN
2500 ------------------------------------------------------
2501 Sort
2502 Sort Key: t1.a
2503 -> Append
2504 -> Hash Join
2505 Hash Cond: (t2_1.b = t1_1.a)
2506 -> Seq Scan on prt2_adv_p1 t2_1
2507 -> Hash
2508 -> Seq Scan on prt1_adv_p1 t1_1
2509 Filter: (b = 0)
2510 -> Hash Join
2511 Hash Cond: (t2_2.b = t1_2.a)
2512 -> Seq Scan on prt2_adv_p2 t2_2
2513 -> Hash
2514 -> Seq Scan on prt1_adv_p2 t1_2
2515 Filter: (b = 0)
2516 -> Hash Join
2517 Hash Cond: (t2_3.b = t1_3.a)
2518 -> Seq Scan on prt2_adv_p3 t2_3
2519 -> Hash
2520 -> Seq Scan on prt1_adv_p3 t1_3
2521 Filter: (b = 0)
2522 (21 rows)
2524 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;
2525 a | c | b | c
2526 -----+------+-----+------
2527 100 | 0100 | 100 | 0100
2528 125 | 0125 | 125 | 0125
2529 200 | 0200 | 200 | 0200
2530 225 | 0225 | 225 | 0225
2531 250 | 0250 | 250 | 0250
2532 275 | 0275 | 275 | 0275
2533 350 | 0350 | 350 | 0350
2534 375 | 0375 | 375 | 0375
2535 (8 rows)
2537 -- semi join
2538 EXPLAIN (COSTS OFF)
2539 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;
2540 QUERY PLAN
2541 ------------------------------------------------------
2542 Sort
2543 Sort Key: t1.a
2544 -> Append
2545 -> Hash Semi Join
2546 Hash Cond: (t1_1.a = t2_1.b)
2547 -> Seq Scan on prt1_adv_p1 t1_1
2548 Filter: (b = 0)
2549 -> Hash
2550 -> Seq Scan on prt2_adv_p1 t2_1
2551 -> Hash Semi Join
2552 Hash Cond: (t1_2.a = t2_2.b)
2553 -> Seq Scan on prt1_adv_p2 t1_2
2554 Filter: (b = 0)
2555 -> Hash
2556 -> Seq Scan on prt2_adv_p2 t2_2
2557 -> Hash Semi Join
2558 Hash Cond: (t1_3.a = t2_3.b)
2559 -> Seq Scan on prt1_adv_p3 t1_3
2560 Filter: (b = 0)
2561 -> Hash
2562 -> Seq Scan on prt2_adv_p3 t2_3
2563 (21 rows)
2565 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;
2566 a | b | c
2567 -----+---+------
2568 100 | 0 | 0100
2569 125 | 0 | 0125
2570 200 | 0 | 0200
2571 225 | 0 | 0225
2572 250 | 0 | 0250
2573 275 | 0 | 0275
2574 350 | 0 | 0350
2575 375 | 0 | 0375
2576 (8 rows)
2578 -- left join
2579 EXPLAIN (COSTS OFF)
2580 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;
2581 QUERY PLAN
2582 ------------------------------------------------------
2583 Sort
2584 Sort Key: t1.a, t2.b
2585 -> Append
2586 -> Hash Right Join
2587 Hash Cond: (t2_1.b = t1_1.a)
2588 -> Seq Scan on prt2_adv_p1 t2_1
2589 -> Hash
2590 -> Seq Scan on prt1_adv_p1 t1_1
2591 Filter: (b = 0)
2592 -> Hash Right Join
2593 Hash Cond: (t2_2.b = t1_2.a)
2594 -> Seq Scan on prt2_adv_p2 t2_2
2595 -> Hash
2596 -> Seq Scan on prt1_adv_p2 t1_2
2597 Filter: (b = 0)
2598 -> Hash Right Join
2599 Hash Cond: (t2_3.b = t1_3.a)
2600 -> Seq Scan on prt2_adv_p3 t2_3
2601 -> Hash
2602 -> Seq Scan on prt1_adv_p3 t1_3
2603 Filter: (b = 0)
2604 (21 rows)
2606 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;
2607 a | c | b | c
2608 -----+------+-----+------
2609 100 | 0100 | 100 | 0100
2610 125 | 0125 | 125 | 0125
2611 150 | 0150 | |
2612 175 | 0175 | |
2613 200 | 0200 | 200 | 0200
2614 225 | 0225 | 225 | 0225
2615 250 | 0250 | 250 | 0250
2616 275 | 0275 | 275 | 0275
2617 300 | 0300 | |
2618 325 | 0325 | |
2619 350 | 0350 | 350 | 0350
2620 375 | 0375 | 375 | 0375
2621 (12 rows)
2623 -- anti join
2624 EXPLAIN (COSTS OFF)
2625 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;
2626 QUERY PLAN
2627 ------------------------------------------------------
2628 Sort
2629 Sort Key: t1.a
2630 -> Append
2631 -> Hash Right Anti Join
2632 Hash Cond: (t2_1.b = t1_1.a)
2633 -> Seq Scan on prt2_adv_p1 t2_1
2634 -> Hash
2635 -> Seq Scan on prt1_adv_p1 t1_1
2636 Filter: (b = 0)
2637 -> Hash Right Anti Join
2638 Hash Cond: (t2_2.b = t1_2.a)
2639 -> Seq Scan on prt2_adv_p2 t2_2
2640 -> Hash
2641 -> Seq Scan on prt1_adv_p2 t1_2
2642 Filter: (b = 0)
2643 -> Hash Right Anti Join
2644 Hash Cond: (t2_3.b = t1_3.a)
2645 -> Seq Scan on prt2_adv_p3 t2_3
2646 -> Hash
2647 -> Seq Scan on prt1_adv_p3 t1_3
2648 Filter: (b = 0)
2649 (21 rows)
2651 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;
2652 a | b | c
2653 -----+---+------
2654 150 | 0 | 0150
2655 175 | 0 | 0175
2656 300 | 0 | 0300
2657 325 | 0 | 0325
2658 (4 rows)
2660 -- full join
2661 EXPLAIN (COSTS OFF)
2662 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;
2663 QUERY PLAN
2664 --------------------------------------------------------------------------
2665 Sort
2666 Sort Key: prt1_adv.a, prt2_adv.b
2667 -> Append
2668 -> Hash Full Join
2669 Hash Cond: (prt1_adv_1.a = prt2_adv_1.b)
2670 Filter: (((175) = prt1_adv_1.a) OR ((425) = prt2_adv_1.b))
2671 -> Seq Scan on prt1_adv_p1 prt1_adv_1
2672 Filter: (b = 0)
2673 -> Hash
2674 -> Seq Scan on prt2_adv_p1 prt2_adv_1
2675 Filter: (a = 0)
2676 -> Hash Full Join
2677 Hash Cond: (prt1_adv_2.a = prt2_adv_2.b)
2678 Filter: (((175) = prt1_adv_2.a) OR ((425) = prt2_adv_2.b))
2679 -> Seq Scan on prt1_adv_p2 prt1_adv_2
2680 Filter: (b = 0)
2681 -> Hash
2682 -> Seq Scan on prt2_adv_p2 prt2_adv_2
2683 Filter: (a = 0)
2684 -> Hash Full Join
2685 Hash Cond: (prt2_adv_3.b = prt1_adv_3.a)
2686 Filter: (((175) = prt1_adv_3.a) OR ((425) = prt2_adv_3.b))
2687 -> Seq Scan on prt2_adv_p3 prt2_adv_3
2688 Filter: (a = 0)
2689 -> Hash
2690 -> Seq Scan on prt1_adv_p3 prt1_adv_3
2691 Filter: (b = 0)
2692 (27 rows)
2694 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;
2695 a | c | b | c
2696 -----+------+-----+------
2697 175 | 0175 | |
2698 | | 425 | 0425
2699 (2 rows)
2701 -- Test cases where one side has an extra partition
2702 CREATE TABLE prt2_adv_extra PARTITION OF prt2_adv FOR VALUES FROM (500) TO (MAXVALUE);
2703 INSERT INTO prt2_adv SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(500, 599) i;
2704 ANALYZE prt2_adv;
2705 -- inner join
2706 EXPLAIN (COSTS OFF)
2707 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;
2708 QUERY PLAN
2709 ------------------------------------------------------
2710 Sort
2711 Sort Key: t1.a
2712 -> Append
2713 -> Hash Join
2714 Hash Cond: (t2_1.b = t1_1.a)
2715 -> Seq Scan on prt2_adv_p1 t2_1
2716 -> Hash
2717 -> Seq Scan on prt1_adv_p1 t1_1
2718 Filter: (b = 0)
2719 -> Hash Join
2720 Hash Cond: (t2_2.b = t1_2.a)
2721 -> Seq Scan on prt2_adv_p2 t2_2
2722 -> Hash
2723 -> Seq Scan on prt1_adv_p2 t1_2
2724 Filter: (b = 0)
2725 -> Hash Join
2726 Hash Cond: (t2_3.b = t1_3.a)
2727 -> Seq Scan on prt2_adv_p3 t2_3
2728 -> Hash
2729 -> Seq Scan on prt1_adv_p3 t1_3
2730 Filter: (b = 0)
2731 (21 rows)
2733 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;
2734 a | c | b | c
2735 -----+------+-----+------
2736 100 | 0100 | 100 | 0100
2737 125 | 0125 | 125 | 0125
2738 200 | 0200 | 200 | 0200
2739 225 | 0225 | 225 | 0225
2740 250 | 0250 | 250 | 0250
2741 275 | 0275 | 275 | 0275
2742 350 | 0350 | 350 | 0350
2743 375 | 0375 | 375 | 0375
2744 (8 rows)
2746 -- semi join
2747 EXPLAIN (COSTS OFF)
2748 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;
2749 QUERY PLAN
2750 ------------------------------------------------------
2751 Sort
2752 Sort Key: t1.a
2753 -> Append
2754 -> Hash Semi Join
2755 Hash Cond: (t1_1.a = t2_1.b)
2756 -> Seq Scan on prt1_adv_p1 t1_1
2757 Filter: (b = 0)
2758 -> Hash
2759 -> Seq Scan on prt2_adv_p1 t2_1
2760 -> Hash Semi Join
2761 Hash Cond: (t1_2.a = t2_2.b)
2762 -> Seq Scan on prt1_adv_p2 t1_2
2763 Filter: (b = 0)
2764 -> Hash
2765 -> Seq Scan on prt2_adv_p2 t2_2
2766 -> Hash Semi Join
2767 Hash Cond: (t1_3.a = t2_3.b)
2768 -> Seq Scan on prt1_adv_p3 t1_3
2769 Filter: (b = 0)
2770 -> Hash
2771 -> Seq Scan on prt2_adv_p3 t2_3
2772 (21 rows)
2774 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;
2775 a | b | c
2776 -----+---+------
2777 100 | 0 | 0100
2778 125 | 0 | 0125
2779 200 | 0 | 0200
2780 225 | 0 | 0225
2781 250 | 0 | 0250
2782 275 | 0 | 0275
2783 350 | 0 | 0350
2784 375 | 0 | 0375
2785 (8 rows)
2787 -- left join
2788 EXPLAIN (COSTS OFF)
2789 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;
2790 QUERY PLAN
2791 ------------------------------------------------------
2792 Sort
2793 Sort Key: t1.a, t2.b
2794 -> Append
2795 -> Hash Right Join
2796 Hash Cond: (t2_1.b = t1_1.a)
2797 -> Seq Scan on prt2_adv_p1 t2_1
2798 -> Hash
2799 -> Seq Scan on prt1_adv_p1 t1_1
2800 Filter: (b = 0)
2801 -> Hash Right Join
2802 Hash Cond: (t2_2.b = t1_2.a)
2803 -> Seq Scan on prt2_adv_p2 t2_2
2804 -> Hash
2805 -> Seq Scan on prt1_adv_p2 t1_2
2806 Filter: (b = 0)
2807 -> Hash Right Join
2808 Hash Cond: (t2_3.b = t1_3.a)
2809 -> Seq Scan on prt2_adv_p3 t2_3
2810 -> Hash
2811 -> Seq Scan on prt1_adv_p3 t1_3
2812 Filter: (b = 0)
2813 (21 rows)
2815 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;
2816 a | c | b | c
2817 -----+------+-----+------
2818 100 | 0100 | 100 | 0100
2819 125 | 0125 | 125 | 0125
2820 150 | 0150 | |
2821 175 | 0175 | |
2822 200 | 0200 | 200 | 0200
2823 225 | 0225 | 225 | 0225
2824 250 | 0250 | 250 | 0250
2825 275 | 0275 | 275 | 0275
2826 300 | 0300 | |
2827 325 | 0325 | |
2828 350 | 0350 | 350 | 0350
2829 375 | 0375 | 375 | 0375
2830 (12 rows)
2832 -- left join; currently we can't do partitioned join if there are no matched
2833 -- partitions on the nullable side
2834 EXPLAIN (COSTS OFF)
2835 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;
2836 QUERY PLAN
2837 ---------------------------------------------------------
2838 Sort
2839 Sort Key: t1.b, t2.a
2840 -> Hash Right Join
2841 Hash Cond: (t2.a = t1.b)
2842 -> Append
2843 -> Seq Scan on prt1_adv_p1 t2_1
2844 -> Seq Scan on prt1_adv_p2 t2_2
2845 -> Seq Scan on prt1_adv_p3 t2_3
2846 -> Hash
2847 -> Append
2848 -> Seq Scan on prt2_adv_p1 t1_1
2849 Filter: (a = 0)
2850 -> Seq Scan on prt2_adv_p2 t1_2
2851 Filter: (a = 0)
2852 -> Seq Scan on prt2_adv_p3 t1_3
2853 Filter: (a = 0)
2854 -> Seq Scan on prt2_adv_extra t1_4
2855 Filter: (a = 0)
2856 (18 rows)
2858 -- anti join
2859 EXPLAIN (COSTS OFF)
2860 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;
2861 QUERY PLAN
2862 ------------------------------------------------------
2863 Sort
2864 Sort Key: t1.a
2865 -> Append
2866 -> Hash Right Anti Join
2867 Hash Cond: (t2_1.b = t1_1.a)
2868 -> Seq Scan on prt2_adv_p1 t2_1
2869 -> Hash
2870 -> Seq Scan on prt1_adv_p1 t1_1
2871 Filter: (b = 0)
2872 -> Hash Right Anti Join
2873 Hash Cond: (t2_2.b = t1_2.a)
2874 -> Seq Scan on prt2_adv_p2 t2_2
2875 -> Hash
2876 -> Seq Scan on prt1_adv_p2 t1_2
2877 Filter: (b = 0)
2878 -> Hash Right Anti Join
2879 Hash Cond: (t2_3.b = t1_3.a)
2880 -> Seq Scan on prt2_adv_p3 t2_3
2881 -> Hash
2882 -> Seq Scan on prt1_adv_p3 t1_3
2883 Filter: (b = 0)
2884 (21 rows)
2886 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;
2887 a | b | c
2888 -----+---+------
2889 150 | 0 | 0150
2890 175 | 0 | 0175
2891 300 | 0 | 0300
2892 325 | 0 | 0325
2893 (4 rows)
2895 -- anti join; currently we can't do partitioned join if there are no matched
2896 -- partitions on the nullable side
2897 EXPLAIN (COSTS OFF)
2898 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;
2899 QUERY PLAN
2900 ---------------------------------------------------------
2901 Sort
2902 Sort Key: t1.b
2903 -> Hash Right Anti Join
2904 Hash Cond: (t2.a = t1.b)
2905 -> Append
2906 -> Seq Scan on prt1_adv_p1 t2_1
2907 -> Seq Scan on prt1_adv_p2 t2_2
2908 -> Seq Scan on prt1_adv_p3 t2_3
2909 -> Hash
2910 -> Append
2911 -> Seq Scan on prt2_adv_p1 t1_1
2912 Filter: (a = 0)
2913 -> Seq Scan on prt2_adv_p2 t1_2
2914 Filter: (a = 0)
2915 -> Seq Scan on prt2_adv_p3 t1_3
2916 Filter: (a = 0)
2917 -> Seq Scan on prt2_adv_extra t1_4
2918 Filter: (a = 0)
2919 (18 rows)
2921 -- full 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.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;
2925 QUERY PLAN
2926 ----------------------------------------------------------------
2927 Sort
2928 Sort Key: prt1_adv.a, prt2_adv.b
2929 -> Hash Full Join
2930 Hash Cond: (prt2_adv.b = prt1_adv.a)
2931 Filter: (((175) = prt1_adv.a) OR ((425) = prt2_adv.b))
2932 -> Append
2933 -> Seq Scan on prt2_adv_p1 prt2_adv_1
2934 Filter: (a = 0)
2935 -> Seq Scan on prt2_adv_p2 prt2_adv_2
2936 Filter: (a = 0)
2937 -> Seq Scan on prt2_adv_p3 prt2_adv_3
2938 Filter: (a = 0)
2939 -> Seq Scan on prt2_adv_extra prt2_adv_4
2940 Filter: (a = 0)
2941 -> Hash
2942 -> Append
2943 -> Seq Scan on prt1_adv_p1 prt1_adv_1
2944 Filter: (b = 0)
2945 -> Seq Scan on prt1_adv_p2 prt1_adv_2
2946 Filter: (b = 0)
2947 -> Seq Scan on prt1_adv_p3 prt1_adv_3
2948 Filter: (b = 0)
2949 (22 rows)
2951 -- 3-way join where not every pair of relations can do partitioned join
2952 EXPLAIN (COSTS OFF)
2953 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;
2954 QUERY PLAN
2955 --------------------------------------------------------------------------------
2956 Sort
2957 Sort Key: t1.b, t2.a
2958 -> Append
2959 -> Nested Loop Left Join
2960 -> Nested Loop
2961 -> Seq Scan on prt2_adv_p1 t1_1
2962 Filter: (a = 0)
2963 -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t3_1
2964 Index Cond: (a = t1_1.b)
2965 -> Index Scan using prt1_adv_p1_a_idx on prt1_adv_p1 t2_1
2966 Index Cond: (a = t1_1.b)
2967 -> Hash Right Join
2968 Hash Cond: (t2_2.a = t1_2.b)
2969 -> Seq Scan on prt1_adv_p2 t2_2
2970 -> Hash
2971 -> Hash Join
2972 Hash Cond: (t3_2.a = t1_2.b)
2973 -> Seq Scan on prt1_adv_p2 t3_2
2974 -> Hash
2975 -> Seq Scan on prt2_adv_p2 t1_2
2976 Filter: (a = 0)
2977 -> Hash Right Join
2978 Hash Cond: (t2_3.a = t1_3.b)
2979 -> Seq Scan on prt1_adv_p3 t2_3
2980 -> Hash
2981 -> Hash Join
2982 Hash Cond: (t3_3.a = t1_3.b)
2983 -> Seq Scan on prt1_adv_p3 t3_3
2984 -> Hash
2985 -> Seq Scan on prt2_adv_p3 t1_3
2986 Filter: (a = 0)
2987 (31 rows)
2989 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;
2990 b | c | a | c | a | c
2991 -----+------+-----+------+-----+------
2992 100 | 0100 | 100 | 0100 | 100 | 0100
2993 125 | 0125 | 125 | 0125 | 125 | 0125
2994 200 | 0200 | 200 | 0200 | 200 | 0200
2995 225 | 0225 | 225 | 0225 | 225 | 0225
2996 250 | 0250 | 250 | 0250 | 250 | 0250
2997 275 | 0275 | 275 | 0275 | 275 | 0275
2998 350 | 0350 | 350 | 0350 | 350 | 0350
2999 375 | 0375 | 375 | 0375 | 375 | 0375
3000 (8 rows)
3002 DROP TABLE prt2_adv_extra;
3003 -- Test cases where a partition on one side matches multiple partitions on
3004 -- the other side; we currently can't do partitioned join in such cases
3005 ALTER TABLE prt2_adv DETACH PARTITION prt2_adv_p3;
3006 -- Split prt2_adv_p3 into two partitions so that prt1_adv_p3 matches both
3007 CREATE TABLE prt2_adv_p3_1 PARTITION OF prt2_adv FOR VALUES FROM (350) TO (375);
3008 CREATE TABLE prt2_adv_p3_2 PARTITION OF prt2_adv FOR VALUES FROM (375) TO (500);
3009 INSERT INTO prt2_adv SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(350, 499) i;
3010 ANALYZE prt2_adv;
3011 -- inner join
3012 EXPLAIN (COSTS OFF)
3013 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;
3014 QUERY PLAN
3015 ------------------------------------------------------
3016 Sort
3017 Sort Key: t1.a
3018 -> Hash Join
3019 Hash Cond: (t2.b = t1.a)
3020 -> Append
3021 -> Seq Scan on prt2_adv_p1 t2_1
3022 -> Seq Scan on prt2_adv_p2 t2_2
3023 -> Seq Scan on prt2_adv_p3_1 t2_3
3024 -> Seq Scan on prt2_adv_p3_2 t2_4
3025 -> Hash
3026 -> Append
3027 -> Seq Scan on prt1_adv_p1 t1_1
3028 Filter: (b = 0)
3029 -> Seq Scan on prt1_adv_p2 t1_2
3030 Filter: (b = 0)
3031 -> Seq Scan on prt1_adv_p3 t1_3
3032 Filter: (b = 0)
3033 (17 rows)
3035 -- semi join
3036 EXPLAIN (COSTS OFF)
3037 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;
3038 QUERY PLAN
3039 --------------------------------------------------------
3040 Sort
3041 Sort Key: t1.a
3042 -> Hash Semi Join
3043 Hash Cond: (t1.a = t2.b)
3044 -> Append
3045 -> Seq Scan on prt1_adv_p1 t1_1
3046 Filter: (b = 0)
3047 -> Seq Scan on prt1_adv_p2 t1_2
3048 Filter: (b = 0)
3049 -> Seq Scan on prt1_adv_p3 t1_3
3050 Filter: (b = 0)
3051 -> Hash
3052 -> Append
3053 -> Seq Scan on prt2_adv_p1 t2_1
3054 -> Seq Scan on prt2_adv_p2 t2_2
3055 -> Seq Scan on prt2_adv_p3_1 t2_3
3056 -> Seq Scan on prt2_adv_p3_2 t2_4
3057 (17 rows)
3059 -- left join
3060 EXPLAIN (COSTS OFF)
3061 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;
3062 QUERY PLAN
3063 ------------------------------------------------------
3064 Sort
3065 Sort Key: t1.a, t2.b
3066 -> Hash Right Join
3067 Hash Cond: (t2.b = t1.a)
3068 -> Append
3069 -> Seq Scan on prt2_adv_p1 t2_1
3070 -> Seq Scan on prt2_adv_p2 t2_2
3071 -> Seq Scan on prt2_adv_p3_1 t2_3
3072 -> Seq Scan on prt2_adv_p3_2 t2_4
3073 -> Hash
3074 -> Append
3075 -> Seq Scan on prt1_adv_p1 t1_1
3076 Filter: (b = 0)
3077 -> Seq Scan on prt1_adv_p2 t1_2
3078 Filter: (b = 0)
3079 -> Seq Scan on prt1_adv_p3 t1_3
3080 Filter: (b = 0)
3081 (17 rows)
3083 -- anti join
3084 EXPLAIN (COSTS OFF)
3085 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;
3086 QUERY PLAN
3087 ------------------------------------------------------
3088 Sort
3089 Sort Key: t1.a
3090 -> Hash Right Anti Join
3091 Hash Cond: (t2.b = t1.a)
3092 -> Append
3093 -> Seq Scan on prt2_adv_p1 t2_1
3094 -> Seq Scan on prt2_adv_p2 t2_2
3095 -> Seq Scan on prt2_adv_p3_1 t2_3
3096 -> Seq Scan on prt2_adv_p3_2 t2_4
3097 -> Hash
3098 -> Append
3099 -> Seq Scan on prt1_adv_p1 t1_1
3100 Filter: (b = 0)
3101 -> Seq Scan on prt1_adv_p2 t1_2
3102 Filter: (b = 0)
3103 -> Seq Scan on prt1_adv_p3 t1_3
3104 Filter: (b = 0)
3105 (17 rows)
3107 -- full join
3108 EXPLAIN (COSTS OFF)
3109 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;
3110 QUERY PLAN
3111 ----------------------------------------------------------------
3112 Sort
3113 Sort Key: prt1_adv.a, prt2_adv.b
3114 -> Hash Full Join
3115 Hash Cond: (prt2_adv.b = prt1_adv.a)
3116 Filter: (((175) = prt1_adv.a) OR ((425) = prt2_adv.b))
3117 -> Append
3118 -> Seq Scan on prt2_adv_p1 prt2_adv_1
3119 Filter: (a = 0)
3120 -> Seq Scan on prt2_adv_p2 prt2_adv_2
3121 Filter: (a = 0)
3122 -> Seq Scan on prt2_adv_p3_1 prt2_adv_3
3123 Filter: (a = 0)
3124 -> Seq Scan on prt2_adv_p3_2 prt2_adv_4
3125 Filter: (a = 0)
3126 -> Hash
3127 -> Append
3128 -> Seq Scan on prt1_adv_p1 prt1_adv_1
3129 Filter: (b = 0)
3130 -> Seq Scan on prt1_adv_p2 prt1_adv_2
3131 Filter: (b = 0)
3132 -> Seq Scan on prt1_adv_p3 prt1_adv_3
3133 Filter: (b = 0)
3134 (22 rows)
3136 DROP TABLE prt2_adv_p3_1;
3137 DROP TABLE prt2_adv_p3_2;
3138 ANALYZE prt2_adv;
3139 -- Test default partitions
3140 ALTER TABLE prt1_adv DETACH PARTITION prt1_adv_p1;
3141 -- Change prt1_adv_p1 to the default partition
3142 ALTER TABLE prt1_adv ATTACH PARTITION prt1_adv_p1 DEFAULT;
3143 ALTER TABLE prt1_adv DETACH PARTITION prt1_adv_p3;
3144 ANALYZE prt1_adv;
3145 -- We can do partitioned join even if only one of relations has the default
3146 -- partition
3147 EXPLAIN (COSTS OFF)
3148 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;
3149 QUERY PLAN
3150 ------------------------------------------------------
3151 Sort
3152 Sort Key: t1.a
3153 -> Append
3154 -> Hash Join
3155 Hash Cond: (t2_1.b = t1_2.a)
3156 -> Seq Scan on prt2_adv_p1 t2_1
3157 -> Hash
3158 -> Seq Scan on prt1_adv_p1 t1_2
3159 Filter: (b = 0)
3160 -> Hash Join
3161 Hash Cond: (t2_2.b = t1_1.a)
3162 -> Seq Scan on prt2_adv_p2 t2_2
3163 -> Hash
3164 -> Seq Scan on prt1_adv_p2 t1_1
3165 Filter: (b = 0)
3166 (15 rows)
3168 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;
3169 a | c | b | c
3170 -----+------+-----+------
3171 100 | 0100 | 100 | 0100
3172 125 | 0125 | 125 | 0125
3173 200 | 0200 | 200 | 0200
3174 225 | 0225 | 225 | 0225
3175 250 | 0250 | 250 | 0250
3176 275 | 0275 | 275 | 0275
3177 (6 rows)
3179 -- Restore prt1_adv_p3
3180 ALTER TABLE prt1_adv ATTACH PARTITION prt1_adv_p3 FOR VALUES FROM (300) TO (400);
3181 ANALYZE prt1_adv;
3182 -- Restore prt2_adv_p3
3183 ALTER TABLE prt2_adv ATTACH PARTITION prt2_adv_p3 FOR VALUES FROM (350) TO (500);
3184 ANALYZE prt2_adv;
3185 -- Partitioned join can't be applied because the default partition of prt1_adv
3186 -- matches prt2_adv_p1 and prt2_adv_p3
3187 EXPLAIN (COSTS OFF)
3188 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;
3189 QUERY PLAN
3190 ------------------------------------------------------
3191 Sort
3192 Sort Key: t1.a
3193 -> Hash Join
3194 Hash Cond: (t2.b = t1.a)
3195 -> Append
3196 -> Seq Scan on prt2_adv_p1 t2_1
3197 -> Seq Scan on prt2_adv_p2 t2_2
3198 -> Seq Scan on prt2_adv_p3 t2_3
3199 -> Hash
3200 -> Append
3201 -> Seq Scan on prt1_adv_p2 t1_1
3202 Filter: (b = 0)
3203 -> Seq Scan on prt1_adv_p3 t1_2
3204 Filter: (b = 0)
3205 -> Seq Scan on prt1_adv_p1 t1_3
3206 Filter: (b = 0)
3207 (16 rows)
3209 ALTER TABLE prt2_adv DETACH PARTITION prt2_adv_p3;
3210 -- Change prt2_adv_p3 to the default partition
3211 ALTER TABLE prt2_adv ATTACH PARTITION prt2_adv_p3 DEFAULT;
3212 ANALYZE prt2_adv;
3213 -- Partitioned join can't be applied because the default partition of prt1_adv
3214 -- matches prt2_adv_p1 and prt2_adv_p3
3215 EXPLAIN (COSTS OFF)
3216 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;
3217 QUERY PLAN
3218 ------------------------------------------------------
3219 Sort
3220 Sort Key: t1.a
3221 -> Hash Join
3222 Hash Cond: (t2.b = t1.a)
3223 -> Append
3224 -> Seq Scan on prt2_adv_p1 t2_1
3225 -> Seq Scan on prt2_adv_p2 t2_2
3226 -> Seq Scan on prt2_adv_p3 t2_3
3227 -> Hash
3228 -> Append
3229 -> Seq Scan on prt1_adv_p2 t1_1
3230 Filter: (b = 0)
3231 -> Seq Scan on prt1_adv_p3 t1_2
3232 Filter: (b = 0)
3233 -> Seq Scan on prt1_adv_p1 t1_3
3234 Filter: (b = 0)
3235 (16 rows)
3237 DROP TABLE prt1_adv_p3;
3238 ANALYZE prt1_adv;
3239 DROP TABLE prt2_adv_p3;
3240 ANALYZE prt2_adv;
3241 CREATE TABLE prt3_adv (a int, b int, c varchar) PARTITION BY RANGE (a);
3242 CREATE TABLE prt3_adv_p1 PARTITION OF prt3_adv FOR VALUES FROM (200) TO (300);
3243 CREATE TABLE prt3_adv_p2 PARTITION OF prt3_adv FOR VALUES FROM (300) TO (400);
3244 CREATE INDEX prt3_adv_a_idx ON prt3_adv (a);
3245 INSERT INTO prt3_adv SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(200, 399) i;
3246 ANALYZE prt3_adv;
3247 -- 3-way join to test the default partition of a join relation
3248 EXPLAIN (COSTS OFF)
3249 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;
3250 QUERY PLAN
3251 ------------------------------------------------------------------
3252 Sort
3253 Sort Key: t1.a, t2.b, t3.a
3254 -> Append
3255 -> Hash Right Join
3256 Hash Cond: (t3_1.a = t1_1.a)
3257 -> Seq Scan on prt3_adv_p1 t3_1
3258 -> Hash
3259 -> Hash Right Join
3260 Hash Cond: (t2_2.b = t1_1.a)
3261 -> Seq Scan on prt2_adv_p2 t2_2
3262 -> Hash
3263 -> Seq Scan on prt1_adv_p2 t1_1
3264 Filter: (b = 0)
3265 -> Hash Right Join
3266 Hash Cond: (t3_2.a = t1_2.a)
3267 -> Seq Scan on prt3_adv_p2 t3_2
3268 -> Hash
3269 -> Hash Right Join
3270 Hash Cond: (t2_1.b = t1_2.a)
3271 -> Seq Scan on prt2_adv_p1 t2_1
3272 -> Hash
3273 -> Seq Scan on prt1_adv_p1 t1_2
3274 Filter: (b = 0)
3275 (23 rows)
3277 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;
3278 a | c | b | c | a | c
3279 -----+------+-----+------+-----+------
3280 100 | 0100 | 100 | 0100 | |
3281 125 | 0125 | 125 | 0125 | |
3282 150 | 0150 | | | |
3283 175 | 0175 | | | |
3284 200 | 0200 | 200 | 0200 | 200 | 0200
3285 225 | 0225 | 225 | 0225 | 225 | 0225
3286 250 | 0250 | 250 | 0250 | 250 | 0250
3287 275 | 0275 | 275 | 0275 | 275 | 0275
3288 (8 rows)
3290 DROP TABLE prt1_adv;
3291 DROP TABLE prt2_adv;
3292 DROP TABLE prt3_adv;
3293 -- Test interaction of partitioned join with partition pruning
3294 CREATE TABLE prt1_adv (a int, b int, c varchar) PARTITION BY RANGE (a);
3295 CREATE TABLE prt1_adv_p1 PARTITION OF prt1_adv FOR VALUES FROM (100) TO (200);
3296 CREATE TABLE prt1_adv_p2 PARTITION OF prt1_adv FOR VALUES FROM (200) TO (300);
3297 CREATE TABLE prt1_adv_p3 PARTITION OF prt1_adv FOR VALUES FROM (300) TO (400);
3298 CREATE INDEX prt1_adv_a_idx ON prt1_adv (a);
3299 INSERT INTO prt1_adv SELECT i, i % 25, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
3300 ANALYZE prt1_adv;
3301 CREATE TABLE prt2_adv (a int, b int, c varchar) PARTITION BY RANGE (b);
3302 CREATE TABLE prt2_adv_p1 PARTITION OF prt2_adv FOR VALUES FROM (100) TO (200);
3303 CREATE TABLE prt2_adv_p2 PARTITION OF prt2_adv FOR VALUES FROM (200) TO (400);
3304 CREATE INDEX prt2_adv_b_idx ON prt2_adv (b);
3305 INSERT INTO prt2_adv SELECT i % 25, i, to_char(i, 'FM0000') FROM generate_series(100, 399) i;
3306 ANALYZE prt2_adv;
3307 EXPLAIN (COSTS OFF)
3308 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;
3309 QUERY PLAN
3310 -----------------------------------------------------------
3311 Sort
3312 Sort Key: t1.a
3313 -> Append
3314 -> Hash Join
3315 Hash Cond: (t2_1.b = t1_1.a)
3316 -> Seq Scan on prt2_adv_p1 t2_1
3317 -> Hash
3318 -> Seq Scan on prt1_adv_p1 t1_1
3319 Filter: ((a < 300) AND (b = 0))
3320 -> Hash Join
3321 Hash Cond: (t2_2.b = t1_2.a)
3322 -> Seq Scan on prt2_adv_p2 t2_2
3323 -> Hash
3324 -> Seq Scan on prt1_adv_p2 t1_2
3325 Filter: ((a < 300) AND (b = 0))
3326 (15 rows)
3328 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;
3329 a | c | b | c
3330 -----+------+-----+------
3331 100 | 0100 | 100 | 0100
3332 125 | 0125 | 125 | 0125
3333 150 | 0150 | 150 | 0150
3334 175 | 0175 | 175 | 0175
3335 200 | 0200 | 200 | 0200
3336 225 | 0225 | 225 | 0225
3337 250 | 0250 | 250 | 0250
3338 275 | 0275 | 275 | 0275
3339 (8 rows)
3341 DROP TABLE prt1_adv_p3;
3342 CREATE TABLE prt1_adv_default PARTITION OF prt1_adv DEFAULT;
3343 ANALYZE prt1_adv;
3344 CREATE TABLE prt2_adv_default PARTITION OF prt2_adv DEFAULT;
3345 ANALYZE prt2_adv;
3346 EXPLAIN (COSTS OFF)
3347 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;
3348 QUERY PLAN
3349 --------------------------------------------------------------------------
3350 Sort
3351 Sort Key: t1.a
3352 -> Append
3353 -> Hash Join
3354 Hash Cond: (t2_1.b = t1_1.a)
3355 -> Seq Scan on prt2_adv_p1 t2_1
3356 -> Hash
3357 -> Seq Scan on prt1_adv_p1 t1_1
3358 Filter: ((a >= 100) AND (a < 300) AND (b = 0))
3359 -> Hash Join
3360 Hash Cond: (t2_2.b = t1_2.a)
3361 -> Seq Scan on prt2_adv_p2 t2_2
3362 -> Hash
3363 -> Seq Scan on prt1_adv_p2 t1_2
3364 Filter: ((a >= 100) AND (a < 300) AND (b = 0))
3365 (15 rows)
3367 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;
3368 a | c | b | c
3369 -----+------+-----+------
3370 100 | 0100 | 100 | 0100
3371 125 | 0125 | 125 | 0125
3372 150 | 0150 | 150 | 0150
3373 175 | 0175 | 175 | 0175
3374 200 | 0200 | 200 | 0200
3375 225 | 0225 | 225 | 0225
3376 250 | 0250 | 250 | 0250
3377 275 | 0275 | 275 | 0275
3378 (8 rows)
3380 DROP TABLE prt1_adv;
3381 DROP TABLE prt2_adv;
3382 -- Tests for list-partitioned tables
3383 CREATE TABLE plt1_adv (a int, b int, c text) PARTITION BY LIST (c);
3384 CREATE TABLE plt1_adv_p1 PARTITION OF plt1_adv FOR VALUES IN ('0001', '0003');
3385 CREATE TABLE plt1_adv_p2 PARTITION OF plt1_adv FOR VALUES IN ('0004', '0006');
3386 CREATE TABLE plt1_adv_p3 PARTITION OF plt1_adv FOR VALUES IN ('0008', '0009');
3387 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);
3388 ANALYZE plt1_adv;
3389 CREATE TABLE plt2_adv (a int, b int, c text) PARTITION BY LIST (c);
3390 CREATE TABLE plt2_adv_p1 PARTITION OF plt2_adv FOR VALUES IN ('0002', '0003');
3391 CREATE TABLE plt2_adv_p2 PARTITION OF plt2_adv FOR VALUES IN ('0004', '0006');
3392 CREATE TABLE plt2_adv_p3 PARTITION OF plt2_adv FOR VALUES IN ('0007', '0009');
3393 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);
3394 ANALYZE plt2_adv;
3395 -- inner join
3396 EXPLAIN (COSTS OFF)
3397 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;
3398 QUERY PLAN
3399 --------------------------------------------------------------------
3400 Sort
3401 Sort Key: t1.a
3402 -> Append
3403 -> Hash Join
3404 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3405 -> Seq Scan on plt2_adv_p1 t2_1
3406 -> Hash
3407 -> Seq Scan on plt1_adv_p1 t1_1
3408 Filter: (b < 10)
3409 -> Hash Join
3410 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3411 -> Seq Scan on plt2_adv_p2 t2_2
3412 -> Hash
3413 -> Seq Scan on plt1_adv_p2 t1_2
3414 Filter: (b < 10)
3415 -> Hash Join
3416 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3417 -> Seq Scan on plt2_adv_p3 t2_3
3418 -> Hash
3419 -> Seq Scan on plt1_adv_p3 t1_3
3420 Filter: (b < 10)
3421 (21 rows)
3423 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;
3424 a | c | a | c
3425 ---+------+---+------
3426 3 | 0003 | 3 | 0003
3427 4 | 0004 | 4 | 0004
3428 6 | 0006 | 6 | 0006
3429 9 | 0009 | 9 | 0009
3430 (4 rows)
3432 -- semi join
3433 EXPLAIN (COSTS OFF)
3434 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;
3435 QUERY PLAN
3436 ----------------------------------------------------------------------
3437 Sort
3438 Sort Key: t1.a
3439 -> Append
3440 -> Nested Loop Semi Join
3441 Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
3442 -> Seq Scan on plt1_adv_p1 t1_1
3443 Filter: (b < 10)
3444 -> Seq Scan on plt2_adv_p1 t2_1
3445 -> Nested Loop Semi Join
3446 Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
3447 -> Seq Scan on plt1_adv_p2 t1_2
3448 Filter: (b < 10)
3449 -> Seq Scan on plt2_adv_p2 t2_2
3450 -> Nested Loop Semi Join
3451 Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
3452 -> Seq Scan on plt1_adv_p3 t1_3
3453 Filter: (b < 10)
3454 -> Seq Scan on plt2_adv_p3 t2_3
3455 (18 rows)
3457 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;
3458 a | b | c
3459 ---+---+------
3460 3 | 3 | 0003
3461 4 | 4 | 0004
3462 6 | 6 | 0006
3463 9 | 9 | 0009
3464 (4 rows)
3466 -- left join
3467 EXPLAIN (COSTS OFF)
3468 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;
3469 QUERY PLAN
3470 --------------------------------------------------------------------
3471 Sort
3472 Sort Key: t1.a
3473 -> Append
3474 -> Hash Right Join
3475 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3476 -> Seq Scan on plt2_adv_p1 t2_1
3477 -> Hash
3478 -> Seq Scan on plt1_adv_p1 t1_1
3479 Filter: (b < 10)
3480 -> Hash Right Join
3481 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3482 -> Seq Scan on plt2_adv_p2 t2_2
3483 -> Hash
3484 -> Seq Scan on plt1_adv_p2 t1_2
3485 Filter: (b < 10)
3486 -> Hash Right Join
3487 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3488 -> Seq Scan on plt2_adv_p3 t2_3
3489 -> Hash
3490 -> Seq Scan on plt1_adv_p3 t1_3
3491 Filter: (b < 10)
3492 (21 rows)
3494 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;
3495 a | c | a | c
3496 ---+------+---+------
3497 1 | 0001 | |
3498 3 | 0003 | 3 | 0003
3499 4 | 0004 | 4 | 0004
3500 6 | 0006 | 6 | 0006
3501 8 | 0008 | |
3502 9 | 0009 | 9 | 0009
3503 (6 rows)
3505 -- anti join
3506 EXPLAIN (COSTS OFF)
3507 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;
3508 QUERY PLAN
3509 --------------------------------------------------------------------
3510 Sort
3511 Sort Key: t1.a
3512 -> Append
3513 -> Hash Right Anti Join
3514 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3515 -> Seq Scan on plt2_adv_p1 t2_1
3516 -> Hash
3517 -> Seq Scan on plt1_adv_p1 t1_1
3518 Filter: (b < 10)
3519 -> Hash Right Anti Join
3520 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3521 -> Seq Scan on plt2_adv_p2 t2_2
3522 -> Hash
3523 -> Seq Scan on plt1_adv_p2 t1_2
3524 Filter: (b < 10)
3525 -> Hash Right Anti Join
3526 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3527 -> Seq Scan on plt2_adv_p3 t2_3
3528 -> Hash
3529 -> Seq Scan on plt1_adv_p3 t1_3
3530 Filter: (b < 10)
3531 (21 rows)
3533 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;
3534 a | b | c
3535 ---+---+------
3536 1 | 1 | 0001
3537 8 | 8 | 0008
3538 (2 rows)
3540 -- full join
3541 EXPLAIN (COSTS OFF)
3542 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;
3543 QUERY PLAN
3544 -----------------------------------------------------------------------------------
3545 Sort
3546 Sort Key: t1.a, t2.a
3547 -> Append
3548 -> Hash Full Join
3549 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
3550 Filter: ((COALESCE(t1_1.b, 0) < 10) AND (COALESCE(t2_1.b, 0) < 10))
3551 -> Seq Scan on plt1_adv_p1 t1_1
3552 -> Hash
3553 -> Seq Scan on plt2_adv_p1 t2_1
3554 -> Hash Full Join
3555 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
3556 Filter: ((COALESCE(t1_2.b, 0) < 10) AND (COALESCE(t2_2.b, 0) < 10))
3557 -> Seq Scan on plt1_adv_p2 t1_2
3558 -> Hash
3559 -> Seq Scan on plt2_adv_p2 t2_2
3560 -> Hash Full Join
3561 Hash Cond: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
3562 Filter: ((COALESCE(t1_3.b, 0) < 10) AND (COALESCE(t2_3.b, 0) < 10))
3563 -> Seq Scan on plt1_adv_p3 t1_3
3564 -> Hash
3565 -> Seq Scan on plt2_adv_p3 t2_3
3566 (21 rows)
3568 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;
3569 a | c | a | c
3570 ---+------+---+------
3571 1 | 0001 | |
3572 3 | 0003 | 3 | 0003
3573 4 | 0004 | 4 | 0004
3574 6 | 0006 | 6 | 0006
3575 8 | 0008 | |
3576 9 | 0009 | 9 | 0009
3577 | | 2 | 0002
3578 | | 7 | 0007
3579 (8 rows)
3581 -- Test cases where one side has an extra partition
3582 CREATE TABLE plt2_adv_extra PARTITION OF plt2_adv FOR VALUES IN ('0000');
3583 INSERT INTO plt2_adv_extra VALUES (0, 0, '0000');
3584 ANALYZE plt2_adv;
3585 -- inner join
3586 EXPLAIN (COSTS OFF)
3587 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;
3588 QUERY PLAN
3589 --------------------------------------------------------------------
3590 Sort
3591 Sort Key: t1.a
3592 -> Append
3593 -> Hash Join
3594 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3595 -> Seq Scan on plt2_adv_p1 t2_1
3596 -> Hash
3597 -> Seq Scan on plt1_adv_p1 t1_1
3598 Filter: (b < 10)
3599 -> Hash Join
3600 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3601 -> Seq Scan on plt2_adv_p2 t2_2
3602 -> Hash
3603 -> Seq Scan on plt1_adv_p2 t1_2
3604 Filter: (b < 10)
3605 -> Hash Join
3606 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3607 -> Seq Scan on plt2_adv_p3 t2_3
3608 -> Hash
3609 -> Seq Scan on plt1_adv_p3 t1_3
3610 Filter: (b < 10)
3611 (21 rows)
3613 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;
3614 a | c | a | c
3615 ---+------+---+------
3616 3 | 0003 | 3 | 0003
3617 4 | 0004 | 4 | 0004
3618 6 | 0006 | 6 | 0006
3619 9 | 0009 | 9 | 0009
3620 (4 rows)
3622 -- semi join
3623 EXPLAIN (COSTS OFF)
3624 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;
3625 QUERY PLAN
3626 ----------------------------------------------------------------------
3627 Sort
3628 Sort Key: t1.a
3629 -> Append
3630 -> Nested Loop Semi Join
3631 Join Filter: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
3632 -> Seq Scan on plt1_adv_p1 t1_1
3633 Filter: (b < 10)
3634 -> Seq Scan on plt2_adv_p1 t2_1
3635 -> Nested Loop Semi Join
3636 Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
3637 -> Seq Scan on plt1_adv_p2 t1_2
3638 Filter: (b < 10)
3639 -> Seq Scan on plt2_adv_p2 t2_2
3640 -> Nested Loop Semi Join
3641 Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
3642 -> Seq Scan on plt1_adv_p3 t1_3
3643 Filter: (b < 10)
3644 -> Seq Scan on plt2_adv_p3 t2_3
3645 (18 rows)
3647 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;
3648 a | b | c
3649 ---+---+------
3650 3 | 3 | 0003
3651 4 | 4 | 0004
3652 6 | 6 | 0006
3653 9 | 9 | 0009
3654 (4 rows)
3656 -- left join
3657 EXPLAIN (COSTS OFF)
3658 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;
3659 QUERY PLAN
3660 --------------------------------------------------------------------
3661 Sort
3662 Sort Key: t1.a
3663 -> Append
3664 -> Hash Right Join
3665 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3666 -> Seq Scan on plt2_adv_p1 t2_1
3667 -> Hash
3668 -> Seq Scan on plt1_adv_p1 t1_1
3669 Filter: (b < 10)
3670 -> Hash Right Join
3671 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3672 -> Seq Scan on plt2_adv_p2 t2_2
3673 -> Hash
3674 -> Seq Scan on plt1_adv_p2 t1_2
3675 Filter: (b < 10)
3676 -> Hash Right Join
3677 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3678 -> Seq Scan on plt2_adv_p3 t2_3
3679 -> Hash
3680 -> Seq Scan on plt1_adv_p3 t1_3
3681 Filter: (b < 10)
3682 (21 rows)
3684 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;
3685 a | c | a | c
3686 ---+------+---+------
3687 1 | 0001 | |
3688 3 | 0003 | 3 | 0003
3689 4 | 0004 | 4 | 0004
3690 6 | 0006 | 6 | 0006
3691 8 | 0008 | |
3692 9 | 0009 | 9 | 0009
3693 (6 rows)
3695 -- left join; currently we can't do partitioned join if there are no matched
3696 -- partitions on the nullable side
3697 EXPLAIN (COSTS OFF)
3698 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;
3699 QUERY PLAN
3700 ---------------------------------------------------------
3701 Sort
3702 Sort Key: t1.a
3703 -> Hash Right Join
3704 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3705 -> Append
3706 -> Seq Scan on plt1_adv_p1 t2_1
3707 -> Seq Scan on plt1_adv_p2 t2_2
3708 -> Seq Scan on plt1_adv_p3 t2_3
3709 -> Hash
3710 -> Append
3711 -> Seq Scan on plt2_adv_extra t1_1
3712 Filter: (b < 10)
3713 -> Seq Scan on plt2_adv_p1 t1_2
3714 Filter: (b < 10)
3715 -> Seq Scan on plt2_adv_p2 t1_3
3716 Filter: (b < 10)
3717 -> Seq Scan on plt2_adv_p3 t1_4
3718 Filter: (b < 10)
3719 (18 rows)
3721 -- anti join
3722 EXPLAIN (COSTS OFF)
3723 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;
3724 QUERY PLAN
3725 --------------------------------------------------------------------
3726 Sort
3727 Sort Key: t1.a
3728 -> Append
3729 -> Hash Right Anti Join
3730 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3731 -> Seq Scan on plt2_adv_p1 t2_1
3732 -> Hash
3733 -> Seq Scan on plt1_adv_p1 t1_1
3734 Filter: (b < 10)
3735 -> Hash Right Anti Join
3736 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3737 -> Seq Scan on plt2_adv_p2 t2_2
3738 -> Hash
3739 -> Seq Scan on plt1_adv_p2 t1_2
3740 Filter: (b < 10)
3741 -> Hash Right Anti Join
3742 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3743 -> Seq Scan on plt2_adv_p3 t2_3
3744 -> Hash
3745 -> Seq Scan on plt1_adv_p3 t1_3
3746 Filter: (b < 10)
3747 (21 rows)
3749 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;
3750 a | b | c
3751 ---+---+------
3752 1 | 1 | 0001
3753 8 | 8 | 0008
3754 (2 rows)
3756 -- anti join; currently we can't do partitioned join if there are no matched
3757 -- partitions on the nullable side
3758 EXPLAIN (COSTS OFF)
3759 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;
3760 QUERY PLAN
3761 ---------------------------------------------------------
3762 Sort
3763 Sort Key: t1.a
3764 -> Hash Right Anti Join
3765 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3766 -> Append
3767 -> Seq Scan on plt1_adv_p1 t2_1
3768 -> Seq Scan on plt1_adv_p2 t2_2
3769 -> Seq Scan on plt1_adv_p3 t2_3
3770 -> Hash
3771 -> Append
3772 -> Seq Scan on plt2_adv_extra t1_1
3773 Filter: (b < 10)
3774 -> Seq Scan on plt2_adv_p1 t1_2
3775 Filter: (b < 10)
3776 -> Seq Scan on plt2_adv_p2 t1_3
3777 Filter: (b < 10)
3778 -> Seq Scan on plt2_adv_p3 t1_4
3779 Filter: (b < 10)
3780 (18 rows)
3782 -- full join; currently we can't do partitioned join if there are no matched
3783 -- partitions on the nullable side
3784 EXPLAIN (COSTS OFF)
3785 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;
3786 QUERY PLAN
3787 -------------------------------------------------------------------------
3788 Sort
3789 Sort Key: t1.a, t2.a
3790 -> Hash Full Join
3791 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3792 Filter: ((COALESCE(t1.b, 0) < 10) AND (COALESCE(t2.b, 0) < 10))
3793 -> Append
3794 -> Seq Scan on plt2_adv_extra t2_1
3795 -> Seq Scan on plt2_adv_p1 t2_2
3796 -> Seq Scan on plt2_adv_p2 t2_3
3797 -> Seq Scan on plt2_adv_p3 t2_4
3798 -> Hash
3799 -> Append
3800 -> Seq Scan on plt1_adv_p1 t1_1
3801 -> Seq Scan on plt1_adv_p2 t1_2
3802 -> Seq Scan on plt1_adv_p3 t1_3
3803 (15 rows)
3805 DROP TABLE plt2_adv_extra;
3806 -- Test cases where a partition on one side matches multiple partitions on
3807 -- the other side; we currently can't do partitioned join in such cases
3808 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p2;
3809 -- Split plt2_adv_p2 into two partitions so that plt1_adv_p2 matches both
3810 CREATE TABLE plt2_adv_p2_1 PARTITION OF plt2_adv FOR VALUES IN ('0004');
3811 CREATE TABLE plt2_adv_p2_2 PARTITION OF plt2_adv FOR VALUES IN ('0006');
3812 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (4, 6);
3813 ANALYZE plt2_adv;
3814 -- inner join
3815 EXPLAIN (COSTS OFF)
3816 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;
3817 QUERY PLAN
3818 ------------------------------------------------------
3819 Sort
3820 Sort Key: t1.a
3821 -> Hash Join
3822 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3823 -> Append
3824 -> Seq Scan on plt2_adv_p1 t2_1
3825 -> Seq Scan on plt2_adv_p2_1 t2_2
3826 -> Seq Scan on plt2_adv_p2_2 t2_3
3827 -> Seq Scan on plt2_adv_p3 t2_4
3828 -> Hash
3829 -> Append
3830 -> Seq Scan on plt1_adv_p1 t1_1
3831 Filter: (b < 10)
3832 -> Seq Scan on plt1_adv_p2 t1_2
3833 Filter: (b < 10)
3834 -> Seq Scan on plt1_adv_p3 t1_3
3835 Filter: (b < 10)
3836 (17 rows)
3838 -- semi join
3839 EXPLAIN (COSTS OFF)
3840 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;
3841 QUERY PLAN
3842 --------------------------------------------------------
3843 Sort
3844 Sort Key: t1.a
3845 -> Hash Semi Join
3846 Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
3847 -> Append
3848 -> Seq Scan on plt1_adv_p1 t1_1
3849 Filter: (b < 10)
3850 -> Seq Scan on plt1_adv_p2 t1_2
3851 Filter: (b < 10)
3852 -> Seq Scan on plt1_adv_p3 t1_3
3853 Filter: (b < 10)
3854 -> Hash
3855 -> Append
3856 -> Seq Scan on plt2_adv_p1 t2_1
3857 -> Seq Scan on plt2_adv_p2_1 t2_2
3858 -> Seq Scan on plt2_adv_p2_2 t2_3
3859 -> Seq Scan on plt2_adv_p3 t2_4
3860 (17 rows)
3862 -- left join
3863 EXPLAIN (COSTS OFF)
3864 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;
3865 QUERY PLAN
3866 ------------------------------------------------------
3867 Sort
3868 Sort Key: t1.a
3869 -> Hash Right Join
3870 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3871 -> Append
3872 -> Seq Scan on plt2_adv_p1 t2_1
3873 -> Seq Scan on plt2_adv_p2_1 t2_2
3874 -> Seq Scan on plt2_adv_p2_2 t2_3
3875 -> Seq Scan on plt2_adv_p3 t2_4
3876 -> Hash
3877 -> Append
3878 -> Seq Scan on plt1_adv_p1 t1_1
3879 Filter: (b < 10)
3880 -> Seq Scan on plt1_adv_p2 t1_2
3881 Filter: (b < 10)
3882 -> Seq Scan on plt1_adv_p3 t1_3
3883 Filter: (b < 10)
3884 (17 rows)
3886 -- anti join
3887 EXPLAIN (COSTS OFF)
3888 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;
3889 QUERY PLAN
3890 ------------------------------------------------------
3891 Sort
3892 Sort Key: t1.a
3893 -> Hash Right Anti Join
3894 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3895 -> Append
3896 -> Seq Scan on plt2_adv_p1 t2_1
3897 -> Seq Scan on plt2_adv_p2_1 t2_2
3898 -> Seq Scan on plt2_adv_p2_2 t2_3
3899 -> Seq Scan on plt2_adv_p3 t2_4
3900 -> Hash
3901 -> Append
3902 -> Seq Scan on plt1_adv_p1 t1_1
3903 Filter: (b < 10)
3904 -> Seq Scan on plt1_adv_p2 t1_2
3905 Filter: (b < 10)
3906 -> Seq Scan on plt1_adv_p3 t1_3
3907 Filter: (b < 10)
3908 (17 rows)
3910 -- full join
3911 EXPLAIN (COSTS OFF)
3912 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;
3913 QUERY PLAN
3914 -------------------------------------------------------------------------
3915 Sort
3916 Sort Key: t1.a, t2.a
3917 -> Hash Full Join
3918 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
3919 Filter: ((COALESCE(t1.b, 0) < 10) AND (COALESCE(t2.b, 0) < 10))
3920 -> Append
3921 -> Seq Scan on plt2_adv_p1 t2_1
3922 -> Seq Scan on plt2_adv_p2_1 t2_2
3923 -> Seq Scan on plt2_adv_p2_2 t2_3
3924 -> Seq Scan on plt2_adv_p3 t2_4
3925 -> Hash
3926 -> Append
3927 -> Seq Scan on plt1_adv_p1 t1_1
3928 -> Seq Scan on plt1_adv_p2 t1_2
3929 -> Seq Scan on plt1_adv_p3 t1_3
3930 (15 rows)
3932 DROP TABLE plt2_adv_p2_1;
3933 DROP TABLE plt2_adv_p2_2;
3934 -- Restore plt2_adv_p2
3935 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p2 FOR VALUES IN ('0004', '0006');
3936 -- Test NULL partitions
3937 ALTER TABLE plt1_adv DETACH PARTITION plt1_adv_p1;
3938 -- Change plt1_adv_p1 to the NULL partition
3939 CREATE TABLE plt1_adv_p1_null PARTITION OF plt1_adv FOR VALUES IN (NULL, '0001', '0003');
3940 INSERT INTO plt1_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 3);
3941 INSERT INTO plt1_adv VALUES (-1, -1, NULL);
3942 ANALYZE plt1_adv;
3943 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p3;
3944 -- Change plt2_adv_p3 to the NULL partition
3945 CREATE TABLE plt2_adv_p3_null PARTITION OF plt2_adv FOR VALUES IN (NULL, '0007', '0009');
3946 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (7, 9);
3947 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
3948 ANALYZE plt2_adv;
3949 -- inner join
3950 EXPLAIN (COSTS OFF)
3951 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;
3952 QUERY PLAN
3953 --------------------------------------------------------------------
3954 Sort
3955 Sort Key: t1.a
3956 -> Append
3957 -> Hash Join
3958 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
3959 -> Seq Scan on plt2_adv_p1 t2_1
3960 -> Hash
3961 -> Seq Scan on plt1_adv_p1_null t1_1
3962 Filter: (b < 10)
3963 -> Hash Join
3964 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
3965 -> Seq Scan on plt2_adv_p2 t2_2
3966 -> Hash
3967 -> Seq Scan on plt1_adv_p2 t1_2
3968 Filter: (b < 10)
3969 -> Hash Join
3970 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
3971 -> Seq Scan on plt2_adv_p3_null t2_3
3972 -> Hash
3973 -> Seq Scan on plt1_adv_p3 t1_3
3974 Filter: (b < 10)
3975 (21 rows)
3977 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;
3978 a | c | a | c
3979 ---+------+---+------
3980 3 | 0003 | 3 | 0003
3981 4 | 0004 | 4 | 0004
3982 6 | 0006 | 6 | 0006
3983 9 | 0009 | 9 | 0009
3984 (4 rows)
3986 -- semi join
3987 EXPLAIN (COSTS OFF)
3988 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;
3989 QUERY PLAN
3990 ----------------------------------------------------------------------
3991 Sort
3992 Sort Key: t1.a
3993 -> Append
3994 -> Hash Semi Join
3995 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
3996 -> Seq Scan on plt1_adv_p1_null t1_1
3997 Filter: (b < 10)
3998 -> Hash
3999 -> Seq Scan on plt2_adv_p1 t2_1
4000 -> Nested Loop Semi Join
4001 Join Filter: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
4002 -> Seq Scan on plt1_adv_p2 t1_2
4003 Filter: (b < 10)
4004 -> Seq Scan on plt2_adv_p2 t2_2
4005 -> Nested Loop Semi Join
4006 Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
4007 -> Seq Scan on plt1_adv_p3 t1_3
4008 Filter: (b < 10)
4009 -> Seq Scan on plt2_adv_p3_null t2_3
4010 (19 rows)
4012 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;
4013 a | b | c
4014 ---+---+------
4015 3 | 3 | 0003
4016 4 | 4 | 0004
4017 6 | 6 | 0006
4018 9 | 9 | 0009
4019 (4 rows)
4021 -- left join
4022 EXPLAIN (COSTS OFF)
4023 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;
4024 QUERY PLAN
4025 --------------------------------------------------------------------
4026 Sort
4027 Sort Key: t1.a
4028 -> Append
4029 -> Hash Right Join
4030 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4031 -> Seq Scan on plt2_adv_p1 t2_1
4032 -> Hash
4033 -> Seq Scan on plt1_adv_p1_null t1_1
4034 Filter: (b < 10)
4035 -> Hash Right Join
4036 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4037 -> Seq Scan on plt2_adv_p2 t2_2
4038 -> Hash
4039 -> Seq Scan on plt1_adv_p2 t1_2
4040 Filter: (b < 10)
4041 -> Hash Right Join
4042 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4043 -> Seq Scan on plt2_adv_p3_null t2_3
4044 -> Hash
4045 -> Seq Scan on plt1_adv_p3 t1_3
4046 Filter: (b < 10)
4047 (21 rows)
4049 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;
4050 a | c | a | c
4051 ----+------+---+------
4052 -1 | | |
4053 1 | 0001 | |
4054 3 | 0003 | 3 | 0003
4055 4 | 0004 | 4 | 0004
4056 6 | 0006 | 6 | 0006
4057 8 | 0008 | |
4058 9 | 0009 | 9 | 0009
4059 (7 rows)
4061 -- anti join
4062 EXPLAIN (COSTS OFF)
4063 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;
4064 QUERY PLAN
4065 --------------------------------------------------------------------
4066 Sort
4067 Sort Key: t1.a
4068 -> Append
4069 -> Hash Right Anti Join
4070 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4071 -> Seq Scan on plt2_adv_p1 t2_1
4072 -> Hash
4073 -> Seq Scan on plt1_adv_p1_null t1_1
4074 Filter: (b < 10)
4075 -> Hash Right Anti Join
4076 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4077 -> Seq Scan on plt2_adv_p2 t2_2
4078 -> Hash
4079 -> Seq Scan on plt1_adv_p2 t1_2
4080 Filter: (b < 10)
4081 -> Hash Right Anti Join
4082 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4083 -> Seq Scan on plt2_adv_p3_null t2_3
4084 -> Hash
4085 -> Seq Scan on plt1_adv_p3 t1_3
4086 Filter: (b < 10)
4087 (21 rows)
4089 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;
4090 a | b | c
4091 ----+----+------
4092 -1 | -1 |
4093 1 | 1 | 0001
4094 8 | 8 | 0008
4095 (3 rows)
4097 -- full join
4098 EXPLAIN (COSTS OFF)
4099 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;
4100 QUERY PLAN
4101 -----------------------------------------------------------------------------------
4102 Sort
4103 Sort Key: t1.a, t2.a
4104 -> Append
4105 -> Hash Full Join
4106 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
4107 Filter: ((COALESCE(t1_1.b, 0) < 10) AND (COALESCE(t2_1.b, 0) < 10))
4108 -> Seq Scan on plt1_adv_p1_null t1_1
4109 -> Hash
4110 -> Seq Scan on plt2_adv_p1 t2_1
4111 -> Hash Full Join
4112 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
4113 Filter: ((COALESCE(t1_2.b, 0) < 10) AND (COALESCE(t2_2.b, 0) < 10))
4114 -> Seq Scan on plt1_adv_p2 t1_2
4115 -> Hash
4116 -> Seq Scan on plt2_adv_p2 t2_2
4117 -> Hash Full Join
4118 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4119 Filter: ((COALESCE(t1_3.b, 0) < 10) AND (COALESCE(t2_3.b, 0) < 10))
4120 -> Seq Scan on plt2_adv_p3_null t2_3
4121 -> Hash
4122 -> Seq Scan on plt1_adv_p3 t1_3
4123 (21 rows)
4125 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;
4126 a | c | a | c
4127 ----+------+----+------
4128 -1 | | |
4129 1 | 0001 | |
4130 3 | 0003 | 3 | 0003
4131 4 | 0004 | 4 | 0004
4132 6 | 0006 | 6 | 0006
4133 8 | 0008 | |
4134 9 | 0009 | 9 | 0009
4135 | | -1 |
4136 | | 2 | 0002
4137 | | 7 | 0007
4138 (10 rows)
4140 DROP TABLE plt1_adv_p1_null;
4141 -- Restore plt1_adv_p1
4142 ALTER TABLE plt1_adv ATTACH PARTITION plt1_adv_p1 FOR VALUES IN ('0001', '0003');
4143 -- Add to plt1_adv the extra NULL partition containing only NULL values as the
4144 -- key values
4145 CREATE TABLE plt1_adv_extra PARTITION OF plt1_adv FOR VALUES IN (NULL);
4146 INSERT INTO plt1_adv VALUES (-1, -1, NULL);
4147 ANALYZE plt1_adv;
4148 DROP TABLE plt2_adv_p3_null;
4149 -- Restore plt2_adv_p3
4150 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p3 FOR VALUES IN ('0007', '0009');
4151 ANALYZE plt2_adv;
4152 -- inner join
4153 EXPLAIN (COSTS OFF)
4154 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;
4155 QUERY PLAN
4156 --------------------------------------------------------------------
4157 Sort
4158 Sort Key: t1.a
4159 -> Append
4160 -> Hash Join
4161 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4162 -> Seq Scan on plt2_adv_p1 t2_1
4163 -> Hash
4164 -> Seq Scan on plt1_adv_p1 t1_1
4165 Filter: (b < 10)
4166 -> Hash Join
4167 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4168 -> Seq Scan on plt2_adv_p2 t2_2
4169 -> Hash
4170 -> Seq Scan on plt1_adv_p2 t1_2
4171 Filter: (b < 10)
4172 -> Hash Join
4173 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4174 -> Seq Scan on plt2_adv_p3 t2_3
4175 -> Hash
4176 -> Seq Scan on plt1_adv_p3 t1_3
4177 Filter: (b < 10)
4178 (21 rows)
4180 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;
4181 a | c | a | c
4182 ---+------+---+------
4183 3 | 0003 | 3 | 0003
4184 4 | 0004 | 4 | 0004
4185 6 | 0006 | 6 | 0006
4186 9 | 0009 | 9 | 0009
4187 (4 rows)
4189 -- left join; currently we can't do partitioned join if there are no matched
4190 -- partitions on the nullable side
4191 EXPLAIN (COSTS OFF)
4192 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;
4193 QUERY PLAN
4194 ---------------------------------------------------------
4195 Sort
4196 Sort Key: t1.a
4197 -> Hash Right Join
4198 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4199 -> Append
4200 -> Seq Scan on plt2_adv_p1 t2_1
4201 -> Seq Scan on plt2_adv_p2 t2_2
4202 -> Seq Scan on plt2_adv_p3 t2_3
4203 -> Hash
4204 -> Append
4205 -> Seq Scan on plt1_adv_p1 t1_1
4206 Filter: (b < 10)
4207 -> Seq Scan on plt1_adv_p2 t1_2
4208 Filter: (b < 10)
4209 -> Seq Scan on plt1_adv_p3 t1_3
4210 Filter: (b < 10)
4211 -> Seq Scan on plt1_adv_extra t1_4
4212 Filter: (b < 10)
4213 (18 rows)
4215 -- full join; currently we can't do partitioned join if there are no matched
4216 -- partitions on the nullable side
4217 EXPLAIN (COSTS OFF)
4218 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;
4219 QUERY PLAN
4220 -------------------------------------------------------------------------
4221 Sort
4222 Sort Key: t1.a, t2.a
4223 -> Hash Full Join
4224 Hash Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
4225 Filter: ((COALESCE(t1.b, 0) < 10) AND (COALESCE(t2.b, 0) < 10))
4226 -> Append
4227 -> Seq Scan on plt1_adv_p1 t1_1
4228 -> Seq Scan on plt1_adv_p2 t1_2
4229 -> Seq Scan on plt1_adv_p3 t1_3
4230 -> Seq Scan on plt1_adv_extra t1_4
4231 -> Hash
4232 -> Append
4233 -> Seq Scan on plt2_adv_p1 t2_1
4234 -> Seq Scan on plt2_adv_p2 t2_2
4235 -> Seq Scan on plt2_adv_p3 t2_3
4236 (15 rows)
4238 -- Add to plt2_adv the extra NULL partition containing only NULL values as the
4239 -- key values
4240 CREATE TABLE plt2_adv_extra PARTITION OF plt2_adv FOR VALUES IN (NULL);
4241 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4242 ANALYZE plt2_adv;
4243 -- inner join
4244 EXPLAIN (COSTS OFF)
4245 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;
4246 QUERY PLAN
4247 --------------------------------------------------------------------
4248 Sort
4249 Sort Key: t1.a
4250 -> Append
4251 -> Hash Join
4252 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4253 -> Seq Scan on plt2_adv_p1 t2_1
4254 -> Hash
4255 -> Seq Scan on plt1_adv_p1 t1_1
4256 Filter: (b < 10)
4257 -> Hash Join
4258 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4259 -> Seq Scan on plt2_adv_p2 t2_2
4260 -> Hash
4261 -> Seq Scan on plt1_adv_p2 t1_2
4262 Filter: (b < 10)
4263 -> Hash Join
4264 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4265 -> Seq Scan on plt2_adv_p3 t2_3
4266 -> Hash
4267 -> Seq Scan on plt1_adv_p3 t1_3
4268 Filter: (b < 10)
4269 (21 rows)
4271 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;
4272 a | c | a | c
4273 ---+------+---+------
4274 3 | 0003 | 3 | 0003
4275 4 | 0004 | 4 | 0004
4276 6 | 0006 | 6 | 0006
4277 9 | 0009 | 9 | 0009
4278 (4 rows)
4280 -- left join
4281 EXPLAIN (COSTS OFF)
4282 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;
4283 QUERY PLAN
4284 ----------------------------------------------------------------------
4285 Sort
4286 Sort Key: t1.a
4287 -> Append
4288 -> Hash Right Join
4289 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4290 -> Seq Scan on plt2_adv_p1 t2_1
4291 -> Hash
4292 -> Seq Scan on plt1_adv_p1 t1_1
4293 Filter: (b < 10)
4294 -> Hash Right Join
4295 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4296 -> Seq Scan on plt2_adv_p2 t2_2
4297 -> Hash
4298 -> Seq Scan on plt1_adv_p2 t1_2
4299 Filter: (b < 10)
4300 -> Hash Right Join
4301 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4302 -> Seq Scan on plt2_adv_p3 t2_3
4303 -> Hash
4304 -> Seq Scan on plt1_adv_p3 t1_3
4305 Filter: (b < 10)
4306 -> Nested Loop Left Join
4307 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
4308 -> Seq Scan on plt1_adv_extra t1_4
4309 Filter: (b < 10)
4310 -> Seq Scan on plt2_adv_extra t2_4
4311 (26 rows)
4313 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;
4314 a | c | a | c
4315 ----+------+---+------
4316 -1 | | |
4317 1 | 0001 | |
4318 3 | 0003 | 3 | 0003
4319 4 | 0004 | 4 | 0004
4320 6 | 0006 | 6 | 0006
4321 8 | 0008 | |
4322 9 | 0009 | 9 | 0009
4323 (7 rows)
4325 -- full join
4326 EXPLAIN (COSTS OFF)
4327 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;
4328 QUERY PLAN
4329 -----------------------------------------------------------------------------------
4330 Sort
4331 Sort Key: t1.a, t2.a
4332 -> Append
4333 -> Hash Full Join
4334 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.c = t2_1.c))
4335 Filter: ((COALESCE(t1_1.b, 0) < 10) AND (COALESCE(t2_1.b, 0) < 10))
4336 -> Seq Scan on plt1_adv_p1 t1_1
4337 -> Hash
4338 -> Seq Scan on plt2_adv_p1 t2_1
4339 -> Hash Full Join
4340 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
4341 Filter: ((COALESCE(t1_2.b, 0) < 10) AND (COALESCE(t2_2.b, 0) < 10))
4342 -> Seq Scan on plt1_adv_p2 t1_2
4343 -> Hash
4344 -> Seq Scan on plt2_adv_p2 t2_2
4345 -> Hash Full Join
4346 Hash Cond: ((t1_3.a = t2_3.a) AND (t1_3.c = t2_3.c))
4347 Filter: ((COALESCE(t1_3.b, 0) < 10) AND (COALESCE(t2_3.b, 0) < 10))
4348 -> Seq Scan on plt1_adv_p3 t1_3
4349 -> Hash
4350 -> Seq Scan on plt2_adv_p3 t2_3
4351 -> Hash Full Join
4352 Hash Cond: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
4353 Filter: ((COALESCE(t1_4.b, 0) < 10) AND (COALESCE(t2_4.b, 0) < 10))
4354 -> Seq Scan on plt1_adv_extra t1_4
4355 -> Hash
4356 -> Seq Scan on plt2_adv_extra t2_4
4357 (27 rows)
4359 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;
4360 a | c | a | c
4361 ----+------+----+------
4362 -1 | | |
4363 1 | 0001 | |
4364 3 | 0003 | 3 | 0003
4365 4 | 0004 | 4 | 0004
4366 6 | 0006 | 6 | 0006
4367 8 | 0008 | |
4368 9 | 0009 | 9 | 0009
4369 | | -1 |
4370 | | 2 | 0002
4371 | | 7 | 0007
4372 (10 rows)
4374 -- 3-way join to test the NULL partition of a join relation
4375 EXPLAIN (COSTS OFF)
4376 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;
4377 QUERY PLAN
4378 --------------------------------------------------------------------------------
4379 Sort
4380 Sort Key: t1.a
4381 -> Append
4382 -> Hash Right Join
4383 Hash Cond: ((t3_1.a = t1_1.a) AND (t3_1.c = t1_1.c))
4384 -> Seq Scan on plt1_adv_p1 t3_1
4385 -> Hash
4386 -> Hash Right Join
4387 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4388 -> Seq Scan on plt2_adv_p1 t2_1
4389 -> Hash
4390 -> Seq Scan on plt1_adv_p1 t1_1
4391 Filter: (b < 10)
4392 -> Hash Right Join
4393 Hash Cond: ((t3_2.a = t1_2.a) AND (t3_2.c = t1_2.c))
4394 -> Seq Scan on plt1_adv_p2 t3_2
4395 -> Hash
4396 -> Hash Right Join
4397 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4398 -> Seq Scan on plt2_adv_p2 t2_2
4399 -> Hash
4400 -> Seq Scan on plt1_adv_p2 t1_2
4401 Filter: (b < 10)
4402 -> Hash Right Join
4403 Hash Cond: ((t3_3.a = t1_3.a) AND (t3_3.c = t1_3.c))
4404 -> Seq Scan on plt1_adv_p3 t3_3
4405 -> Hash
4406 -> Hash Right Join
4407 Hash Cond: ((t2_3.a = t1_3.a) AND (t2_3.c = t1_3.c))
4408 -> Seq Scan on plt2_adv_p3 t2_3
4409 -> Hash
4410 -> Seq Scan on plt1_adv_p3 t1_3
4411 Filter: (b < 10)
4412 -> Nested Loop Left Join
4413 Join Filter: ((t1_4.a = t3_4.a) AND (t1_4.c = t3_4.c))
4414 -> Nested Loop Left Join
4415 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
4416 -> Seq Scan on plt1_adv_extra t1_4
4417 Filter: (b < 10)
4418 -> Seq Scan on plt2_adv_extra t2_4
4419 -> Seq Scan on plt1_adv_extra t3_4
4420 (41 rows)
4422 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;
4423 a | c | a | c | a | c
4424 ----+------+---+------+---+------
4425 -1 | | | | |
4426 1 | 0001 | | | 1 | 0001
4427 3 | 0003 | 3 | 0003 | 3 | 0003
4428 4 | 0004 | 4 | 0004 | 4 | 0004
4429 6 | 0006 | 6 | 0006 | 6 | 0006
4430 8 | 0008 | | | 8 | 0008
4431 9 | 0009 | 9 | 0009 | 9 | 0009
4432 (7 rows)
4434 DROP TABLE plt1_adv_extra;
4435 DROP TABLE plt2_adv_extra;
4436 -- Test default partitions
4437 ALTER TABLE plt1_adv DETACH PARTITION plt1_adv_p1;
4438 -- Change plt1_adv_p1 to the default partition
4439 ALTER TABLE plt1_adv ATTACH PARTITION plt1_adv_p1 DEFAULT;
4440 DROP TABLE plt1_adv_p3;
4441 ANALYZE plt1_adv;
4442 DROP TABLE plt2_adv_p3;
4443 ANALYZE plt2_adv;
4444 -- We can do partitioned join even if only one of relations has the default
4445 -- partition
4446 EXPLAIN (COSTS OFF)
4447 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;
4448 QUERY PLAN
4449 --------------------------------------------------------------------
4450 Sort
4451 Sort Key: t1.a
4452 -> Append
4453 -> Hash Join
4454 Hash Cond: ((t2_1.a = t1_2.a) AND (t2_1.c = t1_2.c))
4455 -> Seq Scan on plt2_adv_p1 t2_1
4456 -> Hash
4457 -> Seq Scan on plt1_adv_p1 t1_2
4458 Filter: (b < 10)
4459 -> Hash Join
4460 Hash Cond: ((t2_2.a = t1_1.a) AND (t2_2.c = t1_1.c))
4461 -> Seq Scan on plt2_adv_p2 t2_2
4462 -> Hash
4463 -> Seq Scan on plt1_adv_p2 t1_1
4464 Filter: (b < 10)
4465 (15 rows)
4467 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;
4468 a | c | a | c
4469 ---+------+---+------
4470 3 | 0003 | 3 | 0003
4471 4 | 0004 | 4 | 0004
4472 6 | 0006 | 6 | 0006
4473 (3 rows)
4475 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p2;
4476 -- Change plt2_adv_p2 to contain '0005' in addition to '0004' and '0006' as
4477 -- the key values
4478 CREATE TABLE plt2_adv_p2_ext PARTITION OF plt2_adv FOR VALUES IN ('0004', '0005', '0006');
4479 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);
4480 ANALYZE plt2_adv;
4481 -- Partitioned join can't be applied because the default partition of plt1_adv
4482 -- matches plt2_adv_p1 and plt2_adv_p2_ext
4483 EXPLAIN (COSTS OFF)
4484 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;
4485 QUERY PLAN
4486 ------------------------------------------------------
4487 Sort
4488 Sort Key: t1.a
4489 -> Hash Join
4490 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4491 -> Append
4492 -> Seq Scan on plt2_adv_p1 t2_1
4493 -> Seq Scan on plt2_adv_p2_ext t2_2
4494 -> Hash
4495 -> Append
4496 -> Seq Scan on plt1_adv_p2 t1_1
4497 Filter: (b < 10)
4498 -> Seq Scan on plt1_adv_p1 t1_2
4499 Filter: (b < 10)
4500 (13 rows)
4502 ALTER TABLE plt2_adv DETACH PARTITION plt2_adv_p2_ext;
4503 -- Change plt2_adv_p2_ext to the default partition
4504 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p2_ext DEFAULT;
4505 ANALYZE plt2_adv;
4506 -- Partitioned join can't be applied because the default partition of plt1_adv
4507 -- matches plt2_adv_p1 and plt2_adv_p2_ext
4508 EXPLAIN (COSTS OFF)
4509 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;
4510 QUERY PLAN
4511 ------------------------------------------------------
4512 Sort
4513 Sort Key: t1.a
4514 -> Hash Join
4515 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4516 -> Append
4517 -> Seq Scan on plt2_adv_p1 t2_1
4518 -> Seq Scan on plt2_adv_p2_ext t2_2
4519 -> Hash
4520 -> Append
4521 -> Seq Scan on plt1_adv_p2 t1_1
4522 Filter: (b < 10)
4523 -> Seq Scan on plt1_adv_p1 t1_2
4524 Filter: (b < 10)
4525 (13 rows)
4527 DROP TABLE plt2_adv_p2_ext;
4528 -- Restore plt2_adv_p2
4529 ALTER TABLE plt2_adv ATTACH PARTITION plt2_adv_p2 FOR VALUES IN ('0004', '0006');
4530 ANALYZE plt2_adv;
4531 CREATE TABLE plt3_adv (a int, b int, c text) PARTITION BY LIST (c);
4532 CREATE TABLE plt3_adv_p1 PARTITION OF plt3_adv FOR VALUES IN ('0004', '0006');
4533 CREATE TABLE plt3_adv_p2 PARTITION OF plt3_adv FOR VALUES IN ('0007', '0009');
4534 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);
4535 ANALYZE plt3_adv;
4536 -- 3-way join to test the default partition of a join relation
4537 EXPLAIN (COSTS OFF)
4538 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;
4539 QUERY PLAN
4540 --------------------------------------------------------------------------------
4541 Sort
4542 Sort Key: t1.a
4543 -> Append
4544 -> Hash Right Join
4545 Hash Cond: ((t3_1.a = t1_1.a) AND (t3_1.c = t1_1.c))
4546 -> Seq Scan on plt3_adv_p1 t3_1
4547 -> Hash
4548 -> Hash Right Join
4549 Hash Cond: ((t2_2.a = t1_1.a) AND (t2_2.c = t1_1.c))
4550 -> Seq Scan on plt2_adv_p2 t2_2
4551 -> Hash
4552 -> Seq Scan on plt1_adv_p2 t1_1
4553 Filter: (b < 10)
4554 -> Hash Right Join
4555 Hash Cond: ((t3_2.a = t1_2.a) AND (t3_2.c = t1_2.c))
4556 -> Seq Scan on plt3_adv_p2 t3_2
4557 -> Hash
4558 -> Hash Right Join
4559 Hash Cond: ((t2_1.a = t1_2.a) AND (t2_1.c = t1_2.c))
4560 -> Seq Scan on plt2_adv_p1 t2_1
4561 -> Hash
4562 -> Seq Scan on plt1_adv_p1 t1_2
4563 Filter: (b < 10)
4564 (23 rows)
4566 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;
4567 a | c | a | c | a | c
4568 ---+------+---+------+---+------
4569 1 | 0001 | | | |
4570 3 | 0003 | 3 | 0003 | |
4571 4 | 0004 | 4 | 0004 | 4 | 0004
4572 6 | 0006 | 6 | 0006 | 6 | 0006
4573 (4 rows)
4575 -- Test cases where one side has the default partition while the other side
4576 -- has the NULL partition
4577 DROP TABLE plt2_adv_p1;
4578 -- Add the NULL partition to plt2_adv
4579 CREATE TABLE plt2_adv_p1_null PARTITION OF plt2_adv FOR VALUES IN (NULL, '0001', '0003');
4580 INSERT INTO plt2_adv SELECT i, i, to_char(i % 10, 'FM0000') FROM generate_series(1, 299) i WHERE i % 10 IN (1, 3);
4581 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4582 ANALYZE plt2_adv;
4583 EXPLAIN (COSTS OFF)
4584 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;
4585 QUERY PLAN
4586 --------------------------------------------------------------------
4587 Sort
4588 Sort Key: t1.a
4589 -> Append
4590 -> Hash Join
4591 Hash Cond: ((t2_1.a = t1_2.a) AND (t2_1.c = t1_2.c))
4592 -> Seq Scan on plt2_adv_p1_null t2_1
4593 -> Hash
4594 -> Seq Scan on plt1_adv_p1 t1_2
4595 Filter: (b < 10)
4596 -> Hash Join
4597 Hash Cond: ((t2_2.a = t1_1.a) AND (t2_2.c = t1_1.c))
4598 -> Seq Scan on plt2_adv_p2 t2_2
4599 -> Hash
4600 -> Seq Scan on plt1_adv_p2 t1_1
4601 Filter: (b < 10)
4602 (15 rows)
4604 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;
4605 a | c | a | c
4606 ---+------+---+------
4607 1 | 0001 | 1 | 0001
4608 3 | 0003 | 3 | 0003
4609 4 | 0004 | 4 | 0004
4610 6 | 0006 | 6 | 0006
4611 (4 rows)
4613 DROP TABLE plt2_adv_p1_null;
4614 -- Add the NULL partition that contains only NULL values as the key values
4615 CREATE TABLE plt2_adv_p1_null PARTITION OF plt2_adv FOR VALUES IN (NULL);
4616 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4617 ANALYZE plt2_adv;
4618 EXPLAIN (COSTS OFF)
4619 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;
4620 QUERY PLAN
4621 ------------------------------------------------------
4622 Sort
4623 Sort Key: t1.a
4624 -> Hash Join
4625 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4626 -> Seq Scan on plt2_adv_p2 t2
4627 -> Hash
4628 -> Seq Scan on plt1_adv_p2 t1
4629 Filter: (b < 10)
4630 (8 rows)
4632 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;
4633 a | c | a | c
4634 ---+------+---+------
4635 4 | 0004 | 4 | 0004
4636 6 | 0006 | 6 | 0006
4637 (2 rows)
4639 DROP TABLE plt1_adv;
4640 DROP TABLE plt2_adv;
4641 DROP TABLE plt3_adv;
4642 -- Test interaction of partitioned join with partition pruning
4643 CREATE TABLE plt1_adv (a int, b int, c text) PARTITION BY LIST (c);
4644 CREATE TABLE plt1_adv_p1 PARTITION OF plt1_adv FOR VALUES IN ('0001');
4645 CREATE TABLE plt1_adv_p2 PARTITION OF plt1_adv FOR VALUES IN ('0002');
4646 CREATE TABLE plt1_adv_p3 PARTITION OF plt1_adv FOR VALUES IN ('0003');
4647 CREATE TABLE plt1_adv_p4 PARTITION OF plt1_adv FOR VALUES IN (NULL, '0004', '0005');
4648 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);
4649 INSERT INTO plt1_adv VALUES (-1, -1, NULL);
4650 ANALYZE plt1_adv;
4651 CREATE TABLE plt2_adv (a int, b int, c text) PARTITION BY LIST (c);
4652 CREATE TABLE plt2_adv_p1 PARTITION OF plt2_adv FOR VALUES IN ('0001', '0002');
4653 CREATE TABLE plt2_adv_p2 PARTITION OF plt2_adv FOR VALUES IN (NULL);
4654 CREATE TABLE plt2_adv_p3 PARTITION OF plt2_adv FOR VALUES IN ('0003');
4655 CREATE TABLE plt2_adv_p4 PARTITION OF plt2_adv FOR VALUES IN ('0004', '0005');
4656 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);
4657 INSERT INTO plt2_adv VALUES (-1, -1, NULL);
4658 ANALYZE plt2_adv;
4659 EXPLAIN (COSTS OFF)
4660 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;
4661 QUERY PLAN
4662 -----------------------------------------------------------------------------------------
4663 Sort
4664 Sort Key: t1.a
4665 -> Append
4666 -> Hash Join
4667 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4668 -> Seq Scan on plt2_adv_p3 t2_1
4669 -> Hash
4670 -> Seq Scan on plt1_adv_p3 t1_1
4671 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4672 -> Hash Join
4673 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4674 -> Seq Scan on plt2_adv_p4 t2_2
4675 -> Hash
4676 -> Seq Scan on plt1_adv_p4 t1_2
4677 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4678 (15 rows)
4680 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;
4681 a | c | a | c
4682 ---+------+---+------
4683 3 | 0003 | 3 | 0003
4684 4 | 0004 | 4 | 0004
4685 5 | 0005 | 5 | 0005
4686 (3 rows)
4688 EXPLAIN (COSTS OFF)
4689 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;
4690 QUERY PLAN
4691 --------------------------------------------------------
4692 Sort
4693 Sort Key: t1.a
4694 -> Hash Right Join
4695 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4696 -> Seq Scan on plt2_adv_p4 t2
4697 -> Hash
4698 -> Seq Scan on plt1_adv_p4 t1
4699 Filter: ((c IS NULL) AND (b < 10))
4700 (8 rows)
4702 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;
4703 a | c | a | c
4704 ----+---+---+---
4705 -1 | | |
4706 (1 row)
4708 CREATE TABLE plt1_adv_default PARTITION OF plt1_adv DEFAULT;
4709 ANALYZE plt1_adv;
4710 CREATE TABLE plt2_adv_default PARTITION OF plt2_adv DEFAULT;
4711 ANALYZE plt2_adv;
4712 EXPLAIN (COSTS OFF)
4713 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;
4714 QUERY PLAN
4715 -----------------------------------------------------------------------------------------
4716 Sort
4717 Sort Key: t1.a
4718 -> Append
4719 -> Hash Join
4720 Hash Cond: ((t2_1.a = t1_1.a) AND (t2_1.c = t1_1.c))
4721 -> Seq Scan on plt2_adv_p3 t2_1
4722 -> Hash
4723 -> Seq Scan on plt1_adv_p3 t1_1
4724 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4725 -> Hash Join
4726 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.c = t1_2.c))
4727 -> Seq Scan on plt2_adv_p4 t2_2
4728 -> Hash
4729 -> Seq Scan on plt1_adv_p4 t1_2
4730 Filter: ((b < 10) AND (c = ANY ('{0003,0004,0005}'::text[])))
4731 (15 rows)
4733 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;
4734 a | c | a | c
4735 ---+------+---+------
4736 3 | 0003 | 3 | 0003
4737 4 | 0004 | 4 | 0004
4738 5 | 0005 | 5 | 0005
4739 (3 rows)
4741 EXPLAIN (COSTS OFF)
4742 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;
4743 QUERY PLAN
4744 --------------------------------------------------------
4745 Sort
4746 Sort Key: t1.a
4747 -> Hash Right Join
4748 Hash Cond: ((t2.a = t1.a) AND (t2.c = t1.c))
4749 -> Seq Scan on plt2_adv_p4 t2
4750 -> Hash
4751 -> Seq Scan on plt1_adv_p4 t1
4752 Filter: ((c IS NULL) AND (b < 10))
4753 (8 rows)
4755 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;
4756 a | c | a | c
4757 ----+---+---+---
4758 -1 | | |
4759 (1 row)
4761 DROP TABLE plt1_adv;
4762 DROP TABLE plt2_adv;
4763 -- Test the process_outer_partition() code path
4764 CREATE TABLE plt1_adv (a int, b int, c text) PARTITION BY LIST (c);
4765 CREATE TABLE plt1_adv_p1 PARTITION OF plt1_adv FOR VALUES IN ('0000', '0001', '0002');
4766 CREATE TABLE plt1_adv_p2 PARTITION OF plt1_adv FOR VALUES IN ('0003', '0004');
4767 INSERT INTO plt1_adv SELECT i, i, to_char(i % 5, 'FM0000') FROM generate_series(0, 24) i;
4768 ANALYZE plt1_adv;
4769 CREATE TABLE plt2_adv (a int, b int, c text) PARTITION BY LIST (c);
4770 CREATE TABLE plt2_adv_p1 PARTITION OF plt2_adv FOR VALUES IN ('0002');
4771 CREATE TABLE plt2_adv_p2 PARTITION OF plt2_adv FOR VALUES IN ('0003', '0004');
4772 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);
4773 ANALYZE plt2_adv;
4774 CREATE TABLE plt3_adv (a int, b int, c text) PARTITION BY LIST (c);
4775 CREATE TABLE plt3_adv_p1 PARTITION OF plt3_adv FOR VALUES IN ('0001');
4776 CREATE TABLE plt3_adv_p2 PARTITION OF plt3_adv FOR VALUES IN ('0003', '0004');
4777 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);
4778 ANALYZE plt3_adv;
4779 -- This tests that when merging partitions from plt1_adv and plt2_adv in
4780 -- merge_list_bounds(), process_outer_partition() returns an already-assigned
4781 -- merged partition when re-called with plt1_adv_p1 for the second list value
4782 -- '0001' of that partition
4783 EXPLAIN (COSTS OFF)
4784 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;
4785 QUERY PLAN
4786 -----------------------------------------------------------------------------------------------
4787 Sort
4788 Sort Key: t1.c, t1.a, t2.a, t3.a
4789 -> Append
4790 -> Hash Full Join
4791 Hash Cond: (t1_1.c = t3_1.c)
4792 Filter: (((COALESCE(t1_1.a, 0) % 5) <> 3) AND ((COALESCE(t1_1.a, 0) % 5) <> 4))
4793 -> Hash Left Join
4794 Hash Cond: (t1_1.c = t2_1.c)
4795 -> Seq Scan on plt1_adv_p1 t1_1
4796 -> Hash
4797 -> Seq Scan on plt2_adv_p1 t2_1
4798 -> Hash
4799 -> Seq Scan on plt3_adv_p1 t3_1
4800 -> Hash Full Join
4801 Hash Cond: (t1_2.c = t3_2.c)
4802 Filter: (((COALESCE(t1_2.a, 0) % 5) <> 3) AND ((COALESCE(t1_2.a, 0) % 5) <> 4))
4803 -> Hash Left Join
4804 Hash Cond: (t1_2.c = t2_2.c)
4805 -> Seq Scan on plt1_adv_p2 t1_2
4806 -> Hash
4807 -> Seq Scan on plt2_adv_p2 t2_2
4808 -> Hash
4809 -> Seq Scan on plt3_adv_p2 t3_2
4810 (23 rows)
4812 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;
4813 a | c | a | c | a | c
4814 ----+------+----+------+----+------
4815 0 | 0000 | | | |
4816 5 | 0000 | | | |
4817 10 | 0000 | | | |
4818 15 | 0000 | | | |
4819 20 | 0000 | | | |
4820 1 | 0001 | | | 1 | 0001
4821 1 | 0001 | | | 6 | 0001
4822 1 | 0001 | | | 11 | 0001
4823 1 | 0001 | | | 16 | 0001
4824 1 | 0001 | | | 21 | 0001
4825 6 | 0001 | | | 1 | 0001
4826 6 | 0001 | | | 6 | 0001
4827 6 | 0001 | | | 11 | 0001
4828 6 | 0001 | | | 16 | 0001
4829 6 | 0001 | | | 21 | 0001
4830 11 | 0001 | | | 1 | 0001
4831 11 | 0001 | | | 6 | 0001
4832 11 | 0001 | | | 11 | 0001
4833 11 | 0001 | | | 16 | 0001
4834 11 | 0001 | | | 21 | 0001
4835 16 | 0001 | | | 1 | 0001
4836 16 | 0001 | | | 6 | 0001
4837 16 | 0001 | | | 11 | 0001
4838 16 | 0001 | | | 16 | 0001
4839 16 | 0001 | | | 21 | 0001
4840 21 | 0001 | | | 1 | 0001
4841 21 | 0001 | | | 6 | 0001
4842 21 | 0001 | | | 11 | 0001
4843 21 | 0001 | | | 16 | 0001
4844 21 | 0001 | | | 21 | 0001
4845 2 | 0002 | 2 | 0002 | |
4846 2 | 0002 | 7 | 0002 | |
4847 2 | 0002 | 12 | 0002 | |
4848 2 | 0002 | 17 | 0002 | |
4849 2 | 0002 | 22 | 0002 | |
4850 7 | 0002 | 2 | 0002 | |
4851 7 | 0002 | 7 | 0002 | |
4852 7 | 0002 | 12 | 0002 | |
4853 7 | 0002 | 17 | 0002 | |
4854 7 | 0002 | 22 | 0002 | |
4855 12 | 0002 | 2 | 0002 | |
4856 12 | 0002 | 7 | 0002 | |
4857 12 | 0002 | 12 | 0002 | |
4858 12 | 0002 | 17 | 0002 | |
4859 12 | 0002 | 22 | 0002 | |
4860 17 | 0002 | 2 | 0002 | |
4861 17 | 0002 | 7 | 0002 | |
4862 17 | 0002 | 12 | 0002 | |
4863 17 | 0002 | 17 | 0002 | |
4864 17 | 0002 | 22 | 0002 | |
4865 22 | 0002 | 2 | 0002 | |
4866 22 | 0002 | 7 | 0002 | |
4867 22 | 0002 | 12 | 0002 | |
4868 22 | 0002 | 17 | 0002 | |
4869 22 | 0002 | 22 | 0002 | |
4870 (55 rows)
4872 DROP TABLE plt1_adv;
4873 DROP TABLE plt2_adv;
4874 DROP TABLE plt3_adv;
4875 -- Tests for multi-level partitioned tables
4876 CREATE TABLE alpha (a double precision, b int, c text) PARTITION BY RANGE (a);
4877 CREATE TABLE alpha_neg PARTITION OF alpha FOR VALUES FROM ('-Infinity') TO (0) PARTITION BY RANGE (b);
4878 CREATE TABLE alpha_pos PARTITION OF alpha FOR VALUES FROM (0) TO (10.0) PARTITION BY LIST (c);
4879 CREATE TABLE alpha_neg_p1 PARTITION OF alpha_neg FOR VALUES FROM (100) TO (200);
4880 CREATE TABLE alpha_neg_p2 PARTITION OF alpha_neg FOR VALUES FROM (200) TO (300);
4881 CREATE TABLE alpha_neg_p3 PARTITION OF alpha_neg FOR VALUES FROM (300) TO (400);
4882 CREATE TABLE alpha_pos_p1 PARTITION OF alpha_pos FOR VALUES IN ('0001', '0003');
4883 CREATE TABLE alpha_pos_p2 PARTITION OF alpha_pos FOR VALUES IN ('0004', '0006');
4884 CREATE TABLE alpha_pos_p3 PARTITION OF alpha_pos FOR VALUES IN ('0008', '0009');
4885 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);
4886 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);
4887 ANALYZE alpha;
4888 CREATE TABLE beta (a double precision, b int, c text) PARTITION BY RANGE (a);
4889 CREATE TABLE beta_neg PARTITION OF beta FOR VALUES FROM (-10.0) TO (0) PARTITION BY RANGE (b);
4890 CREATE TABLE beta_pos PARTITION OF beta FOR VALUES FROM (0) TO ('Infinity') PARTITION BY LIST (c);
4891 CREATE TABLE beta_neg_p1 PARTITION OF beta_neg FOR VALUES FROM (100) TO (150);
4892 CREATE TABLE beta_neg_p2 PARTITION OF beta_neg FOR VALUES FROM (200) TO (300);
4893 CREATE TABLE beta_neg_p3 PARTITION OF beta_neg FOR VALUES FROM (350) TO (500);
4894 CREATE TABLE beta_pos_p1 PARTITION OF beta_pos FOR VALUES IN ('0002', '0003');
4895 CREATE TABLE beta_pos_p2 PARTITION OF beta_pos FOR VALUES IN ('0004', '0006');
4896 CREATE TABLE beta_pos_p3 PARTITION OF beta_pos FOR VALUES IN ('0007', '0009');
4897 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);
4898 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);
4899 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);
4900 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);
4901 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);
4902 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);
4903 ANALYZE beta;
4904 EXPLAIN (COSTS OFF)
4905 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;
4906 QUERY PLAN
4907 --------------------------------------------------------------------
4908 Sort
4909 Sort Key: t1.a, t1.b
4910 -> Append
4911 -> Hash Join
4912 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b))
4913 -> Seq Scan on alpha_neg_p1 t1_1
4914 Filter: ((b >= 125) AND (b < 225))
4915 -> Hash
4916 -> Seq Scan on beta_neg_p1 t2_1
4917 -> Hash Join
4918 Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.b = t1_2.b))
4919 -> Seq Scan on beta_neg_p2 t2_2
4920 -> Hash
4921 -> Seq Scan on alpha_neg_p2 t1_2
4922 Filter: ((b >= 125) AND (b < 225))
4923 -> Hash Join
4924 Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
4925 -> Append
4926 -> Seq Scan on beta_pos_p1 t2_4
4927 -> Seq Scan on beta_pos_p2 t2_5
4928 -> Seq Scan on beta_pos_p3 t2_6
4929 -> Hash
4930 -> Append
4931 -> Seq Scan on alpha_pos_p1 t1_4
4932 Filter: ((b >= 125) AND (b < 225))
4933 -> Seq Scan on alpha_pos_p2 t1_5
4934 Filter: ((b >= 125) AND (b < 225))
4935 -> Seq Scan on alpha_pos_p3 t1_6
4936 Filter: ((b >= 125) AND (b < 225))
4937 (29 rows)
4939 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;
4940 a | b | c | a | b | c
4941 ----+-----+------+----+-----+------
4942 -1 | 126 | 0006 | -1 | 126 | 0006
4943 -1 | 129 | 0009 | -1 | 129 | 0009
4944 -1 | 133 | 0003 | -1 | 133 | 0003
4945 -1 | 134 | 0004 | -1 | 134 | 0004
4946 -1 | 136 | 0006 | -1 | 136 | 0006
4947 -1 | 139 | 0009 | -1 | 139 | 0009
4948 -1 | 143 | 0003 | -1 | 143 | 0003
4949 -1 | 144 | 0004 | -1 | 144 | 0004
4950 -1 | 146 | 0006 | -1 | 146 | 0006
4951 -1 | 149 | 0009 | -1 | 149 | 0009
4952 -1 | 203 | 0003 | -1 | 203 | 0003
4953 -1 | 204 | 0004 | -1 | 204 | 0004
4954 -1 | 206 | 0006 | -1 | 206 | 0006
4955 -1 | 209 | 0009 | -1 | 209 | 0009
4956 -1 | 213 | 0003 | -1 | 213 | 0003
4957 -1 | 214 | 0004 | -1 | 214 | 0004
4958 -1 | 216 | 0006 | -1 | 216 | 0006
4959 -1 | 219 | 0009 | -1 | 219 | 0009
4960 -1 | 223 | 0003 | -1 | 223 | 0003
4961 -1 | 224 | 0004 | -1 | 224 | 0004
4962 1 | 126 | 0006 | 1 | 126 | 0006
4963 1 | 129 | 0009 | 1 | 129 | 0009
4964 1 | 133 | 0003 | 1 | 133 | 0003
4965 1 | 134 | 0004 | 1 | 134 | 0004
4966 1 | 136 | 0006 | 1 | 136 | 0006
4967 1 | 139 | 0009 | 1 | 139 | 0009
4968 1 | 143 | 0003 | 1 | 143 | 0003
4969 1 | 144 | 0004 | 1 | 144 | 0004
4970 1 | 146 | 0006 | 1 | 146 | 0006
4971 1 | 149 | 0009 | 1 | 149 | 0009
4972 1 | 203 | 0003 | 1 | 203 | 0003
4973 1 | 204 | 0004 | 1 | 204 | 0004
4974 1 | 206 | 0006 | 1 | 206 | 0006
4975 1 | 209 | 0009 | 1 | 209 | 0009
4976 1 | 213 | 0003 | 1 | 213 | 0003
4977 1 | 214 | 0004 | 1 | 214 | 0004
4978 1 | 216 | 0006 | 1 | 216 | 0006
4979 1 | 219 | 0009 | 1 | 219 | 0009
4980 1 | 223 | 0003 | 1 | 223 | 0003
4981 1 | 224 | 0004 | 1 | 224 | 0004
4982 (40 rows)
4984 EXPLAIN (COSTS OFF)
4985 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;
4986 QUERY PLAN
4987 --------------------------------------------------------------------------------------------------------------------------------------
4988 Sort
4989 Sort Key: t1.a, t1.b, t2.b
4990 -> Append
4991 -> Hash Join
4992 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.c = t2_2.c))
4993 -> Append
4994 -> Seq Scan on alpha_neg_p1 t1_2
4995 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
4996 -> Seq Scan on alpha_neg_p2 t1_3
4997 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
4998 -> Hash
4999 -> Append
5000 -> Seq Scan on beta_neg_p1 t2_2
5001 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5002 -> Seq Scan on beta_neg_p2 t2_3
5003 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5004 -> Nested Loop
5005 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.c = t2_4.c))
5006 -> Seq Scan on alpha_pos_p2 t1_4
5007 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5008 -> Seq Scan on beta_pos_p2 t2_4
5009 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5010 -> Nested Loop
5011 Join Filter: ((t1_5.a = t2_5.a) AND (t1_5.c = t2_5.c))
5012 -> Seq Scan on alpha_pos_p3 t1_5
5013 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5014 -> Seq Scan on beta_pos_p3 t2_5
5015 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5016 (28 rows)
5018 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;
5019 a | b | c | a | b | c
5020 ----+-----+------+----+-----+------
5021 -1 | 104 | 0004 | -1 | 104 | 0004
5022 -1 | 104 | 0004 | -1 | 204 | 0004
5023 -1 | 109 | 0009 | -1 | 109 | 0009
5024 -1 | 109 | 0009 | -1 | 209 | 0009
5025 -1 | 204 | 0004 | -1 | 104 | 0004
5026 -1 | 204 | 0004 | -1 | 204 | 0004
5027 -1 | 209 | 0009 | -1 | 109 | 0009
5028 -1 | 209 | 0009 | -1 | 209 | 0009
5029 1 | 104 | 0004 | 1 | 104 | 0004
5030 1 | 104 | 0004 | 1 | 204 | 0004
5031 1 | 109 | 0009 | 1 | 109 | 0009
5032 1 | 109 | 0009 | 1 | 209 | 0009
5033 1 | 204 | 0004 | 1 | 104 | 0004
5034 1 | 204 | 0004 | 1 | 204 | 0004
5035 1 | 209 | 0009 | 1 | 109 | 0009
5036 1 | 209 | 0009 | 1 | 209 | 0009
5037 (16 rows)
5039 EXPLAIN (COSTS OFF)
5040 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;
5041 QUERY PLAN
5042 --------------------------------------------------------------------------------------------------------------------------------
5043 Sort
5044 Sort Key: t1.a, t1.b
5045 -> Append
5046 -> Hash Join
5047 Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
5048 -> Seq Scan on alpha_neg_p1 t1_1
5049 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5050 -> Hash
5051 -> Seq Scan on beta_neg_p1 t2_1
5052 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5053 -> Hash Join
5054 Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
5055 -> Seq Scan on alpha_neg_p2 t1_2
5056 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5057 -> Hash
5058 -> Seq Scan on beta_neg_p2 t2_2
5059 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5060 -> Nested Loop
5061 Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
5062 -> Seq Scan on alpha_pos_p2 t1_3
5063 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5064 -> Seq Scan on beta_pos_p2 t2_3
5065 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5066 -> Nested Loop
5067 Join Filter: ((t1_4.a = t2_4.a) AND (t1_4.b = t2_4.b) AND (t1_4.c = t2_4.c))
5068 -> Seq Scan on alpha_pos_p3 t1_4
5069 Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210))))
5070 -> Seq Scan on beta_pos_p3 t2_4
5071 Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
5072 (29 rows)
5074 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;
5075 a | b | c | a | b | c
5076 ----+-----+------+----+-----+------
5077 -1 | 104 | 0004 | -1 | 104 | 0004
5078 -1 | 109 | 0009 | -1 | 109 | 0009
5079 -1 | 204 | 0004 | -1 | 204 | 0004
5080 -1 | 209 | 0009 | -1 | 209 | 0009
5081 1 | 104 | 0004 | 1 | 104 | 0004
5082 1 | 109 | 0009 | 1 | 109 | 0009
5083 1 | 204 | 0004 | 1 | 204 | 0004
5084 1 | 209 | 0009 | 1 | 209 | 0009
5085 (8 rows)
5087 -- partitionwise join with fractional paths
5088 CREATE TABLE fract_t (id BIGINT, PRIMARY KEY (id)) PARTITION BY RANGE (id);
5089 CREATE TABLE fract_t0 PARTITION OF fract_t FOR VALUES FROM ('0') TO ('1000');
5090 CREATE TABLE fract_t1 PARTITION OF fract_t FOR VALUES FROM ('1000') TO ('2000');
5091 -- insert data
5092 INSERT INTO fract_t (id) (SELECT generate_series(0, 1999));
5093 ANALYZE fract_t;
5094 -- verify plan; nested index only scans
5095 SET max_parallel_workers_per_gather = 0;
5096 SET enable_partitionwise_join = on;
5097 EXPLAIN (COSTS OFF)
5098 SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id ASC LIMIT 10;
5099 QUERY PLAN
5100 -----------------------------------------------------------------------
5101 Limit
5102 -> Merge Append
5103 Sort Key: x.id
5104 -> Merge Left Join
5105 Merge Cond: (x_1.id = y_1.id)
5106 -> Index Only Scan using fract_t0_pkey on fract_t0 x_1
5107 -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
5108 -> Merge Left Join
5109 Merge Cond: (x_2.id = y_2.id)
5110 -> Index Only Scan using fract_t1_pkey on fract_t1 x_2
5111 -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
5112 (11 rows)
5114 EXPLAIN (COSTS OFF)
5115 SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DESC LIMIT 10;
5116 QUERY PLAN
5117 --------------------------------------------------------------------------------
5118 Limit
5119 -> Merge Append
5120 Sort Key: x.id DESC
5121 -> Nested Loop Left Join
5122 -> Index Only Scan Backward using fract_t0_pkey on fract_t0 x_1
5123 -> Index Only Scan using fract_t0_pkey on fract_t0 y_1
5124 Index Cond: (id = x_1.id)
5125 -> Nested Loop Left Join
5126 -> Index Only Scan Backward using fract_t1_pkey on fract_t1 x_2
5127 -> Index Only Scan using fract_t1_pkey on fract_t1 y_2
5128 Index Cond: (id = x_2.id)
5129 (11 rows)
5131 -- cleanup
5132 DROP TABLE fract_t;
5133 RESET max_parallel_workers_per_gather;
5134 RESET enable_partitionwise_join;