Re: Display of multi-target-table Modify plan nodes in EXPLAIN

On Mon, Mar 23, 2015 at 8:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> writes:
> > On Mon, Mar 23, 2015 at 10:51 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Hm? We don't have scan nodes that read more than one table, so I'm
> >> not following your point.
>
> > But in the text output you gave
> > Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46)
> > Update on public.pt1
> > Foreign Update on public.ft1
> > Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1
> > Foreign Update on public.ft2
> > Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1
> > Update on public.child3
> > -> Seq Scan on public.pt1 (cost=0.00..0.00 rows=1 width=46)
> > Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid
> > ... etc ...
>
> > For ft1 there is only Update Remote SQL. whereas for child3 you have
> > specified the Seq Scan as well.
>
> I think you're confused by my perhaps-overly-abbreviated example.
> Here's the whole output for the example in HEAD:
>
> # explain update pt1 set c1=c1+1;
> QUERY PLAN
> ---------------------------------------------------------------------
> Update on pt1 (cost=0.00..321.05 rows=3541 width=46)
> Update on pt1
> Foreign Update on ft1
> Foreign Update on ft2
> Update on child3
> -> Seq Scan on pt1 (cost=0.00..0.00 rows=1 width=46)
> -> Foreign Scan on ft1 (cost=100.00..148.03 rows=1170 width=46)
> -> Foreign Scan on ft2 (cost=100.00..148.03 rows=1170 width=46)
> -> Seq Scan on child3 (cost=0.00..25.00 rows=1200 width=46)
> (9 rows)
>
> # explain verbose update pt1 set c1=c1+1;
> QUERY PLAN
>
> ----------------------------------------------------------------------------
> Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46)
> Update on public.pt1
> Foreign Update on public.ft1
> Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1
> Foreign Update on public.ft2
> Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1
> Update on public.child3
> -> Seq Scan on public.pt1 (cost=0.00..0.00 rows=1 width=46)
> Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid
> -> Foreign Scan on public.ft1 (cost=100.00..148.03 rows=1170 width=46)
> Output: (ft1.c1 + 1), ft1.c2, ft1.c3, ft1.ctid
> Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref1 FOR UPDATE
> -> Foreign Scan on public.ft2 (cost=100.00..148.03 rows=1170 width=46)
> Output: (ft2.c1 + 1), ft2.c2, ft2.c3, ft2.ctid
> Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref2 FOR UPDATE
> -> Seq Scan on public.child3 (cost=0.00..25.00 rows=1200 width=46)
> Output: (child3.c1 + 1), child3.c2, child3.c3, child3.ctid
> (17 rows)
>
>
>
Thanks for the clarification. This is good. However, the way the scan nodes
are indented, it looks like they are associated with public.child3 and not
the upper Update nodes. Best way would have been to print scan nodes with
corresponding update nodes. If that's not possible, we may print it the
following way

# explain verbose update pt1 set c1=c1+1;
QUERY PLAN
----------------------------------------------------------------------------
Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46)
Update on public.pt1
Foreign Update on public.ft1
Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1
Foreign Update on public.ft2
Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1
Update on public.child3
Scans (or Plans whatever)
-> Seq Scan on public.pt1 (cost=0.00..0.00 rows=1 width=46)
Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid
-> Foreign Scan on public.ft1 (cost=100.00..148.03 rows=1170 width=46)
Output: (ft1.c1 + 1), ft1.c2, ft1.c3, ft1.ctid
Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref1 FOR UPDATE
-> Foreign Scan on public.ft2 (cost=100.00..148.03 rows=1170 width=46)
Output: (ft2.c1 + 1), ft2.c2, ft2.c3, ft2.ctid
Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref2 FOR UPDATE
-> Seq Scan on public.child3 (cost=0.00..25.00 rows=1200 width=46)
Output: (child3.c1 + 1), child3.c2, child3.c3, child3.ctid
Somehow, we should highlight the fact that every update has associated
scan/plan and update nodes and scan nodes are children of ModifyTable (i.e.
the uppermost Update node).

regards, tom lane
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company