Re: Indexes & Primary Keys (based on the same columns)

[Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com>]

On Mon, 2007-10-22 at 08:20 -0400, Bill Moran wrote:
> In response to "Joshua D. Drake" <jd(at)commandprompt(dot)com>:
> 
> > Ow Mun Heng wrote:
> > > I'm wondering if what I'm doing is redundant.
> > > 
> > > I have a primary key on columns (A,B,C,D)
> > > and I've also defined an index based on the same columns (A,B,C,D)
> > > 
> > > and sometimes in the query explain, I see the pkey being used for the
> > > scan instead of the index.
> > > 
> > > So.. That made me think perhaps the additional index on the _same_
> > > parameter is redundant.
> > 
> > A primary key creates an index so having a second index with the same 
> > definition is redundant.
> 
> Note the "same definition."
> 
> Since this is a multi-column index, there may be some advantage gained
> by having indexes defined slightly differently.  I.e., your PK is
> (ABCD) but you have an additional index on (DCBA)
> 
> Whether or not this is actually helpful depends on the nature of the
> queries you run.
> 

I found that that might not matter as much as there are bitmap indexes
which seems to be able to handle these.