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

The following bug has been logged online:

Bug reference: 2429
Logged by: Cristiano da Cunha Duarte
Email address: cunha17(at)gmail(dot)com
PostgreSQL version: 8.1
Operating system: Debian GNU Linux
Description: Explain does not report object's schema
Details:

1) PROBLEM:

Explain command does not report the schema of objects, so when using objects
having the same name but in different schemas, they will apear as being the
same object.

2) HOW TO REPRODUCE:

EXPLAIN SELECT * FROM schema1.mytable, schema2.mytable WHERE 1=0

3) WHAT IS THE CURRENT BEHAVIOR:

QUERY PLAN
-----------------------------------------------------------------------
Nested Loop (cost=10.66..500630.90 rows=24422640 width=1498)
-> Seq Scan on mytable (cost=0.00..12167.44 rows=407044 width=264)
-> Materialize (cost=10.66..11.26 rows=60 width=1234)
-> Seq Scan on mytable (cost=0.00..10.60 rows=60 width=1234)
(4 records)

3) WHAT SHOULD BE EXPECTED:

QUERY PLAN
-----------------------------------------------------------------------
Nested Loop (cost=10.66..500630.90 rows=24422640 width=1498)
-> Seq Scan on schema2.mytable (cost=0.00..12167.44 rows=407044
width=264)
-> Materialize (cost=10.66..11.26 rows=60 width=1234)
-> Seq Scan on schema1.mytable (cost=0.00..10.60 rows=60
width=1234)
(4 records)

4) ADDITIONAL COMMENTS:
I am developing a snapshot project(Pg::snapshots
http://cunha17.theicy.net/personal/postgresql/snapshots.en_us.php) for
postgresql. It currently has refresh (complete, force, fast), snapshot logs,
dblinks, etc.

It's 99% complete, everything works fine, except the refresh fast, since I
need to discover which objects were involved in a SELECT statement. And
that's how I got into this bug.

With the current EXPLAIN implementation, I can't tell the difference between
the two and thus I can't get the list of involved objects correctly, so I
can't get the snapshot log list, and so on.

IMHO, the schema name will add correctness to the EXPLAIN command output.

"Cristiano da Cunha Duarte" <cunha17(at)gmail(dot)com> writes:
> Explain command does not report the schema of objects,

This is intentional. Most error messages don't mention objects' schemas
either, as it would usually just be clutter.

regards, tom lane

Hi Tom,

Tom Lane wrote:
>> Explain command does not report the schema of objects,
>
> This is intentional. Most error messages don't mention objects' schemas
> either, as it would usually just be clutter.
Oracle's EXPLAIN PLAN generate lots of information including the operation,
search columns, schema(owner) and object name.

In PostgreSQL, the error message when you issue a select statement from an
unexistent table, reports the schema too:

SELECT * FROM public.unexistent;
ERROR: relation "public.unexistent" does not exist

In this case the schema name is clutter, since we are dealing with only one
table, but when you have (or may have) many tables with the same exact
name, you must have a way to distinguish one to another.

This problem is much more significant with the EXPLAIN command since we are
reporting the execution plan of postgresql. It may be difficult with the
current output to distinguish between tables with the same name in order to
optimize the query.

I just think that there should be a way to uniquely identify the target
table on the EXPLAIN output, that's why I don't think that a way to fix an
ambiguous output is clutter.

Regards,

Cristiano Duarte

* cunha17(at)gmail(dot)com ("Cristiano da Cunha Duarte") wrote:
|
| 1) PROBLEM:
|
| Explain command does not report the schema of objects, so when using objects
| having the same name but in different schemas, they will apear as being the
| same object.
|
| 2) HOW TO REPRODUCE:

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.

--
Lars Haugseth

"If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it." -Tom Lehrer

Hi Lars,

Lars Haugseth wrote:
> * cunha17(at)gmail(dot)com ("Cristiano da Cunha Duarte") wrote:
> |
> | 1) PROBLEM:
> |
> | Explain command does not report the schema of objects, so when using
> | objects having the same name but in different schemas, they will apear
> | as being the same object.
> |
> | 2) HOW TO REPRODUCE:
>
> 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.

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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(at)pervasive(dot)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(at)pervasive(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

"Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)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

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On 5/16/06, Martijn van Oosterhout <kleptog(at)svana(dot)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.).

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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

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

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(at)pervasive(dot)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:
>

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

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