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

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

From
"Jim C. Nasby"
Date:
Moving to -hackers

On Wed, May 10, 2006 at 11:26:30AM -0300, Cristiano Duarte wrote:
> > Don't know whether this would help in your situation, but you can make the
> > output of EXPLAIN disambiguous by using table aliases:
> > 
> > EXPLAIN
> >  SELECT *
> >    FROM schema1.mytable AS mt1, schema2.mytable AS mt2
> >   WHERE 1=0
> > 
> > (The AS keyword is optional.)
> > 
> > The aliases will be included in the query plan output.
> > 
> Thanks for the notice, but to do this, I would have to raise errors on
> queries having tables with the same name, which is not what I meant.
> 
> But, if there is no other way, I'll do it.

So you actually write out schemaname.tablename.fieldname for every field
in the SELECT clause? Yikes!

In any case, I agree that there should be a way to have EXPLAIN (and
other things) show schema names. But since this isn't an itch that any
of the developers have felt like scratching, it's unlikely this will
happen unless someone sponsors it. :/

Unless folks think it's specifically a bad idea, can we at least get it
on the TODO so if someone's bored it might get done?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


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

From
"Gurjeet Singh"
Date:
    I don't think Cristiano is asking for the schema_name in the
EXPLAIN o/p. The request is for the table ALIASes to be shown in the
o/p, which makes more sense than schema_name+table_name, since the
same table can be used in the same query more than once.

Gurjeet.

On 5/15/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> Moving to -hackers
>
> On Wed, May 10, 2006 at 11:26:30AM -0300, Cristiano Duarte wrote:
> > > Don't know whether this would help in your situation, but you can make the
> > > output of EXPLAIN disambiguous by using table aliases:
> > >
> > > EXPLAIN
> > >  SELECT *
> > >    FROM schema1.mytable AS mt1, schema2.mytable AS mt2
> > >   WHERE 1=0
> > >
> > > (The AS keyword is optional.)
> > >
> > > The aliases will be included in the query plan output.
> > >
> > Thanks for the notice, but to do this, I would have to raise errors on
> > queries having tables with the same name, which is not what I meant.
> >
> > But, if there is no other way, I'll do it.
>
> So you actually write out schemaname.tablename.fieldname for every field
> in the SELECT clause? Yikes!
>
> In any case, I agree that there should be a way to have EXPLAIN (and
> other things) show schema names. But since this isn't an itch that any
> of the developers have felt like scratching, it's unlikely this will
> happen unless someone sponsors it. :/
>
> Unless folks think it's specifically a bad idea, can we at least get it
> on the TODO so if someone's bored it might get done?
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>


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

From
Tom Lane
Date:
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
>     I don't think Cristiano is asking for the schema_name in the
> EXPLAIN o/p. The request is for the table ALIASes to be shown in the
> o/p, which makes more sense than schema_name+table_name, since the
> same table can be used in the same query more than once.

But EXPLAIN has always shown the aliases.

Possibly a reasonable compromise would be for EXPLAIN to act like rule
reverse-listing does, that is, consult the schema search path and print
a qualified name only if the table wouldn't be found without it.
        regards, tom lane


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

From
Martijn van Oosterhout
Date:
On Tue, May 16, 2006 at 09:15:13AM +0530, Gurjeet Singh wrote:
>    I don't think Cristiano is asking for the schema_name in the
> EXPLAIN o/p. The request is for the table ALIASes to be shown in the
> o/p, which makes more sense than schema_name+table_name, since the
> same table can be used in the same query more than once.

As has been pointed out, aliases ave always been displayed. The OPs
problem was that he was using schema.tablename everywhere and explain
didn't distinguish between schema1.mytable and schema2.mytable. It was
suggested that he use aliases instead to make it work.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

From
"Gurjeet Singh"
Date:
On 5/16/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Tue, May 16, 2006 at 09:15:13AM +0530, Gurjeet Singh wrote:
> As has been pointed out, aliases ave always been displayed. The OPs
> problem was that he was using schema.tablename everywhere and explain
> didn't distinguish between schema1.mytable and schema2.mytable. It was
> suggested that he use aliases instead to make it work.
>
   I should subscribe to the -bugs mailing list too!!!! I didn't get
the complete picture there.
   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.
   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.).


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

From
Martijn van Oosterhout
Date:
On Tue, May 16, 2006 at 04:36:36PM +0530, Gurjeet Singh wrote:
>    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.).

I posted a patch last week on -patches which should dramatically cut
the overhead of EXPLAIN ANALYZE. Perhaps you could try that and report
your experience.

http://archives.postgresql.org/pgsql-patches/2006-05/msg00158.php

BTW, just showing the plan takes no time at all, just use EXPLAIN
without the ANALYZE.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

From
Cristiano Duarte
Date:
Gurjeet Singh wrote:

>     I don't think Cristiano is asking for the schema_name in the
> EXPLAIN o/p. 
In fact I'm requesting for schema_name in EXPLAIN o/p. 

> The request is for the table ALIASes to be shown in the 
> o/p, which makes more sense than schema_name+table_name, since the
> same table can be used in the same query more than once.
SQL table aliases doesn't help locating the real place where the table is.
If I have a table named "test" at the schema "place" and I do:

"EXPLAIN SELECT * FROM place.test mytest"

I will get:

"Seq Scan on test mytest"

With this output I know that "mytest" is an alias to "test", and that's
great, much helpful than aliases only, but, where is "test"? Explain didn't
provide me will the location: I know the table name but I don't know where
it is.

I don't see too much harm if the output was:

"Seq Scan on place.test mytest"

And that's what I'm asking for.

Regards,

Cristiano


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

From
"Jim C. Nasby"
Date:
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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


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

From
"Gurjeet Singh"
Date:
    I agree... VERBOSE option can be made parameterised to include
additional information in the EXPLAIN's output.
   I also agree that adding the schema name wouldn't add any
overhead, and I support Tom's suggestion: 'Possibly a reasonable
compromise would be for EXPLAIN to act like rule reverse-listing
does,'
   But one should be wary of adding any other option that itself
might cause an overhead, especially when doing the ANALYZE.
   For example, from the ones you suggested, 'showing other plans
considered by the optimizer' seems a bit of an overhead. As the number
of JOINed tables increase, so does the number of join permutations,
and trying to keep the plans (in any form) till we send the results to
client, would block-up considerable amount of resources.
   On the other hand, we can add these options and keep a note in
docs saying that the presence of these particular parameters (to
VERBOSE) will affect performance, and if used in conjunction with
ANALYZE, ANALYZE might not give you the correct picture!

On 5/16/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> 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:
>


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

From
Alvaro Herrera
Date:
Cristiano Duarte wrote:

> SQL table aliases doesn't help locating the real place where the table is.
> If I have a table named "test" at the schema "place" and I do:
> 
> "EXPLAIN SELECT * FROM place.test mytest"
> 
> I will get:
> 
> "Seq Scan on test mytest"
> 
> With this output I know that "mytest" is an alias to "test", and that's
> great, much helpful than aliases only, but, where is "test"?

Since you created the mytest alias, you sure know where it's pointing
to.  In fact I'd argue that this should instead display

Seq Scan on mytest


> I don't see too much harm if the output was:
> 
> "Seq Scan on place.test mytest"

Not much harm there, but there will be plenty harm on other node types
where the output is already too wide.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


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

From
Cristiano Duarte
Date:
Alvaro Herrera wrote:

> Cristiano Duarte wrote:
> 
>> SQL table aliases doesn't help locating the real place where the table
>> is. If I have a table named "test" at the schema "place" and I do:
>> 
>> "EXPLAIN SELECT * FROM place.test mytest"
>> 
>> I will get:
>> 
>> "Seq Scan on test mytest"
>> 
>> With this output I know that "mytest" is an alias to "test", and that's
>> great, much helpful than aliases only, but, where is "test"?
> 
> Since you created the mytest alias, you sure know where it's pointing
> to.  
In fact I didn't create the alias, I've got the query already made from a
user function call, and now I have to know where the table is located. 

Also, the user may pass a query without the schema name and even on this
scenario, I need to know the schema name and the "real" table name.

> 
> In fact I'd argue that this should instead display 
> Seq Scan on mytest 
I agree with you if EXPLAIN should only be executed interactivelly(psql,
pgadmin3, etc). 

But, since you can execute EXPLAIN as a regular query to the database, you
may be "explaining" an user supplied query, and doing so, there is no way
to previously know what the aliases mean.

> 
> 
>> I don't see too much harm if the output was:
>> 
>> "Seq Scan on place.test mytest"
> 
> Not much harm there, but there will be plenty harm on other node types
> where the output is already too wide.
Jim C. Nasby suggested a verbosity level to EXPLAIN, using "VERBOSE". It may
solve this issue without harming other node types where the output is
already too wide.

Regards,

Cristiano Duarte