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

Re: sql query not using indexes

From: Sergio de Almeida Lenzi <lenzi(at)k1(dot)com(dot)br>
To:
Cc: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: sql query not using indexes
Date: 2000-09-21 12:15:23
Message-ID: Pine.BSF.4.21.0009210909260.85150-100000@server.ilhadamagia.com.br (view raw or flat)
Thread:
Lists: pgsql-sql

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...


In response to

Responses

pgsql-sql by date

Next:From: Frank BaxDate: 2000-09-21 15:50:10
Subject: Re: [Fwd: Re: no ORDER BY in subselects?]
Previous:From: Karel ZakDate: 2000-09-21 08:32:50
Subject: Re: C functions and int8?

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