Message-ID: <481E9E12.8070208@Sheeky.Biz>
Date: Mon, 05 May 2008 15:11:38 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)
MIME-Version: 1.0
To: Hannes Dorbath <light@theendofthetunnel.de>
CC: "Joshua D. Drake" <jd@commandprompt.com>, pgsql-performance@postgresql.org
Subject: Re: Fastest way / best practice to calculate "next birthdays"
References: <fvka82$1b10$1@news.hub.org> <481DE036.90706@commandprompt.com>
	<481DF76D.3060807@theendofthetunnel.de>
In-Reply-To: <481DF76D.3060807@theendofthetunnel.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hannes Dorbath wrote:

> Sorry, I think I phrased the question badly. What I'm after basically is:
> 
> http://www.depesz.com/index.php/2007/10/26/who-has-birthday-tomorrow/
> 

OK So what I came up with is - (the times are from a G4 1.25Ghz)

CREATE TABLE birthdaytest
(
   id serial PRIMARY KEY,
   birthdate date
);


CREATE INDEX idx_bday_month ON birthdaytest
USING btree(extract(month from birthdate));

CREATE INDEX idx_bday_day ON birthdaytest
USING btree(extract(day from birthdate));


insert into birthdaytest (birthdate) values 
('1930-01-01'::date+generate_series(0,365*70));

... I repeated this another 15 times to load some data


vacuum analyse birthdaytest;

\timing

select count(*) from birthdaytest;

>  count  
> --------
>  408816
> (1 row)
> 
> Time: 233.501 ms


select * from birthdaytest
where extract(month from birthdate) = 5
and extract(day from birthdate) between 6 and 12;

>    id   | birthdate  
> --------+------------
>     126 | 1930-05-06
>     127 | 1930-05-07
>     128 | 1930-05-08
> ...
> ...
>  408613 | 1999-05-11
>  408614 | 1999-05-12
> (7840 rows)
> 
> Time: 211.237 ms


select * from birthdaytest
where extract(month from birthdate) = extract(month from current_date)
and extract(day from birthdate) between extract(day from current_date) 
and extract(day from current_date+14);

>    id   | birthdate  
> --------+------------
>     125 | 1930-05-05
>     126 | 1930-05-06
>     127 | 1930-05-07
> ...
> ...
>  408619 | 1999-05-17
>  408620 | 1999-05-18
>  408621 | 1999-05-19
> (16800 rows)
> 
> Time: 483.915 ms


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz