X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 76C34D6EA9 for ; Tue, 11 Oct 2005 07:18:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26397-03 for ; Tue, 11 Oct 2005 10:18:14 +0000 (GMT) Received: from relay.barclab.com (mail.barclab.com [195.144.76.181]) by svr1.postgresql.org (Postfix) with ESMTP id 79C72D6D78 for ; Tue, 11 Oct 2005 07:18:15 -0300 (ADT) X-Special-Processing: Dr.Web 4.32b scanned X-Special-Processing: Archive BARCLAB Received: from [195.144.76.189] (account psavat@barclab.com HELO [192.168.0.72]) by relay.barclab.com (CommuniGate Pro SMTP 4.1.5) with ESMTP id 11275486 for pgsql-admin@postgresql.org; Tue, 11 Oct 2005 12:18:17 +0200 Message-ID: <434B9169.6040404@barclab.com> Date: Tue, 11 Oct 2005 12:18:17 +0200 From: Pieter-Jan Savat User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Memory/Performance issue: server closed the connection unexpectedly Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.224 required=5 tests=[AWL=0.224] X-Spam-Level: X-Archive-Number: 200510/121 X-Sequence-Number: 19193 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