Re: Really bad insert performance: what did I do wrong?

OK, I'm gonna make a couple of observations here that may help out.

1:  sun's performance on IDE hardware is abysmal.  Both Solaris X86 and
Solaris Sparc are utter dogs at IDE, even when you do get DMA and prefetch
setup and running.  Linux or BSD are much much better at handling IDE
interfaces.

2:  Postgresql under Solaris on Sparc is about 1/2 as fast as Postgresql
under Linux on Sparc, all other things being equal.  On 32 bith Sparc the
chasm widens even more.

3:  Inserting ALL 700,000 rows in one transaction is probably not optimal.
Try putting a test in every 1,000 or 10,000 rows to toss a "commit;begin;"
pair at the database while loading.  Inserting all 700,000 rows at once
means postgresql can't recycle the transaction logs, so you'll have
700,000 rows worth of data in the transaction logs waiting for you to
commit at the end.  That's a fair bit of space, and a large set of files
to keep track of.  My experience has been that anything over 1,000 inserts
in a transaction gains little.

4:  If you want to make sure you don't insert any duplicates, it's
probably faster to use a unique multi-column key on all your columns
(there's a limit on columns in an index depending on which flavor of
postgresql you are running, but I think it's 16 on 7.2 and before and 32
on 7.3 and up.  I could be off by a factor of two there.