Re: Push down more UPDATEs/DELETEs in postgres_fdw

On 2016/09/08 19:55, Etsuro Fujita wrote:

On 2016/09/07 13:21, Ashutosh Bapat wrote:

    * with the patch:
    postgres=# explain verbose delete from ft1 using ft2 where ft1.a =
    ft2.a;
                                                             QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------

     Delete on public.ft1  (cost=100.00..102.04 rows=1 width=38)
       ->  Foreign Delete  (cost=100.00..102.04 rows=1 width=38)
             Remote SQL: DELETE FROM public.t1 r1 USING (SELECT ROW(a,
    b), a FROM public.t2) ss1(c1, c2) WHERE ((r1.a = ss1.c2))
    (3 rows)

The underlying scan on t2 requires ROW(a,b) for locking the row for
update/share. But clearly it's not required if the full query is being
pushed down.

Is there a way we can detect that ROW(a,b) is useless
column (not used anywhere in the other parts of the query like
RETURNING, DELETE clause etc.) and eliminate it?

I don't have a clear solution for that yet, but I'll try to remove that
in the next version.

Similarly for a, it's
part of the targetlist of the underlying scan so that the WHERE clause
can be applied on it. But it's not needed if we are pushing down the
query. If we eliminate the targetlist of the query, we could construct a
remote query without having subquery in it, making it more readable.

Will try to do so also.

I addressed this by improving the deparse logic so that a remote query for performing an UPDATE/DELETE on a join directly on the remote can be created as proposed if possible.  Attached is an updated version of the patch, which is created on top of the patch set [1].  The patch is still WIP (ie, needs more comments and regression tests, at least), but any comments would be gratefully appreciated.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/11eafd10-d3f8-ac8a-b642-b0e65037c76b%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