Re: [PERFORM] pgPool query cache
Rauan Maemirov wrote: I want to ask, if anyone used query_cache of pgPool. The problem is there is no detailed installation steps on how to configure it correctly. Itried to follow it, but guess that it doesn't cache my queries. So, maybe someone adviced me or give link. Nobody use?? I'd say caching is better done on a higher level -- like HTML fragments or what ever you generate from those queries.. Just my two cent. -- Best regards, Hannes Dorbath -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Fastest way / best practice to calculate "next birthdays"
The subject basically says it all, I'm looking for the fastest (indexable) way to calculate the next birthdays relative to NOW() from a dataset of about 1 million users. I'm currently using a function based index, but leap year handling / mapping February 29 to February 28 gives me some headaches. Is there any best practice to do that in PostgreSQL? -- Best regards, Hannes Dorbath -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fastest way / best practice to calculate "next birthdays"
Hannes Dorbath wrote: The subject basically says it all, I'm looking for the fastest (indexable) way to calculate the next birthdays relative to NOW() from a dataset of about 1 million users. I'm currently using a function based index, but leap year handling / mapping February 29 to February 28 gives me some headaches. Is there any best practice to do that in PostgreSQL? postgres=# SELECT current_date|| ' a ' || to_char(current_date, 'Day'), current_date + '1 Year'::interval || ' a ' || to_char(current_date + '1 Year'::interval, 'Day') as next_birthday; ?column?| next_birthday +- 2008-05-04 a Sunday| 2009-05-04 00:00:00 a Monday ? Sincerely, Joshua D. Drake -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fastest way / best practice to calculate "next birthdays"
Joshua D. Drake wrote: postgres=# SELECT current_date|| ' a ' || to_char(current_date, 'Day'), current_date + '1 Year'::interval || ' a ' || to_char(current_date + '1 Year'::interval, 'Day') as next_birthday; ?column?| next_birthday +- 2008-05-04 a Sunday| 2009-05-04 00:00:00 a Monday ? 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/ -- Best regards, Hannes Dorbath -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Fastest way / best practice to calculate "next birthdays"
Hannes Dorbath írta: Joshua D. Drake wrote: postgres=# SELECT current_date|| ' a ' || to_char(current_date, 'Day'), current_date + '1 Year'::interval || ' a ' || to_char(current_date + '1 Year'::interval, 'Day') as next_birthday; ?column?| next_birthday +- 2008-05-04 a Sunday| 2009-05-04 00:00:00 a Monday ? 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/ If you define the same functional index as in the above link: CREATE OR REPLACE FUNCTION indexable_month_day(date) RETURNS TEXT as $BODY$ SELECT to_char($1, 'MM-DD'); $BODY$ language 'sql' IMMUTABLE STRICT; create table user_birthdate ( id serial not null primary key, birthdate date ); create index user_birthdate_day_idx on user_birthdate ( indexable_month_day(birthdate) ); Then you can use this query: select count(*) from user_birthdate where indexable_month_day(birthdate) > '02-28' and indexable_month_day(birthdate) <= '03-01'; In a generic and parametrized way: select * from user_birthdate where indexable_month_day(birthdate) > indexable_month_day(now()::date) and indexable_month_day(birthdate) <= indexable_month_day((now() + '1 days'::interval)::date); This will still use the index and it will work for the poor ones who have birthday every 4 years, too. Assume, it's 02-08 today, 03-01 the next day. The now() < X <= now() + 1 day range will find 02-29. -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
