Jeremy Haile wrote:
What is a decent default setting for work_mem and maintenance_work_mem, considering I am regularly querying tables that are tens of millions of rows and have 2-4 GB of RAM?
Well, work_mem will depend on your query-load. Queries that do a lot of sorting should benefit from increased work_mem. You only have limited RAM though, so it's a balancing act between memory used to cache disk and per-process sort memory. Note that work_mem is per sort, so you can use multiples of that amount in a single query. You can issue a "set" to change the value for a session.
How you set maintenance_work_mem will depend on whether you vacuum continually (e.g. autovacuum) or at set times.
Also - what is the best way to determine decent settings for temp_buffers and random_page_cost?
With all of these, testing I'm afraid. The only sure thing you can say is that random_page_cost should be 1 if all your database fits in RAM.
-- Richard Huxton Archonet Ltd
