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