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

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.