Mail-Followup-To: pgsql-performance@postgresql.org
To: pgsql-performance@postgresql.org
connect(): No such file or directory
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: Analysis Function
Date: Fri, 11 Jun 2010 08:57:43 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 43
Message-ID: <m34ohahrp4.fsf@passepartout.tim-landscheidt.de>
References: <AANLkTikihIMleazgqo1NhLo7LA5Tiujz4TIfBL1agxAp@mail.gmail.com>
	<AANLkTikZKwBCR8QfGph3aH34A2dJ2NQyOjq-Pt3eZ3_z@mail.gmail.com>
	<4C119B3E.3090608@squeakycode.net>
	<AANLkTinjUmGWmB3jsjj-6w_44c0K7f_wSrH_1TALPaOY@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: d220035.adsl.hansenet.de
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:SxHhTl/FvX2bNHAAuiNV+1yyhKw=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.79 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201006/201
X-Sequence-Number: 39057

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