Message-ID: <42EB0700.4040600@actarg.com>
Date: Fri, 29 Jul 2005 22:50:08 -0600
From: Kyle Bateman <kyle@actarg.com>
User-Agent: Mozilla Thunderbird 0.9 (X11/20041127)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: bug in information_schema?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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_attribute a, pg_user u