От: Tim Landscheidt
Тема: Re: Analysis Function
Дата: ,
Msg-id: m3typ9hl6a.fsf@passepartout.tim-landscheidt.de
(см: обсуждение, исходный текст)
Ответ на: 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, )

David Jarvis <> wrote:

>> Have you tested DATE_TRUNC()?

> Not really; it returns a full timestamp and I would still have to
> concatenate strings. My goal is to speed up the following code (where
> *p_*parameters are user inputs):

> *        date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''')
> d1,
>         date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''')
> d2*

> Using DATE_TRUNC() won't help here, as far as I can tell. Removing the
> concatenation will halve the query's time. Such as:

> dateserial( m.taken, p_month1, p_day1 ) d1,
> dateserial( m.taken, p_month2, p_day2 ) d2

> My testing so far has shown a modest improvement by using a C function (to
> avoid concatenation).

You could use:

| (DATE_TRUNC('year', m.taken) + p_month1 * '1 month'::INTERVAL + p_day1 * '1 day'::INTERVAL)::DATE

but whether that is faster or slower I don't know. But I
don't see why this query needs to be fast in the first
place. It seems to be interactive, and therefore I wouldn't
invest too much time to have the user wait not 4.4, but
2.2 seconds. You could also do the concatenation in the ap-
plication if that is faster than PostgreSQL's date arithme-
tics.

Tim


В списке pgsql-performance по дате сообщения:

От: David Jarvis
Дата:
Сообщение: Re: Analysis Function
От: David Jarvis
Дата:
Сообщение: Re: Analysis Function