Sluggish INSERTS with Foreign Keys (7.1beta5)
От | Shaw Terwilliger |
---|---|
Тема | Sluggish INSERTS with Foreign Keys (7.1beta5) |
Дата | |
Msg-id | 20010314170911.G17868@lister.sourcegear.com обсуждение исходный текст |
Ответы |
Re: Sluggish INSERTS with Foreign Keys (7.1beta5)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: