The postgres is running on Linux Fedora core 3 (production will be redhat on Dell 4 proc 8 gig box).
My client pgadminIII is running on XP.
Sorry I was not clearer on this.
Ah! you're the gent who had the problems with SE-Linux on Fedora 3. Sorry - should have made the connection, but there's so much traffic on the lists it's easy to miss.
I am playing with the settings now, I got it to return in 100 secs (the view that is that took 135 on MSSQL). My testing is using identical Dell desktops for the MSSQL and the Linux, with a third machine for the clients.
I do not mind getting up to speed on the proper setting to optimize the hardware, I am worried that as production environment can be somewhat dynamic that I will have issues getting a optimized environment and that it will work for our needs. My whole reason for being here is that our duel proc production MSSQL server is just no longer keeping up with the demand, so it is important that whatever I implement is going to up to the challenge.
You might want to look at the overall design of the database at some point too. Also, don't forget the compromises you made when designing for MSSQL might not be useful (or even harmful) with PG.
> I am still convinced Postgres was the correct choice, especially
with all the guidance I have been able to get here. 100 seconds will be fine compared to the 135 of MSSQL, I just was getting worse responses before adjusting. At the moment I think I went too far as I see it using swap and going slower, but it never used much of the 756 meg (137 max was all I ever saw it use).
If you're on Linux then 135MB sounds like too much (for one client, far too much).
I guess the swap buffers and cache are the important settings (least that seems to be what is affecting the memory). Not sure exactly what would cause it to use seq vrs index, but I will try the force and see if it helps the speed.
Try starting with your shared-buffers at say 4000-8000 (32MB to 64MB), sort-mem/work-mem at 8000-32000 (8MB-32MB), random-page-cost somewhere between 2 and 4. Then, judge how much RAM your box is using to cache disk-space (free -m) and set effective-cache-size accordingly. That's it - you may want to play around with the figures slightly, but pick the lowest numbers above and restart PG and it'll run OK.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
