Re: Push down more UPDATEs/DELETEs in postgres_fdw

Thanks Fujita-san for working on this.

> * 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 WIP patch has been created on top of the join pushdown patch [1]. So,
> for testing, please apply the patch in [1] first.
>
>
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? 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.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company