Thread: EXPLAIN doesn't show sufficient info for wCTE cases
EXPLAIN currently shows ModifyTable nodes as just "Insert", "Update",
or "Delete", without any indication of the target table. This was
more or less good enough when there could only be one such node per
query, but it's looking pretty inadequate to me as I play around
with data-modifying statements in WITH.
The obvious thing to do is show the target table much as we do for
table scan nodes, eg "Update on my_table". There is a deficiency
in that, which is that for inherited UPDATE/DELETE cases a single
ModifyTable node could have multiple target tables. But after
reflecting on it a bit, I think it would be good enough to show
the parent table name. The individual child plans will necessarily
include scans of the individual child tables, so you can figure
out which is which from that if you need to know.
Alternatively we could list all the target tables in a new node
attribute, eg
Update (costs...) Target Tables: foo_parent, foo_child1, ...
But in the majority of cases this seems like a waste of precious
vertical space.
Thoughts?
regards, tom lane
On Mon, Feb 28, 2011 at 11:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > EXPLAIN currently shows ModifyTable nodes as just "Insert", "Update", > or "Delete", without any indication of the target table. This was > more or less good enough when there could only be one such node per > query, but it's looking pretty inadequate to me as I play around > with data-modifying statements in WITH. > > The obvious thing to do is show the target table much as we do for > table scan nodes, eg "Update on my_table". There is a deficiency > in that, which is that for inherited UPDATE/DELETE cases a single > ModifyTable node could have multiple target tables. But after > reflecting on it a bit, I think it would be good enough to show > the parent table name. The individual child plans will necessarily > include scans of the individual child tables, so you can figure > out which is which from that if you need to know. > > Alternatively we could list all the target tables in a new node > attribute, eg > > Update (costs...) > Target Tables: foo_parent, foo_child1, ... > > But in the majority of cases this seems like a waste of precious > vertical space. > > Thoughts? I think it's good to include the table name, for sure. I *think* I agree that it isn't necessary to include the child names. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Feb 28, 2011 at 11:44:06AM -0500, Robert Haas wrote: > On Mon, Feb 28, 2011 at 11:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > EXPLAIN currently shows ModifyTable nodes as just "Insert", "Update", > > or "Delete", without any indication of the target table. This was > > more or less good enough when there could only be one such node per > > query, but it's looking pretty inadequate to me as I play around > > with data-modifying statements in WITH. > > > > The obvious thing to do is show the target table much as we do for > > table scan nodes, eg "Update on my_table". There is a deficiency > > in that, which is that for inherited UPDATE/DELETE cases a single > > ModifyTable node could have multiple target tables. But after > > reflecting on it a bit, I think it would be good enough to show > > the parent table name. The individual child plans will necessarily > > include scans of the individual child tables, so you can figure > > out which is which from that if you need to know. > > > > Alternatively we could list all the target tables in a new node > > attribute, eg > > > > Update (costs...) > > Target Tables: foo_parent, foo_child1, ... > > > > But in the majority of cases this seems like a waste of precious > > vertical space. > > > > Thoughts? > > I think it's good to include the table name, for sure. I *think* I > agree that it isn't necessary to include the child names. Would this affect the use case of breaking up a too-long table into partitions? WITH f AS ( DELETE FROM ONLY foo WHERE foo_ts >= '2011-01-01' AND foo_ts < '2011-02-01' RETURNING * ) INSERT INTO foo_201101 SELECT * FROM f; Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Mon, Feb 28, 2011 at 11:44:06AM -0500, Robert Haas wrote:
>> I think it's good to include the table name, for sure. I *think* I
>> agree that it isn't necessary to include the child names.
> Would this affect the use case of breaking up a too-long table into
> partitions?
> WITH f AS (
> DELETE FROM ONLY foo
> WHERE foo_ts >= '2011-01-01' AND foo_ts < '2011-02-01'
> RETURNING *
> )
> INSERT INTO foo_201101
> SELECT * FROM f;
In that example, each ModifyTable node is constrained to a single
target table, so I'm not sure what your point is.
regards, tom lane