Skip site navigation (1) Skip section navigation (2)

Re: Optimize date query for large child tables: GiST or GIN?

From: Matthew Wakeling <matthew(at)flymine(dot)org>
To: Yeb Havinga <yebhavinga(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-21 19:12:12
Message-ID: alpine.DEB.2.00.1005211508290.30010@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
>> 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.
On Fri, 21 May 2010, Yeb Havinga wrote:
> Shouldn't it be just the other way around - assume all years are non leap 
> years for the doy part field to be indexed.

The mapping doesn't matter massively, as long as all days of the year can 
be mapped uniquely onto a number, and the numbers are sequential. Your 
suggestion does not satisfy the first of those two requirements.

If you assume that all yeasr are leap years, then you merely skip a number 
in the middle of the year, which isn't a problem when you want to check 
for days between two bounds. However, if you assume non leap year, then 
there is no representation for the 29th of February, so not all data 
points will have a representative number to insert into the database.

Matthew

-- 
 No, C++ isn't equal to D. 'C' is undeclared, so we assume it's an int,
 with a default value of zero.  Hence, C++ should really be called 1.
 -- met24, commenting on the quote "C++ -- shouldn't it be called D?"

In response to

pgsql-performance by date

Next:From: Stephen FrostDate: 2010-05-21 19:17:50
Subject: Re: Optimize date query for large child tables: GiST orGIN?
Previous:From: Yeb HavingaDate: 2010-05-21 18:49:36
Subject: Re: Optimize date query for large child tables: GiST or GIN?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group