* 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