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