Re: keeping an index in memory

On Oct 21, 2007, at 10:40 AM, Martijn van Oosterhout wrote:

> On Sun, Oct 21, 2007 at 07:36:00AM -0400, Bill Moran wrote:
>> What version of PG are you using and what is your shared_buffers
>> setting?
>>
>> With 8G of RAM, you should start with shared_buffers around 2 -
>> 3G, if
>> you're using a modern version of PG. With that much shared memory, a
>> large portion of that index should stay in RAM, as long as it's being
>> used often enough that PG doesn't swap it for other data.
>
> With that much memory, the index is likely to remain in memory no
> matter what size shared_memory he has. Anything in shared_memory is
> going to be in the system cache anyway. I wonder if there's something
> else we havn't been told, like how big the actual table is and whether
> there are any other large tables/indexes.

The table itself is about 10M rows corresponding to 14GB. The only
other index on this table is a btree index whose size is ~300MB. The
machine is not running anything else.

Now, it might just be the case that given the size of the index, I
cannot make bounding box queries (which will use the CUBE index) go
any faster. But I am surprised that that the other type of query
(using cube_distance which by definition must use a seq scan) is only
slightly longer. If nothing else, scanning through 14GB of data
should be 3 times slower than scanning through 3GB of data.

-------------------------------------------------------------------
Rajarshi Guha <rguha(at)indiana(dot)edu>
GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE
-------------------------------------------------------------------
All laws are simulations of reality.
-- John C. Lilly