Re: [HACKERS] memory dilemma

On Mon, 27 Dec 1999, Tom Lane wrote:

> That isn't necessarily an impressive demonstration --- what is the data
> type of your "d" column?  Four of the six variants of date_part() are
> implemented as SQL functions, which naturally adds a lot of overhead...


Sorry. I better describe problem now. 
The test-table 'tab':
CRAETE TABLE tab (d datetime);
The 'tab' contain _random_ datetime values (generate via my program
rand_datetime - it is in PG's contrib/dateformat/test). In this table 
is 10000 rows.

Test:

time psql test -c "select d from tab;" -o /dev/null

real    0m0.530s
user    0m0.060s
sys     0m0.020s

time psql test -c "select date_part('second', d) from tab;" -o /dev/null

real    0m0.494s
user    0m0.060s
sys     0m0.030s

time psql test -c "select to_char(d, 'SS') from tab;" -o /dev/null

real    0m0.368s
user    0m0.080s
sys     0m0.000s

(to_char() is a little slowly now (than in previous test), because I rewrite 
any parts)     
This comparison is *not* show cache effect. This test show (probably) better
searching and datetime part extraction in to_char().


Cache has effect for long and complicated 'format-picture' in to_char().

With cache (Cache has implement via malloc/free.) :
~~~~~~~~~~
time psql test -c "select to_char(d, 'HH12:MI:SS YYYY FMMonth Day') from
tab;" -o /dev/null

real    0m0.545s
user    0m0.060s
sys     0m0.010s

Without cache:
~~~~~~~~~~~~~
time psql test -c "select to_char(d, 'HH12:MI:SS YYYY FMMonth Day') from
tab;" -o /dev/null

real    0m0.638s
user    0m0.060s
sys     0m0.010s 

Hmm.. my internal to_char() parser is very fast (0.100s for 10000 
calls only) :-))


Thank for all suggestion. I finaly use in to_char() cache via static buffer,
and if format-picture will bigger than this buffer, to_char will work as
without cache. This solution eliminate memory leak - this solution is used
in current datetime routines. It is good compromise.

I plan in future make small changes in datetime routines. The to_char is
probably fastly, because it use better search algorithm (has a simple index 
for scanned array). The date_part() will fast too :-)

-
A last (PG's novice) question - how problem appear if PG is compilate with 
(gcc) -O3 optimalization? Or why is not used in PG 'inline' function
declaration?  
                        Karel