Re: [BUGS] BUG #2429: Explain does not report object's schema

On Tue, May 16, 2006 at 04:36:36PM +0530, Gurjeet Singh wrote:
> I don't think adding a schema_name to EXPLAIN's output, is really
> a good idea!! The ALIAS feature of the SQL language caters to this
> very need of assigning unambiguous names to tables.

That's assuming that the query was written with aliases. Otherwise you
have to go back and manually add them, which can be a royal pain for a
large, complex query.

> And we should be careful when adding any special code for EXPLAIN
> [ ANALYZE ]. For an example what would happen if we do that, consider
> this:
>
> If a big JOIN query takes N minutes on MS SQL Server, and I wish
> to see the plan why it is taking that long, one would expect me to
> enable 'Show Execution Plan' in the SQL Query Analyzer (similar to
> PG's EXPLAIN ANALYZE). And when I do that, the query now takes more
> than 2*N minutes to come back. I assume this extra delay is caused by
> the overhead of extra processing it does at row-source level (like how
> many rows passed through this row-source, average row-size, etc.).

How does that have anything to do with adding query names to EXPLAIN
output??

The only argument against this that makes any sense to me is that
EXPLAIN is plenty verbose as it is, and we don't need to be making it
worse. But that's a non-issue if showing the schema names is optional.

One way to address this would be to add a verbosity level to EXPLAIN.
Right now, EXPLAIN VERBOSE is pretty useless to users, but there is
additional information that would be handy to get from explain at
different levels:

"side effect" timing, such as time spent in triggers, constraints, etc.
This is there in HEAD for triggers.

Information about what other plans were considered.

More explicit naming information.

Information about statements that ran inside a function (ie: EXPLAIN
SELECT function_name() is pretty useless today).

Having a means to specify a verbosity level would allow for adding these
kind of features without needlessly cluttering up a run-of-the-mill
EXPLAIN.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461