Re: keeping an index in memory

On Oct 21, 2007, at 7:36 AM, Bill Moran wrote:

> Rajarshi Guha <rguha@indiana.edu> wrote:
>>
>> Hi, relating to my previous queries on doing spatial searches on 10M
>> rows, it seems that most of my queries return within 2 minutes.
>> Generally this is not too bad, though faster is always better.
>>
>> Interestingly, it appears that the CUBE index for the table in
>> question is about 3GB (the table itself is about 14GB). Not knowing
>> the details of the postgres internals, I assume that when a query
>> tries to use the index, it will need to read a 3GB file. Is this a
>> correct assumption?
>>
>> In such a situation, is there a way to keep the index in memory? My
>> machine has 8GB installed and currently has about 7.4GB free RAM (64
>> bit linux 2.6.9)
>
> Free or cached/buffered?  Your OS should be using most of that to
> buffer disk blocks.

Aah, correct. Yes they are cached/buffered

>> A side effect of the size of the index is that if I do a query that
>> performs a seq scan (say using cube_distance) it takes longer than
>> when an index is used, but not significantly longer. And this is on a
>> 10M row table.
>>
>> What strategies do people follow when the index becomes very big?
>
> What version of PG are you using and what is your shared_buffers
> setting?

8.2.5

My original shared_buffers setting was 128MB.

> With 8G of RAM, you should start with shared_buffers around 2 - 3G, if
> you're using a modern version of PG.

I can do that but I'm a little confused. Earlier postings on the list
indicate that shared_buffers should be about 10% of the system RAM
and that effective_cache_size can be a large fraction of RAM.

As a result I had effective_cache_size set to 2500MB

Thanks for the pointers

-------------------------------------------------------------------
Rajarshi Guha  <rguha@indiana.edu>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04  06F7 1BB9 E634 9B87 56EE
-------------------------------------------------------------------
How I wish I were what I was when I wished I were what I am.