TO_CHAR SO SLOW???

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 <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

Yes it is TO_CHAR,
look like that OS ( SUN ) related issue, I assume PG uses some of the
lib functions.
Looks like nonsense for me, what is damn difficult in that ( formating
dates ).
going to try date_part, might help me.

Too bad EXPLAIN does not provide statistic of time that spent inside a
function call, would be much helpful in such case.
In comparison with Microsoft SQL, productivity of using
profiling/debugging tools sorry to say that, far behind.

-----Original Message-----
From: Karel Zak [mailto:zakkr@zf.jcu.cz]
Sent: Tuesday, June 24, 2003 1:04 AM
To: Maksim Likharev
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] TO_CHAR SO SLOW???

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 <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/