X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BD58C53874 for ; Tue, 3 May 2005 13:00:04 -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 56178-05 for ; Tue, 3 May 2005 15:59:57 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 42FEB53710 for ; Tue, 3 May 2005 12:59:56 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j43FxlgS019632; Tue, 3 May 2005 11:59:47 -0400 (EDT) To: ashish@openskysoftware.com Cc: pgsql-perform Subject: Re: Foreign key constraints compile faster in 7.4 In-reply-to: <1115133083.3961.5.camel@skyserver1.openskysoftware.com> References: <1115133083.3961.5.camel@skyserver1.openskysoftware.com> Comments: In-reply-to Ashish Arte message dated "Tue, 03 May 2005 10:11:23 -0500" Date: Tue, 03 May 2005 11:59:47 -0400 Message-ID: <19631.1115135987@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/17 X-Sequence-Number: 12181 Ashish Arte writes: > We recently upgraded to Postgres 7.4 from 7.3.9 and noticed that the > foreign key constraints compile noticeably faster. In 7.3 the > constraints would typically take more than an hour to run on our > production data. Now they take a minute or two. > Can anybody explain such a major performance improvement ? Hey, we do do some work on this thing from time to time ;-) Probably you are talking about this: 2003-10-06 12:38 tgl * src/: backend/commands/tablecmds.c, backend/utils/adt/ri_triggers.c, include/commands/trigger.h: During ALTER TABLE ADD FOREIGN KEY, try to check the existing rows using a single LEFT JOIN query instead of firing the check trigger for each row individually. Stephan Szabo, with some kibitzing from Tom Lane and Jan Wieck. regards, tom lane