Skip site navigation (1) Skip section navigation (2)

Push down more full joins in postgres_fdw

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Push down more full joins in postgres_fdw
Date: 2016-08-19 09:20:32
Message-ID: c449261a-b033-dc02-9254-2fe5b7044795@lab.ntt.co.jp (view raw, whole thread or download thread mbox)
Thread:
Lists: 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

Attachment: postgres-fdw-more-full-join-pushdown.patch
Description: binary/octet-stream (108.2 KB)

Responses

pgsql-hackers by date

Next:From: Aleksander AlekseevDate: 2016-08-19 09:50:30
Subject: Re: Re: PROPOSAL: make PostgreSQL sanitizers-friendly (and prevent information disclosure)
Previous:From: Pavel StehuleDate: 2016-08-19 08:58:52
Subject: patch: function xmltable

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group