X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6D9579FB2D5 for ; Thu, 10 Aug 2006 05:15:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 50472-08 for ; Thu, 10 Aug 2006 05:15:05 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.183]) by postgresql.org (Postfix) with ESMTP id 52D379FB2BE for ; Thu, 10 Aug 2006 05:15:04 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id n25so663642pyg for ; Thu, 10 Aug 2006 01:15:03 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=nULVFIwEWe5uBVcp/daLkxkao4JQu7KrAPviciyUlDW3oXdpkQUt3g5BAq/1WdP+kIigG3h8jcITOFkH+S5SALW6zyFEVkr/jaCWtNYW0GmcGzSTk3KUtLpU3qf3t6aF3wVSp6V1nS0CF8JV5O2dlUGUF1+k2C71fKh/QhXTPbE= Received: by 10.35.100.6 with SMTP id c6mr3383642pym; Thu, 10 Aug 2006 01:15:03 -0700 (PDT) Received: from ?10.1.1.22? ( [203.217.18.65]) by mx.gmail.com with ESMTP id 40sm414923nzf.2006.08.10.01.15.01; Thu, 10 Aug 2006 01:15:03 -0700 (PDT) Message-ID: <44DAEB04.9050306@gmail.com> Date: Thu, 10 Aug 2006 18:15:00 +1000 From: Chris User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) MIME-Version: 1.0 To: Sue Fitt CC: pgsql-performance@postgresql.org Subject: Re: setting up foreign keys References: <44DAE8CF.1030107@inf.ed.ac.uk> In-Reply-To: <44DAE8CF.1030107@inf.ed.ac.uk> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.187 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/177 X-Sequence-Number: 20362 Sue Fitt wrote: > Hi all, > > This is my first post to the performance list, I hope someone can help me. > > I'm setting up a table with 2 columns, both of which reference a column > in another table: > > CREATE TABLE headwords_core_lexemes ( > core_id int REFERENCES headwords_core(core_id), > lexeme_id int REFERENCES headwords_core(core_id), > ); One problem here is both of these are referencing the same column ;) I'm sure that's a typo. It sounds like you have something blocking or locking the other table. Check pg_locks (I think it is), 13,000 rows shouldn't take *that* long. Make sure there is an index on headwords_core(core_id) and whatever the other column should be. Foreign keys have to check the other table so without those indexes, it will be slow(er). -- Postgresql & php tutorials http://www.designmagick.com/