Re: Push down more UPDATEs/DELETEs in postgres_fdw

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.

Exactly.

> 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.

Thanks for the comments!

Best regards,
Etsuro Fujita