RE: sql query not using indexes

> -----Original Message-----
> From: Sergio de Almeida Lenzi
>
> On Wed, 20 Sep 2000, Stephan Szabo wrote:
>
> > On Wed, 20 Sep 2000, User Lenzi wrote:
> >
> > > if I start a query:
> > >
> > > explain select * from teste where login = 'xxx'
> > > results:
> > > Index  Scan using  teste1 on teste (cost=0.00..97.88 rows=25 )
> > >
> > >
> > > however a query:
> > > explain select * from teste where login > 'AAA'
> > > results:
> > > Seq Scan on teste ....
> > >
> > >
> > > On a machine running version 6.5 both queries results index scan.
> > >
> > > this results that the version 6.5 is faster than version 7.0.2 on this
> > > kind of
> > > query.
> > >
> > >
> > > Any explanation???
> >
> > Have you done a vacuum analyze on the table?  Also, what does the row
> > count for the second query look like?  It's probably deciding that
> > there are too many rows that will match login >'AAA' for index scan
> > to be cost effective.  So, actually, also, what does
> > select count(*) from teste where login>'AAA" give you on the 7.0.2 box.
>
> Ok I agree with you on the real database there are 127,300 rows and there
> are certanly a great number of rows > 'AAA'. But, supose I make a query
> select * from table where code > 'AAA' limit 10. it will read the entire
> table only to give me the first 10 while in release 6.5 it will fetch the
> index for the first 10 in a very fast manner, indeed the 6.5 release
> resolves in 1 second while the 7.0 release resolves in 10-20 sec.
>

Probably the distribution of rows in teste where login > 'AAA' isn't
uniform. You had better add 'ORDER BY login' to your query.

Regards.

Hiroshi Inoue