pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbug in information_schema?
3+ messages / 3 participants
[nested] [flat]
* bug in information_schema?
@ 2005-07-30 04:50 Kyle Bateman <kyle@actarg.com>
2005-07-30 15:15 ` Re: bug in information_schema? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Kyle Bateman @ 2005-07-30 04:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bug in information_schema?
2005-07-30 04:50 bug in information_schema? Kyle Bateman <kyle@actarg.com>
@ 2005-07-30 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-01 08:42 ` Re: bug in information_schema? Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-07-30 15:15 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: bug in information_schema?
2005-07-30 04:50 bug in information_schema? Kyle Bateman <kyle@actarg.com>
2005-07-30 15:15 ` Re: bug in information_schema? Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-01 08:42 ` Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 3+ messages in thread
From: Peter Eisentraut @ 2005-08-01 08:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kyle Bateman <kyle@actarg.com>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-01 08:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-30 04:50 bug in information_schema? Kyle Bateman <kyle@actarg.com>
2005-07-30 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-01 08:42 ` Peter Eisentraut <peter_e@gmx.net>
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