"Jason E. Stewart" <jason@openinformatics.com> writes:
> I've gotten a bit of feedback from the Perl dbi-users list:
> 1) Transactions: My current approach was to do this inside a
> transaction, but apparently the write-ahead-logging will not handle
> 250k logged inserts well.
> Is this true, and I should commit after every 20 or so spots?
> 2) Indices: apparently every insert updates the indices on the
> table. From my reading of the documentation, the indices aren't
> updated inside a transaction, but instead at the end.
> 3) COPY: I could use COPY, but apparently triggers are not, well,
> triggered under COPY.
I dunno who the heck you got that advice from, but *none* of those
statements are correct, at least not in recent PG releases.
You definitely want to do lots of inserts per transaction. There's
probably not much further improvement to be had beyond a thousand or so
rows per transaction, though.
COPY is faster than a series of INSERTs because you bypass the
parsing and planning overhead needed for each insert.
But my suspicion is that the cycles are actually going into your
triggers. What triggers have you got on this table, and what are they
doing?
regards, tom lane