Postgres Pro
Downloads
Home   >   mailing lists

Re: Analysis Function - Mailing list pgsql-performance

From David Jarvis
Subject Re: Analysis Function
Date
Msg-id AANLkTinjUmGWmB3jsjj-6w_44c0K7f_wSrH_1TALPaOY@mail.gmail.com
Whole thread Raw
In response to Re: Analysis Function  (Andy Colson <andy@squeakycode.net>)
Responses Re: Analysis Function  (David Jarvis <thangalin@gmail.com>)
List pgsql-performance
Tree view
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

pgsql-performance by date:

Previous
From: Anj Adu
Date:
Subject: Re: slow query performance
Next
From: Amit Khandekar
Date:
Subject: Re: query hangs