Thread: TO_CHAR SO SLOW???
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?? P.S Postgres 7.3
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/
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 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/