RE: Foreign Keys Constraints, perforamance analysis
От | Daniel Åkerud |
---|---|
Тема | RE: Foreign Keys Constraints, perforamance analysis |
Дата | |
Msg-id | 005c01c0fce8$44c55850$c901a8c0@automatic100 обсуждение исходный текст |
Ответ на | Foreign Keys Constraints, perforamance analysis (Daniel Åkerud <zilch@home.se>) |
Список | 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
В списке pgsql-general по дате отправления: