Thread: bug in information_schema?

I noticed that it seemed a bit slow to query 
information_schema.view_column_usage.   As I look at the code in 
information_schema.sql, I'm not sure why pg_user is referenced twice 
(once without an alias).  It looks like we can take out the first 
pg_user and remove the DISTINCT keyword and this improves the efficiency 
significantly.  It seems to return the same result but in half the 
time.  Anyone see a problem with this?  (The same problem may also be in 
view_table_usage but I haven't done any testing there yet.)

Code from information_schema.sql:
CREATE VIEW view_column_usage AS   SELECT DISTINCT          CAST(current_database() AS sql_identifier) AS view_catalog,
        CAST(nv.nspname AS sql_identifier) AS view_schema,          CAST(v.relname AS sql_identifier) AS view_name,
       CAST(current_database() AS sql_identifier) AS table_catalog,          CAST(nt.nspname AS sql_identifier) AS
table_schema,         CAST(t.relname AS sql_identifier) AS table_name,             CAST(a.attname AS sql_identifier) AS
column_name  
 
   FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,        pg_depend dt, pg_class t, pg_namespace nt,
pg_attributea, pg_user u
 

Kyle Bateman <kyle@actarg.com> writes:
> I noticed that it seemed a bit slow to query 
> information_schema.view_column_usage.   As I look at the code in 
> information_schema.sql, I'm not sure why pg_user is referenced twice 
> (once without an alias).  It looks like we can take out the first 
> pg_user and remove the DISTINCT keyword and this improves the efficiency 
> significantly.

The unconstrained join against pg_user is clearly unnecessary,
and in fact I took it out a few days ago.  I'm not sure whether the
SELECT DISTINCT is still needed --- it might be, if there can be
multiple pg_depend entries linking the same entities.
Peter, any thoughts?
        regards, tom lane

Am Samstag, 30. Juli 2005 17:15 schrieb Tom Lane:
> The unconstrained join against pg_user is clearly unnecessary,
> and in fact I took it out a few days ago.  I'm not sure whether the
> SELECT DISTINCT is still needed --- it might be, if there can be
> multiple pg_depend entries linking the same entities.

That would have been my guess, but it seems that even if a column or table is 
used multiple times, a dependency is recorded only once, as it should be.  It 
might have been related to the duplicate pg_user mention.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/