Postgres Pro
Downloads
Home   >   mailing lists

Re: Analysis Function - Mailing list pgsql-performance

From David Jarvis
Subject Re: Analysis Function
Date
Msg-id AANLkTikZKwBCR8QfGph3aH34A2dJ2NQyOjq-Pt3eZ3_z@mail.gmail.com
Whole thread Raw
Responses Re: Analysis Function
List pgsql-performance
Tree view
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 = 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

pgsql-performance by date:

Previous
From: Anne Rosset
Date:
Subject: Re: Need to increase performance of a query
Next
From: Robert Haas
Date:
Subject: Re: slow query performance