Re: Analysis Function

Поиск
Список
Период
Сортировка
От David Jarvis
Тема Re: Analysis Function
Дата
Msg-id AANLkTinjUmGWmB3jsjj-6w_44c0K7f_wSrH_1TALPaOY@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Analysis Function  (Andy Colson <andy@squeakycode.net>)
Ответы Re: Analysis Function  (David Jarvis <thangalin@gmail.com>)
Список pgsql-performance
Дерево обсуждения
Hi, Andy.

I assume you are doing this in a loop?  Many Many Many times?  cuz:

Yes. Here are the variations I have benchmarked (times are best of three):

Variation #0
-no date field-
Explain: http://explain.depesz.com/s/Y9R
Time: 2.2s

Variation #1
date('1960-1-1')
Explain: http://explain.depesz.com/s/DW2
Time: 2.6s

Variation #2
date('1960'||'-1-1')
Explain: http://explain.depesz.com/s/YuX
Time: 3.1s

Variation #3
date(extract(YEAR FROM m.taken)||'-1-1')
Explain: http://explain.depesz.com/s/1I
Time: 4.3s

Variation #4
to_date( date_part('YEAR', m.taken)::text, 'YYYY' ) + interval '0 months' + interval '0 days'
Explain: http://explain.depesz.com/s/fIT
Time: 4.4s

What I would like is along Variation #5:

PGTYPESdate_mdyjul(taken_year, p_month1, p_day1)
Time: 2.3s

I find it interesting that variation #2 is half a second slower than variation #1.

The other question I have is: why does PG seem to discard the results? In pgAdmin3, I can keep pressing F5 and (before 8.4.4?) the results came back in 4s for the first response then 1s in subsequent responses.

Dave

В списке pgsql-performance по дате отправления:

Предыдущее
От: Anj Adu
Дата:
Сообщение: Re: slow query performance
Следующее
От: Amit Khandekar
Дата:
Сообщение: Re: query hangs