pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Yeb Havinga <yebhavinga@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: Fri, 21 May 2010 20:21:21 +0200
Message-ID: <4BF6CF21.1030808@gmail.com> (raw)
In-Reply-To: <alpine.DEB.2.00.1005211100240.11385@aragorn.flymine.org>
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>

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




view thread (44+ messages)  latest in thread

Message-ID: <4BF6CF21.1030808@gmail.com>
Permalink:  ../4BF6CF21.1030808@gmail.com/
Also on:    postgresql.org/message-id/4BF6CF21.1030808@gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: yebhavinga@gmail.com, matthew@flymine.org
  Subject: Re: Optimize date query for large child tables: GiST or GIN?
  In-Reply-To: <4BF6CF21.1030808@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox