Re: WIP: Make timestamptz_out less slow.

On 12 September 2015 at 04:24, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2015-09-12 04:00:26 +1200, David Rowley wrote:
> > I've not done anything yet to remove the #ifdef HAVE_INT64_TIMESTAMP from
> > AppendSeconds(). The only way I can think to handle this is to just
> > make fsec_t unconditionally an int64 (since with float datetimes the
> > precision is 10 decimal digits after the dec point, this does not fit in
> > int32). I'd go off and do this, but this means I need to write an int64
> > version of pg_ltostr(). Should I do it this way?
>
> I don't have time to look into this in detail right now. But isn't that
> the wrong view?
>
>
I understand you have a lot on. I've attempted to explain my reasoning
below.

> The precision with float timestamps is still microseconds which you can
> easily express in an integer. That the value can be higher is just
> because fsec_t for floating points also includes seconds, right? It
> shouldn't be too hard to separate those?
>
>
I don't think it's possible to claim that "The precision with float
timestamps is still microseconds". I'd say it's more limited to double
itself.

As a demo (compiled with float datetime)

postgres=# select '11:59:59.0000000002 PM'::time;
time
---------------------
23:59:59.0000000002
(1 row)

Which is 23:59:59 and 0.0002 microseconds, or 0.2 nano seconds, or 200 pico
seconds.

This formatted this way by AppendSeconds as MAX_TIME_PRECISION is defined
as 10 when compiled with HAVE_INT64_TIMESTAMP undefined.

On the other end of the spectrum:

postgres=# select '999999-01-01 11:59:59.0005'::timestamp;
timestamp
-----------------------
999999-01-01 11:59:59
(1 row)

There was no precision left for the fractional seconds here. So I'd say
claims of microsecond precision to be incorrect in many regards.

I could change AppendSeconds() to only format to 6 decimal places, but that
would be a behavioral change that I'm certainly not going to argue for.
Quite possibly this extra precision with the time type is about the only
reason to bother keeping the float time code at all, else what's it good
for?

There's just not enough bits in an int32 to do 0.99 * 10000000000.0. Which
is why I asked about int64. It all just seems more hassle than it's worth
to get rid of a small special case in AppendSeconds()

The other problem with changing timestamp2tm() to output microseconds is
that this would require changes in all functions which call timestamp2tm(),
and likely many functions which call those. Many of these would break 3rd
party code. I found myself in contrib code when looking at changing it.
(Specifically a DecodeDateTime() call in adminpack.c)

I do agree that it's quite ugly that I've only kept TrimTrailingZeros() in
float timestamp mode, but at least it is static.

What's the best way to proceed with this:
1. Change AppendSeconds() to always format to 6 decimal places? (Breaks
applications which rely on more precision with TIME)
2. Change AppendSeconds() to multiply f_sec by 10000000000.0 (this requires
an int64 version of pg_ltostr, but still requires a special case in
AppendSeconds())
3. Just keep things as they are with my proposed patch.
4. ?

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?

Regards

David Rowley

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