RE: [HACKERS] Explain plan output

>> > Anyway, the point of this mail is to say that I have altered the
>> explain
>> > code slightly, so that it dumps the results of the explain into a
>> > table.
>> 
>> What do you mean by that, exactly?  You can't expect to fit long explain
>> outputs into a single table row, so I suppose it's one row per line.
>> How are the rows identified?  What's the expected declaration of the
>> table?
CREATE SEQUENCE explain_plan_seq;
CREATE TABLE explain_plan (id INT4,explain_line CHARACTER VARYING(255),level INT4,plan_order INT4
);

This is pretty close to the way that Oracle implements it.  That's where I
got the idea from.  Just had to add a little SPI, and voila...

>> > I find that a lot more convenient
>> 
>> It strikes me as a lot less convenient for the sorts of things I use
>> explain for.  
When ODBC is the only way in, then it's pretty inconvenient ;-)  If you have
access to psql, then it's fine, but for certain (perhaps most) other
interfaces, it's not so cool.  And when there is separation of duties (i.e.:
the dba is not the same person as the developer), and the developers are
using traditional win32 tools, the table is a lot more accessible.
Generally, in the environments that I've worked in, the developers only use
things like psql if they happen to be a db designer as well.  Frequently,
once the database has been designed and created, the developers only have
ODBC access.  Any change requests go through the dba.

>> But I wouldn't object if it were an optional feature:
>> 
>>     EXPLAIN [ VERBOSE ] [ INTO <table> ] <query>
>> 
>> which would also solve your problem of figuring out which table to write
>> to.

Yes I suppose that's probably the correct way to do it.  I was hoping not to
have to modify the language.  However, what to do if the columns are
incorrect, or the table doesn't exist?   Just generate an elog?  Also, what
about the sequence.  I suppose that I can just elog ANY error generated.

>> > e) The plan id is output using elog.  How would I ensure that this
>> gets back
>> > to any arbitrary client.  If I understand right, elogs don't go to
>> ODBC, and
>> > possibly other, clients.
>> 
>> What's a "plan id", and is it actually necessary?
The plan id is the unique number assigned to the plan, so that you can
identify it.  There will potentially be others in the table, so you need to
be able to identify the one that you have just generated.  So, yes, it is
necessary.


This is what an output looks like (you might need to widen your window if it
wraps):

template1=# select * from explain_plan where id = 10;id |                               explain_line
| level | plan_order 
----+-----------------------------------------------------------------------
----+-------+------------10 | Hash Join  (cost=327.99 rows=228 width=36)
|     1 |          110 |   ->  Seq Scan on test1  (cost=162.17 rows=4096 width=16)
|     2 |          210 |   ->  Hash  (cost=15.38 rows=228 width=20)
|     2 |          310 |   ->  Index Scan using pk1_test2 on test2  (cost=15.38 rows=228
width=20) |     3 |          4
(4 rows)




I will look at changing the EXPLAIN syntax...


MikeA