"Subbiah Stalin-XCGF84" <SSubbiah(at)Motorola(dot)com> 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