Thread: indexes bug or feature

indexes bug or feature

From
Heni Lolov
Date:
Hi

Ihave some problems with the indexes with Pg7.0.3 and
Pg7.2b2. The problem is:

I have folowing table

create table usno(
        reg_id int2,
        id int2,
        ra int4,
        de int4,
        mag_r int2,
        mag_b int2
);

I make indexes in this order:
create index usno_drm_r on gsc_act(de,ra,mag_r);
create index usno_drm_b on gsc_act(de,ra,mag_b);

the problem is:
when I search on "de","ra" and "mag_b" or "de", "ra"
and "mag_r" Postgres uses always "usno_drm_r".
If I create the indexes in reversed order
"usno_drm_b" is always used.

The same problem apears in 7.2b2 but here there is a
similar problem with partial indexes.

When I create two indexes one full and the second on a
subset(partial) and if the searched values exists in
the two of them the index been first created is used,
regardles the partial index search will be faster.

NB I am not sure if the first index or the second
index been created is searched. I cant chek it right
now. The problem is easyly reproduced.

So is this a bug or feature?

yours,
Heni

__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com