Re: Query planner refuses to use index

On Mon, Jul 25, 2005 at 11:23:01AM +0200, Kilian Hagemann wrote:
> shared_buffers, effective_cache_size and cpu_index_tuple_cost all have their
> default values of 1000, 1000 and 0.001 respectively. From their descriptions
> I gather that's reasonable and I don't know how I would optimise these for my
> system (I cannot find any hints in the documentation). It has 512MB of RAM,
> Pentium M 1.5Ghz, but is used as a desktop system (KDE) with the database
> sitting on an external 7200rpm USB 2.0 harddisk for space reasons.

Various third-party tuning guides exist -- here's one (although it
says it's for 8.0, much of the advice applies to earlier versions
as well):

http://www.powerpostgresql.com/PerfList/

With 512MB of RAM you should benefit from raising effective_cache_size,
and you could probably raise shared_buffers as well. You'll have to
experiment to find the best values, especially on a mixed-use machine
where the database is competing with other processes for resources.
The above tuning guide suggests setting effective_cache_size to 2/3 of
RAM on a dedicated server, which for you would be a setting of about
40000, so you should probably stay below that.

As for random_page_cost, on my systems and with my usage patterns,
I've found that a setting of 2 results in more realistic plans than
the default of 4. Your mileage (kilometerage?) may vary.

Whatever the results of your experiments, could you post the settings
you tried and the corresponding EXPLAIN ANALYZE outputs?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/