Re: Optimization for updating foreign tables in Postgres FDW

Hi,

At Mon, 20 Apr 2015 16:40:52 +0900, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote in
<5534AD84.3020501@lab.ntt.co.jp>
> On 2015/04/17 13:16, Amit Langote wrote:
> > On 17-04-2015 PM 12:35, Etsuro Fujita wrote:
> >> (2) that might cause the problem of associating subplans' update
> >> information with subplans' scan information, pointed out by Tom [1].
> 
> > Having realized how it really works now, my +1 to "Foreign Modifying Scan" for
> > cases of pushed down update as suggested by Albe Laurenz. I guess it would be
> > signaled by the proposed ForeignScan.CmdType being CMD_UPDATE / CMP_UPDATE
> > (/CMD_INSERT).
> 
> Thanks for the opinion!  I think that that is an idea.  However, I'd
> like to propose to rename "Foreign Update" ("Foreign Delete") of
> ModifyTable simply to "Update" ("Delete") not only because (1) that
> solves the duplication problem but also because (2) ISTM that is
> consistent with the non-inherited updates in both of the
> non-pushed-down-update case and the pushed-down-update case.  Here are
> examples for (2).

Update node without "Foreign" that runs "Remote SQL" looks to me
somewhat unusual..

It seems to me that the problem is "Foreign Update"s for
ModifyTable that does nothing eventually. Even though I don't
understand this fully, especially what "Foreign Update" for
ModifyTable does when "Foreign Update" in place of "Foreign Scan"
finished substantial work, I think the ForeignUpdate nodes should
be removed during planning if it is really ineffective, or such
"Foreign Update"s shoud be renamed or provided with some
explaination in explain output if it does anything or unremovable
from some reason.

If removed it looks like,

| =# explain verbose update p set b = b + 1;
|                                   QUERY PLAN                                  
| ------------------------------------------------------------------------------
|  Update on public.p  (cost=0.00..360.08 rows=4311 width=14)
|    Update on public.p
|    ->  Seq Scan on public.p  (cost=0.00..0.00 rows=1 width=14)
|          Output: p.a, (p.b + 1), p.ctid
|    ->  Foreign Update on public.ft1  (cost=100.00..180.04 rows=2155 width=14)
|          Remote SQL: UPDATE public.t1 SET b = (b + 1)
|    ->  Foreign Update on public.ft2  (cost=100.00..180.04 rows=2155 width=14)
|          Remote SQL: UPDATE public.t2 SET b = (b + 1)

regards,

> * Inherited and non-inherited updates for the non-pushed-down case:
> 
> 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
>    Update on public.ft1
>      Remote SQL: UPDATE public.t1 SET c1 = $2 WHERE ctid = $1
>    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)
> 
> postgres=# explain verbose update ft1 set c1 = trunc(random() * 9 + 1)::int;
>                                               QUERY PLAN
> ------------------------------------------------------------------------------------------------------
>  Update on public.ft1  (cost=100.00..226.03 rows=2730 width=6)
>    Remote SQL: UPDATE public.t1 SET c1 = $2 WHERE ctid = $1
>    ->  Foreign Scan on public.ft1  (cost=100.00..226.03 rows=2730 width=6)
>          Output: (trunc(((random() * '9'::double precision) +
> '1'::double precision)))::integer, ctid
>          Remote SQL: SELECT ctid FROM public.t1 FOR UPDATE
> (5 rows)
> 
> * Inherited and non-inherited updates for the pushed-down case:
> 
> postgres=# explain verbose update parent set c1 = c1 + 1;
>                                   QUERY PLAN
> ------------------------------------------------------------------------------
>  Update on public.parent  (cost=0.00..376.59 rows=4819 width=10)
>    Update on public.parent
>    Update on public.ft1
>    Update on public.ft2
>    ->  Seq Scan on public.parent  (cost=0.00..0.00 rows=1 width=10)
>          Output: (parent.c1 + 1), parent.ctid
>    ->  Foreign Update on public.ft1  (cost=100.00..188.29 rows=2409
> width=10)
>          Remote SQL: UPDATE public.t1 SET c1 = (c1 + 1)
>    ->  Foreign Update on public.ft2  (cost=100.00..188.29 rows=2409
> width=10)
>          Remote SQL: UPDATE public.t2 SET c1 = (c1 + 1)
> (10 rows)
> 
> postgres=# explain verbose update ft1 set c1 = c1 + 1;
>                                   QUERY PLAN
> ------------------------------------------------------------------------------
>  Update on public.ft1  (cost=100.00..188.29 rows=2409 width=10)
>    ->  Foreign Update on public.ft1  (cost=100.00..188.29 rows=2409
> width=10)
>          Remote SQL: UPDATE public.t1 SET c1 = (c1 + 1)
> (3 rows)
> 
> Comments are welcome.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center