Re: Large table performance

On 12-Jan-07, at 7:31 PM, Mark Dobbrow wrote:

> Hello -
>
> I have a fairly large table (3 million records), and am fetching
> 10,000 non-contigous records doing a simple select on an indexed
> column ie
>
> select grades from large_table where teacher_id = X
>
> This is a test database, so the number of records is always 10,000
> and i have 300 different teacher ids.
>
> The problem is, sometimes fetching un-cached records takes 0.5 secs
> and sometimes (more often) is takes more like 10.0 seconds
>
> (fetching the same records for a given teacher_id a second time
> takes about 0.25 secs)
>
> Has anyone seen similar behavior or know what the solution might be?
>
> any help much appreciated,
> Mark
>
>
>
> ps. My shared_buffers is set at 5000 (kernal max), and work_mem=8192

5000 is pretty low, you need at least 1/4 of memory for an 8.1.x or
newer server.
effective cache should be 3/4 of available memory

Dave
>
>