Re: Analysis Function

Поиск
Список
Период
Сортировка
От Tim Landscheidt
Тема Re: Analysis Function
Дата
Msg-id m34ohahrp4.fsf@passepartout.tim-landscheidt.de
обсуждение исходный текст
Ответ на Re: Analysis Function  (David Jarvis <thangalin@gmail.com>)
Ответы Re: Analysis Function  (David Jarvis <thangalin@gmail.com>)
Список pgsql-performance
David Jarvis <thangalin@gmail.com> wrote:

> [...]
> 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.
> [...]

Have you tested DATE_TRUNC()?

Tim

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

Предыдущее
От: David Jarvis
Дата:
Сообщение: Re: Analysis Function
Следующее
От: David Jarvis
Дата:
Сообщение: Re: Analysis Function