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