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
