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
