Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3DF122E0048 for ; Thu, 6 Mar 2008 22:16:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 80698-04 for ; Thu, 6 Mar 2008 22:15:57 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from beaver.fastcrypt.com (beaver.fastcrypt.com [64.7.141.17]) by postgresql.org (Postfix) with ESMTP id 741182E002F for ; Thu, 6 Mar 2008 22:16:05 -0400 (AST) Received: from [192.168.1.14] (dcdsl.ebox.com [64.7.143.116]) by beaver.fastcrypt.com (Postfix) with ESMTP id DA692158036C; Thu, 6 Mar 2008 21:21:07 -0500 (EST) Cc: "postgresql performance list" Message-Id: From: Dave Cramer To: Stephen Denne In-Reply-To: Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v919.2) Subject: Re: Why the difference in plans ? Date: Thu, 6 Mar 2008 21:16:02 -0500 References: X-Mailer: Apple Mail (2.919.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/139 X-Sequence-Number: 29093 On 6-Mar-08, at 5:10 PM, Stephen Denne wrote: > Dave Cramer wrote: >> I have two almost identical queries. Strangely enough the one >> that uses the index is slower ??? > > The index scan is being used so that it can retrieve the rows in the > name order. > It expects that if it was to retrieve every row via the index, it > would get about 1010 rows that matched the filter, and it knows it > can stop after 250, so assuming the matching rows are evenly > distributed it thinks it can stop after having read only a quarter > of the rows. > > However only 129 rows matched. Consequently it had to read every row > in the table anyway, seeking a fair bit as the read order was > specified by the index rather than in sequential order, and it also > had to read the index. These extra costs were much larger than > reading the lot sequentially, and sorting 129 resulting rows. > > The first query picked a sequential scan as it thought it was only > going to get 11 results, so was expecting that the limit wasn't > going to come into play, and that every row would have to be read > anyway. > The strange thing of course is that the data is exactly the same for both runs, the tables have not been changed between runs, and I did them right after another. Even more strange is that the seq scan is faster than the index scan. Dave > Regards, > Stephen Denne. > > Disclaimer: > At the Datamail Group we value team commitment, respect, > achievement, customer focus, and courage. This email with any > attachments is confidential and may be subject to legal privilege. > If it is not intended for you please advise by reply immediately, > destroy it and do not copy, disclose or use it in any way. > > __________________________________________________________________ > This email has been scanned by the DMZGlobal Business Quality > Electronic Messaging Suite. > Please see http://www.dmzglobal.com/services/bqem.htm for details. > __________________________________________________________________ > >