From: Tim Landscheidt <tim@tim-landscheidt.de>
To: pgsql-performance@postgresql.org
Subject: Re: Analysis Function
Date: Fri, 11 Jun 2010 11:18:37 +0000
Message-ID: <m3typ9hl6a.fsf@passepartout.tim-landscheidt.de> (raw)
References: <AANLkTikihIMleazgqo1NhLo7LA5Tiujz4TIfBL1agxAp@mail.gmail.com>
<AANLkTikZKwBCR8QfGph3aH34A2dJ2NQyOjq-Pt3eZ3_z@mail.gmail.com>
<4C119B3E.3090608@squeakycode.net>
<AANLkTinjUmGWmB3jsjj-6w_44c0K7f_wSrH_1TALPaOY@mail.gmail.com>
<m34ohahrp4.fsf@passepartout.tim-landscheidt.de>
<AANLkTik3l6uedzVJLqn-nueNva46NAFwxbpSe1XSc26_@mail.gmail.com>
David Jarvis <thangalin@gmail.com> wrote:
>> 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).
You could use:
| (DATE_TRUNC('year', m.taken) + p_month1 * '1 month'::INTERVAL + p_day1 * '1 day'::INTERVAL)::DATE
but whether that is faster or slower I don't know. But I
don't see why this query needs to be fast in the first
place. It seems to be interactive, and therefore I wouldn't
invest too much time to have the user wait not 4.4, but
2.2 seconds. You could also do the concatenation in the ap-
plication if that is faster than PostgreSQL's date arithme-
tics.
Tim
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tim@tim-landscheidt.de
Subject: Re: Analysis Function
In-Reply-To: <m3typ9hl6a.fsf@passepartout.tim-landscheidt.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox