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') d2FROM climate.city c, climate.station s, climate.station_category sc, climate.measurement mWHERE 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