Re: [PERFORM] pgPool query cache

2008-05-04 Thread Hannes Dorbath

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"

2008-05-04 Thread Hannes Dorbath
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"

2008-05-04 Thread Joshua D. Drake

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"

2008-05-04 Thread Hannes Dorbath

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"

2008-05-04 Thread Zoltan Boszormenyi

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"

2008-05-04 Thread Shane Ambler

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