Very slow INFORMATION_SCHEMA

Hi,

I'm ****ting an application written with pretty ****table SQL, but tested 
almost exclusively on MySQL.

I'm wondering why would this query take about 90 seconds to return 74
rows?


SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME, 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME, 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS, 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
        AND 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA=INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
        AND 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA='mydbname'
        AND 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE='FOREIGN KEY'
        ORDER BY INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME, 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION

An equivalent query with the same data set on the same server takes a 
couple of milliseconds on MySQL 5.
Is it something I'm doing wrong or it's just that PostgreSQL 
INFORMATION_SCHEMA is not optimized for speed? BTW, what I'm trying to 
do is get some info on every FOREIGN KEY in a database.

It's PostgreSQL 8.2.7 on Fedora 8 64, Athlon 64 X2 3600+.

Ernesto


-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance