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

On Sun, Mar 22, 2015 at 6:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I've gotten the foreign table inheritance patch to a state where I'm
almost ready to commit it, but there's one thing that's bothering me,
which is what it does for EXPLAIN. As it stands you might get something
like

regression=# explain (verbose) update pt1 set c1=c1+1;
QUERY PLAN

----------------------------------------------------------------------------
Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46)
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
-> 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
(15 rows)

which seems fairly messy to me because you have to guess at which of
the child plan subtrees goes with which "Remote SQL" item.

In a green field we might choose to solve this by refactoring the output
so that it's logically

Multi-Table Update
[
Update Target: pt1
Plan: (seq scan on pt1 here)
]
[
Update Target: ft1
Remote SQL: UPDATE ref1 ...
Plan: (foreign scan on ft1 here)
]
[
Update Target: ft2
Remote SQL: UPDATE ref2 ...
Plan: (foreign scan on ft2 here)
]
[
Update Target: child3
Plan: (seq scan on child3 here)
]

but I think that ship has sailed. Changing the logical structure of
EXPLAIN output like this would break clients that know what's where in
JSON/YAML/XML formats, which is exactly what we said we wouldn't do with
those output formats.

What I'm imagining instead is that when there's more than one
target relation, we produce output like

Multi-Table Update
Relation Name: pt1 -- this is the *nominal* target
Target Relations:
[
Relation Name: pt1 -- first actual target
Schema: public
Alias: pt1
]
[
Relation Name: ft1
Schema: public
Alias: ft1
Remote SQL: UPDATE ref1 ...
]
[
Relation Name: ft2
Schema: public
Alias: ft2
Remote SQL: UPDATE ref2 ...
]
[
Relation Name: child3
Schema: public
Alias: child3
]
Plans:
Plan: (seq scan on pt1 here)
Plan: (foreign scan on ft1 here)
Plan: (foreign scan on ft2 here)
Plan: (seq scan on child3 here)

That is, there'd be a new subnode of ModifyTable (which existing clients
would ignore), and that would fully identify *each* target table not only
foreign ones. The text-mode output might look like

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

where there would always now be as many target tables listed as
there are child plan trees.

This looks better.
In the format above, you have specified both the Remote SQL for scan as
well as update but in the example you have only mentioned only Remote SQL
for update; it may be part of "... etc ...". It's better to provide both.

Thoughts, better ideas?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:

On Sun, Mar 22, 2015 at 6:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

What I'm imagining instead is that when there's more than one
target relation, we produce output like ...

This looks better.
In the format above, you have specified both the Remote SQL for scan as
well as update but in the example you have only mentioned only Remote SQL
for update; it may be part of "... etc ...". It's better to provide both.

Hm? We don't have scan nodes that read more than one table, so I'm
not following your point.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Mar 23, 2015 at 10:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:

On Sun, Mar 22, 2015 at 6:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

What I'm imagining instead is that when there's more than one
target relation, we produce output like ...

This looks better.
In the format above, you have specified both the Remote SQL for scan as
well as update but in the example you have only mentioned only Remote SQL
for update; it may be part of "... etc ...". It's better to provide both.

Hm? We don't have scan nodes that read more than one table, so I'm
not following your point.

regards, tom lane

In the format you specified
Multi-Table Update
Relation Name: pt1 -- this is the *nominal* target
Target Relations:
[
Relation Name: pt1 -- first actual target
Schema: public
Alias: pt1
]
[
Relation Name: ft1
Schema: public
Alias: ft1
Remote SQL: UPDATE ref1 ...
]

Plans:
Plan: (seq scan on pt1 here)
Plan: (foreign scan on ft1 here)
For relation ft1, there is an Update node as well as a scan node. Update
node has Update Remote SQL and Scan will have corresponding SELECT Remote
SQL.

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 was wondering if the foreign scan on ft1
is part of "...etc..."?. Further we have to make it clear which scan goes
with which Update, either by listing the scans in the same order as updates
or by associating each scan with corresponding update.

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

On 03/22/2015 03:02 AM, Tom Lane wrote:

In a green field we might choose to solve this by refactoring the output
so that it's logically

Multi-Table Update
[
Update Target: pt1
Plan: (seq scan on pt1 here)
]
[
Update Target: ft1
Remote SQL: UPDATE ref1 ...
Plan: (foreign scan on ft1 here)
]
[
Update Target: ft2
Remote SQL: UPDATE ref2 ...
Plan: (foreign scan on ft2 here)
]
[
Update Target: child3
Plan: (seq scan on child3 here)
]

The "Remote SQL" nodes should go under the Foreign Scan nodes.

but I think that ship has sailed. Changing the logical structure of
EXPLAIN output like this would break clients that know what's where in
JSON/YAML/XML formats, which is exactly what we said we wouldn't do with
those output formats.

If we have promised that, I think we should break the promise. No
application should depend on the details of EXPLAIN output, even if it's
in JSON/YAML/XML format. EXPLAIN is used by humans, and by tools like
pgAdmin that display the output for humans, so let's do what makes most
sense for humans. Admin tools will have to deal with new node types, and
also new plan structures in every new release anyway. And if an admin
tool doesn't recognize the new format, it surely falls back to
displaying them in some a reasonable generic form.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Mar 22, 2015 at 11:38 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:

If we have promised that, I think we should break the promise. No
application should depend on the details of EXPLAIN output, even if it's in
JSON/YAML/XML format. EXPLAIN is used by humans, and by tools like pgAdmin
that display the output for humans, so let's do what makes most sense for
humans. Admin tools will have to deal with new node types, and also new plan
structures in every new release anyway. And if an admin tool doesn't
recognize the new format, it surely falls back to displaying them in some a
reasonable generic form.

+1

--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Heikki Linnakangas <hlinnaka@iki.fi> writes:

On 03/22/2015 03:02 AM, Tom Lane wrote:

In a green field we might choose to solve this by refactoring the output
so that it's logically ...
but I think that ship has sailed. Changing the logical structure of
EXPLAIN output like this would break clients that know what's where in
JSON/YAML/XML formats, which is exactly what we said we wouldn't do with
those output formats.

If we have promised that, I think we should break the promise. No
application should depend on the details of EXPLAIN output, even if it's
in JSON/YAML/XML format.

I think this is entirely wrong. The entire point of having those
machine-readable output formats was to let people write tools that would
process plans in some intelligent manner. Relocating where child plans of
a Modify appear in the data structure would certainly break any tool that
had any understanding of plan trees. Now, maybe there are no such tools,
but in that case the whole exercise in adding those formats was a waste of
time and we should rip them out.

In any case, what I was suggesting here is only very marginally cleaner
than what got implemented, so it really doesn't seem to me to be worth
breaking backwards compatibility here, even if I bought the premise that
backwards compatibility of this output is of low priority.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:

On Mon, Mar 23, 2015 at 10:51 AM, Tom Lane <tgl@sss.pgh.pa.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)

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Mar 23, 2015 at 10:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Heikki Linnakangas <hlinnaka@iki.fi> writes:

On 03/22/2015 03:02 AM, Tom Lane wrote:

In a green field we might choose to solve this by refactoring the output
so that it's logically ...
but I think that ship has sailed. Changing the logical structure of
EXPLAIN output like this would break clients that know what's where in
JSON/YAML/XML formats, which is exactly what we said we wouldn't do with
those output formats.

If we have promised that, I think we should break the promise. No
application should depend on the details of EXPLAIN output, even if it's
in JSON/YAML/XML format.

I think this is entirely wrong. The entire point of having those
machine-readable output formats was to let people write tools that would
process plans in some intelligent manner. Relocating where child plans of
a Modify appear in the data structure would certainly break any tool that
had any understanding of plan trees. Now, maybe there are no such tools,
but in that case the whole exercise in adding those formats was a waste of
time and we should rip them out.

In any case, what I was suggesting here is only very marginally cleaner
than what got implemented, so it really doesn't seem to me to be worth
breaking backwards compatibility here, even if I bought the premise that
backwards compatibility of this output is of low priority.

I agree that we shouldn't break backward compatibility here for no
particularly good reason, but I also think it would be fine to break
it if the new output were a significant improvement. People who write
tools that parse this output should, and I think do, understand that
sometimes we'll make changes upstream and they'll need to adjust for
it. We shouldn't do that on a whim, but we shouldn't let it stand in
the way of progress, either.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:

On Mon, Mar 23, 2015 at 10:51 AM, Tom Lane <tgl@sss.pgh.pa.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