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

RE: sql query not using indexes

From: "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp>
To: "Sergio de Almeida Lenzi" <lenzi(at)k1(dot)com(dot)br>
Cc: "pgsql-sql" <pgsql-sql(at)postgresql(dot)org>
Subject: RE: sql query not using indexes
Date: 2000-09-21 21:28:18
Message-ID: EKEJJICOHDIEMGPNIFIJCEPHCFAA.Inoue@tpf.co.jp (view raw or flat)
Thread:
Lists: pgsql-sql
> -----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


In response to

pgsql-sql by date

Next:From: Forest WilkinsonDate: 2000-09-22 00:35:27
Subject: Re: C functions and int8?
Previous:From: Jie LiangDate: 2000-09-21 20:25:05
Subject: Re: Convert from Seconds-Since-Epoch to Timestamp

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