Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E3E9C63591E for ; Thu, 20 May 2010 16:02:33 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 81023-10 for ; Thu, 20 May 2010 19:02:26 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id C385C634C41 for ; Thu, 20 May 2010 16:02:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o4KJ2JP5007144; Thu, 20 May 2010 15:02:19 -0400 (EDT) To: Thom Brown cc: David Jarvis , pgsql-performance@postgresql.org Subject: Re: Optimize date query for large child tables: GiST or GIN? In-reply-to: References: Comments: In-reply-to Thom Brown message dated "Thu, 20 May 2010 19:36:36 +0100" Date: Thu, 20 May 2010 15:02:19 -0400 Message-ID: <7143.1274382139@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/117 X-Sequence-Number: 38701 Thom Brown writes: > On 20 May 2010 17:36, David Jarvis 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