Re: [HACKERS] memory dilemma

At 02:26 PM 12/27/99 +0100, Karel Zak - Zakkr wrote:

>Sorry, but it is not good argument. If any routine (in the query path)  
>spend time is not interesting write (other) fast routine? No, we must 
>try rewrite this slowly part to faster version.
>
>*Very* simpl test over 10000 rows:
>
>$ time psql test -c  "select date_part('second', d)
>from dtest;"  -o /dev/null
>
>real    0m0.504s
>user    0m0.100s
>sys     0m0.000s
>
>$ time psql test -c  "select to_char(d, 'SI') from
>dtest;"  -o /dev/null
>
>real    0m0.288s
>user    0m0.100s
>sys     0m0.000s

This would seem to be a great argument to investigate why
date_part's so much slower.  However, it says nothing about
the times saving of caching vs. not caching.

A more interesting comparison, more germane to the point under
discussion, would be:

time psql test -c "select d from dtest;"

In other words, how much overhead does "to_char" add?  That's what
you need to look at if you want to measure whether or not caching's
worth it.  Caching the parse of the format string will save a 
percentage of the to_char overhead, but a test like the above
will at least help you get a handle on how much overhead the
format string parse adds.

>> Your caching code needs to guarantee that it can't leak memory
>> in any circumstance.  In environments where database servers
>> run 24/7 that's far more important than minor increases in
>> speed.

>Yes, I agree - robus SQL is more importent, but always say "speed is not
>interesting, we can robus only" is way to robus-snail-SQL. 

Which, of course, isn't what I said...after all, I've spent most of
my adult life writing highly optimizing compilers.  I merely asked
if a typical query wouldn't swamp any savings that caching the
parse of a format string might yield.

>I want nice-robus-fast-SQL :-)

Sure, but given the great disparity between "date_part" and your
initial "to_char" implementation, more people might see a more
significant speed-up if you spent time speeding up "date_part"...
of course, if caching greatly speeds up queries using "to_char"
then it's probably worth doing, but at least try to measure first
before adding the complication.

At least, that's how I tend to work...




- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.