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

>
> FOR ( i = 1 to N*2)
>   insert into person
>
> FOR (i = 1 to N)
>   insert into married or married_fkc
>
> FOR (i = 1 to 2*N)
>   insert into child or child_fkc
>
> if (fkc)
>   delete from person;
> else
>   delete from person, delete from married, delete from child;
>

Forgott to say that these 4 sections is in four transactions. and with
vacuum analyse in between all of them.

NOW, why is it that the difference between the married/married_fkc (which is
about 50% longer per insert) is the same on child/child_fkc ? Ofcourse
child/child_fkc should take roughly twice the time as married/married_fkc
ignoring the fact that
there are FK Constraints.
But considering the double foreign keys constraints in married_fkc that is
quite strange...

Is there something wrong with the tables?

DROP SEQUENCE person_id_seq;
DROP SEQUENCE married_fkc_id_seq;
DROP SEQUENCE married_id_seq;
DROP SEQUENCE child_fkc_id_seq;
DROP SEQUENCE child_id_seq;

CREATE SEQUENCE person_id_seq MINVALUE 0;
CREATE SEQUENCE married_fkc_id_seq MINVALUE 0;
CREATE SEQUENCE married_id_seq MINVALUE 0;
CREATE SEQUENCE child_fkc_id_seq MINVALUE 0;
CREATE SEQUENCE child_id_seq MINVALUE 0;

DROP TABLE person;
DROP TABLE married_fkc;
DROP TABLE married;
DROP TABLE child_fkc;
DROP TABLE child;

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
);

CREATE UNIQUE INDEX child_id_key ON child(id);

Daniel Åkerud