Re: generic options for explain

On Mon, May 25, 2009 at 6:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I agree with this, but there is a lot of sentiment (which I share)
>> that it should be possible to capture EXPLAIN output using subselect
>> or CTAS syntax, regardless of exactly what that output ends up being.
>
> Well, it should be possible to capture the output, but let's not
> prejudge the syntax.
>
>> That seems to require that EXPLAIN be a fully-reserved keyword, so I
>> wonder what we think about that.
>
> Nonstarter, especially when it's so obviously possible to do it without
> that.  The threshold for reserving words that aren't reserved by SQL
> spec has to be really high, because you will break innocent applications
> that way.
>
> Before anyone gets overly excited about having special syntax for this,
> I should point out that you can do it today, for instance like so:
>
> regression=# create function expl(q text) returns setof text as $$
> regression$# declare r record;
> regression$# begin
> regression$#   for r in execute 'explain ' || q loop
> regression$#     return next r."QUERY PLAN";
> regression$#   end loop;
> regression$# end$$ language plpgsql;
> CREATE FUNCTION
>
> regression=# select * from expl('select * from tenk1');
>                            expl
> -------------------------------------------------------------
>  Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)
> (1 row)
>
> Admittedly this is a bit inconvenient, but the point is that the
> functionality does exist.  There is no need to have a built-in
> version of this function unless we get significant advantages
> from having it built-in, and right now I'm not seeing those.

The only problem I see with this is that there's no convenient way of
specifying the options you want. Granted, it wouldn't be all that
difficult to add a couple of boolean options to specify the state of
EXPLAIN and ANALYZE, but if we have more options, it starts to get a
bit complicated, especially if they do things like change the set of
output columns. I'm still liking that idea, but even if we don't end
up implementing that particular thing, I'm really doubtful that the
need to make EXPLAIN do more things is likely to go away.

...Robert