Re: Push down more full joins in postgres_fdw - Mailing list pgsql-hackers

Hi,

The postgres_fdw join pushdown in 9.6 is great, but it can't handle full joins on relations with restrictions.  The reason for that is, postgres_fdw can't support deparsing subqueries when creating a remote join query.  So, by adding the deparsing logic to it, I removed that limitation.  Attached is a patch for that, which is based on the patch I posted before [1].

Also, by the deparsing logic, I improved the handling of PHVs so that PHVs are evaluated remotely if it's safe, as discussed in [2].  Here is an example from the regression test, which pushes down multiple levels of PHVs to the remote:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT ss.*, ft2.c1 FROM ft2 LEFT JOIN (SELECT 13, q.a, ft2.c1 FROM (SELECT 13 FROM ft1 WHERE c1 = 13) q(a) RIGHT JOIN ft2 ON (q.a = ft2.c1) WHERE ft2.\
c1 BETWEEN 10 AND 15) ss(f1, f2, f3) ON (ft2.c1 = ss.f1) WHERE ft2.c1 BETWEEN 10 AND 15;
              \
                           QUERY PLAN             \

-------------------------------------------------------------------------------------------------------------------------------------------------------\
-------------------------------------------------------------------------------------------------------------------------------------------------------\
---------------------------------------------------------------
  Foreign Scan
    Output: (13), (13), ft2_1.c1, ft2.c1
    Relations: (public.ft2) LEFT JOIN ((public.ft2) LEFT JOIN (public.ft1))
    Remote SQL: SELECT r1."C 1", ss2.c1, ss2.c2, ss2.c3 FROM ("S 1"."T 1" r1 LEFT JOIN (SELECT r5."C 1", 13, ss1.c1 FROM ("S 1"."T 1" r5 LEFT JOIN (SELE\
CT 13 FROM "S 1"."T 1" WHERE (("C 1" = 13))) ss1(c1) ON (((13 = r5."C 1")))) WHERE ((r5."C 1" >= 10)) AND ((r5."C 1" <= 15))) ss2(c1, c2, c3) ON (((r1.\
"C 1" = 13)))) WHERE ((r1."C 1" >= 10)) AND ((r1."C 1" <= 15))
(4 rows)

Also, in the same way as the PHV handling, I improved the handling of whole-row reference (and system columns other than ctid), as proposed in [3].  We don't need the ""CASE WHEN ... IS NOT NULL THEN ROW(...) END" conditions any more, as shown in the below example:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.ctid, t1, t2, t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
              \
                         QUERY PLAN             \

-------------------------------------------------------------------------------------------------------------------------------------------------------\
-------------------------------------------------------------------------------------------------------------------------------------------------------\
-----------------------------------------------------------
  Limit
    Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
    ->  Foreign Scan
          Output: t1.ctid, t1.*, t2.*, t1.c1, t1.c3
          Relations: (public.ft1 t1) INNER JOIN (public.ft2 t2)
          Remote SQL: SELECT ss1.c1, ss1.c2, ss1.c3, ss1.c4, ss2.c1 FROM ((SELECT ctid, ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1", c3 FROM "S 1"."T \
1") ss1(c1, c2, c3, c4) INNER JOIN (SELECT ROW("C 1", c2, c3, c4, c5, c6, c7, c8), "C 1" FROM "S 1"."T 1") ss2(c1, c2) ON (TRUE)) WHERE ((ss1.c3 = ss2.\
c2)) ORDER BY ss1.c4 ASC NULLS LAST, ss1.c3 ASC NULLS LAST
(6 rows)

I'll add this to the next CF.  Comments are welcome!

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/5710D7E2.7010302%40lab.ntt.co.jp
[2] https://www.postgresql.org/message-id/b4549406-909f-7d15-dc34-499835a8f0b3%40lab.ntt.co.jp
[3] https://www.postgresql.org/message-id/a1fa1c4c-bf96-8ea5-cff5-85b927298e73%40lab.ntt.co.jp


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers