Re: Optimization for updating foreign tables in Postgres FDW

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).

* 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.

Best regards,
Etsuro Fujita