Re: Optimization for updating foreign tables in Postgres FDW

On 2016/02/12 21:46, Robert Haas wrote:
> On Fri, Feb 12, 2016 at 7:19 AM, Etsuro Fujita
> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> I think that displaying target lists would be confusing for users. Here is
>> an example:
>>
>> EXPLAIN (verbose, costs off)
>> DELETE FROM rem1; -- can be pushed down
>> QUERY PLAN
>> ---------------------------------------------
>> Delete on public.rem1
>> -> Foreign Delete on public.rem1
>> Output: ctid
>> Remote SQL: DELETE FROM public.loc1
>> (4 rows)
>>
>> Should we output the "Output" line?

> I see your point, but what if there's a RETURNING clause?

IMO I think that would be confusing in that case. Here is an example:

EXPLAIN (verbose, costs off)
DELETE FROM rem1 RETURNING *; -- can be pushed down
QUERY PLAN
--------------------------------------------------------------
Delete on public.rem1
Output: f1, f2
-> Foreign Delete on public.rem1
Output: ctid
Remote SQL: DELETE FROM public.loc1 RETURNING f1, f2
(5 rows)

The Output line beneath the ForeignScan node doesn't match the RETURNING
expressions in the remote query as the Output line beneath the
ModifyTable node does, so I think displaying that would be confusing
even in that case.

Another example:

postgres=# explain verbose update foo set a = a + 1 returning *;
QUERY PLAN
------------------------------------------------------------------------------
Update on public.foo (cost=100.00..137.50 rows=1000 width=10)
Output: a
-> Foreign Update on public.foo (cost=100.00..137.50 rows=1000
width=10)
Output: (a + 1), ctid
Remote SQL: UPDATE public.foo SET a = (a + 1) RETURNING a
(5 rows)

Same above.

As for case of INSERT .. RETURNING .., I guess there is not such a
mismatch, but I'm not sure that displaying that is that helpful,
honestly, so I'd vote for suppressing that in all cases, for consistency.

Best regards,
Etsuro Fujita