Re: DBD::Pg timings

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(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.

> 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 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.

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.