Re: WIP: Make timestamptz_out less slow. - Mailing list pgsql-hackers
| From | David Rowley |
|---|---|
| Subject | Re: WIP: Make timestamptz_out less slow. |
| Date | |
| Msg-id | CAKJS1f8==vyWaWPKdhgKZGBMkahWvObCgR7pai+Uc4QYh41MgQ@mail.gmail.com Whole thread Raw |
| In response to | Re: WIP: Make timestamptz_out less slow. (Andres Freund <andres@anarazel.de>) |
| Responses |
Re: WIP: Make timestamptz_out less slow.
(Jim Nasby <Jim.Nasby@BlueTreble.com>)
|
| List | pgsql-hackers |
On 12 September 2015 at 04:24, Andres Freund <andres@anarazel.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/
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
pgsql-hackers by date: