• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3412
  • Last Modified:

How to optimize PostgreSQL for a 16GB RAM Quad-Core machine?

I would like to optimize postgres for the best possible performance for a single query. I am not going to use it in a multi-user environment, but performance of a single query is most important.
Could someone suggest correct config settings for PostgreSQL 8.2 for the following hardware:
Dell PowerEdge 2900, 1 Xeon 2.6GHz Quad-Core processor, 16GB memory, 4 RAID0 15000RPM SAS hard-disks, Windows 2003 R2 64Bit Server Edition (default configuration).
When I just use the default config file, I have a feeling postgres is not even using a 0.1% of the given resources, especially in terms of memory.
  • 2
  • 2
1 Solution

First of all, you have Windows 2003 R2 64Bit Server Edition (default configuration), which means
that you run PostgreSQL via Cygwin. I belive some of the performance will be lost in the Cygwin layer.

That beeing said, I don't think that anyone will just give you such configuration for your server, because is quite new and because there aren't so many people running PostgreSQL in top of Cygwin in an Windows environment.

To find the best you have to try several posibilities and see which one is the best.
I would start with running EXPLAIN on your query and see how the query is planned, maybe you
get some hints from there on what is going on in the server and what you can improve.

Some pointers to what I would try first:
1. Operating System Environment
    You can play with some of these setting if they are available in Cygwin.
2. Server Configuration
    Start with: Resource Consumption
    - increase shared_buffers, temp_buffers
    - i would definitely increase work_mem and maintenance_work_mem
      if you say that you only have one query
3. Play with WAL settings:

You can use the runtime statistics to see which configuration is better:

Also, some tips on performance are here:

mersisAuthor Commented:
Thanks adrpo. No, I am not using cygwin to run PostgreSQL. Why would I need it?
I have just downloaded the windows installer for postgreSQL and used it. It worked fine and the database works well too.

My bad. I sow now that they made a MinGW-based version.
Sorry about that. However, you can still play with points 2 and 3 from my last post.

mersisAuthor Commented:
Ok thanks adrpo, will have a look at those links and see if it makes things any better.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now