David Jarvis <thangalin@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
