Allen, > a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and > each fk needed.
Did you ANALYZE after the copy? > If there isn't a significant difference between all of them, performance > wise, I think something is dreadfully wrong here. Running "a", the ALTER > TABLE to add the PK ran for 17 hours and still wasn't finished. Adding the *primary key* locked up? This seems unlikely; we have a known problem with *foreign* keys until the current beta. But I've added primary keys on 20Gb tables and had it complete in a couple of hours. Ignore this adivice and look for Stephan Szabo's FK patch instead if what you really meant was that the FK creation locked up. > shared_buffers = 30000 hmmm ... 236MB .... > max_fsm_pages = 2000000 2MB, fine ... > wal_buffers = 128 1MB, also fine ... > sort_mem = 1310720 (1.2GB) Problem here. As documented everywhere, sort_mem is allocated *per sort* not per query, user, or shared. This means that if the "add PK" operation involves 2 or more sorts (not sure, haven't tested it), then you're allocating .7GB RAM more than you acutally have. This may be the cause of your problem, particularly if *anything* is going on concurrent to the load. > checkpoint_segments = 64 IF you have the disk space (+ 2GB) I'd raise this to 150-300 during the load operation. > commit_delay = 20000 > commit_siblings = 2 These settings are for heavy multi-user update activity. They are not useful for a single-user load, and may even lower performance. > stats_start_collector = true > stats_command_string = true > stats_row_level = true > stats_block_level = true If you can do without stats collection during load, I would suggest that you do so. The above add both RAM and I/O overhead to your operation. -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster