Skip site navigation (1) Skip section navigation (2)

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-29 17:16:46
Message-ID: 20011229171646.77305.qmail@web21007.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-admin
--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

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

In response to

pgsql-admin by date

Next:From: MarcDate: 2001-12-31 11:24:29
Subject: Allowing TCP/IP connections
Previous:From: Bruce MomjianDate: 2001-12-29 05:30:15
Subject: Re: I wrote a program to migrate Interbase -> PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group