EXPLAIN omits schema?
I was just looking at implementing some query tuning/debugging features
in pgAdmin, and was looking to use EXPLAIN output to get a list of the
base tables involved in the users' query. Unfortunately though it
doesn't include the schema name in the output which means I have no way
of telling for sure which table is being referred to (even in a single
query, consider SELECT * FROM s1.foo, s2.foo).
Looking to fix this, a comment in src/backend/commands/explain.c
indicates that this is intentional:
/* We only show the rel name, not schema name */
relname = get_rel_name(rte->relid);
Anyone know why? This seems like a bug to me given the ambiguity of
possible output.
Regards, Dave.
* Dave Page (dpage@postgresql.org) wrote:
/* We only show the rel name, not schema name */
relname = get_rel_name(rte->relid);Anyone know why? This seems like a bug to me given the ambiguity of
possible output.
I'd assume it's to keep the explain output smaller with the
expectation/assumption that in general you're going to know. A possible
work-around would be to just always provide table aliases for your
queries, as those are shown in the explain.
In terms of behaviour changes, I think it'd be nice to show the schema
name when necessary but otherwise don't, ala how '\d <view>' works.
Another option might be to omit the schema when an alias is provided, or
maybe even omit the entire table name in favor of the alias.
Just my 2c.
Thanks,
Stephen
Stephen Frost wrote:
* Dave Page (dpage@postgresql.org) wrote:
/* We only show the rel name, not schema name */
relname = get_rel_name(rte->relid);Anyone know why? This seems like a bug to me given the ambiguity of
possible output.I'd assume it's to keep the explain output smaller with the
expectation/assumption that in general you're going to know. A possible
work-around would be to just always provide table aliases for your
queries, as those are shown in the explain.
I have no control over the queries themselves.
In terms of behaviour changes, I think it'd be nice to show the schema
name when necessary but otherwise don't, ala how '\d <view>' works.
In my case that would be awkward as pgAdmin would then need to try to
work out what the actual table was based on the search path used for the
users query.
Another option might be to omit the schema when an alias is provided, or
maybe even omit the entire table name in favor of the alias.
That would make it very painful as I'd need to parse the query client
side to resolve the table names. Yeuch.
Just adding the schema name seems the most sensible and usable option -
not to mention the easiest!
Regards, Dave
* Dave Page (dpage@postgresql.org) wrote:
Stephen Frost wrote:
In terms of behaviour changes, I think it'd be nice to show the schema
name when necessary but otherwise don't, ala how '\d <view>' works.In my case that would be awkward as pgAdmin would then need to try to
work out what the actual table was based on the search path used for the
users query.
Actually, as mentioned in another thread, a function to take a table
name and a search_path and return the 'fully qualified' table name would
make that much easier, and would be useful in other situations.
Another option might be to omit the schema when an alias is provided, or
maybe even omit the entire table name in favor of the alias.That would make it very painful as I'd need to parse the query client
side to resolve the table names. Yeuch.
Indeed, if you're not constructing the queries that would make things
somewhat difficult. Then again, parsing the explain output seems like
it's going to be rather difficult itself anyway.
Just adding the schema name seems the most sensible and usable option -
not to mention the easiest!
While completely ignoring the current behaviour and likely the reason
it's done the way it is now... explain output was, and still is
primairly, for humans to read.
Thanks,
Stephen
Stephen Frost wrote:
Indeed, if you're not constructing the queries that would make things
somewhat difficult. Then again, parsing the explain output seems like
it's going to be rather difficult itself anyway.
Well, we do that anyway - and just grabbing the base table names isn't
too hard.
Just adding the schema name seems the most sensible and usable option -
not to mention the easiest!While completely ignoring the current behaviour and likely the reason
it's done the way it is now... explain output was, and still is
primairly, for humans to read.
Humans deserve schemas as well!! :-). As for the likely reason for the
current behaviour, well, I'd rather have precise,
non-potentially-ambiguous info than save a few characters.
Regards, Dave
Stephen Frost wrote:
* Dave Page (dpage@postgresql.org) wrote:
/* We only show the rel name, not schema name */
relname = get_rel_name(rte->relid);Anyone know why? This seems like a bug to me given the ambiguity of
possible output.I'd assume it's to keep the explain output smaller with the
expectation/assumption that in general you're going to know. A possible
work-around would be to just always provide table aliases for your
queries, as those are shown in the explain.
I am hoping that once we have WITH RECURSIVE, we could optionally
provide a normalized dump into a table of the EXPLAIN output, that could
then be easily "connected" the the old output using WITH RECURSIVE.
regards,
Lukas
On Wed, Jun 13, 2007 at 01:20:25PM +0100, Dave Page wrote:
Just adding the schema name seems the most sensible and usable option -
not to mention the easiest!While completely ignoring the current behaviour and likely the reason
it's done the way it is now... explain output was, and still is
primairly, for humans to read.Humans deserve schemas as well!! :-). As for the likely reason for the
current behaviour, well, I'd rather have precise,
non-potentially-ambiguous info than save a few characters.
Just to open a whole new can of worms ;-)
I read an article a couple of days ago about the "machine readable showplan
output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
format). It does make a lot of sense if yourp rimary interface is !=
commandline (psql), such as pgadmin or phppgadmin. The idea being that you
can stick in *all* the details you want, since you can't possibly clutter
up the display. And you stick them in a well-defined XML format (or another
format if you happen to hate XML) where the client-side program can easily
parse out whatever it needs. It's also future-proof - if you add a new
field somewhere, the client program parser won't break.
Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?
//Magnus
Magnus Hagander wrote:
Just to open a whole new can of worms ;-)
I read an article a couple of days ago about the "machine readable showplan
output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
format). It does make a lot of sense if yourp rimary interface is !=
commandline (psql), such as pgadmin or phppgadmin. The idea being that you
can stick in *all* the details you want, since you can't possibly clutter
up the display. And you stick them in a well-defined XML format (or another
format if you happen to hate XML) where the client-side program can easily
parse out whatever it needs. It's also future-proof - if you add a new
field somewhere, the client program parser won't break.Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?
FYI a patch was posted for this some time ago, because a friend of mine
wanted to help a student to write an EXPLAIN parsing tool.
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)
On Wed, Jun 13, 2007 at 08:47:30AM -0400, Alvaro Herrera wrote:
Magnus Hagander wrote:
Just to open a whole new can of worms ;-)
I read an article a couple of days ago about the "machine readable showplan
output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
format). It does make a lot of sense if yourp rimary interface is !=
commandline (psql), such as pgadmin or phppgadmin. The idea being that you
can stick in *all* the details you want, since you can't possibly clutter
up the display. And you stick them in a well-defined XML format (or another
format if you happen to hate XML) where the client-side program can easily
parse out whatever it needs. It's also future-proof - if you add a new
field somewhere, the client program parser won't break.Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?FYI a patch was posted for this some time ago, because a friend of mine
wanted to help a student to write an EXPLAIN parsing tool.
Didn't see that one. Explain in XML format? Got an URL for it, I can't seem
to find it on -patches.
//Magnus
Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?//Magnus
It's good idea. Similar situation is in stack trace output.
Pavel
Magnus Hagander wrote:
On Wed, Jun 13, 2007 at 01:20:25PM +0100, Dave Page wrote:
Just adding the schema name seems the most sensible and usable option -
not to mention the easiest!While completely ignoring the current behaviour and likely the reason
it's done the way it is now... explain output was, and still is
primairly, for humans to read.Humans deserve schemas as well!! :-). As for the likely reason for the
current behaviour, well, I'd rather have precise,
non-potentially-ambiguous info than save a few characters.Just to open a whole new can of worms ;-)
I read an article a couple of days ago about the "machine readable showplan
output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
format). It does make a lot of sense if yourp rimary interface is !=
commandline (psql), such as pgadmin or phppgadmin. The idea being that you
can stick in *all* the details you want, since you can't possibly clutter
up the display. And you stick them in a well-defined XML format (or another
format if you happen to hate XML) where the client-side program can easily
parse out whatever it needs. It's also future-proof - if you add a new
field somewhere, the client program parser won't break.Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?
I agree it would be nice to have machine readable explain output.
DB2 has the concept of "explain tables". Explain output is written to
tables, which tools query and pretty print the output. I like that idea
in principle. PostgreSQL is a relational database, so having the explain
output in relations make sense. No need for XML or any other extra
libraries, in either the server or client. Having the data in relational
format allows you to query them. For example, show me all sequential
scans, or all nodes where the estimated number of rows is off by a
certain factor.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
* Heikki Linnakangas (heikki@enterprisedb.com) wrote:
Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?I agree it would be nice to have machine readable explain output.
Seconded here, I'd much rather see this as a seperate option rather than
cluttering up regular 'explain' output for the humans. I do think we
should provide the schema name when it's not clear from the search_path
tho, since that helps the humans too. :)
DB2 has the concept of "explain tables". Explain output is written to
tables, which tools query and pretty print the output. I like that idea
in principle. PostgreSQL is a relational database, so having the explain
output in relations make sense. No need for XML or any other extra
libraries, in either the server or client. Having the data in relational
format allows you to query them. For example, show me all sequential
scans, or all nodes where the estimated number of rows is off by a
certain factor.
I like this approach, the only downside is someone/something needs to manage
those tables, unless you can say where the tables are to put the explain
output into or similar? Also, with tables, if someone really wants XML
the tables can be extracted as XML.
Thanks,
Stephen
On Wed, Jun 13, 2007 at 02:02:24PM +0100, Heikki Linnakangas wrote:
Magnus Hagander wrote:
On Wed, Jun 13, 2007 at 01:20:25PM +0100, Dave Page wrote:
Just adding the schema name seems the most sensible and usable option -
not to mention the easiest!While completely ignoring the current behaviour and likely the reason
it's done the way it is now... explain output was, and still is
primairly, for humans to read.Humans deserve schemas as well!! :-). As for the likely reason for the
current behaviour, well, I'd rather have precise,
non-potentially-ambiguous info than save a few characters.Just to open a whole new can of worms ;-)
I read an article a couple of days ago about the "machine readable showplan
output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
format). It does make a lot of sense if yourp rimary interface is !=
commandline (psql), such as pgadmin or phppgadmin. The idea being that you
can stick in *all* the details you want, since you can't possibly clutter
up the display. And you stick them in a well-defined XML format (or another
format if you happen to hate XML) where the client-side program can easily
parse out whatever it needs. It's also future-proof - if you add a new
field somewhere, the client program parser won't break.Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?I agree it would be nice to have machine readable explain output.
DB2 has the concept of "explain tables". Explain output is written to
tables, which tools query and pretty print the output. I like that idea
in principle. PostgreSQL is a relational database, so having the explain
output in relations make sense. No need for XML or any other extra
libraries, in either the server or client. Having the data in relational
format allows you to query them. For example, show me all sequential
scans, or all nodes where the estimated number of rows is off by a
certain factor.
Assuming you can actually *represent* the whole plan as tables, that would
of course work fine. But I assume you mean "virtual tables"? So I do
EXPLAIN whatever, and get back one or more resultssets with the data? Or do
they write it to *actual* tables in the database?
Machine-readable is of course the main point - the exact format is more of
an implementation detail.
//Magnus
Magnus Hagander wrote:
On Wed, Jun 13, 2007 at 02:02:24PM +0100, Heikki Linnakangas wrote:
DB2 has the concept of "explain tables". Explain output is written to
tables, which tools query and pretty print the output. I like that idea
in principle. PostgreSQL is a relational database, so having the explain
output in relations make sense. No need for XML or any other extra
libraries, in either the server or client. Having the data in relational
format allows you to query them. For example, show me all sequential
scans, or all nodes where the estimated number of rows is off by a
certain factor.Assuming you can actually *represent* the whole plan as tables, that would
of course work fine.
Sure you can. It's just a question of how complex the schema is :).
But I assume you mean "virtual tables"? So I do
EXPLAIN whatever, and get back one or more resultssets with the data? Or do
they write it to *actual* tables in the database?
I'm not sure. DB2 had real tables, but I found that a bit clumsy. It was
nice because your old explain results were accumulated, but it was also
not nice because of that same thing.
One idea would be temporary tables.
Machine-readable is of course the main point - the exact format is more of
an implementation detail.
Agreed.
A potential problem is that as we add new node types etc., we need to
extend the schema (whether it's a real relational schema or XML), and
clients need to understand it. But I guess we already have the same
problem with clients that parse the current explain output.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Magnus Hagander wrote:
On Wed, Jun 13, 2007 at 08:47:30AM -0400, Alvaro Herrera wrote:
Magnus Hagander wrote:
Just to open a whole new can of worms ;-)
I read an article a couple of days ago about the "machine readable showplan
output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
format). It does make a lot of sense if yourp rimary interface is !=
commandline (psql), such as pgadmin or phppgadmin. The idea being that you
can stick in *all* the details you want, since you can't possibly clutter
up the display. And you stick them in a well-defined XML format (or another
format if you happen to hate XML) where the client-side program can easily
parse out whatever it needs. It's also future-proof - if you add a new
field somewhere, the client program parser won't break.Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?FYI a patch was posted for this some time ago, because a friend of mine
wanted to help a student to write an EXPLAIN parsing tool.Didn't see that one. Explain in XML format? Got an URL for it, I can't seem
to find it on -patches.
I can't find the patch itself ... maybe he didn't ever post it. He last
talked about it here:
http://archives.postgresql.org/pgsql-hackers/2006-04/msg00455.php
BTW can I bug you to add the Message-Ids in the messages as displayed in
our archives?
--
Alvaro Herrera http://www.PlanetPostgreSQL.org/
"El d�a que dejes de cambiar dejar�s de vivir"
* Alvaro Herrera <alvherre@commandprompt.com> [070613 09:58]:
BTW can I bug you to add the Message-Ids in the messages as displayed in
our archives?
Yes! Yes! Yes! Pretty please!
--
Aidan Van Dyk Create like a god,
aidan@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.
Dave Page <dpage@postgresql.org> writes:
Looking to fix this, a comment in src/backend/commands/explain.c
indicates that this is intentional:
Quite.
Anyone know why?
As already noted, it'd usually be clutter in lines that are too long
already. Also, conditionally adding a schema name isn't very good
because it makes life even more complicated for programs that are
parsing EXPLAIN output (yes, there are some).
I agree with the idea of having an option to get EXPLAIN's output in
an entirely different, more machine-readable format. Not wedded to
XML, but I fear that a pure relational structure might be too strict ---
there's a lot of variability in the entries already. XML also could
deal naturally with nesting, whereas we'd have to jump through hoops
to represent the plan tree structure in relational form.
regards, tom lane
On Wed, Jun 13, 2007 at 09:55:19AM -0400, Alvaro Herrera wrote:
Magnus Hagander wrote:
On Wed, Jun 13, 2007 at 08:47:30AM -0400, Alvaro Herrera wrote:
Magnus Hagander wrote:
Just to open a whole new can of worms ;-)
I read an article a couple of days ago about the "machine readable showplan
output" in SQL Server 2005 (basically, it's EXPLAIN output but in XML
format). It does make a lot of sense if yourp rimary interface is !=
commandline (psql), such as pgadmin or phppgadmin. The idea being that you
can stick in *all* the details you want, since you can't possibly clutter
up the display. And you stick them in a well-defined XML format (or another
format if you happen to hate XML) where the client-side program can easily
parse out whatever it needs. It's also future-proof - if you add a new
field somewhere, the client program parser won't break.Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?FYI a patch was posted for this some time ago, because a friend of mine
wanted to help a student to write an EXPLAIN parsing tool.Didn't see that one. Explain in XML format? Got an URL for it, I can't seem
to find it on -patches.I can't find the patch itself ... maybe he didn't ever post it. He last
talked about it here:
http://archives.postgresql.org/pgsql-hackers/2006-04/msg00455.phpBTW can I bug you to add the Message-Ids in the messages as displayed in
our archives?
No. Because I don't know how to do that :-) And what work is done to th
archives should be done to redo the whole thing and not bandaid what we
have now.
That said, you can get the message-id if you do a view-source. It's in a
comment at the beginning of the page.
//Magnus
On 6/13/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
Magnus Hagander wrote:
On Wed, Jun 13, 2007 at 02:02:24PM +0100, Heikki Linnakangas wrote:
DB2 has the concept of "explain tables". Explain output is written to
tables, which tools query and pretty print the output. I like that idea
in principle. PostgreSQL is a relational database, so having the explain
output in relations make sense. No need for XML or any other extra
libraries, in either the server or client. Having the data in relational
format allows you to query them. For example, show me all sequential
scans, or all nodes where the estimated number of rows is off by a
certain factor.Assuming you can actually *represent* the whole plan as tables, that would
of course work fine.Sure you can. It's just a question of how complex the schema is :).
But I assume you mean "virtual tables"? So I do
EXPLAIN whatever, and get back one or more resultssets with the data? Or do
they write it to *actual* tables in the database?I'm not sure. DB2 had real tables, but I found that a bit clumsy. It was
nice because your old explain results were accumulated, but it was also
not nice because of that same thing.One idea would be temporary tables.
Machine-readable is of course the main point - the exact format is more of
an implementation detail.Agreed.
A potential problem is that as we add new node types etc., we need to
extend the schema (whether it's a real relational schema or XML), and
clients need to understand it. But I guess we already have the same
problem with clients that parse the current explain output.
Oracle forces you (AFAIK) to create a set of tables to store explain
plan output, so when you EXPLAIN, it populates those tables, and then
you have to query to get it out. This is nice for admin tools that
have to parse the explain output, though it's obviously a pain for
explain-ing inside a command-line. An XML explain would be neat.
On a different sideline based on the original note of this thread,
much as EXPLAIN doesn't include the schema, \d doesn't include the
schema to describe INHERIT relationships in 8.2.4. If you have two
tables called PARENT, in two different schemas, and a child that
inherits from one of them, \d won't tell you which of the two it
inherits from.
- Josh
