Skip site navigation (1) Skip section navigation (2)

Re: Very slow catalog query

From: "Just Someone" <just(dot)some(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Very slow catalog query
Date: 2008-03-31 19:02:07
Message-ID: 36932f270803311202t4e83ed8bv67bad67655ccf281@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi Tom,


>  Well, it's hard to be sure what the problem is when you're not showing
>  us a problem case ...  but I notice that this indexscan is estimated
>  awfully high:

Whenever I do it manually it works fast. But in the log I see lots of
slow ones. Could it be caused by auto vacuum? Or by check pointing or
WAL writing? Are there way to check that?


>  >                            ->  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)
>
>  The reason is not far to seek: the scan is checking only the second
>  index key, meaning that it has to scan the entire index.  (I am
>  surprised it didn't use a seqscan instead.  Are you using enable_seqscan
>  = off?  Not a great idea.)  Since you know you are looking for a table,
>  you could improve matters by adding a constraint on refclassid:
>
>         dep.refclassid = 'pg_class'::regclass

enable_setscan is on. Is there a way to analyze/vacuum those tables?

I will look if I can also improve the query to be more exact.

Bye,

Guy.

In response to

pgsql-general by date

Next:From: Gurjeet SinghDate: 2008-03-31 19:15:13
Subject: Re: Can Postgres 8.x start if some disks containing tablespaces are not mounted?
Previous:From: Pavan DeolaseeDate: 2008-03-31 18:34:25
Subject: Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group