Re: indexes bug or feature

You don't need to post questions four times ;-)

Heni Lolov <hal_bg(at)yahoo(dot)com> writes:
> 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.

I bet that it's only using the first two columns of the index,
so that it doesn't really matter which index is used. Are you
being careful to cast the values compared to mag_r or mag_b to
int2? "mag_r = 42" isn't indexable because 42 is an int4.

regards, tom lane