Thread: Indexes & Primary Keys (based on the same columns)
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. Appreciate comments.
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. > > Appreciate comments. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
On Sun, 2007-10-21 at 20:49 -0700, Joshua D. Drake wrote: > 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. Many thanks for the confirmation. I'm dropping them...
Ow Mun Heng <Ow.Mun.Heng@wdc.com> writes:
> 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)
Yup, 100% redundant.
regards, tom lane
In response to "Joshua D. Drake" <jd@commandprompt.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. -- Bill Moran http://www.potentialtech.com
On Mon, 2007-10-22 at 08:20 -0400, Bill Moran wrote: > In response to "Joshua D. Drake" <jd@commandprompt.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.