Re: [HACKERS] EXPLAIN omits schema?

2007-06-18 Thread Simon Riggs
On Wed, 2007-06-13 at 14:01 -0400, Tom Lane wrote:
 Gregory Stark [EMAIL PROTECTED] writes:
  Arguably this is a bug if it's causing pg_admin difficulties in parsing the
  output. Even for a user in an environment where, for example, he has several
  identical schemas and may be accidentally getting a different table than 
  he's
  expecting the current output is ambiguous.
 
  Attached is a small patch which adds this conditionally on a guc that
  pg_admin or other GUI tools could set, leaving it unchanged for users.
 
 That makes things *worse* not better, since now tools would have to
 deal with both possibilities.

The context here was a new feature in PgAdmin, which needs to be able to
parse the SQL *and* find out the schema of a table.

The idea was to have a tool that would issue an EXPLAIN *and* collect
all the other relevant details required to submit an optimizer question
to the lists. The tool would then be able to check for simple things
like not having run ANALYZE.

That feature would be very useful in identifying optimizer issues, as
well as filtering out many requests that arrive, only to be easily
explainable. Greg's small patch will allow this useful utility to be
available for use with the 8.3 release package, so I hope you'll
reconsider.

Of course, XML output can be done for the next release.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] EXPLAIN omits schema?

2007-06-18 Thread Stefan Kaltenbrunner
Tom Lane wrote:
 Gregory Stark [EMAIL PROTECTED] writes:
 Once you have an XML plan what can you do with it? All you can do is parse it
 into constituent bits and display it. You cant do any sort of comparison
 between plans, aggregate results, search for plans matching constraints, etc.
 
 Sure you can, just not in SQL ;-)
 
 Given the amount of trouble we'd have to go to to put the data into a
 pure SQL format, I don't think that's exactly an ideal answer either.
 I'm for making the raw EXPLAIN output be in a simple and robust format,
 which people can then postprocess however they want --- including
 forcing it into SQL if that's what they want.  But just because we're a
 SQL database doesn't mean we should think SQL is the best answer to
 every problem.
 
 While I'm surely not an XML fanboy, it looks better suited to this
 problem than a pure relational representation would be.

If we are looking into such a format we could even think a bit about
including basic plan-influencing information like work_mem, enable_*
settings, effective_cache_size,.. there too ...

Stefan

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] EXPLAIN omits schema?

2007-06-14 Thread Gregory Stark
Tom Lane [EMAIL PROTECTED] writes:

 Attached is a small patch which adds this conditionally on a guc that
 pg_admin or other GUI tools could set, leaving it unchanged for users.

 That makes things *worse* not better, since now tools would have to
 deal with both possibilities.

I was thinking tools would set the guc before issuing an EXPLAIN they planned
to parse.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Stephen Frost
* Dave Page ([EMAIL PROTECTED]) 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


signature.asc
Description: Digital signature


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Dave Page

Stephen Frost wrote:

* Dave Page ([EMAIL PROTECTED]) 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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Stephen Frost
* Dave Page ([EMAIL PROTECTED]) 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


signature.asc
Description: Digital signature


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Dave Page

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

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Lukas Kahwe Smith

Stephen Frost wrote:

* Dave Page ([EMAIL PROTECTED]) 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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Magnus Hagander
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


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Alvaro Herrera
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)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Magnus Hagander
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

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Pavel Stehule


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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Heikki Linnakangas

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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Stephen Frost
* Heikki Linnakangas ([EMAIL PROTECTED]) 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


signature.asc
Description: Digital signature


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Magnus Hagander
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


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Heikki Linnakangas

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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Alvaro Herrera
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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Aidan Van Dyk
* Alvaro Herrera [EMAIL PROTECTED] [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,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Tom Lane
Dave Page [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Magnus Hagander
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.php
 
 BTW 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


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Josh Tolley

On 6/13/07, Heikki Linnakangas [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Dave Page

Magnus Hagander wrote:


Something worth doing? Not to replace the current explain output, but as a
second option (EXPLAIN XML whatever)?


Yeah, thats been mentioned before. I was looking to bring it up for 8.4.

/D

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Tom Lane
Josh Tolley [EMAIL PROTECTED] writes:
 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.

Yes it does, because that's actually regclass output.  It'll be
schema-qualified if the table is not visible in your search path.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Gregory Stark

Magnus Hagander [EMAIL PROTECTED] writes:

 Assuming you can actually *represent* the whole plan as tables, that would
 of course work fine. But I assume you mean virtual tables? 

Are you saying there are data structures relational databases aren't good at
representing?

In Oracle you had to run explain and the go run a query against your
plan_table to generate a text report of the plan. It was kind of cumbersome in
the usual case but it's very convenient for gui tools which can then perform
different queries than users would run from the command-line client.

The main advantage of using real tables is that you can then let your
application run unchanged and go look at the plans that it generated from
another connection. The plan it generated may well be different from what you
would get if you tried to run the same query later in a different connection.

It also means you could go query for things like what query performed the
largest disk sort or what is the average cost/millisecond ratio or which
query nodes had the largest and smallest expected-rows/actual rows ratio etc.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Dave Page

Tom Lane wrote:

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


Well, yes - that's precisely what pgAdmin does, which is why I'd want to 
see the schema name all the time. Up until now though all we've done is 
graphically represent the plan, so the object names haven't really been 
an issue. To take that further and allow the user to drill down to 
further information, or to provide tools to help tune queries we need to 
know for certain what table we're dealing with.


Regards, Dave


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Andrew Dunstan



Tom Lane wrote:

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.

  


I agree. XML seems like a fairly natural fit for this. Just as people 
should not try to shoehorn everything into XML, neither should they try 
to shoehorn everything into a relational format either.


Now all we need is an XML schema for it ;-)

cheers

andrew

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Lukas Kahwe Smith

Tom Lane wrote:


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.


Which was my point regarding needing WITH RECURSIVE to make this truely 
useful.


XML output is nice, but only as an addition imho. Then again it would 
indeed be quite useful for external development tools.


regards,
Lukas

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Aidan Van Dyk
  BTW can I bug you to add the Message-Ids in the messages as displayed in
  our archives?
 
 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.

I hadn't seen that before...

2 bookmarklets I find useful and have in my Personal Toolbar Folder:
  - GMANE message lookup:
javascript:var id=prompt('Enter 
Message-ID','');window.location='http://news.gmane.org/find-root.php?message_id='+id;

  - Mhonarc message - GMANE lookup
  javascript:var id=prompt('Enter Mhonarc 
URL','');window.location='http://www.highrise.ca/cgi-bin/mhonarc/'+id;

a.

-- 
Aidan Van Dyk Create like a god,
[EMAIL PROTECTED]   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Joshua D. Drake

Andrew Dunstan wrote:



Tom Lane wrote:

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.

  


I agree. XML seems like a fairly natural fit for this. Just as people 
should not try to shoehorn everything into XML, neither should they try 
to shoehorn everything into a relational format either.


Now all we need is an XML schema for it ;-)


Well I am not a big fan of XML but it certainly seems applicable in this 
case.


Joshua D. Drake




cheers

andrew

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Joshua D. Drake

Tom Lane wrote:

Dave Page [EMAIL PROTECTED] 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).


We shouldn't do it conditionally. We should do it explicitly. If I have 
a partitioned table with 30 child partitions, how do I know which table 
is getting the seqscan?


Joshua D. Drake




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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster




--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Gregory Stark

Tom Lane [EMAIL PROTECTED] writes:

 Dave Page [EMAIL PROTECTED] 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).

Arguably this is a bug if it's causing pg_admin difficulties in parsing the
output. Even for a user in an environment where, for example, he has several
identical schemas and may be accidentally getting a different table than he's
expecting the current output is ambiguous.

Attached is a small patch which adds this conditionally on a guc that pg_admin
or other GUI tools could set, leaving it unchanged for users.

But it doesn't really seem like all that much clutter to add it to the scans
all the time:

  QUERY PLAN
  
--
 Nested Loop  (cost=3.77..1337.74 rows=62 width=8)
   -  Hash Join  (cost=3.77..92.30 rows=123 width=8)
 Hash Cond: (p.oid = (a.aggfnoid)::oid)
 -  Seq Scan on pg_catalog.pg_proc p  (cost=0.00..78.49 rows=2349 
width=4)
 -  Hash  (cost=2.23..2.23 rows=123 width=8)
   -  Seq Scan on pg_catalog.pg_aggregate a  (cost=0.00..2.23 
rows=123 width=8)
   -  Index Scan using pg_operator_oid_index on pg_catalog.pg_operator o  
(cost=0.00..10.11 rows=1 width=4)
 Index Cond: (o.oid = a.aggsortop)
 Filter: (NOT (subplan))
 SubPlan
   -  Index Scan using pg_amop_opr_fam_index on pg_catalog.pg_amop  
(cost=1.05..9.33 rows=1 width=0)
 Index Cond: (amopopr = $1)
 Filter: ((amopmethod = $0) AND (amoplefttype = $2) AND 
(amoprighttype = $3))
 InitPlan
   -  Seq Scan on pg_catalog.pg_am  (cost=0.00..1.05 rows=1 
width=4)
 Filter: (amname = 'btree'::name)
(16 rows)




explain-with-schema-guc.patch.gz
Description: Binary data


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Gregory Stark
Joshua D. Drake [EMAIL PROTECTED] writes:

 I agree. XML seems like a fairly natural fit for this. Just as people should
 not try to shoehorn everything into XML, neither should they try to shoehorn
 everything into a relational format either.

 Now all we need is an XML schema for it ;-)

 Well I am not a big fan of XML but it certainly seems applicable in this
 case.

I'm not a fan either so perhaps I'm biased, but this seems like a good example
of where it would be an *awful* idea.

Once you have an XML plan what can you do with it? All you can do is parse it
into constituent bits and display it. You cant do any sort of comparison
between plans, aggregate results, search for plans matching constraints, etc.

How would I, with XML output, do something like:

SELECT distinct node.relation 
  FROM plan_table 
 WHERE node.expected_rows  node.actual_rows*2;

or

SELECT node.type, average(node.ms/node.cost)
  FROM plan_table 
 GROUP BY node.type;

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


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

This reminded me of a quick function I wrote up for my PGCon talk last month. 
I've posted it on the blog:

http://people.planetpostgresql.org/greg/index.php?/archives/106-Putting-EXPLAIN-results-into-a-table.html

I'd rather see tables with a convert-to-XML function than direct XML FWIW.

- --
Greg Sabino Mullane [EMAIL PROTECTED]
End Point Corporation
PGP Key: 0x14964AC8 200706131211
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iD8DBQFGcBdnvJuQZxSWSsgRA/irAJsH0ZT3wLNN4mLirsTryiK1m9gyHwCg6+9A
0MuJqGxJ9gkEIWVUeq4iXag=
=NeB/
-END PGP SIGNATURE-



---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Tom Lane
Gregory Stark [EMAIL PROTECTED] writes:
 Arguably this is a bug if it's causing pg_admin difficulties in parsing the
 output. Even for a user in an environment where, for example, he has several
 identical schemas and may be accidentally getting a different table than he's
 expecting the current output is ambiguous.

 Attached is a small patch which adds this conditionally on a guc that
 pg_admin or other GUI tools could set, leaving it unchanged for users.

That makes things *worse* not better, since now tools would have to
deal with both possibilities.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Joshua D. Drake

Gregory Stark wrote:

Joshua D. Drake [EMAIL PROTECTED] writes:


I agree. XML seems like a fairly natural fit for this. Just as people should
not try to shoehorn everything into XML, neither should they try to shoehorn
everything into a relational format either.

Now all we need is an XML schema for it ;-)

Well I am not a big fan of XML but it certainly seems applicable in this
case.


I'm not a fan either so perhaps I'm biased, but this seems like a good example
of where it would be an *awful* idea.

Once you have an XML plan what can you do with it? All you can do is parse it
into constituent bits and display it. You cant do any sort of comparison
between plans, aggregate results, search for plans matching constraints, etc.


Honestly, I had never even considered doing such a thing. I would just 
like a nice way to parse explain output :)


Joshua D. Drake




How would I, with XML output, do something like:

SELECT distinct node.relation 
  FROM plan_table 
 WHERE node.expected_rows  node.actual_rows*2;


or

SELECT node.type, average(node.ms/node.cost)
  FROM plan_table 
 GROUP BY node.type;





--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Florian G. Pflug

Gregory Stark wrote:

Joshua D. Drake [EMAIL PROTECTED] writes:

I agree. XML seems like a fairly natural fit for this. Just as people should
not try to shoehorn everything into XML, neither should they try to shoehorn
everything into a relational format either.

Now all we need is an XML schema for it ;-)

Well I am not a big fan of XML but it certainly seems applicable in this
case.


I'm not a fan either so perhaps I'm biased, but this seems like a good example
of where it would be an *awful* idea.

Once you have an XML plan what can you do with it? All you can do is parse it
into constituent bits and display it. You cant do any sort of comparison
between plans, aggregate results, search for plans matching constraints, etc.

How would I, with XML output, do something like:

SELECT distinct node.relation 
  FROM plan_table 
 WHERE node.expected_rows  node.actual_rows*2;


or

SELECT node.type, average(node.ms/node.cost)
  FROM plan_table 
 GROUP BY node.type;


I believe that XQuery actually supports such queries. So if postgres
supported XQuery (or does it already? I honestly don't know), writing
such a query wouldn't be that hard I think. The execution probably
won't be super-efficient, but for query plans that seems OK.

greetings, Florian Pflug

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Tom Lane
Gregory Stark [EMAIL PROTECTED] writes:
 Once you have an XML plan what can you do with it? All you can do is parse it
 into constituent bits and display it. You cant do any sort of comparison
 between plans, aggregate results, search for plans matching constraints, etc.

Sure you can, just not in SQL ;-)

Given the amount of trouble we'd have to go to to put the data into a
pure SQL format, I don't think that's exactly an ideal answer either.
I'm for making the raw EXPLAIN output be in a simple and robust format,
which people can then postprocess however they want --- including
forcing it into SQL if that's what they want.  But just because we're a
SQL database doesn't mean we should think SQL is the best answer to
every problem.

While I'm surely not an XML fanboy, it looks better suited to this
problem than a pure relational representation would be.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [HACKERS] EXPLAIN omits schema?

2007-06-13 Thread Nikolay Samokhvalov

On 6/13/07, Gregory Stark [EMAIL PROTECTED] wrote:

I'm not a fan either so perhaps I'm biased, but this seems like a good example
of where it would be an *awful* idea.

Once you have an XML plan what can you do with it? All you can do is parse it
into constituent bits and display it.


...and display it -- this, I suppose, covers the most frequent needs
(starting from displaying entire plans in some tools and finishing
with odd but useful examples like
http://archives.postgresql.org/pgsql-sql/2005-08/msg00046.php).


You cant do any sort of comparison
between plans, aggregate results, search for plans matching constraints, etc.


Wrong.


How would I, with XML output, do something like:

SELECT distinct node.relation
  FROM plan_table
 WHERE node.expected_rows  node.actual_rows*2;

or

SELECT node.type, average(node.ms/node.cost)
  FROM plan_table
 GROUP BY node.type;


XPath can help here. Now almost every language has XML with XPath
support. That's the point, that's why XML is suitable here -- it
simplifies application development (in this specific case ;-) ).

--
Best regards,
Nikolay

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match