Skip site navigation (1) Skip section navigation (2)

Re: Analysis Function

From: David Jarvis <thangalin(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Analysis Function
Date: 2010-06-11 09:18:08
Message-ID: AANLkTik3l6uedzVJLqn-nueNva46NAFwxbpSe1XSc26_@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi, Tim.

Have you tested DATE_TRUNC()?
>

Not really; it returns a full timestamp and I would still have to
concatenate strings. My goal is to speed up the following code (where
*p_*parameters are user inputs):

*        date(extract(YEAR FROM m.taken)||''-'||p_month1||'-'||p_day1||''')
d1,
        date(extract(YEAR FROM m.taken)||''-'||p_month2||'-'||p_day2||''')
d2*

Using DATE_TRUNC() won't help here, as far as I can tell. Removing the
concatenation will halve the query's time. Such as:

dateserial( m.taken, p_month1, p_day1 ) d1,
dateserial( m.taken, p_month2, p_day2 ) d2

My testing so far has shown a modest improvement by using a C function (to
avoid concatenation).

Dave

In response to

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2010-06-11 09:42:34
Subject: Re: Analysis Function
Previous:From: Tim LandscheidtDate: 2010-06-11 08:57:43
Subject: Re: Analysis Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group