Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 7DD7E2E004F
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Fri,  2 May 2008 18:30:29 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 71556-03-5
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Fri,  2 May 2008 18:30:16 -0300 (ADT)
X-Greylist: delayed 00:21:22.771894 by SQLgrey-1.7.5
Received: from republica.dattaweb.com (republica.dattaweb.com [200.58.115.18])
	by developer.postgresql.org (Postfix) with ESMTP id 623FD2E0080
	for <pgsql-performance@postgresql.org>;
	Fri,  2 May 2008 18:30:08 -0300 (ADT)
Received: from [190.48.182.41] (helo=[192.168.1.9])
	by republica.dattaweb.com with esmtpa (Exim 4.63)
	(envelope-from <equistango@gmail.com>) id 1Js2Uj-0003Se-Qa
	for pgsql-performance@postgresql.org; Fri, 02 May 2008 18:08:43 -0300
Message-ID: <481B82AE.8050607@gmail.com>
Date: Fri, 02 May 2008 18:07:58 -0300
From: Ernesto <equistango@gmail.com>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Very slow INFORMATION_SCHEMA
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Spam-Score-Int: 63
X-Spam-Bar: ++++++
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - republica.dattaweb.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [502 502]
X-AntiAbuse: Sender Address Domain - gmail.com
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.49 tagged_above=0 required=5
	tests=UPPERCASE_50_75=0.49
X-Spam-Level: 
X-Archive-Number: 200805/34
X-Sequence-Number: 30002


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