Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 87B962E00AD for ; Fri, 7 Mar 2008 07:22:01 -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 28130-10 for ; Fri, 7 Mar 2008 07:21:53 -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 0FD6C2E0290 for ; Fri, 7 Mar 2008 07:21:59 -0400 (AST) Received: from [192.168.1.14] (dcdsl.ebox.com [64.7.143.116]) by beaver.fastcrypt.com (Postfix) with ESMTP id 43D11158036C; Fri, 7 Mar 2008 06:27:04 -0500 (EST) Cc: "postgresql performance list" Message-Id: <1EA4326F-42CD-4112-B993-E10FFA22C6CE@fastcrypt.com> 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: Fri, 7 Mar 2008 06:21:56 -0500 References: X-Mailer: Apple Mail (2.919.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/154 X-Sequence-Number: 29108 On 6-Mar-08, at 9:30 PM, Stephen Denne wrote: >> 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. > > It is not strange at all, since both queries read ALL the rows in > your table, checking each and every row to see whether it matched > your predicates. > > The sequential scan read them in the order they are on the disk, > meaning your disk didn't have to seek as much (assuming low file > fragmentation). > > The index scan again reads all the rows in your table, but reads > them in the order they were in the index, which is probably quite > different from the order that they are on the disk, so the disk had > to seek a lot. In addition, it had to read the index. > OK, that makes sense. So given that the predicates are essentially the same why would the planner decide to use or not use the index ? > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance