X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1DD13475E77 for ; Tue, 6 May 2003 09:04:31 -0400 (EDT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by postgresql.org (Postfix) with ESMTP id C4D4F474E42 for ; Tue, 6 May 2003 09:04:26 -0400 (EDT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 19D27R-0008L9-0W; Tue, 06 May 2003 14:04:29 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 814FF168BF; Tue, 6 May 2003 14:04:28 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id D49CE168B6; Tue, 6 May 2003 14:04:27 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: Reiner Dassing , pgsql-performance@postgresql.org Subject: Re: Select on timestamp-day slower than timestamp alone Date: Tue, 6 May 2003 14:04:24 +0100 User-Agent: KMail/1.4.3 References: <3EB75D5F.5080406@wettzell.ifag.de> In-Reply-To: <3EB75D5F.5080406@wettzell.ifag.de> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200305061404.24194.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Spam-Status: No, hits=-38.9 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_KMAIL autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/60 X-Sequence-Number: 1839 On Tuesday 06 May 2003 7:59 am, Reiner Dassing wrote: > Hello all! > > On PostgreSQL V7.3.2 on TRU64 I recognized the following phenomena > that a SELECT using a difference of a timestamp and an interval > in the WHERE clause does not use the index > but using a timestamp without a difference does use the index. > The semantic of both SELECT's is equal, i.e., the result is equal. > > Therefore, the second way is much faster. > > Any ideas? > Select not using index: > ----------------------- > wetter=3D# explain select * from wetter where epoche between > '2003-05-06 06:50:54+00'::timestamp-'1 days'::interval > AND '2003-05-06 04:45:36'; > > QUERY PLAN > > -------------------------------------------------------------------------= -- >--------------------------------------------------------------------------= -- >-------------------- Seq Scan on wetter (cost=3D0.00..768644.57 rows=3D10= 253528 > width=3D16) Filter: ((epoche >=3D ('2003-05-05 06:50:54'::timestamp witho= ut > time zone)::timestamp with time zone) AND (epoche <=3D '2003-05-06 > 04:45:36+00'::timestamp with time zone)) > (2 rows) Well, the "why" is because the number of rows recommended is so big=20 (rows=3D10253528) - I'm also puzzled why we get "timestamp without time zon= e".=20 Does an explicit cast to "with time zone" help? --=20 Richard Huxton