Re: WIP: Make timestamptz_out less slow.

On 28 July 2015 at 19:10, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2015-07-28 10:59:15 +1200, David Rowley wrote:
> > It won't be quite as fast as what you've written, but I think it will be
> > much neater and more likely to be used in other places if we invent a
> > function like pg_ltoa() which returns a pointer to the new end of string.
> >
> > Also if we're specifying padding with zeros then we can skip the reverse
> > part that's in pg_ltoa(), (normally needed since the numeric string is
> > build in reverse)
> >
> > The code could then be written as:
> >
> > str = pg_int2str_pad(str, year, 4);
> > *str++ = '-';
> > str = pg_int2str_pad(str, tm->tm_mon, 2);
> > *str++ = '-';
> > str = pg_int2str_pad(str, tm->tm_mday, 2);
> >
> > etc
> >
> > I've used this method before and found it to be about 10 times faster
> than
> > snprintf(), but I was reversing the string, so quite likely it be more
> than
> > 10 times.
>
> Yes, that might be worthwhile to try. Certainly would look less
> ugly. Willing to give it a try?
>
>
I had a quick try at this and ended up just writing a small test program to
see what's faster.

Please excuse the mess of the file, I just hacked it together as quickly as
I could with the sole intention of just to get an idea of which is faster
and by how much.

For me the output is as follows:

timestamp_out() = 2015-07-29 02:24:33.34 in 3.506000
timestamp_out_old() = 2015-07-29 02:24:33.034 in 64.518000
timestamp_out_af() = 2015-07-29 02:24:33.034 in 2.981000

timestamp_out_old is master's version, the timestamp_out_af() is yours, and
timestamp_out() is my one. times are in seconds to perform 100 million
calls.

So it appears your version is a bit faster than mine, but we're both about
20 times faster than the current one.
Also mine needs fixed up as the fractional part is not padded the same as
yours, but I doubt that'll affect the performance by much.

My view: It's probably not worth going quite as far as you've gone for a
handful of nanoseconds per call, but perhaps something along the lines of
mine can be fixed up.

Have you thought about what to do when HAVE_INT64_TIMESTAMP is not defined?

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services