Re: Optimization for updating foreign tables in Postgres FDW

On 2015/04/16 19:57, Amit Langote wrote:
> On 16-04-2015 PM 07:50, Etsuro Fujita wrote:
>>> The EXPLAIN output has also been improved as discussed in [1].
>>
>> I noticed that the EXPLAIN for a pushed-down update (delete) on inheritance
>> childs doubly displays "Foreign Update" ("Foreign Delete"), one for
>> ForeignScan and the other for ModifyTable. Here is an example:
>>
>> postgres=# explain verbose update parent set c1 = c1;
>> QUERY PLAN
>> ------------------------------------------------------------------------------
>> Update on public.parent (cost=0.00..364.54 rows=4819 width=10)
>> Update on public.parent
>> Foreign Update on public.ft1
>> Foreign Update on public.ft2
>> -> Seq Scan on public.parent (cost=0.00..0.00 rows=1 width=10)
>> Output: parent.c1, parent.ctid
>> -> Foreign Update on public.ft1 (cost=100.00..182.27 rows=2409 width=10)
>> Remote SQL: UPDATE public.t1 SET c1 = c1
>> -> Foreign Update on public.ft2 (cost=100.00..182.27 rows=2409 width=10)
>> Remote SQL: UPDATE public.t2 SET c1 = c1
>> (10 rows)
>>
>> Should we do something? Suggestions are welcome.

>>From what I see in Tom's commit message[0] for FTI patch, this shouldn't be,
> right?
>
> To be specific, there should be "Foreign Scan" there as per the commit. Am I
> missing something?

As shown in the below example, this patch doesn't change the EXPLAIN
output for non-pushed-down update (delete) cases, but since we changed
the EXPLAIN output as discussed in [1], the patch doubly displays
"Foreign Update" ("Foreign Delete") for pushed-down update (delet) cases
like the above example.

postgres=# explain verbose update parent set c1 = trunc(random() * 9 +
1)::int;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Update on public.parent (cost=0.00..452.06 rows=5461 width=6)
Update on public.parent
Foreign Update on public.ft1
Remote SQL: UPDATE public.t1 SET c1 = $2 WHERE ctid = $1
Foreign Update on public.ft2
Remote SQL: UPDATE public.t2 SET c1 = $2 WHERE ctid = $1
-> Seq Scan on public.parent (cost=0.00..0.01 rows=1 width=6)
Output: (trunc(((random() * '9'::double precision) +
'1'::double precision)))::integer, parent.ctid
-> Foreign Scan on public.ft1 (cost=100.00..226.03 rows=2730 width=6)
Output: (trunc(((random() * '9'::double precision) +
'1'::double precision)))::integer, ft1.ctid
Remote SQL: SELECT ctid FROM public.t1 FOR UPDATE
-> Foreign Scan on public.ft2 (cost=100.00..226.03 rows=2730 width=6)
Output: (trunc(((random() * '9'::double precision) +
'1'::double precision)))::integer, ft2.ctid
Remote SQL: SELECT ctid FROM public.t2 FOR UPDATE
(14 rows)

Best regards,
Etsuro Fujita