The following bug has been logged online:
Bug reference: 2429
Logged by: Cristiano da Cunha Duarte
Email address: cunha17@gmail.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.