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 performance if you use t.dt without formatting or if try some other function an example extract()? SELECT t.x, t.y, t.dt FROM ( SELECT x, y, dt FROM .... ) AS t; SELECT t.x, t.y, EXTRACT(year from t.dt) FROM ( SELECT x, y, dt FROM .... ) AS t; Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org