Re: WIP: Make timestamptz_out less slow.

On 15 September 2015 at 05:52, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Jim Nasby wrote:
> > On 9/13/15 2:43 AM, David Rowley wrote:
> > >Are you worried about this because I've not focused on optimising float
> > >timestamps as much as int64 timestamps? Are there many people compiling
> > >with float timestamps in the real world?
> >
> > My $0.02: the default was changed some 5 years ago so FP time is probably
> > pretty rare now.
>
> The default was FP for 8.3 and was changed before 8.4. Anybody who was
> running with the default back then and who has pg_upgraded all the way
> to current releases is still using floating-point date/times.
>
> > I don't think it's worth a bunch of extra work to speed them up.
>
> Not sure about that.
>
>
It's not like nothing is improved in float timestamps with this patch, it's
only appending the non-zero fractional seconds that I've left alone. Every
other portion of the timestamp has been improved.

I made a quick test as a demo. This is compiled with float timestamps.

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;
create table ts2 (ts timestamp not null);
insert into ts2 select generate_series('2010-01-01 00:00:00', '2010-01-01
00:00:31.536001', '1 microsecond'::interval);
vacuum freeze ts2;

Master:
copy ts to '/dev/null'; -- Test 1
Time: 18252.128 ms
Time: 18230.650 ms
Time: 18247.622 ms

copy ts2 to '/dev/null'; -- Test 2
Time: 30928.999 ms
Time: 30973.576 ms
Time: 30935.489 ms

Patched

copy ts to '/dev/null'; -- Test 1 (247%)
Time: 7378.243 ms
Time: 7383.486 ms
Time: 7385.675 ms

copy ts2 to '/dev/null'; -- Test 2 (142%)
Time: 21761.047 ms
Time: 21757.026 ms
Time: 21759.621 ms

The patched difference between ts and ts2 can be accounted for by the fact
that I didn't find a better way to do:

if (fillzeros)
sprintf(cp, "%0*.*f", precision + 3, precision, fabs(sec + fsec));
else
sprintf(cp, "%.*f", precision, fabs(sec + fsec));

A fast path exists when the fractional seconds is zero, which is why
there's such a variation between the 2 tests.

I did, however spend some time a few weekends ago writing a function to
improve this, which is more aimed at improving poor performance of
float4out and float8out. It's quite likely if I can get finished one day,
it can help improve this case too.

Regards

David Rowley

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