pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rudi Starcevic <rudi@oasis.net.au>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: constraint with reference to the same table
Date: Thu, 15 May 2003 11:09:12 +1000
Message-ID: <3EC2E8B8.8070805@oasis.net.au> (raw)
In-Reply-To: <20030514174416.K52132-100000@megazone23.bigpanda.com>
References: <20030514174416.K52132-100000@megazone23.bigpanda.com>

Stephan,

Thanks also - I'm actually building a new database as I write this so 
this topic is perfect timing for me.

I'm using ref. integrity right now mostly for many-to-many type situations.

For example.
I create a table of People,
then a table of Business's,
then I need to relate many people to many business's.

So I create a business_people table *with* index's to the referred to tables
Eg:
CREATE TABLE business_people
(
b_p_id serial PRIMARY KEY,
b_id integer REFERENCES business ON UPDATE CASCADE ON DELETE CASCADE NOT 
NULL,
p_id integer REFERENCES people   ON UPDATE CASCADE ON DELETE CASCADE NOT 
NULL
);
CREATE  INDEX b_p_b_id_idx ON  business_people (b_id);
CREATE  INDEX b_p_p_id_idx ON  business_people (p_id);

The b_id and p_id are primary key's in other table's so they have an 
index too.

So far I think I've done every thing right.
Can I ask if you'd agree or not ?

As a side note when I build my PG database's I do it 100% by hand in text.
That is I write Create table statements, save them to file then 
cut'n'paste them into phpPgAdmin or use PSQL.
So the code I have below is the same code I use build the DB.
I wonder if this is OK or would make other PG user's gasp.
I'm sure most database people out there, not sure about PG people, would 
use some sort of GUI.

Thanks kindly
I appreciate your time guy's.
Regards
Rudi.








Stephan Szabo wrote:

>On Thu, 15 May 2003, Rudi Starcevic wrote:
>
>  
>
>>Can I confirm what this means then ..
>>
>>For large table's each column with ref. inegritry I should create an
>>index on those columns ?
>>    
>>
>
>In general, yes.  There's always an additional cost with having additional
>indexes to modifications to the table, so you need to balance the costs by
>what sorts of queries you're doing.  For example, if you're doing a
>references constraint to a table that is mostly there for say providing a
>nice name for something and those values aren't likely to change (and it's
>okay if a change were expensive) then you wouldn't necessarily want the
>additional index.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>  
>

view thread (18+ messages)  latest in thread

Message-ID: <3EC2E8B8.8070805@oasis.net.au>
Permalink:  ../3EC2E8B8.8070805@oasis.net.au/
Also on:    postgresql.org/message-id/3EC2E8B8.8070805@oasis.net.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rudi@oasis.net.au, sszabo@megazone23.bigpanda.com
  Subject: Re: constraint with reference to the same table
  In-Reply-To: <3EC2E8B8.8070805@oasis.net.au>
  References: <20030514174416.K52132-100000@megazone23.bigpanda.com> <3EC2E8B8.8070805@oasis.net.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox