From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jason E. Stewart <jason@openinformatics.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Frontend/Backend protocol changes?
Date: Fri, 22 Nov 2002 12:01:34 -0500
Message-ID: <25204.1037984494@sss.pgh.pa.us> (raw)
In-Reply-To: <87of8ha7ch.fsf@openinformatics.com>
References: <87of8ha7ch.fsf@openinformatics.com>
"Jason E. Stewart" <jason@openinformatics.com> writes:
> I've gotten a bit of feedback from the Perl dbi-users list:
> 1) Transactions: My current approach was to do this inside a
> transaction, but apparently the write-ahead-logging will not handle
> 250k logged inserts well.
> Is this true, and I should commit after every 20 or so spots?
> 2) Indices: apparently every insert updates the indices on the
> table. From my reading of the documentation, the indices aren't
> updated inside a transaction, but instead at the end.
> 3) COPY: I could use COPY, but apparently triggers are not, well,
> triggered under COPY.
I dunno who the heck you got that advice from, but *none* of those
statements are correct, at least not in recent PG releases.
You definitely want to do lots of inserts per transaction. There's
probably not much further improvement to be had beyond a thousand or so
rows per transaction, though.
COPY is faster than a series of INSERTs because you bypass the
parsing and planning overhead needed for each insert.
But my suspicion is that the cycles are actually going into your
triggers. What triggers have you got on this table, and what are they
doing?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, jason@openinformatics.com
Subject: Re: Frontend/Backend protocol changes?
In-Reply-To: <25204.1037984494@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox