Re: Fastest way / best practice to calculate "next birthdays"

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