Re: Push down more UPDATEs/DELETEs in postgres_fdw

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Push down more UPDATEs/DELETEs in postgres_fdw
Date: 2016-11-11 11:30:39
Message-ID: 38245b84-fabf-0899-1b24-8f94cdc5900c@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
postgres-fdw-more-update-pushdown-WIP-2.patch application/x-patch 49.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Craig Ringer 2016-11-11 11:46:57 Re: Shared memory estimation for postgres
Previous Message Petr Jelinek 2016-11-11 11:15:46 Re: Logical Replication WIP