On Oct 21, 2007, at 7:36 AM, Bill Moran wrote:
Rajarshi Guha <rguha@xxxxxxxxxxx> 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@xxxxxxxxxxx>
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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
