On 06/10/2010 07:41 PM, David Jarvis wrote:
Hi,
I found a slow part of the query:
SELECT
* date(extract(YEAR FROM m.taken)||'-1-1') d1,*
* date(extract(YEAR FROM m.taken)||'-1-31') d2*
FROM
climate.city c,
climate.station s,
climate.station_category sc,
climate.measurement m
WHERE
c.id <http://c.id> = 5148 AND ...
Date extraction is 3.2 seconds, but without is 1.5 seconds. The PL/pgSQL
code that actually runs (where p_month1, p_day1, and p_month2, p_day2
are integers):
* date(extract(YEAR FROM
m.taken)||''-'||p_month1||'-'||p_day1||''') d1,
date(extract(YEAR FROM
m.taken)||''-'||p_month2||'-'||p_day2||''') d2
*
What is a better way to create those dates (without string
concatenation, I presume)?
Dave
I assume you are doing this in a loop? Many Many Many times? cuz:
andy=# select date(extract(year from current_date) || '-1-1');
date
------------
2010-01-01
(1 row)
Time: 0.528 ms
Its pretty quick. You say "without" its 1.5 seconds? Thats all you change? Can we see the sql and 'explain analyze' for both?
-Andy
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
