X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1EB39D1B8B3 for ; Fri, 5 Sep 2003 16:40:12 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 56496-06 for ; Fri, 5 Sep 2003 13:39:40 -0300 (ADT) 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 2188CD1BA38 for ; Fri, 5 Sep 2003 13:39:35 -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 19vJcU-000JxW-0Z; Fri, 05 Sep 2003 17:39:34 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id 1721E16AC1; Fri, 5 Sep 2003 17:39:33 +0100 (BST) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 608AB16ABA; Fri, 5 Sep 2003 17:39:32 +0100 (BST) From: Richard Huxton To: rasmus@defero.se Subject: Re: Performance problems on a fairly big table with two Date: Fri, 5 Sep 2003 17:39:32 +0100 User-Agent: KMail/1.5 Cc: pgsql-performance@postgresql.org References: <1564.62.119.108.236.1062716026.squirrel@www.defero.se> <200309050934.52082.dev@archonet.com> <1081.62.119.108.236.1062776210.squirrel@www.defero.se> In-Reply-To: <1081.62.119.108.236.1062776210.squirrel@www.defero.se> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200309051739.32467.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/98 X-Sequence-Number: 3464 On Friday 05 September 2003 16:36, Rasmus Aveskogh wrote: > Richard, > > Thanks a lot! You were right - the query parser "misunderstood" > now() - '1 day'::interval and only used one of the indexes (as I already > noticed). > > Actually all I had to do was to cast the result like this: > > (now() - '1 day'::interval)::date > > 75s is not between 10ms and 200ms. > > Thanks again! Ah - good. You also want to be careful with differences between timestamp with/without time zone etc. -- Richard Huxton Archonet Ltd