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 0D2C43A53DE for ; Wed, 1 Dec 2004 22:17:02 +0000 (GMT) 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 84205-09 for ; Wed, 1 Dec 2004 22:16:52 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 82EB73A54DB for ; Wed, 1 Dec 2004 22:16:52 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iB1MGrCh019908; Wed, 1 Dec 2004 17:16:53 -0500 (EST) To: Brian Hirt Cc: "'Postgresql Performance'" Subject: Re: query with timestamp not using index In-reply-to: <9CFDF39A-43E5-11D9-AD0F-000D93AD2E74@mobygames.com> References: <95241C28-43B8-11D9-AD0F-000D93AD2E74@mobygames.com> <18427.1101931618@sss.pgh.pa.us> <9CFDF39A-43E5-11D9-AD0F-000D93AD2E74@mobygames.com> Comments: In-reply-to Brian Hirt message dated "Wed, 01 Dec 2004 15:09:04 -0700" Date: Wed, 01 Dec 2004 17:16:53 -0500 Message-ID: <19907.1101939413@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200412/22 X-Sequence-Number: 9397 Brian Hirt writes: > it's more like: > declare > foo_date date; > begin > select some_date into foo_date from some_table where something = something_else; > select blah from redir_log where redir_timestamp >= foo_date::timestamp without time zone at time zone 'GMT'; > etc / etc / etc Ah. In that case you're going to have trouble anyway with the planner having no clue what the effective value of the comparison expression is, because it'll certainly not be able to fold the plpgsql variable to a constant. I agree with the other person who suggested faking it out by adding a dummy other-side-of-the-range constraint, perhaps AND redir_timestamp <= now() (or whatever upper bound is okay for your purposes). This should coax it into using an indexscan. regards, tom lane