On Wed, Sep 20, 2017 at 3:13 PM, Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com> wrote:
> On Wed, Sep 20, 2017 at 9:44 AM, Thomas Munro
> <thomas.mu...@enterprisedb.com> wrote:
> > 2. What queries in the 0008 patch are hitting lines that 0007 doesn't
> hit?
> >
> > I thought about how to answer questions like this and came up with a
> > shell script that (1) makes computers run really hot for quite a long
> > time and (2) tells you which blocks of SQL hit which lines of C.
> > Please find attached the shell script and its output. The .sql files
> > have been annotated with "block" numbers (blocks being chunks of SQL
> > stuff separated by blank lines), and the C files annotated with
> > references to those block numbers where A<n> = block <n>
> > partition_join.sql and B<n> = block <n> in partition_join_extras.sql.
> >
> > Then to find lines that B queries hit but A queries don't and know
> > which particular queries hit them, you might use something like:
> >
> > grep -v 'SQL blocks: .*A[0-9]' < joinpath.c.aggregated_coverage | \
> > grep 'SQL blocks: .*B[0-9]'
> >
>
> Thanks for this. It generates a lot of output (970 lines over all the
> coverage files). It will take some time for getting anything
> meaningful out of this. May be there's some faster way by looking at
> the lines that are covered by B but not A. BTW, I checked those lines
> to see if there could be any bug there. But I don't see what could go
> wrong with those lines.
>
> I have also tried to find test cases in B which hits some extra line which
is not
hitting by A, with the help of results attached by Thomas in
coverage.tarball_FILES.
It took lot of time but I am able to find some test cases. which if adding
in partition_join.sql
increasing no of lines hit by 14. but for hitting these 14 extra line
attached patch is doing
900+ line inserts in partition_join.sql and partition_join.out file.
I have used gcov-lcov to find coverage for files changed by
partition-wise-join patches
with and without attached patch which is below.
*with existing partition_join.sql* *partition_join.sql + some test cases of
partition_join_extra.sql*
*Modifed Files* *Line Coverage* *Functions* *Line Coverage* *Functions*
src/backend/optimizer/geqo 79.4 % 269/339 96.6 % 28/29 79.4 % 269/339 96.6 %
28/29
src/backend/optimizer/path/allpaths.c 92.3 % 787 / 853 95.5 % 42 / 44
92.6 % 790
/ 853 95.5 % 42 / 44
src/backend/optimizer/path/costsize.c 96.8 % 1415 / 1462 98.4 % 61 / 62
96.9 % 1416 / 1462 98.4 % 61 / 62
src/backend/optimizer/path/joinpath.c 95.5 % 404 / 423 100.0 % 16 / 16
95.5 % 404 / 423 100.0 % 16 / 16
src/backend/optimizer/path/joinrels.c 92.5 % 422 / 456 100.0 % 16 / 16
93.0 % 424 / 456 100.0 % 16 / 16
src/backend/optimizer/plan/createplan.c 90.9 % 1928 / 2122 96.3 % 103 / 107
91.0 % 1930 / 2122 96.3 % 103 / 107
src/backend/optimizer/plan/planner.c 94.9 % 1609 / 1696 97.6 % 41 / 42
94.9 % 1609 / 1696 97.6 % 41 / 42
src/backend/optimizer/plan/setrefs.c 91.3 % 806 / 883 94.3 % 33 / 35 91.3 % 806
/ 883 94.3 % 33 / 35
src/backend/optimizer/prep/prepunion.c 95.5 % 661 / 692 100.0 % 25 / 25
95.5 % 661 / 692 100.0 % 25 / 25
src/backend/optimizer/util/pathnode.c 88.7 % 1144 / 1290 98.1 % 52 / 53
88.8 % 1146 / 1290 98.1 % 52 / 53
src/backend/optimizer/util/placeholder.c 96.5 % 139 / 144 100.0 % 10 / 10
96.5 % 139 / 144 100.0 % 10 / 10
src/backend/optimizer/util/plancat.c 89.0 % 540 / 607 94.7 % 18 / 19 89.6 % 544
/ 607 94.7 % 18 / 19
src/backend/optimizer/util/relnode.c 95.3 % 548 / 575 100.0 % 24 / 24
95.3 % 548
/ 575 100.0 % 24 / 24
src/backend/utils/misc/guc.c 67.4 % 1536 / 2278 89.7 % 113 / 126 67.4 % 1536
/ 2278 89.7 % 113 / 126
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 9fec170..ab411b6 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -584,6 +584,215 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
550 | |
(12 rows)
+-- join with aggregate
+EXPLAIN (VERBOSE, COSTS OFF)
+select t1.a, count(t2.*) from prt1 t1 left join prt1 t2 on (t1.a = t2.a) where t1.a % 25 = 0 group by t1.a;
+ QUERY PLAN
+---------------------------------------------------------------
+ GroupAggregate
+ Output: t1.a, count(((t2.*)::prt1))
+ Group Key: t1.a
+ -> Sort
+ Output: t1.a, ((t2.*)::prt1)
+ Sort Key: t1.a
+ -> Append
+ -> Hash Right Join
+ Output: t1.a, ((t2.*)::prt1)
+ Hash Cond: (t2.a = t1.a)
+ -> Seq Scan on public.prt1_p1 t2
+ Output: t2.*, t2.a
+ -> Hash
+ Output: t1.a
+ -> Seq Scan on public.prt1_p1 t1
+ Output: t1.a
+ Filter: ((t1.a % 25) = 0)
+ -> Hash Right Join
+ Output: t1_1.a, ((t2_1.*)::prt1)
+ Hash Cond: (t2_1.a = t1_1.a)
+ -> Seq Scan on public.prt1_p2 t2_1
+ Output: t2_1.*, t2_1.a
+ -> Hash
+ Output: t1_1.a
+ -> Seq Scan on public.prt1_p2 t1_1
+ Output: t1_1.a
+ Filter: ((t1_1.a % 25) = 0)
+ -> Hash Right Join
+ Output: t1_2.a, ((t2_2.*)::prt1)
+ Hash Cond: (t2_2.a = t1_2.a)
+ -> Seq Scan on public.prt1_p3 t2_2
+ Output: t2_2.*, t2_2.a
+ -> Hash
+ Output: t1_2.a
+ -> Seq Scan on public.prt1_p3 t1_2
+ Output: t1_2.a
+ Filter: ((t1_2.a % 25) = 0)
+(37 rows)
+
+select t1.a, count(t2.*) from prt1 t1 left join prt1 t2 on (t1.a = t2.a) where t1.a % 25 = 0 group by t1.a;
+ a | count
+-----+-------
+ 0 | 1
+ 50 | 1
+ 100 | 1
+ 150 | 1
+ 200 | 1
+ 250 | 1
+ 300 | 1
+ 350 | 1
+ 400 | 1
+ 450 | 1
+ 500 | 1
+ 550 | 1
+(12 rows)
+
+-- lateral with VALUES
+EXPLAIN (VERBOSE, COSTS OFF)
+select count(*) from prt1 a, prt2 b join lateral (values(a.a)) ss(x) on b.b = ss.x;
+ QUERY PLAN
+--------------------------------------------------------
+ Aggregate
+ Output: count(*)
+ -> Append
+ -> Hash Join
+ Hash Cond: (a.a = b.b)
+ -> Seq Scan on public.prt1_p1 a
+ Output: a.a
+ -> Hash
+ Output: b.b
+ -> Seq Scan on public.prt2_p1 b
+ Output: b.b
+ -> Hash Join
+ Hash Cond: (a_1.a = b_1.b)
+ -> Seq Scan on public.prt1_p2 a_1
+ Output: a_1.a
+ -> Hash
+ Output: b_1.b
+ -> Seq Scan on public.prt2_p2 b_1
+ Output: b_1.b
+ -> Hash Join
+ Hash Cond: (a_2.a = b_2.b)
+ -> Seq Scan on public.prt1_p3 a_2
+ Output: a_2.a
+ -> Hash
+ Output: b_2.b
+ -> Seq Scan on public.prt2_p3 b_2
+ Output: b_2.b
+(27 rows)
+
+select count(*) from prt1 a, prt2 b join lateral (values(a.a)) ss(x) on b.b = ss.x;
+ count
+-------
+ 100
+(1 row)
+
+-- join with rank
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.b, t2.b,rank() OVER (ORDER BY t2.b DESC) FROM prt1 t1 inner join prt2 t2 on (t1.a = t2.b) where t1.a % 25 = 0 order by 1,2,3;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Sort
+ Output: t1.a, t1.b, t2.b, (rank() OVER (?))
+ Sort Key: t1.a, t1.b
+ -> WindowAgg
+ Output: t1.a, t1.b, t2.b, rank() OVER (?)
+ -> Sort
+ Output: t2.b, t1.a, t1.b
+ Sort Key: t2.b DESC
+ -> Result
+ Output: t2.b, t1.a, t1.b
+ -> Append
+ -> Hash Join
+ Output: t1.a, t1.b, t2.b
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on public.prt2_p1 t2
+ Output: t2.b
+ -> Hash
+ Output: t1.a, t1.b
+ -> Seq Scan on public.prt1_p1 t1
+ Output: t1.a, t1.b
+ Filter: ((t1.a % 25) = 0)
+ -> Hash Join
+ Output: t1_1.a, t1_1.b, t2_1.b
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on public.prt2_p2 t2_1
+ Output: t2_1.b
+ -> Hash
+ Output: t1_1.a, t1_1.b
+ -> Seq Scan on public.prt1_p2 t1_1
+ Output: t1_1.a, t1_1.b
+ Filter: ((t1_1.a % 25) = 0)
+ -> Hash Join
+ Output: t1_2.a, t1_2.b, t2_2.b
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on public.prt2_p3 t2_2
+ Output: t2_2.b
+ -> Hash
+ Output: t1_2.a, t1_2.b
+ -> Seq Scan on public.prt1_p3 t1_2
+ Output: t1_2.a, t1_2.b
+ Filter: ((t1_2.a % 25) = 0)
+(41 rows)
+
+SELECT t1.a, t1.b, t2.b,rank() OVER (ORDER BY t2.b DESC) FROM prt1 t1 inner join prt2 t2 on (t1.a = t2.b) where t1.a % 25 = 0 order by 1,2,3;
+ a | b | b | rank
+-----+---+-----+------
+ 0 | 0 | 0 | 4
+ 150 | 0 | 150 | 3
+ 300 | 0 | 300 | 2
+ 450 | 0 | 450 | 1
+(4 rows)
+
+--join with prepare statement
+PREPARE ij(int) AS select t1.a,t2.b from prt1 t1 inner join prt2 t2 on (t1.a = t2.b and t1.a % $1 = 0) ORDER BY 1,2;
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE ij(25);
+ QUERY PLAN
+---------------------------------------------------------
+ Sort
+ Output: t1.a, t2.b
+ Sort Key: t1.a
+ -> Append
+ -> Hash Join
+ Output: t1.a, t2.b
+ Hash Cond: (t2.b = t1.a)
+ -> Seq Scan on public.prt2_p1 t2
+ Output: t2.b
+ -> Hash
+ Output: t1.a
+ -> Seq Scan on public.prt1_p1 t1
+ Output: t1.a
+ Filter: ((t1.a % 25) = 0)
+ -> Hash Join
+ Output: t1_1.a, t2_1.b
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on public.prt2_p2 t2_1
+ Output: t2_1.b
+ -> Hash
+ Output: t1_1.a
+ -> Seq Scan on public.prt1_p2 t1_1
+ Output: t1_1.a
+ Filter: ((t1_1.a % 25) = 0)
+ -> Hash Join
+ Output: t1_2.a, t2_2.b
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on public.prt2_p3 t2_2
+ Output: t2_2.b
+ -> Hash
+ Output: t1_2.a
+ -> Seq Scan on public.prt1_p3 t1_2
+ Output: t1_2.a
+ Filter: ((t1_2.a % 25) = 0)
+(34 rows)
+
+EXECUTE ij(25);
+ a | b
+-----+-----
+ 0 | 0
+ 150 | 150
+ 300 | 300
+ 450 | 450
+(4 rows)
+
+DEALLOCATE ij;
--
-- partitioned by expression
--
@@ -1094,7 +1303,8 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
-> Seq Scan on prt2_p3 t2_2
(52 rows)
-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;
+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;
a | c | b | c | ?column? | c
-----+------+-----+------+----------+---
0 | 0000 | 0 | 0000 | 0 | 0
@@ -1111,6 +1321,234 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
550 | 0550 | | | 1100 | 0
(12 rows)
+EXPLAIN (VERBOSE, COSTS OFF)
+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.a %25 = 0)) AND t1.a % 25 = 0 ORDER BY t1.a;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Merge Append
+ Sort Key: t1.a
+ -> Merge Semi Join
+ Output: t1.a, t1.b, t1.c
+ Merge Cond: (t1.a = t1_3.b)
+ -> Sort
+ Output: t1.a, t1.b, t1.c
+ Sort Key: t1.a
+ -> Seq Scan on public.prt1_p1 t1
+ Output: t1.a, t1.b, t1.c
+ Filter: ((t1.a % 25) = 0)
+ -> Merge Semi Join
+ Output: t1_3.b, t1_6.a, t1_6.b
+ Merge Cond: (t1_3.b = (((t1_6.a + t1_6.b) / 2)))
+ -> Sort
+ Output: t1_3.b
+ Sort Key: t1_3.b
+ -> Seq Scan on public.prt2_p1 t1_3
+ Output: t1_3.b
+ -> Sort
+ Output: t1_6.a, t1_6.b, (((t1_6.a + t1_6.b) / 2))
+ Sort Key: (((t1_6.a + t1_6.b) / 2))
+ -> Seq Scan on public.prt1_e_p1 t1_6
+ Output: t1_6.a, t1_6.b, ((t1_6.a + t1_6.b) / 2)
+ Filter: ((t1_6.a % 25) = 0)
+ -> Merge Semi Join
+ Output: t1_1.a, t1_1.b, t1_1.c
+ Merge Cond: (t1_1.a = t1_4.b)
+ -> Sort
+ Output: t1_1.a, t1_1.b, t1_1.c
+ Sort Key: t1_1.a
+ -> Seq Scan on public.prt1_p2 t1_1
+ Output: t1_1.a, t1_1.b, t1_1.c
+ Filter: ((t1_1.a % 25) = 0)
+ -> Merge Semi Join
+ Output: t1_4.b, t1_7.a, t1_7.b
+ Merge Cond: (t1_4.b = (((t1_7.a + t1_7.b) / 2)))
+ -> Sort
+ Output: t1_4.b
+ Sort Key: t1_4.b
+ -> Seq Scan on public.prt2_p2 t1_4
+ Output: t1_4.b
+ -> Sort
+ Output: t1_7.a, t1_7.b, (((t1_7.a + t1_7.b) / 2))
+ Sort Key: (((t1_7.a + t1_7.b) / 2))
+ -> Seq Scan on public.prt1_e_p2 t1_7
+ Output: t1_7.a, t1_7.b, ((t1_7.a + t1_7.b) / 2)
+ Filter: ((t1_7.a % 25) = 0)
+ -> Merge Semi Join
+ Output: t1_2.a, t1_2.b, t1_2.c
+ Merge Cond: (t1_2.a = t1_5.b)
+ -> Sort
+ Output: t1_2.a, t1_2.b, t1_2.c
+ Sort Key: t1_2.a
+ -> Seq Scan on public.prt1_p3 t1_2
+ Output: t1_2.a, t1_2.b, t1_2.c
+ Filter: ((t1_2.a % 25) = 0)
+ -> Merge Semi Join
+ Output: t1_5.b, t1_8.a, t1_8.b
+ Merge Cond: (t1_5.b = (((t1_8.a + t1_8.b) / 2)))
+ -> Sort
+ Output: t1_5.b
+ Sort Key: t1_5.b
+ -> Seq Scan on public.prt2_p3 t1_5
+ Output: t1_5.b
+ -> Sort
+ Output: t1_8.a, t1_8.b, (((t1_8.a + t1_8.b) / 2))
+ Sort Key: (((t1_8.a + t1_8.b) / 2))
+ -> Seq Scan on public.prt1_e_p3 t1_8
+ Output: t1_8.a, t1_8.b, ((t1_8.a + t1_8.b) / 2)
+ Filter: ((t1_8.a % 25) = 0)
+(71 rows)
+
+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.a %25 = 0)) AND t1.a % 25 = 0 ORDER BY t1.a;
+ a | b | c
+-----+---+------
+ 0 | 0 | 0000
+ 150 | 0 | 0150
+ 300 | 0 | 0300
+ 450 | 0 | 0450
+(4 rows)
+
+SET enable_seqscan TO off;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t2.b = (t3.a + t3.b)/2) WHERE t3.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Sort
+ Output: t1.a, t1.c, t2.b, t2.c, ((t3.a + t3.b)), t3.c
+ Sort Key: t1.a, t2.b, ((t3.a + t3.b))
+ -> Result
+ Output: t1.a, t1.c, t2.b, t2.c, (t3.a + t3.b), t3.c
+ -> Append
+ -> Merge Left Join
+ Output: t3.a, t3.b, t3.c, t2.b, t2.c, t1.a, t1.c
+ Merge Cond: (t2.b = t1.a)
+ -> Sort
+ Output: t3.a, t3.b, t3.c, t2.b, t2.c
+ Sort Key: t2.b
+ -> Merge Left Join
+ Output: t3.a, t3.b, t3.c, t2.b, t2.c
+ Merge Cond: (((t3.a + t3.b) / 2) = t2.b)
+ -> Index Scan using iprt1_e_p1_ab2 on public.prt1_e_p1 t3
+ Output: t3.a, t3.b, t3.c
+ Filter: ((t3.a % 25) = 0)
+ -> Index Scan using iprt2_p1_b on public.prt2_p1 t2
+ Output: t2.b, t2.c
+ -> Index Scan using iprt1_p1_a on public.prt1_p1 t1
+ Output: t1.a, t1.c
+ -> Merge Left Join
+ Output: t3_1.a, t3_1.b, t3_1.c, t2_1.b, t2_1.c, t1_1.a, t1_1.c
+ Merge Cond: (t2_1.b = t1_1.a)
+ -> Sort
+ Output: t3_1.a, t3_1.b, t3_1.c, t2_1.b, t2_1.c
+ Sort Key: t2_1.b
+ -> Merge Left Join
+ Output: t3_1.a, t3_1.b, t3_1.c, t2_1.b, t2_1.c
+ Merge Cond: (((t3_1.a + t3_1.b) / 2) = t2_1.b)
+ -> Index Scan using iprt1_e_p2_ab2 on public.prt1_e_p2 t3_1
+ Output: t3_1.a, t3_1.b, t3_1.c
+ Filter: ((t3_1.a % 25) = 0)
+ -> Index Scan using iprt2_p2_b on public.prt2_p2 t2_1
+ Output: t2_1.b, t2_1.c
+ -> Index Scan using iprt1_p2_a on public.prt1_p2 t1_1
+ Output: t1_1.a, t1_1.c
+ -> Merge Right Join
+ Output: t3_2.a, t3_2.b, t3_2.c, t2_2.b, t2_2.c, t1_2.a, t1_2.c
+ Merge Cond: (t2_2.b = ((t3_2.a + t3_2.b) / 2))
+ -> Merge Left Join
+ Output: t2_2.b, t2_2.c, t1_2.a, t1_2.c
+ Merge Cond: (t2_2.b = t1_2.a)
+ -> Index Scan using iprt2_p3_b on public.prt2_p3 t2_2
+ Output: t2_2.b, t2_2.c
+ -> Index Scan using iprt1_p3_a on public.prt1_p3 t1_2
+ Output: t1_2.a, t1_2.c
+ -> Index Scan using iprt1_e_p3_ab2 on public.prt1_e_p3 t3_2
+ Output: t3_2.a, t3_2.b, t3_2.c
+ Filter: ((t3_2.a % 25) = 0)
+(51 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t2.b = (t3.a + t3.b)/2) WHERE t3.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+ a | c | b | c | ?column? | c
+-----+------+-----+------+----------+---
+ 0 | 0000 | 0 | 0000 | 0 | 0
+ 150 | 0150 | 150 | 0150 | 300 | 0
+ 300 | 0300 | 300 | 0300 | 600 | 0
+ 450 | 0450 | 450 | 0450 | 900 | 0
+ | | | | 100 | 0
+ | | | | 200 | 0
+ | | | | 400 | 0
+ | | | | 500 | 0
+ | | | | 700 | 0
+ | | | | 800 | 0
+ | | | | 1000 | 0
+ | | | | 1100 | 0
+(12 rows)
+
+-- lateral references and parameterized paths
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM prt1 t1 LEFT JOIN LATERAL
+(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
+ON t1.a = ss.t2a WHERE t1.a % 25 = 0 ORDER BY t1.a;
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Nested Loop Left Join
+ Output: t1.a, t1.b, t1.c, t2.a, t3.a, (LEAST(t1.a, t2.a, t3.a)), t1.a
+ -> Merge Append
+ Sort Key: t1.a
+ -> Index Scan using iprt1_p1_a on public.prt1_p1 t1
+ Output: t1.a, t1.b, t1.c
+ Filter: ((t1.a % 25) = 0)
+ -> Index Scan using iprt1_p2_a on public.prt1_p2 t1_1
+ Output: t1_1.a, t1_1.b, t1_1.c
+ Filter: ((t1_1.a % 25) = 0)
+ -> Index Scan using iprt1_p3_a on public.prt1_p3 t1_2
+ Output: t1_2.a, t1_2.b, t1_2.c
+ Filter: ((t1_2.a % 25) = 0)
+ -> Append
+ -> Merge Join
+ Output: t2.a, t3.a, LEAST(t1.a, t2.a, t3.a)
+ Merge Cond: (t2.a = t3.b)
+ -> Index Only Scan using iprt1_p1_a on public.prt1_p1 t2
+ Output: t2.a
+ Index Cond: (t2.a = t1.a)
+ -> Index Scan using iprt2_p1_b on public.prt2_p1 t3
+ Output: t3.a, t3.b
+ -> Merge Join
+ Output: t2_1.a, t3_1.a, LEAST(t1.a, t2_1.a, t3_1.a)
+ Merge Cond: (t2_1.a = t3_1.b)
+ -> Index Only Scan using iprt1_p2_a on public.prt1_p2 t2_1
+ Output: t2_1.a
+ Index Cond: (t2_1.a = t1.a)
+ -> Index Scan using iprt2_p2_b on public.prt2_p2 t3_1
+ Output: t3_1.a, t3_1.b
+ -> Merge Join
+ Output: t2_2.a, t3_2.a, LEAST(t1.a, t2_2.a, t3_2.a)
+ Merge Cond: (t2_2.a = t3_2.b)
+ -> Index Only Scan using iprt1_p3_a on public.prt1_p3 t2_2
+ Output: t2_2.a
+ Index Cond: (t2_2.a = t1.a)
+ -> Index Scan using iprt2_p3_b on public.prt2_p3 t3_2
+ Output: t3_2.a, t3_2.b
+(38 rows)
+
+SELECT * FROM prt1 t1 LEFT JOIN LATERAL
+(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
+ON t1.a = ss.t2a WHERE t1.a % 25 = 0 ORDER BY t1.a;
+ a | b | c | t2a | t3a | least
+-----+---+------+-----+-----+-------
+ 0 | 0 | 0000 | 0 | 0 | 0
+ 50 | 0 | 0050 | | |
+ 100 | 0 | 0100 | | |
+ 150 | 0 | 0150 | 150 | 0 | 0
+ 200 | 0 | 0200 | | |
+ 250 | 0 | 0250 | | |
+ 300 | 0 | 0300 | 300 | 0 | 0
+ 350 | 0 | 0350 | | |
+ 400 | 0 | 0400 | | |
+ 450 | 0 | 0450 | 450 | 0 | 0
+ 500 | 0 | 0500 | | |
+ 550 | 0 | 0550 | | |
+(12 rows)
+
+RESET enable_seqscan;
-- MergeAppend on nullable column
EXPLAIN (COSTS OFF)
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;
@@ -1238,6 +1676,187 @@ CREATE TABLE plt2_p2 PARTITION OF plt2 FOR VALUES IN ('0001', '0005', '0002', '0
CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0011');
INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
ANALYZE plt2;
+--full join using
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM plt1 t1 FULL JOIN plt2 t2 USING (c) FULL JOIN plt1 t3 USING (c)
+where t1.a %150 =0 and t2.b % 150 = 0 and t3.a % 150 = 0 order by 1,2,3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Sort
+ Output: (COALESCE(COALESCE(t1.c, t2.c), t3.c)), t1.a, t1.b, t2.a, t2.b, t3.a, t3.b
+ Sort Key: (COALESCE(COALESCE(t1.c, t2.c), t3.c)), t1.a, t1.b
+ -> Nested Loop
+ Output: COALESCE(COALESCE(t1.c, t2.c), t3.c), t1.a, t1.b, t2.a, t2.b, t3.a, t3.b
+ Join Filter: (COALESCE(t1.c, t2.c) = t3.c)
+ -> Nested Loop
+ Output: t1.c, t1.a, t1.b, t2.c, t2.a, t2.b
+ Join Filter: (t1.c = t2.c)
+ -> Append
+ -> Seq Scan on public.plt1_p1 t1
+ Output: t1.c, t1.a, t1.b
+ Filter: ((t1.a % 150) = 0)
+ -> Seq Scan on public.plt1_p2 t1_1
+ Output: t1_1.c, t1_1.a, t1_1.b
+ Filter: ((t1_1.a % 150) = 0)
+ -> Seq Scan on public.plt1_p3 t1_2
+ Output: t1_2.c, t1_2.a, t1_2.b
+ Filter: ((t1_2.a % 150) = 0)
+ -> Materialize
+ Output: t2.c, t2.a, t2.b
+ -> Append
+ -> Seq Scan on public.plt2_p1 t2
+ Output: t2.c, t2.a, t2.b
+ Filter: ((t2.b % 150) = 0)
+ -> Seq Scan on public.plt2_p2 t2_1
+ Output: t2_1.c, t2_1.a, t2_1.b
+ Filter: ((t2_1.b % 150) = 0)
+ -> Seq Scan on public.plt2_p3 t2_2
+ Output: t2_2.c, t2_2.a, t2_2.b
+ Filter: ((t2_2.b % 150) = 0)
+ -> Append
+ -> Seq Scan on public.plt1_p1 t3
+ Output: t3.c, t3.a, t3.b
+ Filter: ((t3.a % 150) = 0)
+ -> Seq Scan on public.plt1_p2 t3_1
+ Output: t3_1.c, t3_1.a, t3_1.b
+ Filter: ((t3_1.a % 150) = 0)
+ -> Seq Scan on public.plt1_p3 t3_2
+ Output: t3_2.c, t3_2.a, t3_2.b
+ Filter: ((t3_2.a % 150) = 0)
+(41 rows)
+
+SELECT * FROM plt1 t1 FULL JOIN plt2 t2 USING (c) FULL JOIN plt1 t3 USING (c)
+where t1.a %150 =0 and t2.b % 150 = 0 and t3.a % 150 = 0 order by 1,2,3;
+ c | a | b | a | b | a | b
+------+-----+-----+-----+-----+-----+-----
+ 0000 | 0 | 0 | 0 | 0 | 0 | 0
+ 0003 | 150 | 150 | 150 | 150 | 150 | 150
+ 0006 | 300 | 300 | 300 | 300 | 300 | 300
+ 0009 | 450 | 450 | 450 | 450 | 450 | 450
+(4 rows)
+
+-- test placement of movable quals in a parameterized join tree
+EXPLAIN (VERBOSE, COSTS OFF)
+select b.b from prt1 a join prt2 b on a.a = b.b left join plt1 c on b.a % 25 = 0 and c.c = a.c join prt1 i1 on b.b = i1.a
+right join prt2 i2 on i2.b = b.b where b.a % 25 = 0 order by 1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Sort
+ Output: b.b
+ Sort Key: b.b
+ -> Hash Right Join
+ Output: b.b
+ Hash Cond: (c.c = (a.c)::text)
+ Join Filter: ((b.a % 25) = 0)
+ -> Append
+ -> Seq Scan on public.plt1_p1 c
+ Output: c.c
+ -> Seq Scan on public.plt1_p2 c_1
+ Output: c_1.c
+ -> Seq Scan on public.plt1_p3 c_2
+ Output: c_2.c
+ -> Hash
+ Output: a.c, b.b, b.a
+ -> Append
+ -> Nested Loop
+ Output: a.c, b.b, b.a
+ -> Nested Loop
+ Output: a.a, a.c, b.b, b.a, i2.b
+ Join Filter: (b.b = a.a)
+ -> Hash Join
+ Output: b.b, b.a, i2.b
+ Hash Cond: (i2.b = b.b)
+ -> Seq Scan on public.prt2_p1 i2
+ Output: i2.b
+ -> Hash
+ Output: b.b, b.a
+ -> Seq Scan on public.prt2_p1 b
+ Output: b.b, b.a
+ Filter: ((b.a % 25) = 0)
+ -> Index Scan using iprt1_p1_a on public.prt1_p1 a
+ Output: a.a, a.c
+ Index Cond: (a.a = i2.b)
+ -> Index Only Scan using iprt1_p1_a on public.prt1_p1 i1
+ Output: i1.a
+ Index Cond: (i1.a = a.a)
+ -> Nested Loop
+ Output: a_1.c, b_1.b, b_1.a
+ -> Nested Loop
+ Output: a_1.a, a_1.c, b_1.b, b_1.a, i2_1.b
+ Join Filter: (b_1.b = a_1.a)
+ -> Hash Join
+ Output: b_1.b, b_1.a, i2_1.b
+ Hash Cond: (i2_1.b = b_1.b)
+ -> Seq Scan on public.prt2_p2 i2_1
+ Output: i2_1.b
+ -> Hash
+ Output: b_1.b, b_1.a
+ -> Seq Scan on public.prt2_p2 b_1
+ Output: b_1.b, b_1.a
+ Filter: ((b_1.a % 25) = 0)
+ -> Index Scan using iprt1_p2_a on public.prt1_p2 a_1
+ Output: a_1.a, a_1.c
+ Index Cond: (a_1.a = i2_1.b)
+ -> Index Only Scan using iprt1_p2_a on public.prt1_p2 i1_1
+ Output: i1_1.a
+ Index Cond: (i1_1.a = a_1.a)
+ -> Nested Loop
+ Output: a_2.c, b_2.b, b_2.a
+ -> Nested Loop
+ Output: a_2.a, a_2.c, b_2.b, b_2.a, i2_2.b
+ Join Filter: (b_2.b = a_2.a)
+ -> Hash Join
+ Output: b_2.b, b_2.a, i2_2.b
+ Hash Cond: (i2_2.b = b_2.b)
+ -> Seq Scan on public.prt2_p3 i2_2
+ Output: i2_2.b
+ -> Hash
+ Output: b_2.b, b_2.a
+ -> Seq Scan on public.prt2_p3 b_2
+ Output: b_2.b, b_2.a
+ Filter: ((b_2.a % 25) = 0)
+ -> Index Scan using iprt1_p3_a on public.prt1_p3 a_2
+ Output: a_2.a, a_2.c
+ Index Cond: (a_2.a = i2_2.b)
+ -> Index Only Scan using iprt1_p3_a on public.prt1_p3 i1_2
+ Output: i1_2.a
+ Index Cond: (i1_2.a = a_2.a)
+(80 rows)
+
+select b.b from prt1 a join prt2 b on a.a = b.b left join plt1 c on b.a % 25 = 0 and c.c = a.c join prt1 i1 on b.b = i1.a
+right join prt2 i2 on i2.b = b.b where b.a % 25 = 0 order by 1;
+ b
+-----
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 0
+ 150
+ 300
+ 450
+(28 rows)
+
--
-- list partitioned by expression
--
@@ -1304,6 +1923,86 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM plt1 t1, pl
574.0000000000000000 | 574.5000000000000000 | 1148.0000000000000000 | 0011 | 0011 | A0011
(12 rows)
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.a = t2.b AND t1.c = t2.c) LEFT JOIN plt1_e t3 ON (t2.b = t3.a AND t2.c = ltrim(t3.c, 'A')) WHERE t1.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------
+ Sort
+ Output: t1.a, t1.c, t2.b, t2.c, ((t3.a + t3.b)), t3.c
+ Sort Key: t1.a, t2.b, ((t3.a + t3.b))
+ -> Result
+ Output: t1.a, t1.c, t2.b, t2.c, (t3.a + t3.b), t3.c
+ -> Append
+ -> Hash Right Join
+ Output: t1.a, t1.c, t2.b, t2.c, t3.a, t3.b, t3.c
+ Hash Cond: ((t3.a = t2.b) AND (ltrim(t3.c, 'A'::text) = t2.c))
+ -> Seq Scan on public.plt1_e_p1 t3
+ Output: t3.a, t3.b, t3.c
+ -> Hash
+ Output: t1.a, t1.c, t2.b, t2.c
+ -> Hash Right Join
+ Output: t1.a, t1.c, t2.b, t2.c
+ Hash Cond: ((t2.b = t1.a) AND (t2.c = t1.c))
+ -> Seq Scan on public.plt2_p1 t2
+ Output: t2.b, t2.c
+ -> Hash
+ Output: t1.a, t1.c
+ -> Seq Scan on public.plt1_p1 t1
+ Output: t1.a, t1.c
+ Filter: ((t1.a % 25) = 0)
+ -> Hash Right Join
+ Output: t1_1.a, t1_1.c, t2_1.b, t2_1.c, t3_1.a, t3_1.b, t3_1.c
+ Hash Cond: ((t3_1.a = t2_1.b) AND (ltrim(t3_1.c, 'A'::text) = t2_1.c))
+ -> Seq Scan on public.plt1_e_p2 t3_1
+ Output: t3_1.a, t3_1.b, t3_1.c
+ -> Hash
+ Output: t1_1.a, t1_1.c, t2_1.b, t2_1.c
+ -> Hash Right Join
+ Output: t1_1.a, t1_1.c, t2_1.b, t2_1.c
+ Hash Cond: ((t2_1.b = t1_1.a) AND (t2_1.c = t1_1.c))
+ -> Seq Scan on public.plt2_p2 t2_1
+ Output: t2_1.b, t2_1.c
+ -> Hash
+ Output: t1_1.a, t1_1.c
+ -> Seq Scan on public.plt1_p2 t1_1
+ Output: t1_1.a, t1_1.c
+ Filter: ((t1_1.a % 25) = 0)
+ -> Hash Right Join
+ Output: t1_2.a, t1_2.c, t2_2.b, t2_2.c, t3_2.a, t3_2.b, t3_2.c
+ Hash Cond: ((t3_2.a = t2_2.b) AND (ltrim(t3_2.c, 'A'::text) = t2_2.c))
+ -> Seq Scan on public.plt1_e_p3 t3_2
+ Output: t3_2.a, t3_2.b, t3_2.c
+ -> Hash
+ Output: t1_2.a, t1_2.c, t2_2.b, t2_2.c
+ -> Hash Right Join
+ Output: t1_2.a, t1_2.c, t2_2.b, t2_2.c
+ Hash Cond: ((t2_2.b = t1_2.a) AND (t2_2.c = t1_2.c))
+ -> Seq Scan on public.plt2_p3 t2_2
+ Output: t2_2.b, t2_2.c
+ -> Hash
+ Output: t1_2.a, t1_2.c
+ -> Seq Scan on public.plt1_p3 t1_2
+ Output: t1_2.a, t1_2.c
+ Filter: ((t1_2.a % 25) = 0)
+(57 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.a = t2.b AND t1.c = t2.c) LEFT JOIN plt1_e t3 ON (t2.b = t3.a AND t2.c = ltrim(t3.c, 'A')) WHERE t1.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+ a | c | b | c | ?column? | c
+-----+------+-----+------+----------+-------
+ 0 | 0000 | 0 | 0000 | 0 | A0000
+ 50 | 0001 | | | |
+ 100 | 0002 | | | |
+ 150 | 0003 | 150 | 0003 | 300 | A0003
+ 200 | 0004 | | | |
+ 250 | 0005 | | | |
+ 300 | 0006 | 300 | 0006 | 600 | A0006
+ 350 | 0007 | | | |
+ 400 | 0008 | | | |
+ 450 | 0009 | 450 | 0009 | 900 | A0009
+ 500 | 0010 | | | |
+ 550 | 0011 | | | |
+(12 rows)
+
-- joins where one of the relations is proven empty
EXPLAIN (COSTS OFF)
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;
@@ -1361,6 +2060,172 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1
One-Time Filter: false
(14 rows)
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(t1.a), t1.c, avg(t2.b), t2.c FROM plt1 t1, plt2 t2 WHERE t1.c = t2.c AND t1.c NOT IN ('0001', '0005', '0002', '0009') AND t2.c NOT IN ('0000', '0003', '0004', '0010') GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Sort
+ Output: (sum(t1.a)), t1.c, (avg(t2.b)), t2.c
+ Sort Key: t1.c
+ -> HashAggregate
+ Output: sum(t1.a), t1.c, avg(t2.b), t2.c
+ Group Key: t1.c, t2.c
+ -> Result
+ Output: t1.c, t2.c, t1.a, t2.b
+ -> Append
+ -> Hash Join
+ Output: t1.a, t1.c, t2.b, t2.c
+ Hash Cond: (t1.c = t2.c)
+ -> Seq Scan on public.plt1_p3 t1
+ Output: t1.a, t1.c
+ Filter: (t1.c <> ALL ('{0001,0005,0002,0009}'::text[]))
+ -> Hash
+ Output: t2.b, t2.c
+ -> Seq Scan on public.plt2_p3 t2
+ Output: t2.b, t2.c
+ Filter: (t2.c <> ALL ('{0000,0003,0004,0010}'::text[]))
+(20 rows)
+
+SELECT sum(t1.a), t1.c, avg(t2.b), t2.c FROM plt1 t1, plt2 t2 WHERE t1.c = t2.c AND t1.c NOT IN ('0001', '0005', '0002', '0009') AND t2.c NOT IN ('0000', '0003', '0004', '0010') GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+ sum | c | avg | c
+--------+------+----------------------+------
+ 137700 | 0006 | 324.0000000000000000 | 0006
+ 158950 | 0007 | 375.0000000000000000 | 0007
+ 169600 | 0008 | 424.5000000000000000 | 0008
+ 229600 | 0011 | 574.5000000000000000 | 0011
+(4 rows)
+
+-- test merge join with index scan
+CREATE INDEX iplt1_p1_c on plt1_p1(c);
+CREATE INDEX iplt1_p2_c on plt1_p2(c);
+CREATE INDEX iplt1_p3_c on plt1_p3(c);
+CREATE INDEX iplt2_p1_c on plt2_p1(c);
+CREATE INDEX iplt2_p2_c on plt2_p2(c);
+CREATE INDEX iplt2_p3_c on plt2_p3(c);
+CREATE INDEX iplt1_e_p1_c on plt1_e_p1(ltrim(c, 'A'));
+CREATE INDEX iplt1_e_p2_c on plt1_e_p2(ltrim(c, 'A'));
+CREATE INDEX iplt1_e_p3_c on plt1_e_p3(ltrim(c, 'A'));
+ANALYZE plt1;
+ANALYZE plt2;
+ANALYZE plt1_e;
+SET enable_hashjoin TO off;
+SET enable_nestloop TO off;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM ((SELECT * FROM plt1 WHERE plt1.a % 25 = 0) t1 FULL JOIN (SELECT * FROM plt2 WHERE plt2.b % 25 = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c)) FULL JOIN (SELECT * FROM plt1_e WHERE plt1_e.a % 25 = 0) t3 ON (t1.a = t3.a AND ltrim(t3.c, 'A') = t1.c) ORDER BY t1.a, t2.b, t3.a + t3.b;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: plt1_p1.a, plt1_p1.c, plt2_p1.b, plt2_p1.c, ((plt1_e_p1.a + plt1_e_p1.b)), plt1_e_p1.c
+ Sort Key: plt1_p1.a, plt2_p1.b, ((plt1_e_p1.a + plt1_e_p1.b))
+ -> Result
+ Output: plt1_p1.a, plt1_p1.c, plt2_p1.b, plt2_p1.c, (plt1_e_p1.a + plt1_e_p1.b), plt1_e_p1.c
+ -> Append
+ -> Merge Full Join
+ Output: plt1_p1.a, plt1_p1.c, plt2_p1.b, plt2_p1.c, plt1_e_p1.a, plt1_e_p1.b, plt1_e_p1.c
+ Merge Cond: ((plt1_p1.a = plt1_e_p1.a) AND (plt1_p1.c = (ltrim(plt1_e_p1.c, 'A'::text))))
+ -> Sort
+ Output: plt1_p1.a, plt1_p1.c, plt2_p1.b, plt2_p1.c
+ Sort Key: plt1_p1.a, plt1_p1.c
+ -> Merge Full Join
+ Output: plt1_p1.a, plt1_p1.c, plt2_p1.b, plt2_p1.c
+ Merge Cond: ((plt1_p1.a = plt2_p1.b) AND (plt1_p1.c = plt2_p1.c))
+ -> Sort
+ Output: plt1_p1.a, plt1_p1.c
+ Sort Key: plt1_p1.a, plt1_p1.c
+ -> Seq Scan on public.plt1_p1
+ Output: plt1_p1.a, plt1_p1.c
+ Filter: ((plt1_p1.a % 25) = 0)
+ -> Sort
+ Output: plt2_p1.b, plt2_p1.c
+ Sort Key: plt2_p1.b, plt2_p1.c
+ -> Seq Scan on public.plt2_p1
+ Output: plt2_p1.b, plt2_p1.c
+ Filter: ((plt2_p1.b % 25) = 0)
+ -> Sort
+ Output: plt1_e_p1.a, plt1_e_p1.b, plt1_e_p1.c, (ltrim(plt1_e_p1.c, 'A'::text))
+ Sort Key: plt1_e_p1.a, (ltrim(plt1_e_p1.c, 'A'::text))
+ -> Seq Scan on public.plt1_e_p1
+ Output: plt1_e_p1.a, plt1_e_p1.b, plt1_e_p1.c, ltrim(plt1_e_p1.c, 'A'::text)
+ Filter: ((plt1_e_p1.a % 25) = 0)
+ -> Merge Full Join
+ Output: plt1_p2.a, plt1_p2.c, plt2_p2.b, plt2_p2.c, plt1_e_p2.a, plt1_e_p2.b, plt1_e_p2.c
+ Merge Cond: ((plt1_p2.a = plt1_e_p2.a) AND (plt1_p2.c = (ltrim(plt1_e_p2.c, 'A'::text))))
+ -> Sort
+ Output: plt1_p2.a, plt1_p2.c, plt2_p2.b, plt2_p2.c
+ Sort Key: plt1_p2.a, plt1_p2.c
+ -> Merge Full Join
+ Output: plt1_p2.a, plt1_p2.c, plt2_p2.b, plt2_p2.c
+ Merge Cond: ((plt1_p2.a = plt2_p2.b) AND (plt1_p2.c = plt2_p2.c))
+ -> Sort
+ Output: plt1_p2.a, plt1_p2.c
+ Sort Key: plt1_p2.a, plt1_p2.c
+ -> Seq Scan on public.plt1_p2
+ Output: plt1_p2.a, plt1_p2.c
+ Filter: ((plt1_p2.a % 25) = 0)
+ -> Sort
+ Output: plt2_p2.b, plt2_p2.c
+ Sort Key: plt2_p2.b, plt2_p2.c
+ -> Seq Scan on public.plt2_p2
+ Output: plt2_p2.b, plt2_p2.c
+ Filter: ((plt2_p2.b % 25) = 0)
+ -> Sort
+ Output: plt1_e_p2.a, plt1_e_p2.b, plt1_e_p2.c, (ltrim(plt1_e_p2.c, 'A'::text))
+ Sort Key: plt1_e_p2.a, (ltrim(plt1_e_p2.c, 'A'::text))
+ -> Seq Scan on public.plt1_e_p2
+ Output: plt1_e_p2.a, plt1_e_p2.b, plt1_e_p2.c, ltrim(plt1_e_p2.c, 'A'::text)
+ Filter: ((plt1_e_p2.a % 25) = 0)
+ -> Merge Full Join
+ Output: plt1_p3.a, plt1_p3.c, plt2_p3.b, plt2_p3.c, plt1_e_p3.a, plt1_e_p3.b, plt1_e_p3.c
+ Merge Cond: ((plt1_p3.a = plt1_e_p3.a) AND (plt1_p3.c = (ltrim(plt1_e_p3.c, 'A'::text))))
+ -> Sort
+ Output: plt1_p3.a, plt1_p3.c, plt2_p3.b, plt2_p3.c
+ Sort Key: plt1_p3.a, plt1_p3.c
+ -> Merge Full Join
+ Output: plt1_p3.a, plt1_p3.c, plt2_p3.b, plt2_p3.c
+ Merge Cond: ((plt1_p3.a = plt2_p3.b) AND (plt1_p3.c = plt2_p3.c))
+ -> Sort
+ Output: plt1_p3.a, plt1_p3.c
+ Sort Key: plt1_p3.a, plt1_p3.c
+ -> Seq Scan on public.plt1_p3
+ Output: plt1_p3.a, plt1_p3.c
+ Filter: ((plt1_p3.a % 25) = 0)
+ -> Sort
+ Output: plt2_p3.b, plt2_p3.c
+ Sort Key: plt2_p3.b, plt2_p3.c
+ -> Seq Scan on public.plt2_p3
+ Output: plt2_p3.b, plt2_p3.c
+ Filter: ((plt2_p3.b % 25) = 0)
+ -> Sort
+ Output: plt1_e_p3.a, plt1_e_p3.b, plt1_e_p3.c, (ltrim(plt1_e_p3.c, 'A'::text))
+ Sort Key: plt1_e_p3.a, (ltrim(plt1_e_p3.c, 'A'::text))
+ -> Seq Scan on public.plt1_e_p3
+ Output: plt1_e_p3.a, plt1_e_p3.b, plt1_e_p3.c, ltrim(plt1_e_p3.c, 'A'::text)
+ Filter: ((plt1_e_p3.a % 25) = 0)
+(87 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM ((SELECT * FROM plt1 WHERE plt1.a % 25 = 0) t1 FULL JOIN (SELECT * FROM plt2 WHERE plt2.b % 25 = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c)) FULL JOIN (SELECT * FROM plt1_e WHERE plt1_e.a % 25 = 0) t3 ON (t1.a = t3.a AND ltrim(t3.c, 'A') = t1.c) ORDER BY t1.a, t2.b, t3.a + t3.b;
+ a | c | b | c | ?column? | c
+-----+------+-----+------+----------+-------
+ 0 | 0000 | 0 | 0000 | 0 | A0000
+ 50 | 0001 | | | 100 | A0001
+ 100 | 0002 | | | 200 | A0002
+ 150 | 0003 | 150 | 0003 | 300 | A0003
+ 200 | 0004 | | | 400 | A0004
+ 250 | 0005 | | | 500 | A0005
+ 300 | 0006 | 300 | 0006 | 600 | A0006
+ 350 | 0007 | | | 700 | A0007
+ 400 | 0008 | | | 800 | A0008
+ 450 | 0009 | 450 | 0009 | 900 | A0009
+ 500 | 0010 | | | 1000 | A0010
+ 550 | 0011 | | | 1100 | A0011
+ | | 75 | 0001 | |
+ | | 225 | 0004 | |
+ | | 375 | 0007 | |
+ | | 525 | 0010 | |
+(16 rows)
+
+RESET enable_hashjoin;
+RESET enable_nestloop;
--
-- multi-leveled partitions
--
@@ -1687,6 +2552,42 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1_l WHERE a = 1 AND a = 2)
One-Time Filter: false
(11 rows)
+-- Join with pruned partitions from joining relations
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c AND t1.b + t1.a = t2.a + t2.b AND t1.a < 450 AND t2.b > 250 AND t1.a % 25 = 0 ORDER BY t1.a, t2.b;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
+ Sort
+ Output: t1.a, t1.c, t2.b, t2.c
+ Sort Key: t1.a
+ -> Append
+ -> Hash Join
+ Output: t1.a, t1.c, t2.b, t2.c
+ Hash Cond: ((t2.b = t1.a) AND (t2.a = t1.b) AND ((t2.c)::text = (t1.c)::text) AND ((t2.a + t2.b) = (t1.b + t1.a)))
+ -> Append
+ -> Seq Scan on public.prt2_l_p2_p1 t2
+ Output: t2.b, t2.c, t2.a
+ Filter: (t2.b > 250)
+ -> Seq Scan on public.prt2_l_p2_p2 t2_1
+ Output: t2_1.b, t2_1.c, t2_1.a
+ Filter: (t2_1.b > 250)
+ -> Hash
+ Output: t1.a, t1.c, t1.b
+ -> Append
+ -> Seq Scan on public.prt1_l_p2_p1 t1
+ Output: t1.a, t1.c, t1.b
+ Filter: ((t1.a < 450) AND ((t1.a % 25) = 0))
+ -> Seq Scan on public.prt1_l_p2_p2 t1_1
+ Output: t1_1.a, t1_1.c, t1_1.b
+ Filter: ((t1_1.a < 450) AND ((t1_1.a % 25) = 0))
+(23 rows)
+
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c AND t1.b + t1.a = t2.a + t2.b AND t1.a < 450 AND t2.b > 250 AND t1.a % 25 = 0 ORDER BY t1.a, t2.b;
+ a | c | b | c
+-----+------+-----+------
+ 300 | 0000 | 300 | 0000
+(1 row)
+
--
-- negative testcases
--
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 21734f5..d8f818a 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -99,6 +99,27 @@ SELECT t1.a, ss.t2a, ss.t2c FROM prt1 t1 LEFT JOIN LATERAL
(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
ON t1.c = ss.t2c WHERE (t1.b + coalesce(ss.t2b, 0)) = 0 ORDER BY t1.a;
+-- join with aggregate
+EXPLAIN (VERBOSE, COSTS OFF)
+select t1.a, count(t2.*) from prt1 t1 left join prt1 t2 on (t1.a = t2.a) where t1.a % 25 = 0 group by t1.a;
+select t1.a, count(t2.*) from prt1 t1 left join prt1 t2 on (t1.a = t2.a) where t1.a % 25 = 0 group by t1.a;
+
+-- lateral with VALUES
+EXPLAIN (VERBOSE, COSTS OFF)
+select count(*) from prt1 a, prt2 b join lateral (values(a.a)) ss(x) on b.b = ss.x;
+select count(*) from prt1 a, prt2 b join lateral (values(a.a)) ss(x) on b.b = ss.x;
+
+-- join with rank
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.b, t2.b,rank() OVER (ORDER BY t2.b DESC) FROM prt1 t1 inner join prt2 t2 on (t1.a = t2.b) where t1.a % 25 = 0 order by 1,2,3;
+SELECT t1.a, t1.b, t2.b,rank() OVER (ORDER BY t2.b DESC) FROM prt1 t1 inner join prt2 t2 on (t1.a = t2.b) where t1.a % 25 = 0 order by 1,2,3;
+
+--join with prepare statement
+PREPARE ij(int) AS select t1.a,t2.b from prt1 t1 inner join prt2 t2 on (t1.a = t2.b and t1.a % $1 = 0) ORDER BY 1,2;
+EXPLAIN (VERBOSE, COSTS OFF) EXECUTE ij(25);
+EXECUTE ij(25);
+DEALLOCATE ij;
+
--
-- partitioned by expression
--
@@ -163,7 +184,28 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1 WHERE t1.b IN (
EXPLAIN (COSTS OFF)
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;
-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;
+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;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+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.a %25 = 0)) AND t1.a % 25 = 0 ORDER BY t1.a;
+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.a %25 = 0)) AND t1.a % 25 = 0 ORDER BY t1.a;
+
+SET enable_seqscan TO off;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t2.b = (t3.a + t3.b)/2) WHERE t3.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 RIGHT JOIN prt2 t2 ON t1.a = t2.b) RIGHT JOIN prt1_e t3 ON (t2.b = (t3.a + t3.b)/2) WHERE t3.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+
+-- lateral references and parameterized paths
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM prt1 t1 LEFT JOIN LATERAL
+(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
+ON t1.a = ss.t2a WHERE t1.a % 25 = 0 ORDER BY t1.a;
+SELECT * FROM prt1 t1 LEFT JOIN LATERAL
+(SELECT t2.a AS t2a, t3.a AS t3a, least(t1.a,t2.a,t3.a) FROM prt1 t2 JOIN prt2 t3 ON (t2.a = t3.b)) ss
+ON t1.a = ss.t2a WHERE t1.a % 25 = 0 ORDER BY t1.a;
+RESET enable_seqscan;
-- MergeAppend on nullable column
EXPLAIN (COSTS OFF)
@@ -211,6 +253,20 @@ CREATE TABLE plt2_p3 PARTITION OF plt2 FOR VALUES IN ('0006', '0007', '0008', '0
INSERT INTO plt2 SELECT i, i, to_char(i/50, 'FM0000') FROM generate_series(0, 599, 3) i;
ANALYZE plt2;
+--full join using
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT * FROM plt1 t1 FULL JOIN plt2 t2 USING (c) FULL JOIN plt1 t3 USING (c)
+where t1.a %150 =0 and t2.b % 150 = 0 and t3.a % 150 = 0 order by 1,2,3;
+SELECT * FROM plt1 t1 FULL JOIN plt2 t2 USING (c) FULL JOIN plt1 t3 USING (c)
+where t1.a %150 =0 and t2.b % 150 = 0 and t3.a % 150 = 0 order by 1,2,3;
+
+-- test placement of movable quals in a parameterized join tree
+EXPLAIN (VERBOSE, COSTS OFF)
+select b.b from prt1 a join prt2 b on a.a = b.b left join plt1 c on b.a % 25 = 0 and c.c = a.c join prt1 i1 on b.b = i1.a
+right join prt2 i2 on i2.b = b.b where b.a % 25 = 0 order by 1;
+select b.b from prt1 a join prt2 b on a.a = b.b left join plt1 c on b.a % 25 = 0 and c.c = a.c join prt1 i1 on b.b = i1.a
+right join prt2 i2 on i2.b = b.b where b.a % 25 = 0 order by 1;
+
--
-- list partitioned by expression
--
@@ -226,6 +282,10 @@ EXPLAIN (COSTS OFF)
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.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;
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.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;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.a = t2.b AND t1.c = t2.c) LEFT JOIN plt1_e t3 ON (t2.b = t3.a AND t2.c = ltrim(t3.c, 'A')) WHERE t1.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (plt1 t1 LEFT JOIN plt2 t2 ON t1.a = t2.b AND t1.c = t2.c) LEFT JOIN plt1_e t3 ON (t2.b = t3.a AND t2.c = ltrim(t3.c, 'A')) WHERE t1.a % 25 = 0 ORDER BY t1.a, t2.b, t3.a + t3.b;
+
-- joins where one of the relations is proven empty
EXPLAIN (COSTS OFF)
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;
@@ -239,6 +299,35 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a = 1 AND a = 2) t1
EXPLAIN (COSTS OFF)
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;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT sum(t1.a), t1.c, avg(t2.b), t2.c FROM plt1 t1, plt2 t2 WHERE t1.c = t2.c AND t1.c NOT IN ('0001', '0005', '0002', '0009') AND t2.c NOT IN ('0000', '0003', '0004', '0010') GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+SELECT sum(t1.a), t1.c, avg(t2.b), t2.c FROM plt1 t1, plt2 t2 WHERE t1.c = t2.c AND t1.c NOT IN ('0001', '0005', '0002', '0009') AND t2.c NOT IN ('0000', '0003', '0004', '0010') GROUP BY t1.c, t2.c ORDER BY t1.c, t2.c;
+
+-- test merge join with index scan
+CREATE INDEX iplt1_p1_c on plt1_p1(c);
+CREATE INDEX iplt1_p2_c on plt1_p2(c);
+CREATE INDEX iplt1_p3_c on plt1_p3(c);
+CREATE INDEX iplt2_p1_c on plt2_p1(c);
+CREATE INDEX iplt2_p2_c on plt2_p2(c);
+CREATE INDEX iplt2_p3_c on plt2_p3(c);
+CREATE INDEX iplt1_e_p1_c on plt1_e_p1(ltrim(c, 'A'));
+CREATE INDEX iplt1_e_p2_c on plt1_e_p2(ltrim(c, 'A'));
+CREATE INDEX iplt1_e_p3_c on plt1_e_p3(ltrim(c, 'A'));
+
+ANALYZE plt1;
+ANALYZE plt2;
+ANALYZE plt1_e;
+
+SET enable_hashjoin TO off;
+SET enable_nestloop TO off;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM ((SELECT * FROM plt1 WHERE plt1.a % 25 = 0) t1 FULL JOIN (SELECT * FROM plt2 WHERE plt2.b % 25 = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c)) FULL JOIN (SELECT * FROM plt1_e WHERE plt1_e.a % 25 = 0) t3 ON (t1.a = t3.a AND ltrim(t3.c, 'A') = t1.c) ORDER BY t1.a, t2.b, t3.a + t3.b;
+SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM ((SELECT * FROM plt1 WHERE plt1.a % 25 = 0) t1 FULL JOIN (SELECT * FROM plt2 WHERE plt2.b % 25 = 0) t2 ON (t1.a = t2.b AND t1.c = t2.c)) FULL JOIN (SELECT * FROM plt1_e WHERE plt1_e.a % 25 = 0) t3 ON (t1.a = t3.a AND ltrim(t3.c, 'A') = t1.c) ORDER BY t1.a, t2.b, t3.a + t3.b;
+
+RESET enable_hashjoin;
+RESET enable_nestloop;
+
--
-- multi-leveled partitions
--
@@ -297,6 +386,11 @@ SELECT * FROM prt1_l t1 LEFT JOIN LATERAL
EXPLAIN (COSTS OFF)
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;
+-- Join with pruned partitions from joining relations
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c AND t1.b + t1.a = t2.a + t2.b AND t1.a < 450 AND t2.b > 250 AND t1.a % 25 = 0 ORDER BY t1.a, t2.b;
+SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1, prt2_l t2 WHERE t1.a = t2.b AND t1.b = t2.a AND t1.c = t2.c AND t1.b + t1.a = t2.a + t2.b AND t1.a < 450 AND t2.b > 250 AND t1.a % 25 = 0 ORDER BY t1.a, t2.b;
+
--
-- negative testcases
--
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
