From: Andy Colson <andy@squeakycode.net>
To: David Jarvis <thangalin@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Analysis Function
Date: Thu, 10 Jun 2010 21:11:10 -0500
Message-ID: <4C119B3E.3090608@squeakycode.net> (raw)
In-Reply-To: <AANLkTikZKwBCR8QfGph3aH34A2dJ2NQyOjq-Pt3eZ3_z@mail.gmail.com>
References: <AANLkTikihIMleazgqo1NhLo7LA5Tiujz4TIfBL1agxAp@mail.gmail.com>
<AANLkTikZKwBCR8QfGph3aH34A2dJ2NQyOjq-Pt3eZ3_z@mail.gmail.com>
On 06/10/2010 07:41 PM, David Jarvis wrote:
> Hi,
>
> I found a slow part of the query:
>
> SELECT
> * date(extract(YEAR FROM m.taken)||'-1-1') d1,*
> * date(extract(YEAR FROM m.taken)||'-1-31') d2*
> FROM
> climate.city c,
> climate.station s,
> climate.station_category sc,
> climate.measurement m
> WHERE
> c.id <http://c.id; = 5148 AND ...
>
> Date extraction is 3.2 seconds, but without is 1.5 seconds. The PL/pgSQL
> code that actually runs (where p_month1, p_day1, and p_month2, p_day2
> are integers):
>
> * date(extract(YEAR FROM
> m.taken)||''-'||p_month1||'-'||p_day1||''') d1,
> date(extract(YEAR FROM
> m.taken)||''-'||p_month2||'-'||p_day2||''') d2
> *
> What is a better way to create those dates (without string
> concatenation, I presume)?
>
> Dave
>
I assume you are doing this in a loop? Many Many Many times? cuz:
andy=# select date(extract(year from current_date) || '-1-1');
date
------------
2010-01-01
(1 row)
Time: 0.528 ms
Its pretty quick. You say "without" its 1.5 seconds? Thats all you change? Can we see the sql and 'explain analyze' for both?
-Andy
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: andy@squeakycode.net, thangalin@gmail.com
Subject: Re: Analysis Function
In-Reply-To: <4C119B3E.3090608@squeakycode.net>
* 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