Re: WIP: Make timestamptz_out less slow.

On 29 July 2015 at 03:25, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2015-07-29 03:10:41 +1200, David Rowley wrote:
> > 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.
>
> That looks good.
>
> > 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.
>
> Worthwhile to finish that bit and try ;)
>
> > 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.
>
> Yes, I agreee that your's is probably going to be fast enough.
>
>
I took a bit of weekend time to finish this one off. Patch attached.

A quick test shows a pretty good performance increase:

create table ts (ts timestamp not null);
insert into ts select generate_series('2010-01-01 00:00:00', '2011-01-01
00:00:00', '1 sec'::interval);
vacuum freeze ts;

Master:
david=# copy ts to 'l:/ts.sql';
COPY 31536001
Time: 20444.468 ms

Patched:
david=# copy ts to 'l:/ts.sql';
COPY 31536001
Time: 10947.097 ms

There's probably a bit more to squeeze out of this.
1. EncodeDateTime() could return the length of the string to allow callers
to use pnstrdup() instead of pstrdup(), which would save the strlen() call.
2. Have pg_int2str_zeropad() and pg_int2str() skip appending the NUL char
and leave this up to the calling function.
3. Make something to replace the sprintf(str, " %.*s", MAXTZLEN, tzn); call.

Perhaps 1 and 3 are worth looking into, but I think 2 is likely too error
prone for the small gain we'd get from it.

Also I was not too sure on if pg_int2str() was too similar to pg_ltoa().
Perhaps pg_ltoa() should just be modified to return the end of string?

Thoughts?

Regards

David Rowley

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