Skip site navigation (1) Skip section navigation (2)

Re: generic options for explain

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Joshua Tolley <eggyknap(at)gmail(dot)com>, Greg Smith <gsmith(at)gregsmith(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: generic options for explain
Date: 2009-05-25 22:32:57
Message-ID: 9359.1243290777@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Gevik BabakhaniDate: 2009-05-25 23:07:09
Subject: usability of pg_get_function_arguments
Previous:From: Tom LaneDate: 2009-05-25 22:15:06
Subject: Re: from_collapse_limit vs. geqo_threshold

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group