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=NXTmANBZnn/6cSmY/o6Bh2rFKFKwKRKODyMogoyX+lYAQ/4b/kQclwu16L3lLJ/wrK
	wyYdD3xAp2xHZKuE+InKdhhqCTpSjpoge7OUE7r5j9NmOs65Zlqt5zILTxXA7A6Z5QPk
	hYif4CiscEFw0fPfwpuaiBsVJGytHeoHGV85k=
Message-ID: <4BF4F0DA.7060208@gmail.com>
Date: Thu, 20 May 2010 10:20:42 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: David Jarvis <thangalin@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or
 GIN?
References: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
In-Reply-To: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello David,
> The table aggregates 237 million rows from its child tables. The 
> sluggishness comes from this part of the query:
>
>       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
Either I had too less coffee and completely misunderstand this 
expression, or it is always true and can be omitted. Could you explain a 
bit what this part tries to do and maybe also show it's original 
counterpart in the source database?

regards,
Yeb Havinga