I'm using 7.3.2 on Linux, with a decent amount of muscle behind it
(1.5 GHz PPro CPU, 1G mem, 20M/sec disks, xlog on different disk than
data).
I've got a database that has several foreign keys, and I'm copying a
bunch of data from an MS-SQL server into it via Perl DBI. I noticed
that inserts into this database are very slow, on the order of 100 per
second on this hardware. All the inserts are happening in a single
transaction. The postmaster I'm connected to appears to be CPU
limited, as it's pegging the CPU at a constant 85 percent or more.
I have no problem with that under normal circumstances (i.e., the
foreign key constraints are actively being enforced): it may well be
the nature of foreign keys, but the problem is this: all the keys are
DEFERRABLE INITIALLY DEFERRED and, on top of that, the Perl program
will SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction.
If I remove all the foreign key constraints, my performance goes up to
700 inserts per second!
Why isn't the insert performance with all the constraints deferred
approximating that of the performance I get without the foreign keys??
If anything, I should get a big delay at transaction commit time while
all the foreign key constraints are checked (and, indeed, I get that
too), but the performance during the transaction prior to the commit
should be the same as it is without the foreign key constraints.
It's almost as if the foreign key constraints are being invoked and
the results ignored during the inserts...
In essence, this smells like a bug to me, but I don't know enough
about the internals to really call it that.
Any ideas on what can be done about this?
--
Kevin Brown kevin@sysexperts.com