X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A44BD475F00 for ; Wed, 26 Mar 2003 09:17:50 -0500 (EST) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id 04A88475CBC for ; Wed, 26 Mar 2003 09:17:46 -0500 (EST) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id 3033D1384F9; Wed, 26 Mar 2003 09:17:48 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 18yBit-0001FJ-00; Wed, 26 Mar 2003 09:17:47 -0500 To: bsamwel@xs4all.nl Cc: pgsql-performance@postgresql.org Subject: Re: Adding a foreign key constraint is extremely slow References: <20505.194.109.187.67.1048442304.squirrel@webmail.xs4all.nl> In-Reply-To: <20505.194.109.187.67.1048442304.squirrel@webmail.xs4all.nl> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 26 Mar 2003 09:17:47 -0500 Message-ID: <874r5qmctw.fsf@stark.dyndns.tv> Lines: 19 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-38.8 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_GNUS_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/177 X-Sequence-Number: 1475 bsamwel@xs4all.nl writes: > alter table triples add foreign key(id1) references wwwlog(id); > > PostgreSQL starts doing heavy work for at least one and a half hour, and I > broke it off at that. It is not possible to "explain" a statement like > this! Probably what it does is that it will check the foreign key > constraint for every field in the table. This will make it completely > impossible to load my data, because: > > (2) I cannot set the foreign key constraints AFTER loading the 0.9 million > records because I've got no clue at all how long this operation is going > to take. Try adding an index on wwwlog(id) so that it can check the constraint without doing a full table scan for each value being checked. -- greg