X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0F00047652B for ; Wed, 14 May 2003 22:02:39 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 88873-05 for ; Wed, 14 May 2003 22:02:37 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id 993D3476528 for ; Wed, 14 May 2003 22:02:36 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id MAA17859; Thu, 15 May 2003 12:11:58 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3EC2F65F.2050004@oasis.net.au> Date: Thu, 15 May 2003 12:07:27 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Victor Yegorov Cc: Stephan Szabo , Postgres Performance Subject: Re: constraint with reference to the same table References: <20030514181136.S52444-100000@megazone23.bigpanda.com> <3EC2F1E4.8080706@oasis.net.au> <20030515015811.GE1549@nordlb.lv> In-Reply-To: <20030515015811.GE1549@nordlb.lv> Content-Type: multipart/alternative; boundary="------------030903040507010607030403" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/171 X-Sequence-Number: 1950 This is a multi-part message in MIME format. --------------030903040507010607030403 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Victor, >> 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. Very true. Instead of: b_pn_b_id_idx, I think better would be: busines_person_b_id_idx Thanks Rudi. Victor Yegorov wrote: >* 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. > > > --------------030903040507010607030403 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
Victor,

>> 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.

Very true.
Instead of: b_pn_b_id_idx,
I think better would be: busines_person_b_id_idx

Thanks
Rudi.



Victor Yegorov wrote:
* Rudi Starcevic <rudi@oasis.net.au> [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.

  

--------------030903040507010607030403--