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: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: 2010-05-21 15:08:38
Message-ID: alpine.DEB.2.00.1005211100240.11385@aragorn.flymine.org (view raw or flat)
Thread:
Lists: pgsql-performance
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.

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.

In this case, partitioning is only really useful when you are going to be 
forced to do seq scans. If you can get a suitably selective index, in the 
case where you are selecting a small proportion of the data, then I would 
concentrate on getting the index right, rather than the partition, and 
maybe even not do partitioning.

Matthew

-- 
 Trying to write a program that can't be written is... well, it can be an
 enormous amount of fun!                 -- Computer Science Lecturer

In response to

Responses

pgsql-performance by date

Next:From: David JarvisDate: 2010-05-21 15:17:57
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Previous:From: Tyler HildebrandtDate: 2010-05-21 13:54:15
Subject: Query timing increased from 3s to 55s when used as functioninstead of select

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