Re: Analysis Function
От | Andy Colson |
---|---|
Тема | Re: Analysis Function |
Дата | |
Msg-id | 4C119B3E.3090608@squeakycode.net обсуждение исходный текст |
Ответ на | Re: Analysis Function (David Jarvis <thangalin@gmail.com>) |
Ответы |
Re: Analysis Function
|
Список | pgsql-performance |
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' forboth? -Andy
В списке pgsql-performance по дате отправления: