From: | Daniel Åkerud <zilch(at)home(dot)se> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Foreign Keys Constraints, perforamance analysis |
Date: | 2001-06-24 19:31:37 |
Message-ID: | 001a01c0fce4$49034cf0$c901a8c0@automatic100 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> > No,
> > I compare
> > DELETE FROM person;
> > against
> > DELETE FROM person;
> > DELETE FROM married;
> > DELETE FROM child;
> >
> > Which I think has very much to do with performane of real-worl
> applications
> > i think. I often think of Accounts, where there are numerous records
> stored
> > for this account - which should be deleted when the account is deleted.
>
> It doesn't unless you delete all your people alot (as Tom said).
Agreed, but I don't want to measure the performance of real-world
application anyway, I just want
to issolate how much you loose having the database manager handle the
deletion for you, as the ON DELETE
CASCADE foreign key constraint does.
> There's a BIG difference between
> delete from person where name='foo' compared to
> delete from person where name='foo'; delete from married where ... ;
delete
> from child where ...;
> and
> delete from person; compared to
> delete from person; delete from married; delete from child;
I can see that,
In the first case there are a hell lot of overhead sending the queries.
> In the first case, the system sees either 1 statement that expands into 3
> statements effectively versus 3 statements. Not too different.
ok...
> In the second case the system sees 1 statement + 1 statement per row
versus
> 3 statements.
I can't see what you mean here... "+ 1 statement per row"... there is only
one row?
> Very different, because it doesn't know it's going to be deleting all of
the
> rows so it's probably going to choose to index scan to find the matching
> rows for each row per each row in person versus knowing before hand to
> delete them all.
OK... hmm... *confused* :)
What is the difference between these two (only comparing the tables with
foreign keys constraits now):
DELETE FROM PERSON;
and
DELETE FROM PERSON where id = 1;
DELETE FROM PERSON where id = 2;
The only thing I can see (which I assume is what I do wrong here), is that
there is a lot of overhead sending the queries. If we ignore the overhead in
our conversation, what is the difference?
> In addition, with match unspecified, these two behaviors are also not
> guaranteed to be the same. With NULLs in the FK fields, you can have rows
> that shouldn't get deleted when you delete all of the PK rows. ("At least
> one of the values of the referencing columns in R1 shall be a null value,
or
> the value of each referencing column in R1 shall be equal to the value of
> the corresponding referenced column in some row of the referenced
table....
> let matching rows be all rows in the referencing table whose referencing
> column values equal the corresponding referenced column values for the
> referential constraint")
OK, but this is just a test i write. I _am_ sure there are no NULLs there. I
just want to make myself
aware of how what it costs in performance having foreign keys constraints.
> There are problems, and it would be nice to figure out a way to combine
> actions and checks when a large number of changes are seen (of course how
do
> you define a large number, but...) to get around some of these bulk cases.
before I send this message I just gotta say thanks!
i appreciate your input more than you think :)
Anyway, what I do is, in pseudocode:
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;
I guess this last example shows quite good what I do. Don't this change your
minds?
Daniel Åkerud
From | Date | Subject | |
---|---|---|---|
Next Message | Jose Manuel Lorenzo Lopez | 2001-06-24 19:53:41 | strange behavior using foreign keys |
Previous Message | Stephan Szabo | 2001-06-24 17:40:41 | Re: Foreign Keys Constraints, perforamance analysis |