DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=HPD30fsaGgjG9GILDhhBh3LLBZe3txN7SrdN1LWrGEMEQe7IVLhwJNTXt2HsgRUQVy
	vn5P0PLGXK3VAyB/nfvFlGWetVQUsd3HPA14jPetFDCnPm+9E9P+Nf+8X2gmZNJe//Ui
	AOTOYJSKHUkNf3NmboIB90AJpDidUdG9VIzsY=
Message-ID: <4BF5A4C5.5090505@gmail.com>
Date: Thu, 20 May 2010 23:08:21 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: David Jarvis <thangalin@gmail.com>, Thom Brown <thombrown@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or
 GIN?
References: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
	<AANLkTimZ9zIEebspkg8Iy8vhEeBhe-JLJeJiYPcW88sJ@mail.gmail.com>
	<AANLkTinTEyf_9ooDHv12vfsIuLihB2VqJ6NZGD2DnyHf@mail.gmail.com>
	<AANLkTimk7lRS8FHJOot3tF-c6Ds4jexCvWKEQNU1V0Xt@mail.gmail.com>
	<7143.1274382139@sss.pgh.pa.us>
	<AANLkTim7O3Aalp31WKwBuTufYXeUMQWkwy51nvRcI0QY@mail.gmail.com>
	<AANLkTikl8wlHPJaArMWO9XWgG0vk9tununw3eUAYh1IC@mail.gmail.com>
	<7767.1274385170@sss.pgh.pa.us>
	<AANLkTikOEC-Gr3eqGlyGauRAZJknQEaS5DEWw-ZOcl6r@mail.gmail.com>
	<7917.1274385834@sss.pgh.pa.us>
	<AANLkTimLdBWaVpSF5Sc3ArT_9jcAjhNiB9WBBA4HIyXj@mail.gmail.com>
	<8132.1274386738@sss.pgh.pa.us>
In-Reply-To: <8132.1274386738@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> David Jarvis <thangalin@gmail.com> writes:
>   
>> I was hoping to eliminate this part of the query:
>>         (cast(extract( YEAR FROM m.taken ) + greatest( -1 *
>>           sign(
>>             (extract( YEAR FROM m.taken )||'-12-31')::date -
>>             (extract( YEAR FROM m.taken )||'-01-01')::date ), 0
>>         ) AS text)||'-12-31')::date
>>     
>> That uses functions to create the dates, which is definitely the problem.
>>     
>
> Well, it's not the functions per se that's the problem, it's the lack of
> a useful index on the expression.  But as somebody remarked upthread,
> that expression doesn't look correct at all.  Doesn't the whole
> greatest() subexpression reduce to a constant?
>   
That somebody was probably me. I still think the whole BETWEEN 
expression is a tautology. A small test did not provide a 
counterexample. In the select below everything but the select was 
copy/pasted.

create table m (taken timestamptz);
insert into m values (now());
insert into m values ('1900-12-31');
insert into m values ('2000-04-06');
select m.taken BETWEEN
        /* Start date. */
      (extract( YEAR FROM m.taken )||'-01-01')::date AND
        /* End date. Calculated by checking to see if the end date wraps
          into the next year. If it does, then add 1 to the current year.
        */
        (cast(extract( YEAR FROM m.taken ) + greatest( -1 *
          sign(
            (extract( YEAR FROM m.taken )||'-12-31')::date -
            (extract( YEAR FROM m.taken )||'-01-01')::date ), 0
        ) AS text)||'-12-31')::date from m;
  ?column?
----------
 t
 t
 t
(3 rows)

Another thing is that IF the climate measurements is partitioned on time 
(e.g each year?), then a function based index on the year part of 
m.taken is useless, pardon my french. I'm not sure if it is partitioned 
that way but it is an interesting thing to inspect, and perhaps rewrite 
the query to use constraint exclusion.

regards,
Yeb Havinga