I don't see the transaction steps, so forgive me if you just didn't show them. But the common knowledge is that you need to do large inserts inside a transaction, and group that in chunks of 1000 lines or so (don't know where 1000 comes from, but people smarter than me have said this). WIthout that, each insert goes through the "begin transaction, do action, commit transaction" overhead, and it's sloooooooww.

Also, if you have indexes, add them after the entire load is complete. No sense spending a lot of work to maintain a index that won't be queried in its current state.

Even cooler, because DDL in PostgreSQL can also be made part of a transaction, you can begin the transaction, create the table, do all the data loading, add any index and triggers, and then finally commit. Any other thread watching the database would see nothing until the final commit, so it'll look like the entire table sprang into existence fully formed! Even Oracle can't do that, I'm told. (Yes, you can roll back a table drop or schema change in PostgreSQL. Cool.)

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.