Hey All,
I'd like to start off by saying that I've happily resolved my insert
problems. The conclusion was extra baggage in un-needed constraints
and triggers, especially triggers.
So, thanks everyone (especially Tom Lane) for helping me figure this
out.
"David Duff" <dduff@sockeye.com> writes:
> i timed inserts of 100k records using the following three techniques:
>
> 1. row-at-a-time insert using a prepared insert statement.
I timed 250k records only using 1.
> results:
>
> 1. 1053 records per second
- 647 records per second
After I figured out that most of my problem was a trigger, and
whittled it down to the bare essentials.
> general conclusion:
Be careful of your triggers.
I removed a foreign key constraint from my table (kept the column,
removed the constraint as it was already satisfied by another trigger
I didn't need two triggers for the same job).
I also removed one of the two inserts from the trigger.
> details:
>
> this was postgres running on solaris.
mine was a dual 1.8G Athlon box running debian linux.
> table had six fields - three varchar(32)'s, two integers, and a float.
> string values inserted were short - ~5-8 bytes.
>
> there was one unique index defined on the table.
table had 21 fields but I was only inserting into the 5 not null
fields: 2 varchar(128)'s, 2 name's, and a bigint.
There are two unique indices on the table.
> autocommit was off. a single $dbh->commit was done in each test
yup.
Also, I had a little extra baggage:
* All inserts were done in a loop reading values out of a perl 2D array. Before each insert the primary key value was
selectedout of a sequence in the DB.
* Then there was the trigger on INSERT for each row that inserted a timestamp into an audit table.
So given my extra unique index, the select on the sequence and the
trigger doing an extra INSERT, I'm pretty happy that I'm seeing 60% of
what you were getting.
Conclusions:
- watch your triggers.
This now seems idiotically obvious now that I've gone through all this
(and I feel dumb for potentially wasting a lot of people's time), but
I was getting what I thought was reasonable performance for small
inserts (<1000 rows) but when I tried inserting something really big
for a change everything seemed to fall apart.
I'd just like to repeat:
Thanks everyone for helping me figure this out.
Cheers,
jas.