Very slow catalog query - Mailing list pgsql-general

From Just Someone
Subject Very slow catalog query
Date
Msg-id 36932f270803310713u24bb9de2rcb486c8b406c1e14@mail.gmail.com
Whole thread Raw
Responses Re: Very slow catalog query
Re: Very slow catalog query
List pgsql-general
Hi,

I have a DB with a large number schemas (around 10K) and a large
number of tables (400K). The app became slow lately, and logging the
slow queries, I see more than a few like this:

SELECT: LOG:  duration: 169547.424 ms  statement:           SELECT
attr.attname, name.nspname, seq.relname
                  FROM pg_class      seq,
                       pg_attribute  attr,
                       pg_depend     dep,
                       pg_namespace  name,
                       pg_constraint cons
                  WHERE seq.oid           = dep.objid
                    AND seq.relnamespace  = name.oid
                    AND seq.relkind       = 'S'
                    AND attr.attrelid     = dep.refobjid
                    AND attr.attnum       = dep.refobjsubid
                    AND attr.attrelid     = cons.conrelid
                    AND attr.attnum       = cons.conkey[1]
                    AND cons.contype      = 'p'
                    AND dep.refobjid      = 'activities'::regclass

Almost all slow queries are of this type, though most of those do
finish really fast. From time to time it gets really slow.

Some details on the setup:
Dual Opteron with 4GB RAM
RAID1 for WAL on 10K SCSI
RAID10 over 6 x 10K scsi drives for main the rest for the DB files

Auto vaccum is on, and in addition I do some vacuuming for specific
high use tables nightly

Any ideas how to start finding the culprit?

Bye,

Guy.


--
Family management on rails: http://www.famundo.com
My development related blog: http://devblog.famundo.com

pgsql-general by date:

Previous
From: "Roberts, Jon"
Date:
Subject: Re: Using tables in other PostGreSQL database
Next
From: Pedro Doria Meunier
Date:
Subject: Re: Very slow catalog query