Postgres Professional
en

Re: Analysis Function

От: Andy Colson
Тема: Re: Analysis Function
Дата: ,
Msg-id: 4C119B3E.3090608@squeakycode.net
(см: обсуждение, исходный текст)
Ответ на: Re: Analysis Function  (David Jarvis)
Ответы: Re: Analysis Function  (David Jarvis)
Список: pgsql-performance

Скрыть дерево обсуждения

Re: Analysis Function  (David Jarvis, )
 Re: Analysis Function  (Andy Colson, )
  Re: Analysis Function  (David Jarvis, )
   Re: Analysis Function  (David Jarvis, )
    Re: Analysis Function  (Heikki Linnakangas, )
     Query about index usage  (Jayadevan M, )
      Re: Query about index usage  ("Kevin Grittner", )
       Re: Query about index usage  (Jayadevan M, )
      Re: Query about index usage  (Greg Smith, )
      Re: Query about index usage  (Bob Lunney, )
 Re: Analysis Function  (Tim Landscheidt, )
  Re: Analysis Function  (David Jarvis, )
 Re: Analysis Function  (Tim Landscheidt, )
  Re: Analysis Function  (David Jarvis, )
   Re: Analysis Function  (David Jarvis, )
    Re: Analysis Function  (Tom Lane, )
     Re: Analysis Function  (David Jarvis, )
      Re: Analysis Function  (David Jarvis, )
       Re: Analysis Function  (Heikki Linnakangas, )
        Re: Analysis Function  (David Jarvis, )
         Re: Analysis Function  (Magnus Hagander, )
          Re: Analysis Function  (Tom Lane, )
           Re: Analysis Function  (Magnus Hagander, )
            Re: Analysis Function  (Tom Lane, )
             Re: Analysis Function  (Magnus Hagander, )
        Re: Analysis Function  (Tom Lane, )
         Re: Analysis Function  (David Jarvis, )
          Re: Analysis Function  (Magnus Hagander, )
           Re: Analysis Function  (David Jarvis, )
           Re: Analysis Function  (Tom Lane, )
            Re: Analysis Function  (Magnus Hagander, )
             Re: Analysis Function  (David Jarvis, )
              Re: Analysis Function  (Tom Lane, )
               Re: Analysis Function  (Bruce Momjian, )
 Re: Analysis Function  (Tim Landscheidt, )
 Re: Query about index usage  (Jayadevan M, )
  Re: Query about index usage  (Greg Smith, )
   Re: Query about index usage  (Jayadevan M, ) 
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 по дате сообщения:

Предыдущее:
От: Bob Lunney
Дата:
Сообщение: Re: requested shared memory size overflows size_t
Следующее:
От: David Jarvis
Дата:
Сообщение: Re: Analysis Function