"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Having an SQL format would make it easier to allow for a mode that
> captures explain or explain analyze output from every query. Turn that
> mode on, run an application's test suite, and now you have a pretty good
> idea of how all the queries will run. Or, take a production system and
> turn that option on for a single connection. Another option is to have
> any queries that take more than X amount of time store an EXPLAIN of the
> query.
>
> Having this info in machine format would make it easier to write
> something that sets the various cost estimator values (random_page_cost,
> etc).
I'm particularly fond of the idea of storing the info in an SQL table. When I
first met this in Oracle it seemed awkward and annoying. But as I used it I
found more and more reasons why it's useful.
I had just such a mode for our application that explained queries before
running them (actually just a 1 time in 100 to avoid performance impacts). I
could look at an internal administrative web page that listed all queries that
showed profiling information, execution counts, explain plan, etc.
One advantage this would have is that the SQL table could include much more
detailed information than the text output can readably display. Then there
could be a function that displays the data from the SQL table in a format
similar to the current EXPLAIN output and other functions to display
additional information.
--
greg