Re: Very slow catalog query

Hi Tom,

Here is the result of explain analyze (though this one took 1500ms and
not 169000):

On Mon, Mar 31, 2008 at 7:37 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Just Someone" <just(dot)some(at)gmail(dot)com> writes:
> > Any ideas how to start finding the culprit?
>
> EXPLAIN ANALYZE?

explain analyze 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;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=64956.07..65025.73 rows=31 width=192) (actual
time=1547.720..1547.749 rows=1 loops=1)
-> Nested Loop (cost=64956.07..64987.48 rows=1 width=166) (actual
time=1547.662..1547.684 rows=1 loops=1)
-> Nested Loop (cost=64956.07..64981.47 rows=1 width=106)
(actual time=1547.616..1547.631 rows=1 loops=1)
-> Merge Join (cost=64956.07..64957.36 rows=4
width=42) (actual time=1547.484..1547.502 rows=3 loops=1)
Merge Cond: ("outer"."?column3?" = "inner".refobjsubid)
-> Sort (cost=4.08..4.08 rows=3 width=30)
(actual time=0.149..0.151 rows=1 loops=1)
Sort Key: cons.conkey[1]
-> Index Scan using
pg_constraint_conrelid_index on pg_constraint cons (cost=0.00..4.05
rows=3 width=30) (actual time=0.110..0.112 rows=1 loops=1)
Index Cond: (30375069::oid = conrelid)
Filter: (contype = 'p'::"char")
-> Sort (cost=64951.99..64952.61 rows=247
width=12) (actual time=1547.303..1547.318 rows=9 loops=1)
Sort Key: dep.refobjsubid
-> Index Scan using
pg_depend_reference_index on pg_depend dep (cost=0.00..64942.17
rows=247 width=12) (actual time=396.542..1547.172 rows=22 loops=1)
Index Cond: (refobjid = 30375069::oid)
-> Index Scan using pg_class_oid_index on pg_class seq
(cost=0.00..6.02 rows=1 width=72) (actual time=0.034..0.035 rows=0
loops=3)
Index Cond: (seq.oid = "outer".objid)
Filter: (relkind = 'S'::"char")
-> Index Scan using pg_namespace_oid_index on pg_namespace
name (cost=0.00..6.00 rows=1 width=68) (actual time=0.039..0.041
rows=1 loops=1)
Index Cond: ("outer".relnamespace = name.oid)
-> Index Scan using pg_attribute_relid_attnum_index on
pg_attribute attr (cost=0.00..38.00 rows=20 width=70) (actual
time=0.050..0.052 rows=1 loops=1)
Index Cond: ((30375069::oid = attr.attrelid) AND (attr.attnum
= "outer".refobjsubid))
Total runtime: 1548.082 ms

Bye,

Guy.