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.etsuro@lab.ntt.co.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
Deleteon 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.50rows=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