Re: TO_CHAR SO SLOW??? - Mailing list pgsql-hackers

From Karel Zak
Subject Re: TO_CHAR SO SLOW???
Date
Msg-id 20030624080412.GA16307@zf.jcu.cz
Whole thread Raw
In response to TO_CHAR SO SLOW???  ("Maksim Likharev" <mlikharev@aurigin.com>)
List pgsql-hackers
On Mon, Jun 23, 2003 at 06:08:19PM -0700, Maksim Likharev wrote:
> Hi,
> I have some SQL function, just regular function selects data by using 4
> joins nothing fancy,
> but one thing pretty noticeable,
> I have to display 3 different columns with same date formatted
> differently,
> here are 3 different snippets:
> 
> 1. SELECT t.x,t.y,TO_CHAR(t.dt, 'DD/MM/YYYY')    
>     FROM ( SELECT x, y, dt FROM .... ) AS t
>         ...
> 2. SELECT t.x,t.y,TO_CHAR(t.dt, 'DD/MM/YYYY'), TO_CHAR(t.dt, 'Mon-YYYY')
>     FROM ( SELECT x, y, dt FROM .... ) AS t
>     ..
> 3. SELECT t.x,t.y,TO_CHAR(t.dt, 'DD/MM/YYYY'), TO_CHAR(t.dt,
> 'Mon-YYYY'), TO_CHAR(t.dt, 'YYYY')
>     FROM ( SELECT x, y, dt FROM .... ) AS t
>         ...
> 
> # 1: 15000 rows, I getting data for  130 sec
> # 2: 15000 rows, I getting data for  160 sec
> # 3: 15000 rows, I getting data for  220 sec
> 
> adding different fields into output change query time only marginally
> but adding or removing to_char, 
> just heavily knocks performance.
> 
> is it TO_CHAR so slow??
I don't think to_char() is so slow. What happen with performanceif you use t.dt without formatting or if try some other
functionanexample extract()?SELECT t.x, t.y, t.dt FROM ( SELECT x, y, dt FROM .... ) AS t;SELECT t.x, t.y, EXTRACT(year
fromt.dt) FROM ( SELECT x, y, dt FROM .... ) AS t;   Karel
 

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/


pgsql-hackers by date:

Previous
From: Tatsuo Ishii
Date:
Subject: Re: again: Bug #943: Server-Encoding from EUC_TW toUTF-8
Next
From: "Dave Page"
Date:
Subject: Re: ftp mirror