Received: from p2272.nsk.ne.jp (p2272.nsk.ne.jp [210.145.18.145]) by hub.org (8.10.1/8.10.1) with ESMTP id e8LLSJs92456 for ; Thu, 21 Sep 2000 17:28:19 -0400 (EDT) Received: from mcadnote1 (ppm102.noc.fukui.nsk.ne.jp [210.161.188.21]) by p2272.nsk.ne.jp (8.9.3/3.7W-20000722) with SMTP id GAA10399; Fri, 22 Sep 2000 06:26:40 +0900 (JST) From: "Hiroshi Inoue" To: "Sergio de Almeida Lenzi" Cc: "pgsql-sql" Subject: RE: sql query not using indexes Date: Fri, 22 Sep 2000 06:28:18 +0900 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Importance: Normal X-Archive-Number: 200009/178 > -----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