I have the following table, im_contacts:
-------------------------------------------------------------
CREATE TABLE im_contacts
(
id int NOT NULL,
contactid int NOT NULL,
CONSTRAINT contacts_contact_valid CHECK (id <> contactid),
CONSTRAINT im_contacts_fkey1 FOREIGN KEY (id) REFERENCES im_users (id)
MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT im_contacts_fkey2 FOREIGN KEY (contactid) REFERENCES im_users (id)
MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE UNIQUE INDEX im_contacts_uindex ON im_contacts (id, contactid);
-------------------------------------------------------------
id and contactid must reference id from im_users. The UNIQUE INDEX
on (id, contactid) should prevent duplicate pairs. id in im_users is
a SERIAL value (no other fancy triggers or rules).
After inserting a few hundred rows, each insert time seems to take longer
and longer. COPYing 25,000 rows to im_contacts takes about 59 seconds on
a dual Pentium III 550 (512 MB RAM, very fast 10K RPM disks) with no other
system load. I get similar results on 7.0.3 and the 7.1 betas.
COPYing 50,000 (2x increase) takes 13 and a half minutes (13.5x increase)!
These databases are clean (newly CREATEd before I did these tests).
The postmaster process doesn't seem to be allocating any more memory
as this COPY takes place.
I'm guessing this is the trigger overhead of the foreign keys (the
CHECK for id <> contactid should be in constant time). The SERIAL
type is UNIQUEly INDEXEd, right? Because I COPY in the im_users
from sorted data (and thus the records are inserted sorted by id),
is this triggering that b-tree balancing problem brought up here a few
days/weeks ago?
Is there anything I could do to make my im_contacts COPY happen faster?
I'd like to get at least 5,000,000 rows in there for testing, but
at the current rate, that could take months.
--
Shaw Terwilliger <sterwill@sourcegear.com>
SourceGear Corporation
217.356.0105 x 641
