From: | Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Stephen Frost <sfrost(at)snowman(dot)net>, Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Optimization for updating foreign tables in Postgres FDW |
Date: | 2015-04-16 11:40:39 |
Message-ID: | 552F9FB7.8090707@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2015-04-16 11:57:03 | Re: Streaming replication and WAL archive interactions |
Previous Message | Amit Langote | 2015-04-16 10:57:43 | Re: Optimization for updating foreign tables in Postgres FDW |