From: Hiroshi Inoue <Inoue@tpf.co.jp>
To: Sergio de Almeida Lenzi <lenzi@k1.com.br>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: RE: sql query not using indexes
Date: Fri, 22 Sep 2000 06:28:18 +0900
Message-ID: <EKEJJICOHDIEMGPNIFIJCEPHCFAA.Inoue@tpf.co.jp> (raw)
In-Reply-To: <Pine.BSF.4.21.0009210909260.85150-100000@server.ilhadamagia.com.br>
> -----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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: Inoue@tpf.co.jp, lenzi@k1.com.br
Subject: Re: sql query not using indexes
In-Reply-To: <EKEJJICOHDIEMGPNIFIJCEPHCFAA.Inoue@tpf.co.jp>
References: <Pine.BSF.4.21.0009210909260.85150-100000@server.ilhadamagia.com.br> <EKEJJICOHDIEMGPNIFIJCEPHCFAA.Inoue@tpf.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox