Hi,
I'm porting an application written with pretty portable 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
