Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 65E916333C6
	for <pgsql-bugs-postgresql.org@mail.postgresql.org>;
	Wed, 20 Jan 2010 13:06:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 49861-10 for
	<pgsql-bugs-postgresql.org@mail.postgresql.org>;
	Wed, 20 Jan 2010 17:06:01 +0000 (UTC)
X-Greylist: delayed 00:09:44.413917 by SQLgrey-1.7.6
Received: from chocolate.ruo3.org (87-194-171-29.bethere.co.uk
 [87.194.171.29])
	by mail.postgresql.org (Postfix) with ESMTP id ABF9C6320CC
	for <pgsql-bugs@postgresql.org>; Wed, 20 Jan 2010 13:06:02 -0400 (AST)
Received: from [127.0.0.1] (chocolate.ruo3.org [127.0.0.1])
	by chocolate.ruo3.org (Postfix) with ESMTP id 660096008E;
	Wed, 20 Jan 2010 16:56:16 +0000 (GMT)
Message-ID: <4B5735B0.7050106@cam.ac.uk>
Date: Wed, 20 Jan 2010 16:56:16 +0000
From: Richard Neill <rn214@cam.ac.uk>
Organization: RUO3.org
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-bugs@postgresql.org
Subject: Re: BUG #5293: constant function (date_trunc) is	 repeatedly
	evaluated inside loop
References: <201001201423.o0KENXDR099157@wwwmaster.postgresql.org>
	<4B56CA85020000250002E85A@gw.wicourts.gov>
In-Reply-To: <4B56CA85020000250002E85A@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.843 tagged_above=-10 required=5 tests=AWL=-0.944,
	BAYES_50=0.001, RDNS_DYNAMIC=0.1
X-Spam-Level: 
X-Archive-Number: 201001/195
X-Sequence-Number: 25699



Kevin Grittner wrote:
> "Richard Neill" <rn214@cam.ac.uk> wrote:
>  
>> date_trunc('day', timestamp '2010-01-20 10:16:55')
>  
> What happens with a "timestamp with time zone" literal?
>  
> -Kevin
> 


Good call!

This query is fast:

SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= 
date_trunc('day', timestamp with time zone '2010-01-20 10:16:55') AND 
srep_timestamp <  date_trunc('day', timestamp with time zone '2010-01-20 
10:16:55') + INTERVAL '24 hour' )) ;


In other words:

#fast
WHERE column <  '2010-010-20 00:00:00'

#fast
WHERE column <  date_trunc('day', timestamp with time zone
                              '2010-01-20 10:16:55')

#slow
WHERE column <  date_trunc('day', timestamp
                              '2010-01-20 10:16:55')


Why is that, I wonder?

Richard