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=NQAd/v8FxogmZB4Ian91z0eHF1hQH97scGZEfyw1qiD06tfY1Tg9M9553OAK7BoVNK
	lJgDDRtdNNxJMMlZdLvKFK+mTcJH9jn0vxlO2SDdp8g6rjQ++YtsO2vDLmaqGUdlPQK1
	0WluWqaQKhyuFdSUJgyzhScveotAwPL92HvGo=
Message-ID: <4BF6CF21.1030808@gmail.com>
Date: Fri, 21 May 2010 20:21:21 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: Matthew Wakeling <matthew@flymine.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or
 GIN?
References: <AANLkTimk7lRS8FHJOot3tF-c6Ds4jexCvWKEQNU1V0Xt@mail.gmail.com>
	<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>
	<AANLkTikK_xUrI7iOU9CzpYAKbtxZz-chc3SaHGhTEGXP@mail.gmail.com>
	<20100520210106.GO21875@tamriel.snowman.net>
	<AANLkTinAs8sWmMjEynf1K0DNsmaIiqRRNqVR5tNlkbuC@mail.gmail.com>
	<4BF64693.8050800@gmail.com>
	<alpine.DEB.2.00.1005211100240.11385@aragorn.flymine.org>
In-Reply-To: <alpine.DEB.2.00.1005211100240.11385@aragorn.flymine.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Matthew Wakeling wrote:
> On Fri, 21 May 2010, Yeb Havinga wrote:
>> For time based data I would for sure go for year based indexing.
>
> On the contrary, most of the queries seem to be over many years, but 
> rather restricting on the time of year. Therefore, partitioning by 
> month or some other per-year method would seem sensible.
The fact is that at the time I wrote my mail, I had not read a specifion 
of distribution of parameters (or I missed it). That's why the sentence 
of my mail before the one you quoted said: "the partitioning is only 
useful for speed, if it matches how your queries select data.". In most 
of the databases I've worked with, the recent data was queried most 
(accounting, medical) but I can see that for climate analysis this might 
be different.
> Regarding the leap year problem, you might consider creating a 
> modified day of year field, which always assumes that the year 
> contains a leap day. Then a given number always resolves to a given 
> date, regardless of year. If you then partition (or index) on that 
> field, then you may get a benefit.
Shouldn't it be just the other way around - assume all years are non 
leap years for the doy part field to be indexed.

regards,
Yeb Havinga