From: Shane Ambler <pgsql@Sheeky.Biz>
To: Hannes Dorbath <light@theendofthetunnel.de>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Fastest way / best practice to calculate "next birthdays"
Date: Mon, 05 May 2008 15:11:38 +0930
Message-ID: <481E9E12.8070208@Sheeky.Biz> (raw)
In-Reply-To: <481DF76D.3060807@theendofthetunnel.de>
References: <fvka82$1b10$1@news.hub.org>
<481DE036.90706@commandprompt.com>
<481DF76D.3060807@theendofthetunnel.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql@Sheeky.Biz, light@theendofthetunnel.de, jd@commandprompt.com
Subject: Re: Fastest way / best practice to calculate "next birthdays"
In-Reply-To: <481E9E12.8070208@Sheeky.Biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox