X-Original-To: pgsql-hackers@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9E4CE475ADE; Fri, 18 Apr 2003 01:11:34 -0400 (EDT) Received: from filer (12-234-86-219.client.attbi.com [12.234.86.219]) by postgresql.org (Postfix) with ESMTP id C6430475458; Fri, 18 Apr 2003 01:11:33 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) (uid 1000) by filer with local; Thu, 17 Apr 2003 22:11:33 -0700 Date: Thu, 17 Apr 2003 22:11:33 -0700 From: Kevin Brown To: pgsql-hackers@postgresql.org Cc: pgsql-performance@postgresql.org Subject: Foreign key performance Message-ID: <20030418051133.GK1833@filer> Mail-Followup-To: Kevin Brown , pgsql-hackers@postgresql.org, pgsql-performance@postgresql.org Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline User-Agent: Mutt/1.4i Organization: Frobozzco International X-Spam-Status: No, hits=-9.4 required=5.0 tests=BAYES_20,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/678 X-Sequence-Number: 38168 I'm using 7.3.2 on Linux, with a decent amount of muscle behind it (1.5 GHz PPro CPU, 1G mem, 20M/sec disks, xlog on different disk than data). I've got a database that has several foreign keys, and I'm copying a bunch of data from an MS-SQL server into it via Perl DBI. I noticed that inserts into this database are very slow, on the order of 100 per second on this hardware. All the inserts are happening in a single transaction. The postmaster I'm connected to appears to be CPU limited, as it's pegging the CPU at a constant 85 percent or more. I have no problem with that under normal circumstances (i.e., the foreign key constraints are actively being enforced): it may well be the nature of foreign keys, but the problem is this: all the keys are DEFERRABLE INITIALLY DEFERRED and, on top of that, the Perl program will SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction. If I remove all the foreign key constraints, my performance goes up to 700 inserts per second! Why isn't the insert performance with all the constraints deferred approximating that of the performance I get without the foreign keys?? If anything, I should get a big delay at transaction commit time while all the foreign key constraints are checked (and, indeed, I get that too), but the performance during the transaction prior to the commit should be the same as it is without the foreign key constraints. It's almost as if the foreign key constraints are being invoked and the results ignored during the inserts... In essence, this smells like a bug to me, but I don't know enough about the internals to really call it that. Any ideas on what can be done about this? -- Kevin Brown kevin@sysexperts.com