X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 2DCC9CC764 for ; Thu, 17 Jul 2003 12:34:21 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 20881-02 for ; Thu, 17 Jul 2003 12:34:45 +0000 (GMT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id F0911CADB5 for ; Thu, 17 Jul 2003 09:34:18 -0300 (ADT) Received: from tmsl-adsl.demon.co.uk ([80.177.114.181] helo=bacon.tmsl.demon.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 19d7yB-00050B-0V; Thu, 17 Jul 2003 13:34:48 +0100 Date: Thu, 17 Jul 2003 13:34:45 +0100 From: Paul Thomas To: Fabian Kreitner Cc: "pgsql-performance @ postgresql . org" Subject: Re: index / sequential scan problem Message-ID: <20030717133445.A27991@bacon> References: <5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245> <5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245> <20030717111248.C27264@bacon> <5.1.0.14.0.20030717131157.03957fa0@195.145.148.245> Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset=ISO-8859-1 In-Reply-To: <5.1.0.14.0.20030717131157.03957fa0@195.145.148.245>; from fabian.kreitner@ainea-ag.de on Thu, Jul 17, 2003 at 12:13:06 +0100 X-Mailer: Balsa 1.2.3 Lines: 31 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/250 X-Sequence-Number: 2543 On 17/07/2003 12:13 Fabian Kreitner wrote: > That is what I read too and is why Im confused that the index is indeed > executing faster. Can this be a problem with the hardware and/or > postgress installation? It's more likely that the OS has most of the data cached after the first query and so doesn't need to re-read that data from disk when you retry the query with seq scan disabled. Try something like this: set enable_seqscan to true; explain analyze ...... set enable_seqscan to false; explain analyze ...... set enable_seqscan to true; explain analyze ...... I expect you will find that the third query is also a lot faster that the first query. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+