I have recently moved postgres from one server to another (a newer server) and have encounter a problem I cant figure out. I have a table (roughly 7.5GB) that gets re-created every night with fresh data, to insert the data I run the following procedure.
Drop all indexes
The problem I have is that it takes around 15 mins to insert the data into the table which is a massive improvement from the old server but it then takes 40 mins to create two indexes one 1GB index and another that is a 2GB index this is considerably slower than on the older server.
I have had a play around with some of the settings but with no real success, in particular the maintenance_work_mem setting. Any help on this would be greatly appreciated. The new server is a Virtual Server running RedHat Linux, Postgres 8.3 , 8GB of RAM with 4 x 2.66ghz. The current settings in postgresql.conf I have are
You don't have the WAL archive mode set on, do you?
Are you using COPY to insert the records? This is the preferred (fastest) method in this case. If you must use inserts, first prepare once, then execute N times, which will save the repeated parsing/preparation of the inserts.
Are there ANY foreign key constraints which reference the table? They should be dropped & recreated, if so.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert