Re: PERFORMANCE and SIZE

On Tue, 13 May 2003, Josh Berkus wrote:

> Alfranio,
>
> > And now, the optimizer started to use a table scan and in consequence gives
> > me:
>
> What appears to me to be happening is that the planner has incorrect estimates
> of the cost of an index lookup. The base estimate is contained in the
> postgresql.conf parameter:
> cpu_index_tuple_cost = 0.001
>
> >From the look of things, your disk/array has much better random seek times
> than the standard, or you have enough RAM to cache most of your tables.
> Either way, I would experiment with lowering the index_tuple_cost to, say,
> 0.0003 and see if you get better use of indexes.
>
> If that does work for you, make sure to check some other queries unrelated to
> the "customers" table to make sure that the new setting doesn't mess them up
> in some way.

Also, you can lower random page cost. And make sure the query planner has
some idea how much effective cache you have, as it can kind of take that
into account too. i.e. a machine wiht 800 Meg cache is far more likely to
have data in memory than one 100 MEg cache. This is kernel cache I'm
talking about, by the way. effective cache size is set in 8k blocks.