From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Rajarshi Guha" <rguha(at)indiana(dot)edu> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: keeping an index in memory |
Date: | 2007-10-21 04:45:59 |
Message-ID: | dcc563d10710202145m1c2e5dbbk6396053b0198fa7d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/20/07, Rajarshi Guha <rguha(at)indiana(dot)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?
Not sure. I assume not, but will wait for someone more conversant
with pgsql index usage to post that answer.
> 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)
The kernel will tend to keep it in memory. Usually it does a pretty
good job of that.
> 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.
Depending on how much of the table you're getting back, eventually a
seq scan will outperform an index scan, because in pgsql-land, you
ALWAYS have to hit the table whether there's an index entry or not,
for visibility reasons. So, at some point, a certain percentage of
the table being retrieved (1 to 25%) will trigger a sequential scan,
and rightly so.
> What strategies do people follow when the index becomes very big?
You can only fit so much data into memory. Once a db gets big enough
that it simply can't be stuffed into memory, you need a fast storage
subsystem.
From | Date | Subject | |
---|---|---|---|
Next Message | snacktime | 2007-10-21 06:11:32 | looking for some real world performance numbers |
Previous Message | Trevor Talbot | 2007-10-21 03:55:01 | Re: 8.2.3: Server crashes on Windows using Eclipse/Junit |