indexes bug or feature

From: Heni Lolov <hal_bg(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: indexes bug or feature
Date: 2001-12-28 10:57:12
Message-ID: 20011228105712.66233.qmail@web21002.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Heni Lolov 2001-12-28 11:01:49 indexes bug or feature
Previous Message Jules Alberts 2001-12-27 09:25:47 Re: Database alternate locations