From: Kyle Bateman <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Subject: bug in information_schema?
Date: Fri, 29 Jul 2005 22:50:08 -0600
Message-ID: <42EB0700.4040600@actarg.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kyle@actarg.com
Subject: Re: bug in information_schema?
In-Reply-To: <42EB0700.4040600@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox