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/ ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
