X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2D358475F26 for ; Wed, 14 May 2003 21:55:31 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 88576-01 for ; Wed, 14 May 2003 21:55:29 -0400 (EDT) Received: from shield.nordlb.lv (shield.pirmabanka.lv [195.114.57.4]) by postgresql.org (Postfix) with ESMTP id 1A179475F16 for ; Wed, 14 May 2003 21:55:29 -0400 (EDT) Received: from wall2.pirmabanka.lv (wall2-pub.nordlb.lv [195.114.57.14]) by shield.nordlb.lv (8.12.9/8.12.9) with SMTP id h4F1tWbg019213 for ; Thu, 15 May 2003 04:55:32 +0300 Received: from share.nordlb.lv ([10.0.0.10]) by wall2.pirmabanka.lv; Thu, 15 May 2003 04:55:31 +0300 (EET DST) Received: from pc311x1.nordlb.lv (localhost.localdomain [127.0.0.1]) by nordlb.lv (8.11.6/8.11.6) with SMTP id h4F1tUE22956 for ; Thu, 15 May 2003 04:55:30 +0300 Received: (qmail 2590 invoked by uid 100); 15 May 2003 01:58:11 -0000 Date: Thu, 15 May 2003 04:58:11 +0300 From: "Victor Yegorov" To: Rudi Starcevic Cc: Stephan Szabo , Postgres Performance Subject: Re: constraint with reference to the same table Message-ID: <20030515015811.GE1549@nordlb.lv> References: <20030514181136.S52444-100000@megazone23.bigpanda.com> <3EC2F1E4.8080706@oasis.net.au> Mime-Version: 1.0 Content-Type: text/plain; charset=koi8-r Content-Disposition: inline In-Reply-To: <3EC2F1E4.8080706@oasis.net.au> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/170 X-Sequence-Number: 1949 * Rudi Starcevic [15.05.2003 04:46]: > Stephen, > > > New: > CREATE TABLE business_person > ( > b_id integer REFERENCES business ON UPDATE CASCADE ON DELETE CASCADE NOT > NULL, > pn_id integer REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE NOT NULL > PRIMARY KEY(b_id,pn_id); > ); > CREATE INDEX b_pn_b_id_idx ON business_person (b_id); > CREATE INDEX b_pn_pn_id_idx ON business_person (pn_id); May be it's better to name indexes a bit more clearer? No impact on overall performance, but you'll ease your life, if you project will grow to hundreds of tables and thousands of indicies. > As I'd like to sometime's look up business's, sometime's look up people and > sometimes > look up both I think I should keep the Index's. If your lookups are part of business logic, than it's ok. Also, if your system generates reports using several table joins that may speed up the things. Otherwise, for curiosity cases, it's better to wait some time for the result of one-time queries. -- Victor Yegorov