> How are you loading the tables? Copy? Insert? Once the data is transformed, it is inserted. I don't have stats, but the programs visual feedback does not spend a lot of time on the "inserting data" message. Then again, if there is an asynchronous component to an insert, perhaps I am not seeing how slow an insert really is until I query the table. > Yes. Linux or FreeBSD is going to stomp Win32 for PostgreSQL performance. Don't suppose you'd care to hazard a guess on what sort of scale we're talking about? Are we talking about 10%? 100% I know this is a hard one to judge, My impression was that the *NIX improvements were with concurrent use and right now, I am obsessing on this single-threaded issue. > Depends. PostgreSQL is much better with the Xeon in general, but are you > running woodcrest based CPUs or the older models? Weren't those released in July? This server is a few months older, so I guess not. But maybe? Does Dell have the ability to install CPUs from the future like Cyberdyne does? ;-) > I don't know about Windows, but *nix has a number of tools available > directly at the operating system level to help you determine various > bottlenecks. Are we talking about I/O operations? I was thinking of SQL query analysis. The stuff I read here about query analysis is pretty intruiging, but to someone unfamiliar with this type of query analysis it all looks quite uncertain to me. I mean, I read the threads and it all looks like a lot of trying ot figure out how to cajole PostgreSQL to do what you want, rather than telling it: HEY I CREATED THAT INDEX FOR A REASON, USE IT! I know this may be non-dba sophistication on my part, but I would like a tool that would make this whole process a little less arcane. I'm not the Gandalf type. >> 4) Can anyone recommend any commercial PostgreSQL service providers that >> may >> be able to swiftly come in and assist us with our performance issues? > > http://www.commandprompt.com/ (disclaimer, I am an employee) Very much appreciated. >> max_connections = 100 >> shared_buffers = 50000 > This could probably be higher. Ok, good start... > >> work_mem = 32768 > > Depending on what you are doing, this is could be to low or to high. Is this like "You could be too fat or too thin"? Aren't you impressed with the fact that I managed to pick the one number that was not right for anything? > >> maintenance_work_mem = 32768 >> checkpoint_segments = 128 >> effective_cache_size = 10000 > > This coudl probably be higher. ... noted... > >> random_page_cost = 3 >> stats_start_collector = on >> stats_command_string = on >> stats_row_level = on >> autovacuum = on > > Stats are a hit... you need to determine if you actually need them. Unfortunately, this is the only way I know of of getting the query string to appear in the PostgreSQL server status display. While trying to figure out what is slowing things down, having that is really helpful. I also imagined that this sort of thing would be a performance hit when you are getting lots of small, concurrent queries. In my case, we have queries which are taking around a second to perform outer joins. They aren't competing with any other requests as the site is not running, we are just running one app to seed the data.
