From: | Daniel Åkerud <zilch(at)home(dot)se> |
---|---|
To: | "PostgreSQL-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Foreign Keys Constraints, perforamance analysis |
Date: | 2001-06-23 15:12:34 |
Message-ID: | 000001c0fc02$5986bb30$c901a8c0@automatic100 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I wanted to know how Foreign Keys Constraints affected the performance. The MySQL team has not implemented this feature yet as they state it has many disadvantages. Well, the only one I could see was speed. Still the software industry is more and more aiming towards maintainable, clean code... so I still prefer using it.
Anyway, I wanter to see in detail how much you loose when having it, so I created a few tests. I started by building two small hiearchies of tables (with the help of this mailinglist) that looks like this:
Person -> married -> child
Person -> married_fkc -> child_fkc
The *_fkc has foreign keys, and foreign keys constraints, while the other hasn't.
The tables look like this (no SERIAL due to the need to reset the sequence values to 0):
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-06-23 15:56:03 | Re: Speed... |
Previous Message | Daniel Åkerud | 2001-06-23 12:29:22 | Re: TCP/IP Sockets, UNIX Sockets |