Re: generic options for explain

On Sun, May 24, 2009 at 11:57:13AM -0400, Andrew Dunstan wrote:
>
>
> Robert Haas wrote:
>>>> EXPLAIN ('hash_detail', 'on') query...
>>>>
>>
>> Oops, I should have written EXPLAIN (hash_detail 'on') query... can't
>> follow my own syntax.
>>
>>
>>> I am sorry - this is really strange syntax . Who will use this syntax?
>>> For some parser is little bit better function call, than parametrized
>>> statement. Some dificulties with options should be fixed with named
>>> param (we are speaking about 8.5).
>>>
>>> select explain_xml("select ...", true as hash_detail, ...)
>>>
>>
>> See to me THAT is a really strange syntax, so I guess we need some more votes.
>>
>>
>>
>
> Both of these seem both odd an unnecessary. Why not just have a setting
> called, say, explain_format which governs the output?
>
> set explain_format = 'xml, verbose';
> explain select * from foo;
>
> No new function or syntax would be required.

A further possibility: Oracle's equivalent of EXPLAIN doesn't actually output
anything to the screen, but rather fills in a (temporary?) table somewhere with
details of the query plan. I mostly found this irritating when working with
Oracle, because each time I used it I had to look up an example query to
generate output like PostgreSQL's EXPLAIN, which is generally what I really
wanted. But since we'd still have the old EXPLAIN behavior available, perhaps
something such as an Oracle-like table filler would be useful.

Such a proposal doesn't answer the need to allow users to specify, for
performance and other reasons, the precise subset of statistics they're
interested in; for whatever it's worth, my current favorite contender in that
field is EXPLAIN (a, b, c) <query>.

- Josh / eggyknap