Thread: Explain plan output

Hi, again,

Merry Christmas to those of you who celebrate it, and if you're reading this
within three days of Christmas, then you're as sorry an excuse for a human
being as I am ;-)

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.  I
find that a lot more convenient for two reasons: a) it's persistent, b) it's
accessible, no matter what interface you're using.  Poor old Dave Page has
been racking his brain trying to figure out how to get a plan to pgAdmin (if
he hasn't already), and I even proposed supplying a server-side library that
would trap the output, and send that contents back through a stored
procedure, or something.  Anyway, with the current way that I have coded it,
in addition to the plan output using elog, it gets dumped into a table, the
rows of which can the be selected.  There are a couple of issues that I
would like to resolve.

a) This is a useful format, but are there enough interested people to
warrant continuing?
b) If so, should the output table be a system table?
c) If not, then how does the explain decide what to do if the table doesn't
exist?
d) And if the table does exist, then should it elog the plan?
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.


I'm sure there are other things that I will remember, but that's all for
now.

MikeA

"Ansley, Michael" <Michael.Ansley@intec.co.za> writes:
> 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?

> I find that a lot more convenient

It strikes me as a lot less convenient for the sorts of things I use
explain for.  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.

> 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?
        regards, tom lane