Re: generic options for explain

On Mon, May 25, 2009 at 07:14:56AM -0400, Robert Haas wrote:
> Many people who responded to this
> thread were fine with the idea of some sort of options syntax, but we
> had at least four different proposals for how to implement it:
>
> Robert Haas: EXPLAIN (foo 'bar', baz 'bletch', ...) query
> Pavel Stehule: explain_query(query, options...) [exact format of
> options not specified]
> Andrew Dunstan: SET explain_format = 'foo, baz'; EXPLAIN query
> Josh Tolley: EXPLAIN (foo, baz, ...) query [also suggested by me as an
> idea I rejected]

I hadn't actually caught that there were two ideas on the table with syntax
similar to "EXPLAIN (...) <query>", and don't mean to champion either of the
two specifically (at least until I've read closely enough to note the
difference). I just kinda liked the "EXPLAIN (some options of some sort)
<query>" syntax better than other proposals.

That said, I think I'm changing my vote in favor of Pavel. It's my guess that
some variant of his version would be the easiest to make compliant with the
bit I'm most interested in, which is not being limited to, say,
XML/JSON/YAML/etc. in the output. Applications that use PostgreSQL will of
necessity need to know how to handle data presented in tables, so let's
present our explain results as a table. As has been said in other branches of
this thread, that way we don't force applications also to support
XML/JSON/YAML/etc. We might consider providing functions to convert the
tabular result to one or more of those formats, but at its inception, the data
should live as tuples in a relation.

In other messages, I've advocated actually inserting the data into a table. I
think that was a mistake. Who makes the table? What's it called? What schema
is it in? Who cleans it up when we're done with it? ...etc. I'd much rather
see a bunch of rows returned as a set, which I can then insert into a table,
pass into a function for reformatting, or just consume in an application.

All of which leads me to this variant of the functional approach as my answer:

SELECT * FROM pg_explain_query("<query>", <options in a still-unspecified
format>);

I could then do things like this:

CREATE TABLE explain_results AS SELECT * FROM pg_explain_query(...);

and this:

SELECT xmlify_a_record(pg_explain_query(...));

- Josh / eggyknap