Date: 23 Nov 2002 03:01:43 -0700
Message-ID: <87of8g4naw.fsf@openinformatics.com>
From: "Jason E. Stewart" <jason@openinformatics.com>
To: "David Duff" <dduff@sockeye.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: DBD::Pg timings
References: <KDELIMLFNCKLNDCMBNIIEEMACFAA.dduff@sockeye.com>
In-Reply-To: <KDELIMLFNCKLNDCMBNIIEEMACFAA.dduff@sockeye.com>
Lines: 86
User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Honest Recruiter)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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