Hi,
I'm performing some tests on performance: when to use or not use foreign
keys.
I have a table A with 50 columns, 8 of them have a foreign key to some
other table,
and there is a table B with 65 columns, only 5 of them have a foreign
key to some table (so foreign data is copied locally).
The data inside tables A and B is identical. The only difference is that
table A has 3 (foreign) ids more than
table B, and table B contains the actual (foreign) data.
My first test of course pointed out that the amount of memory used by
table A is a lot less than table B.
In my second test I wanted to check if table A was slower in doing
updates because of its foreign keys, or if
it would be faster (because the amount of data temporarily stored to
commit the updates in one transaction would
be less)
The queries I used:
- update A set flag = 'X' where type_id = 13
- update B set flag = 'X' where type_id = 13
And I have to mention that there is no foreign key on the flag column.
Now here's my problem. Updating table B works fine, but updating table A
makes the server "close the connection unexpectedly".
After looking at the 'top' of my server I found that the update of B
took a fixed amount of data (28MB), while the update of A resulted in a
continuous increase in the amount of data needed (it crashed when
reaching 55MB)
I'm doing my tests on a small server (not much RAM) with out-of-the-box
settings using PostgreSQL 8.0.
Does anyone know why
a) the server "closes the connection unexpectedly" ?
b) the update of a table with less data but more foreign keys requires
increasingly more memory?
-- pj