Re: Best memory/planner settings for Postgres

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Thom Brown
> Sent: Tuesday, November 04, 2008 1:45 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Best memory/planner settings for Postgres
>
> We've got a dedicated database server running PostgresSQL 8.0.9 (yes,
> I know it needs upgrading), but I've noticed it looks criminally
> under-configured.
>
> Basically it's running on a server with 2 dual-core Intel Xeon 2.33
> Ghz processors and 4Gb memory, but has the following settings in
> postgresql.conf
>
> shared_buffers = 1000
> work_mem = 1024
> effective_cache_size = 2500
> default_statistics_target = 100
>
> Maybe other settings should be the subject of focus too. The type of
> data we have uses quite extensive use of IN lists (e.g. WHERE
> target_id IN (3423452, 65465, 6523, 2436, 26464, 2646464, 4, 2644624,
> 264642, 5344342, 65746735, 25332, 6435375, 251353, 3573573, 357363634,
> 252523523, 235235235, 5688282, 28647532564, 452525, 335745, 376357357,
> 375757357, 3573735735)
>
> That's just an example as lists can often be a longer than that, and
> I've noticed it doesn't appear to be using the index on the column
> being queried. Queries such as that are used very frequently. We
> also make at least a couple joins on most queries and often use
> DISTINCT.
>
> Has anyone got recommendations on what the config settings should be
> set to? And also any other settings I have neglected to highlight?
> I feels like PostgreSQL is the only resident in a mansion, but is
> locked in a room on the ground floor.

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
http://www.powerpostgresql.com/download/TFCKUpload/5.x-pdf
http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.h
tml
http://www.scribd.com/doc/4846381/PostgreSQL-Performance-Tuning