Index and performance
Hi,
to get the best query performance is it necessary to create a separate
index on a column which is already part of an another index?
Example:
Table TEST:
COL1
COL2
COL3
The PRIMARY KEY consists of COL1 and COL2. But if I run a query only on
the not unique COL1 is it better for the query performance to have a
separate index or is the existence of the primary key sufficient?
Greetings,
Andreas
On Sun, 15 Dec 2002 22:45:49 +0100, Andreas Schlegel
<schlegelaw@gmx.de> wrote:
The PRIMARY KEY consists of COL1 and COL2. But if I run a query only on
the not unique COL1 is it better for the query performance to have a
separate index or is the existence of the primary key sufficient?
*If* the index is used (and if col2 is not extremely wide), the
performance impact of an unused index column shouldn't be noticeable.
However, I have seen cases where the presence of additional index
columns fooled the planner into not choosing the index scan. The
decision depends mainly on the selectivity of your col1 condition and
on how well the tuples are sorted by col1.
EXPLAIN ANALYZE is your friend.
Servus
Manfred