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@sss.pgh.pa.us> wrote:
> "Just Someone" <just.some@gmail.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.