X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8F97947631A for ; Wed, 26 Mar 2003 12:08:57 -0500 (EST) Received: from smtpzilla5.xs4all.nl (smtpzilla5.xs4all.nl [194.109.127.141]) by postgresql.org (Postfix) with ESMTP id 0EEA4476332 for ; Wed, 26 Mar 2003 12:08:54 -0500 (EST) Received: from liacs.nl (zappa.liacs.nl [132.229.137.201]) by smtpzilla5.xs4all.nl (8.12.0/8.12.0) with ESMTP id h2QH8r0j049051; Wed, 26 Mar 2003 18:08:56 +0100 (CET) Message-ID: <3E81DEA3.7060305@liacs.nl> Date: Wed, 26 Mar 2003 18:08:51 +0100 From: Bart Samwel User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3) Gecko/20030316 Debian/1.3-1 X-Accept-Language: en MIME-Version: 1.0 To: Greg Stark Cc: bsamwel@xs4all.nl, 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> <874r5qmctw.fsf@stark.dyndns.tv> In-Reply-To: <874r5qmctw.fsf@stark.dyndns.tv> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-34.8 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,REFERENCES, REPLY_WITH_QUOTES,USER_AGENT_MOZILLA_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/178 X-Sequence-Number: 1476 Greg Stark wrote: > 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. AFAIK, because wwwlog(id) is the primary key, this index already exists implicitly. Still, 0.9 million separate index lookups are too slow for my purposes, if for example it takes something as low as 1 ms per lookup it will still take 900 seconds (= 15 minutes) to complete. As the complete adding of the foreign key constraint took about an hour, that would suggest an average of 4 ms per lookup, which suggests that the index is, in fact, present. :) Anyway, I've actually waited for the operation to complete. The problem is out of my way for now. Bart -- Leiden Institute of Advanced Computer Science (http://www.liacs.nl) E-mail: bsamwel@liacs.nl Telephone: +31-71-5277037 Homepage: http://www.liacs.nl/~bsamwel Opinions stated in this e-mail are mine and not necessarily my employer's.