Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154] (may be forged)) by hub.org (8.10.1/8.10.1) with ESMTP id e8MGqNs31103 for ; Fri, 22 Sep 2000 12:52:23 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.0/8.11.0) with ESMTP id e8MGpWB29458; Fri, 22 Sep 2000 12:51:32 -0400 (EDT) To: Stephan Szabo cc: Sergio de Almeida Lenzi , pgsql-sql Subject: Re: sql query not using indexes In-reply-to: References: Comments: In-reply-to Stephan Szabo message dated "Thu, 21 Sep 2000 10:26:20 -0700" Date: Fri, 22 Sep 2000 12:51:32 -0400 Message-ID: <29455.969641492@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200009/183 Stephan Szabo writes: >> 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. No, it'll still do a seqscan. 6.5 was in fact too ready to use indexscans; the current code may have overcorrected a shade, but I think it's closer to reality than 6.5 was. As Hiroshi already commented, the difference in results suggests that the desired data is very nonuniformly scattered in the table. 7.0 computes cost estimates on the assumption that the target data is uniformly scattered. For a sufficiently nonselective WHERE condition (ie, one that the planner thinks will match a large fraction of the table's rows) it looks better to do a seqscan and pick up the matching rows than to follow the index pointers. Adding a LIMIT doesn't change this equation. I like Hiroshi's recommendation: add an ORDER BY to help favor the indexscan. regards, tom lane