pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Ernesto <equistango@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Very slow INFORMATION_SCHEMA
Date: Fri, 02 May 2008 18:07:58 -0300
Message-ID: <481B82AE.8050607@gmail.com> (raw)


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

view thread (4+ messages)  latest in thread

Message-ID: <481B82AE.8050607@gmail.com>
Permalink:  ../481B82AE.8050607@gmail.com/
Also on:    postgresql.org/message-id/481B82AE.8050607@gmail.com

copy link · copy postgr.es

reply

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-performance@postgresql.org
  Cc: equistango@gmail.com
  Subject: Re: Very slow INFORMATION_SCHEMA
  In-Reply-To: <481B82AE.8050607@gmail.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