From: Michael Fuhr <mike@fuhr.org>
To: T E Schmitz <mailreg@numerixtechnology.de>
Cc: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us
Cc: dev@archonet.com
Subject: Re: Join Table
Date: Mon, 1 Nov 2004 21:22:52 -0700
Message-ID: <20041102042252.GA56614@winnie.fuhr.org> (raw)
In-Reply-To: <41867C72.4060001@numerixtechnology.de>
References: <41866598.5040909@numerixtechnology.de>
<20041101170324.GA18943@winnie.fuhr.org>
<41867C72.4060001@numerixtechnology.de>
On Mon, Nov 01, 2004 at 06:12:02PM +0000, T E Schmitz wrote:
> I see. If using a multi-column PK, the order matters.
> So, if I want to access the table both via the 1st and 2nd PK column, I
> would have to define an index for the 2nd column to avoid a full table scan.
If you want to use an index scan when querying by the 2nd column
alone then you'd need to create an index on it. Queries using the
1st column alone or the 1st column with the 2nd column will use the
primary key index. You can use EXPLAIN ANALYZE to see which index,
if any, the planner uses.
> Let's ask the question the other way round: I remember seeing a
> discussion (re Oracle) whether to use a multi-column PK or a unique
> constraint in such a situation - I got the impression it is one of these
> "religious" discussions ;-).
> What are the pros and cons?
Here's an excerpt from PostgreSQL's CREATE TABLE documentation:
Technically, PRIMARY KEY is merely a combination of UNIQUE and
NOT NULL, but identifying a set of columns as primary key also
provides metadata about the design of the schema, as a primary
key implies that other tables may rely on this set of columns as
a unique identifier for rows.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, mailreg@numerixtechnology.de, tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: Join Table
In-Reply-To: <20041102042252.GA56614@winnie.fuhr.org>
* 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