Re: sql query not using indexes

On Thu, 21 Sep 2000, Sergio de Almeida Lenzi wrote:

> > > 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.
Hmm, I believe Tom Lane was doing alot of stuff with the optimizer and
limit but I don't remember if that was before or after the 7.0 release.
It might be worth trying on current sources to see if that goes back to
an index scan. Or if your data set is safe to give out, I could try
it on my current source machine.

> Is there a way to tell the optimizer to consider going on indixes??
Well, there is a SET you can do to turn off seqscans unless that's the
only way to go, but that's a broad instrument since it affects all
statements until you change it back.