Postgres Pro
Downloads
Home   >   mailing lists

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

Re: indexes bug or feature

From
Tom Lane
Date:
You don't need to post questions four times ;-)

Heni Lolov <hal_bg@yahoo.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

indexes bug or feature

From
Heni Lolov
Date:
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> You don't need to post questions four times ;-)
>
> Heni Lolov <hal_bg@yahoo.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.

I am sure that the values are int2 and there is no
casting when I am using psql to examine this.


>
>             regards, tom lane


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