Re: sql query not using indexes

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.

Is there a way to tell the optimizer to consider going on indixes??

I did make a vaccum analyze on both tables.. and the result remains the
same...

Thanks for any help...