pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Mischa Sandberg <ischamay.andbergsay@activestateway.com>
To: pgsql-sql@postgresql.org
Subject: Re: Join Table
Date: Mon, 01 Nov 2004 19:55:57 GMT
Message-ID: <hxwhd.75176$9b.22716@edtnps84> (raw)
In-Reply-To: <41867C72.4060001@numerixtechnology.de>
References: <41866598.5040909@numerixtechnology.de>
	<20041101170324.GA18943@winnie.fuhr.org>
	<41867C72.4060001@numerixtechnology.de>

T E Schmitz wrote:
>> On Mon, Nov 01, 2004 at 04:34:32PM +0000, T E Schmitz wrote:
>>
>>> Question: is it necessary/advisable to create an index for the 
>>> ITEM_FK column? Or is this redundantbecause this column is already 
>>> one of the PK columns?
>>
>> However, read the "Multicolumn Indexes" section in the "Indexes"
>> chapter to be sure you understand when the index will be used and
>> when it won't be:
>>
>> http://www.postgresql.org/docs/7.4/static/indexes-multicolumn.html
> 
> 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.
> 
> 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?

Oracle uses a tactic called 'index skip scan' that tries to make use
of an index when the first column is not restricted by the query.
	
http://www.oracle-base.com/articles/9i/IndexSkipScanning.php

The idea is that scanning the data in the index is fast, and the
results sets of rowids can be sorted and batched for (relatively)
efficient retrieval from the heap.

I've read one review that indicates there were pitfalls with using it in 9i:

http://www.praetoriate.com/oracle_tips_skip_scan.htm

Having used such schemes for querying (blush) ISAM files
I'd say that this isn't surprising.

view thread (7+ messages)  latest in thread

Message-ID: <hxwhd.75176$9b.22716@edtnps84>
Permalink:  ../hxwhd.75176$9b.22716@edtnps84/
Also on:    postgresql.org/message-id/hxwhd.75176$9b.22716@edtnps84

copy link · copy postgr.es

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: ischamay.andbergsay@activestateway.com
  Subject: Re: Join Table
  In-Reply-To: <hxwhd.75176$9b.22716@edtnps84>

* 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