Re: Analysis Function

[David Jarvis <thangalin@xxxxxxxxx>]

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