От: Kevin Grittner
Тема: Re: Query help
Дата: ,
Msg-id: 4A799A560200002500029467@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Re: Query help  ("Subbiah Stalin-XCGF84")
Список: pgsql-performance

Скрыть дерево обсуждения

Query help  ("Subbiah Stalin-XCGF84", )
 Re: Query help  ("Kevin Grittner", )
  Re: Query help  ("Subbiah Stalin-XCGF84", )
   Re: Query help  ("Kevin Grittner", )
    Re: Query help  ("Subbiah Stalin-XCGF84", )
     Re: Query help  ("Kevin Grittner", )
      Re: Query help  ("Subbiah Stalin-XCGF84", )
      Re: Query help  ("Subbiah Stalin-XCGF84", )
       Re: Query help  (Scott Carey, )
       Re: Query help  ("Kevin Grittner", )

"Subbiah Stalin-XCGF84" <> wrote:

> We have found the problem.

Great news!

> Apparently there was a query doing count on 45 million rows table
> run prior to the episode of slow query.  Definitely cached data is
> pushed out the memory.

Yeah, that would completely explain your symptoms.

> Is there way to assign portion of memory to recycling purposes like
> in oracle, so the cached data doesn't get affected by queries like
> these.

Right now you have 8GB in shared buffers and 22GB in OS cache.  You
could try playing with that ratio, but benefit there would be iffy.
To solve this particular problem, you might want to see if they
actually need an exact count, versus a reasonable estimate.  The
estimated tuple count from the latest statistics run is often close
enough (and a lot faster).

-Kevin


В списке pgsql-performance по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: Query help
От: Ip Wing Kin John
Дата:
Сообщение: Bottleneck?