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

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

From: Yeb Havinga <yebhavinga(at)gmail(dot)com>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-21 18:21:21
Message-ID: 4BF6CF21.1030808@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Yeb HavingaDate: 2010-05-21 18:49:36
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Previous:From: David JarvisDate: 2010-05-21 15:17:57
Subject: Re: Optimize date query for large child tables: GiST or GIN?

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