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:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 1:04 AM To: Maksim Likharev Cc: [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
