Skip to content

Latest commit

 

History

History
350 lines (321 loc) · 25.6 KB

20231207_03.md

File metadata and controls

350 lines (321 loc) · 25.6 KB
Raw

PostgreSQL 17 preview - postgres_fdw 支持semi-join pushdown (exists (...))

作者

digoal

日期

2023-12-07

标签

PostgreSQL , PolarDB , DuckDB , semi-join , postgres_fdw , pushdown

背景

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=824dbea3e41efa3b35094163c834988dea7eb139

Add support for deparsing semi-joins to contrib/postgres_fdw  
  
author	Alexander Korotkov <akorotkov@postgresql.org>	  
Tue, 5 Dec 2023 20:53:12 +0000 (22:53 +0200)  
committer	Alexander Korotkov <akorotkov@postgresql.org>	  
Tue, 5 Dec 2023 20:53:12 +0000 (22:53 +0200)  
commit	824dbea3e41efa3b35094163c834988dea7eb139  
tree	f810eb49e750dee3b601385328faa0f4369291f6	tree  
parent	278eb13c48236c261ed4bab1cb4696321e346eb7	commit | diff  
Add support for deparsing semi-joins to contrib/postgres_fdw  
  
SEMI-JOIN is deparsed as the EXISTS subquery. It references outer and inner  
relations, so it should be evaluated as the condition in the upper-level WHERE  
clause. The signatures of deparseFromExprForRel() and deparseRangeTblRef() are  
revised so that they can add conditions to the upper level.  
  
PgFdwRelationInfo now has a hidden_subquery_rels field, referencing the relids  
used in the inner parts of semi-join.  They can't be referred to from upper  
relations and should be used internally for equivalence member searches.  
  
The planner can create semi-join, which refers to inner rel vars in its target  
list. However, we deparse semi-join as an exists() subquery. So we skip the  
case when the target list references to inner rel of semi-join.  
  
Author: Alexander Pyhalov  
Reviewed-by: Ashutosh Bapat, Ian Lawrence Barwick, Yuuki Fujii, Tomas Vondra  
Discussion: https://postgr.es/m/c9e2a757cf3ac2333714eaf83a9cc184@postgrespro.ru

例子:

+-- ===================================================================  
+-- test SEMI-JOIN pushdown  
+-- ===================================================================  
+EXPLAIN (verbose, costs off)  
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1  
+  WHERE ft2.c1 > 900  
+  AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)  
+  ORDER BY ft2.c1;  
+                                                                                                                                                QUERY PLAN                                                                                                                                                   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Foreign Scan  
+   Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3  
+   Relations: ((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft5)  
+   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r2.c1, r2.c2, r2.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1.c2 = r2.c1)) AND ((r1."C 1" > 900)))) WHERE EXISTS (SELECT NULL FROM "S 1"."T 4" r4 WHERE ((r1.c2 = r4.c1))) ORDER BY r1."C 1" ASC NULLS LAST  
+(4 rows)  
+  
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN ft4 ON ft2.c2 = ft4.c1  
+  WHERE ft2.c1 > 900  
+  AND EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)  
+  ORDER BY ft2.c1;  
+ c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  | c1 | c2 |   c3     
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 916 |  6 | 00916 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 926 |  6 | 00926 | Tue Jan 27 00:00:00 1970 PST | Tue Jan 27 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 936 |  6 | 00936 | Fri Feb 06 00:00:00 1970 PST | Fri Feb 06 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 946 |  6 | 00946 | Mon Feb 16 00:00:00 1970 PST | Mon Feb 16 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 956 |  6 | 00956 | Thu Feb 26 00:00:00 1970 PST | Thu Feb 26 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 966 |  6 | 00966 | Sun Mar 08 00:00:00 1970 PST | Sun Mar 08 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 976 |  6 | 00976 | Wed Mar 18 00:00:00 1970 PST | Wed Mar 18 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 986 |  6 | 00986 | Sat Mar 28 00:00:00 1970 PST | Sat Mar 28 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 996 |  6 | 00996 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+(10 rows)  
+  
+-- The same query, different join order  
+EXPLAIN (verbose, costs off)  
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN  
+  (SELECT * FROM ft4 WHERE  
+  EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4  
+  ON ft2.c2 = ft4.c1  
+  WHERE ft2.c1 > 900  
+  ORDER BY ft2.c1;  
+                                                                                                                                                QUERY PLAN                                                                                                                                                   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Foreign Scan  
+   Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3  
+   Relations: ((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft5)  
+   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r4.c1, r4.c2, r4.c3 FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r4 ON (((r1.c2 = r4.c1)) AND ((r1."C 1" > 900)))) WHERE EXISTS (SELECT NULL FROM "S 1"."T 4" r5 WHERE ((r4.c1 = r5.c1))) ORDER BY r1."C 1" ASC NULLS LAST  
+(4 rows)  
+  
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN  
+  (SELECT * FROM ft4 WHERE  
+  EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4  
+  ON ft2.c2 = ft4.c1  
+  WHERE ft2.c1 > 900  
+  ORDER BY ft2.c1;  
+ c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  | c1 | c2 |   c3     
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 916 |  6 | 00916 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 926 |  6 | 00926 | Tue Jan 27 00:00:00 1970 PST | Tue Jan 27 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 936 |  6 | 00936 | Fri Feb 06 00:00:00 1970 PST | Fri Feb 06 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 946 |  6 | 00946 | Mon Feb 16 00:00:00 1970 PST | Mon Feb 16 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 956 |  6 | 00956 | Thu Feb 26 00:00:00 1970 PST | Thu Feb 26 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 966 |  6 | 00966 | Sun Mar 08 00:00:00 1970 PST | Sun Mar 08 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 976 |  6 | 00976 | Wed Mar 18 00:00:00 1970 PST | Wed Mar 18 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 986 |  6 | 00986 | Sat Mar 28 00:00:00 1970 PST | Sat Mar 28 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 996 |  6 | 00996 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+(10 rows)  
+  
+-- Left join  
+EXPLAIN (verbose, costs off)  
+SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN  
+  (SELECT * FROM ft4 WHERE  
+  EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4  
+  ON ft2.c2 = ft4.c1  
+  WHERE ft2.c1 > 900  
+  ORDER BY ft2.c1 LIMIT 10;  
+                                                                                                                                                                                  QUERY PLAN                                                                                                                                                                                    
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Foreign Scan  
+   Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3  
+   Relations: (public.ft2) LEFT JOIN ((public.ft4) SEMI JOIN (public.ft5))  
+   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, s6.c1, s6.c2, s6.c3 FROM ("S 1"."T 1" r1 LEFT JOIN (SELECT r4.c1, r4.c2, r4.c3 FROM "S 1"."T 3" r4 WHERE EXISTS (SELECT NULL FROM "S 1"."T 4" r5 WHERE ((r4.c1 = r5.c1)))) s6(c1, c2, c3) ON (((r1.c2 = s6.c1)))) WHERE ((r1."C 1" > 900)) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint  
+(4 rows)  
+  
+SELECT ft2.*, ft4.* FROM ft2 LEFT JOIN  
+  (SELECT * FROM ft4 WHERE  
+  EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4  
+  ON ft2.c2 = ft4.c1  
+  WHERE ft2.c1 > 900  
+  ORDER BY ft2.c1 LIMIT 10;  
+ c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  | c1 | c2 |   c3     
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------  
+ 901 |  1 | 00901 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo |    |    |   
+ 902 |  2 | 00902 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2  | 2          | foo |    |    |   
+ 903 |  3 | 00903 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3  | 3          | foo |    |    |   
+ 904 |  4 | 00904 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4  | 4          | foo |    |    |   
+ 905 |  5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5  | 5          | foo |    |    |   
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 907 |  7 | 00907 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo |    |    |   
+ 908 |  8 | 00908 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8  | 8          | foo |    |    |   
+ 909 |  9 | 00909 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9  | 9          | foo |    |    |   
+ 910 |  0 | 00910 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0  | 0          | foo |    |    |   
+(10 rows)  
+  
+-- Several semi-joins per upper level join  
+EXPLAIN (verbose, costs off)  
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN  
+  (SELECT * FROM ft4 WHERE  
+  EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4  
+  ON ft2.c2 = ft4.c1  
+  INNER JOIN (SELECT * FROM ft5 WHERE  
+  EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5  
+  ON ft2.c2 <= ft5.c1  
+  WHERE ft2.c1 > 900  
+  ORDER BY ft2.c1 LIMIT 10;  
+                                                                                                                                                                                                                     QUERY PLAN                                                                                                                                                                                                                        
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Foreign Scan  
+   Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3  
+   Relations: ((((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft5)) INNER JOIN (public.ft5 ft5_1)) SEMI JOIN (public.ft4 ft4_1)  
+   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r6.c1, r6.c2, r6.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r6 ON (((r1.c2 = r6.c1)) AND ((r1."C 1" > 900)))) INNER JOIN "S 1"."T 4" r8 ON (((r1.c2 <= r8.c1)))) WHERE EXISTS (SELECT NULL FROM "S 1"."T 3" r9 WHERE ((r8.c1 = r9.c1))) AND EXISTS (SELECT NULL FROM "S 1"."T 4" r7 WHERE ((r6.c1 = r7.c1))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint  
+(4 rows)  
+  
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN  
+  (SELECT * FROM ft4 WHERE  
+  EXISTS (SELECT 1 FROM ft5 WHERE ft4.c1 = ft5.c1)) ft4  
+  ON ft2.c2 = ft4.c1  
+  INNER JOIN (SELECT * FROM ft5 WHERE  
+  EXISTS (SELECT 1 FROM ft4 WHERE ft4.c1 = ft5.c1)) ft5  
+  ON ft2.c2 <= ft5.c1  
+  WHERE ft2.c1 > 900  
+  ORDER BY ft2.c1 LIMIT 10;  
+ c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  | c1 | c2 |   c3     
+-----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+ 906 |  6 | 00906 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo |  6 |  7 | AAA006  
+(10 rows)  
+  
+-- Semi-join below Semi-join  
+EXPLAIN (verbose, costs off)  
+SELECT ft2.* FROM ft2 WHERE  
+  c1 = ANY (  
+   SELECT c1 FROM ft2 WHERE  
+     EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2))  
+  AND ft2.c1 > 900  
+  ORDER BY ft2.c1 LIMIT 10;  
+                                                                                                                                                          QUERY PLAN                                                                                                                                                             
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Foreign Scan  
+   Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8  
+   Relations: (public.ft2) SEMI JOIN ((public.ft2 ft2_1) SEMI JOIN (public.ft4))  
+   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" > 900)) AND EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r1."C 1" = r3."C 1")) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r4 WHERE ((r3.c2 = r4.c2)))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint  
+(4 rows)  
+  
+SELECT ft2.* FROM ft2 WHERE  
+  c1 = ANY (  
+   SELECT c1 FROM ft2 WHERE  
+     EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2))  
+  AND ft2.c1 > 900  
+  ORDER BY ft2.c1 LIMIT 10;  
+ c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8    
+-----+----+-------+------------------------------+--------------------------+----+------------+-----  
+ 903 |  3 | 00903 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3  | 3          | foo  
+ 905 |  5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5  | 5          | foo  
+ 907 |  7 | 00907 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo  
+ 909 |  9 | 00909 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9  | 9          | foo  
+ 913 |  3 | 00913 | Wed Jan 14 00:00:00 1970 PST | Wed Jan 14 00:00:00 1970 | 3  | 3          | foo  
+ 915 |  5 | 00915 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5  | 5          | foo  
+ 917 |  7 | 00917 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7  | 7          | foo  
+ 919 |  9 | 00919 | Tue Jan 20 00:00:00 1970 PST | Tue Jan 20 00:00:00 1970 | 9  | 9          | foo  
+ 923 |  3 | 00923 | Sat Jan 24 00:00:00 1970 PST | Sat Jan 24 00:00:00 1970 | 3  | 3          | foo  
+ 925 |  5 | 00925 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5  | 5          | foo  
+(10 rows)  
+  
+-- Upper level relations shouldn't refer EXISTS() subqueries  
+EXPLAIN (verbose, costs off)  
+SELECT * FROM ft2 ftupper WHERE  
+   EXISTS (  
+   SELECT c1 FROM ft2 WHERE  
+     EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 )  
+  AND ftupper.c1 > 900  
+  ORDER BY ftupper.c1 LIMIT 10;  
+                                                                                                                                                          QUERY PLAN                                                                                                                                                             
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Foreign Scan  
+   Output: ftupper.c1, ftupper.c2, ftupper.c3, ftupper.c4, ftupper.c5, ftupper.c6, ftupper.c7, ftupper.c8  
+   Relations: (public.ft2 ftupper) SEMI JOIN ((public.ft2) SEMI JOIN (public.ft4))  
+   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" > 900)) AND EXISTS (SELECT NULL FROM "S 1"."T 1" r2 WHERE ((r1."C 1" = r2."C 1")) AND EXISTS (SELECT NULL FROM "S 1"."T 3" r3 WHERE ((r2.c2 = r3.c2)))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint  
+(4 rows)  
+  
+SELECT * FROM ft2 ftupper WHERE  
+   EXISTS (  
+   SELECT c1 FROM ft2 WHERE  
+     EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 )  
+  AND ftupper.c1 > 900  
+  ORDER BY ftupper.c1 LIMIT 10;  
+ c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8    
+-----+----+-------+------------------------------+--------------------------+----+------------+-----  
+ 903 |  3 | 00903 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3  | 3          | foo  
+ 905 |  5 | 00905 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5  | 5          | foo  
+ 907 |  7 | 00907 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo  
+ 909 |  9 | 00909 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9  | 9          | foo  
+ 913 |  3 | 00913 | Wed Jan 14 00:00:00 1970 PST | Wed Jan 14 00:00:00 1970 | 3  | 3          | foo  
+ 915 |  5 | 00915 | Fri Jan 16 00:00:00 1970 PST | Fri Jan 16 00:00:00 1970 | 5  | 5          | foo  
+ 917 |  7 | 00917 | Sun Jan 18 00:00:00 1970 PST | Sun Jan 18 00:00:00 1970 | 7  | 7          | foo  
+ 919 |  9 | 00919 | Tue Jan 20 00:00:00 1970 PST | Tue Jan 20 00:00:00 1970 | 9  | 9          | foo  
+ 923 |  3 | 00923 | Sat Jan 24 00:00:00 1970 PST | Sat Jan 24 00:00:00 1970 | 3  | 3          | foo  
+ 925 |  5 | 00925 | Mon Jan 26 00:00:00 1970 PST | Mon Jan 26 00:00:00 1970 | 5  | 5          | foo  
+(10 rows)  
+  
+-- EXISTS should be propogated to the highest upper inner join  
+EXPLAIN (verbose, costs off)  
+   SELECT ft2.*, ft4.* FROM ft2 INNER JOIN  
+   (SELECT * FROM ft4 WHERE EXISTS (  
+       SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4  
+   ON ft2.c2 = ft4.c1  
+   INNER JOIN  
+   (SELECT * FROM ft2 WHERE EXISTS (  
+       SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21  
+   ON ft2.c2 = ft21.c2  
+   WHERE ft2.c1 > 900  
+   ORDER BY ft2.c1 LIMIT 10;  
+                                                                                                                                                                                                                     QUERY PLAN                                                                                                                                                                                                                       
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Foreign Scan  
+   Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3  
+   Relations: ((((public.ft2) INNER JOIN (public.ft4)) SEMI JOIN (public.ft2 ft2_1)) INNER JOIN (public.ft2 ft2_2)) SEMI JOIN (public.ft4 ft4_1)  
+   Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, r6.c1, r6.c2, r6.c3 FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r6 ON (((r1.c2 = r6.c1)) AND ((r1."C 1" > 900)))) INNER JOIN "S 1"."T 1" r8 ON (((r1.c2 = r8.c2)))) WHERE EXISTS (SELECT NULL FROM "S 1"."T 3" r9 WHERE ((r1.c2 = r9.c2))) AND EXISTS (SELECT NULL FROM "S 1"."T 1" r7 WHERE ((r7.c2 = r6.c2))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT 10::bigint  
+(4 rows)  
+  
+SELECT ft2.*, ft4.* FROM ft2 INNER JOIN  
+   (SELECT * FROM ft4 WHERE EXISTS (  
+       SELECT 1 FROM ft2 WHERE ft2.c2 = ft4.c2)) ft4  
+   ON ft2.c2 = ft4.c1  
+   INNER JOIN  
+   (SELECT * FROM ft2 WHERE EXISTS (  
+       SELECT 1 FROM ft4 WHERE ft2.c2 = ft4.c2)) ft21  
+   ON ft2.c2 = ft21.c2  
+   WHERE ft2.c1 > 900  
+   ORDER BY ft2.c1 LIMIT 10;  
+ c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3   
+----+----+----+----+----+----+----+----+----+----+----  
+(0 rows)  
+  
+-- Can't push down semi-join with inner rel vars in targetlist  
+EXPLAIN (verbose, costs off)  
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE  
+   ft1.c1 IN (  
+       SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)  
+   ORDER BY ft1.c1 LIMIT 5;  
+                                                                            QUERY PLAN                                                                               
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------  
+ Limit  
+   Output: ft1.c1  
+   ->  Merge Semi Join  
+         Output: ft1.c1  
+         Merge Cond: (ft1.c1 = ft2_1.c1)  
+         ->  Foreign Scan  
+               Output: ft1.c1, ft2.c1  
+               Relations: (public.ft1) INNER JOIN (public.ft2)  
+               Remote SQL: SELECT r1."C 1", r2."C 1" FROM ("S 1"."T 1" r1 INNER JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r1."C 1")))) ORDER BY r1."C 1" ASC NULLS LAST  
+         ->  Foreign Scan  
+               Output: ft2_1.c1, ft4.c1  
+               Relations: (public.ft2 ft2_1) INNER JOIN (public.ft4)  
+               Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST  
+(13 rows)

期望 PostgreSQL|开源PolarDB 增加什么功能?

PolarDB 云原生分布式开源数据库

PolarDB 学习图谱: 训练营、培训认证、在线互动实验、解决方案、内核开发公开课、生态合作、写心得拿奖品

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

购买PolarDB云服务折扣活动进行中, 55元起

About 德哥