Skip site navigation (1) Skip section navigation (2)

Re: constraint with reference to the same table

From: "Victor Yegorov" <viy(at)nordlb(dot)lv>
To: Rudi Starcevic <rudi(at)oasis(dot)net(dot)au>
Cc: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>,Postgres Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: constraint with reference to the same table
Date: 2003-05-15 01:58:11
Message-ID: 20030515015811.GE1549@nordlb.lv (view raw or flat)
Thread:
Lists: pgsql-performance
* Rudi Starcevic <rudi(at)oasis(dot)net(dot)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.

-- 

Victor Yegorov

In response to

Responses

pgsql-performance by date

Next:From: Rudi StarcevicDate: 2003-05-15 02:07:27
Subject: Re: constraint with reference to the same table
Previous:From: Rudi StarcevicDate: 2003-05-15 01:48:20
Subject: Re: constraint with reference to the same table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group