Foreign Keys Constraints, perforamance analysis - Mailing list pgsql-general

CREATE TABLE person (

       id         integer DEFAULT nextval('person_id_seq'),

       name        TEXT

);

CREATE UNIQUE INDEX person_id_key ON person(id);

CREATE TABLE married_fkc (

       id         integer DEFAULT nextval('married_fkc_id_seq'),

       person1ID     integer NOT NULL REFERENCES person ( id ) ON DELETE CASCADE,

       person2ID     integer NOT NULL REFERENCES person ( id ) ON DELETE CASCADE,          

       UNIQUE ( person1ID ),

       UNIQUE ( person2ID )

);

CREATE UNIQUE INDEX married_fkc_id_key ON married_fkc(id);

CREATE TABLE married (

       id         integer DEFAULT nextval('married_id_seq'),

       person1ID     integer NOT NULL,

       person2ID     integer NOT NULL,

       UNIQUE ( person1ID ),

       UNIQUE ( person2ID )

);

CREATE UNIQUE INDEX married_id_key ON married(id);

CREATE TABLE child_fkc (

       id         integer DEFAULT nextval('child_fkc_id_seq'),

       marriedID     integer NOT NULL REFERENCES married_fkc ( id ) ON DELETE CASCADE,

       name        TEXT

);

CREATE UNIQUE INDEX child_fkc_id_key ON child_fkc(id);

CREATE TABLE child (

       id            integer DEFAULT nextval('child_id_seq'),

       marriedID     integer NOT NULL,

       name          TEXT

);

1. First, with no measuring of time, I fill the person table with 2*N persons.

2. Filling the married tables with N tuples ( [1, 2] [3, 4] [5, 6] ... ). Measuring time.

3. Fillinf the child tables with 2*N tuples ( [1] [1] [2] [2] ... ) (two children per married couple). Measuring time.

4. Emptying the tables. This means, for *_fkc tables only delete person table. But for the other tables, manual deletion of all the tables.

(this is ofcourse run in two rounds, as step four deletes a common (to both sets) table)

I was not very surprised to see how little difference it made when inserting into the married_fkc table (< 3%), compared to inserting to the married table. I was VERY surprised to see that the difference when inserting into child and child_fkc gave more than 5 times a difference than inserting into the married and married_fkc (25% slower).

Deleting really showed what the MySQL team means. The deletion was sometimes 30 seconds to < 1 second.

If anyone could help, I would really appriciate if someone could tell me why the child/child_fkc difference was so much more than the married/married_fkc difference...

I doubt is was becuase of the lack of VACUUM ANALYSE. It was quite a big difference. Strange is that married_fkc has TWO foreign keys, while child_fkc has only ONE.

Thanks.

Daniel Åkerud