Thread: 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