From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thom Brown <thombrown@gmail.com>
Cc: David Jarvis <thangalin@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimize date query for large child tables: GiST or GIN?
Date: Thu, 20 May 2010 15:02:19 -0400
Message-ID: <7143.1274382139@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTimk7lRS8FHJOot3tF-c6Ds4jexCvWKEQNU1V0Xt@mail.gmail.com>
References: <AANLkTimi_Jh4J6bLZ8lZ0cGQ0VVG7XPCKaFN9GRHd6mP@mail.gmail.com>
<AANLkTilud7Qc25XZDziBMfzRnn8XCaNCo2jKHtW2-vdF@mail.gmail.com>
<AANLkTinmnWi8B_OQn_kQlG6t9SxZzhFmHbuKfBHhX3Vv@mail.gmail.com>
<AANLkTinD2fOrHDGEXPmEid85FzKGMKCq7raTaBoAsBdV@mail.gmail.com>
<AANLkTikKoZrb2i8XQaq-HVQ2Wxi9WXeclD3JefLiLMmk@mail.gmail.com>
<AANLkTimZ9zIEebspkg8Iy8vhEeBhe-JLJeJiYPcW88sJ@mail.gmail.com>
<AANLkTinTEyf_9ooDHv12vfsIuLihB2VqJ6NZGD2DnyHf@mail.gmail.com>
<AANLkTimk7lRS8FHJOot3tF-c6Ds4jexCvWKEQNU1V0Xt@mail.gmail.com>
Thom Brown <thombrown@gmail.com> writes:
> On 20 May 2010 17:36, David Jarvis <thangalin@gmail.com> wrote:
> Okay, get your app to convert the month-date to a day of year, so we
> have year_start, year_end, day_of_year_start, day_of_year_end
> and your where clause would something like this:
> WHERE extract(YEAR from m.taken) BETWEEN year1 and year2
> AND (
> extract(DOY from m.taken) BETWEEN day_of_year_start AND day_of_year_end
> OR (
> extract(DOY from m.taken) >= day_of_year_start OR extract(DOY from
> m.taken) <= day_of_year_end
> )
> )
extract(DOY) seems a bit problematic here, because its day numbering is
going to be different between leap years and non-leap years, and David's
problem statement doesn't allow for off-by-one errors. You could
certainly invent your own function that worked similarly but always
translated a given month/day to the same number.
The other thing that's messy here is the wraparound requirement.
Rather than trying an OR like the above (which I think doesn't quite
work anyway --- won't it select everything?), it would be better if
you can have the app distinguish wraparound from non-wraparound cases
and issue different queries in the two cases. In the non-wrap case
(start_day < end_day) it's pretty easy, just
my_doy(m.taken) BETWEEN start_val AND end_val
The easy way to handle the wrap case is
my_doy(m.taken) <= start_val OR my_doy(m.taken) >= end_val
although I can't help feeling there should be a smarter way to do
this where you can use an AND range check on some modified expression
derived from the date.
regards, tom lane
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: tgl@sss.pgh.pa.us, thombrown@gmail.com, thangalin@gmail.com
Subject: Re: Optimize date query for large child tables: GiST or GIN?
In-Reply-To: <7143.1274382139@sss.pgh.pa.us>
* 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