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.
Show quoted text
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.