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
"David Duff" <dduff(at)sockeye(dot)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.
> 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.
> 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
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 selected out 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.
- 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.
pgsql-interfaces by date
|Next:||From: Artur Trzewik||Date: 2002-11-23 15:44:59|
|Subject: pgtcl has no coresponding function to PQcmdTuples|
|Previous:||From: Tom Lane||Date: 2002-11-23 02:43:54|
|Subject: Re: Frontend/Backend protocol changes? |