Re: Optimizer not using index on 120M row table

On Wednesday 30 April 2003 11:26, Jim C. Nasby wrote:
> On Wed, Apr 30, 2003 at 12:14:34AM -0400, Tom Lane wrote:
> > Plenty, see many past threads in pgsql-performance and other lists.
> > There are strong reasons to think that you should let the kernel do the
> > bulk of the caching work.
>
> OK, anyone have any ideas on how to get FreeBSD to cache more than 300M?

some sysctl tweaks? HAven't booted in BSD for months so not exactly helpful
but still..

> BTW, all the searches I did on this topic and FreeBSD resulted in
> results saying I should set shared_buffers way high, so it seems FBSD
> users are having pgsql do the caching instead of the OS.

Well, that is the postgresql philosphy on any platform. Further about setting
the shared buffers, ideally you could watch ipcs output for shared memory
usage and set the shared buffers between 120%-150% of average shared memory
usage.

Here one of the database I am playing with now has 80M rows and 6GB big.. but
shared memory usage for random index scan queries does not seem to grow
beyond 10MB. Of course there are not many queries fired on it but this is
just to give you an idea..

HTH

Shridhar