X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7F879D1B1EC for ; Mon, 5 Jul 2004 08:41:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 52753-04 for ; Mon, 5 Jul 2004 11:41:22 +0000 (GMT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id 660F7D1B1D9 for ; Mon, 5 Jul 2004 08:41:18 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1) id 1BhRqY-000OAX-0Z; Mon, 05 Jul 2004 12:41:19 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 7C49717943; Mon, 5 Jul 2004 12:41:15 +0100 (BST) Message-ID: <40E93E5C.1000907@archonet.com> Date: Mon, 05 Jul 2004 12:41:16 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: eleven@ludojad.itpp.pl Cc: pgsql-performance@postgresql.org Subject: Re: Seq scan vs. Index scan with different query conditions References: <20040705101515.GA12879@ludojad.itpp.pl> In-Reply-To: <20040705101515.GA12879@ludojad.itpp.pl> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/10 X-Sequence-Number: 7401 eleven@ludojad.itpp.pl wrote: > -> Index Scan using "DateTimeIndex" on "tablex" (cost=0.00..298272.66 rows=89903 width=8) > -> Seq Scan on "tablex" (cost=0.00..307137.34 rows=97900 width=8) > Basically, the difference is in upper "Time" value (as you can see, it's > 18:01:00 in the first query and 19:01:00 in the other one). > The question is - why does it use index in first case and > it tries to do full sequential scan when the upper "Time" value > is different? Look at the rows, and more importantly the cost. PG thinks the cost in the second case (seq scan) is only slightly more than in the first case (index), so presumably the index scan worked out more expensive. You can test this by issuing "SET ENABLE_SEQSCAN=OFF;" and re-running the second explain. Now, the question is whether PG is right in these cost estimates. You'll need to run "EXPLAIN ANALYSE" rather than just EXPLAIN to see what it actually costs. PS - all the usual questions: make sure you've vacuumed, have you read the tuning document on varlena.com? -- Richard Huxton Archonet Ltd