Re: Postgresql 7.4.8 inconsistent index usage

On 7/8/05, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> On Fri, Jul 08, 2005 at 12:04:07PM -0400, Stephen Bowman wrote:
> >
> > Clearly it needs to use the index =)
>
> Indeed -- now to figure out why the estimates for index scans are
> so high. The row count estimates are almost spot-on, so that's not
> it. What are your settings for the following configuration variables?
>
> shared_buffers
> random_page_cost
> effective_cache_size
> cpu_tuple_cost
> cpu_index_tuple_cost
>
> How much RAM do you have? Have you set shared_buffers and
> effective_cache_size accordingly? The default values are pretty
> low for most modern equipment; see the following for tips on
> choosing appropriate values:
>
> http://www.powerpostgresql.com/PerfList/
>
> You mentioned that you've analyzed the table, but have you also
> vacuumed it recently?
>
> BTW, I should have mentioned earlier that this thread might be
> on-topic in pgsql-performance.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

This is on a Xeon 3ghz with 2gb of RAM. There are 2 SCSI U/320 disks.

For the variables, I have everything defaulted except for:

shared_buffers = 20000
effective_cache_size = 68916

I just added random_page_cost=3 and it seems to have fixed it. Do the
values I have set for these variables make sense with my hardware?
The database is large (~15g).

My apologies on the wrong mailing list - next time I'll use -perf.

Thanks,
--Stephen