Horton

Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: David Steele <david@pgmasters.net>, Pavel Stehule <pavel.stehule@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Petr Fedorov <petr.fedorov@phystech.edu>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2021-03-19T20:06:57Z
Lists: pgsql-bugs, pgsql-hackers 
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Well, I had an idea that I put to work.  In most of these cases where we 
> need division, we divide an integer by a power of 10.  That can be done 
> with numeric very quickly by just shifting the weight and scale around. 
> So I wrote a function that does that specifically (look for 
> int64_div_fast_to_numeric()).  With that, the slow cases I mentioned now 
> have the same performance as the other cases that didn't have any 
> numeric division.  You just get the overhead for constructing and 
> passing around a numeric instead of a double, which can't be avoided.

Yeah, I was wondering if we could do something like that, but I hadn't
got as far as figuring a way to deal with divisors not a multiple of
NBASE.

Looking at the proposed code, I wonder if it wouldn't be better to
define the function as taking the base-10-log of the divisor, so that
you'd have the number of digits to shift (and the dscale) immediately
instead of needing repeated integer divisions to get that.  Also, the
risk of intermediate overflow here seems annoying:

+		if (unlikely(pg_mul_s64_overflow(val1, NBASE/x, &val1)))
+			elog(ERROR, "overflow");

Maybe that's unreachable for the ranges of inputs the current patch could
create, but it seems like it makes the function distinctly less
general-purpose than one would think from its comment.  Maybe, if that
overflows, we could handle the failure by making that adjustment after
we've converted to numeric?

> So here is an intermediate patch that does this.  I haven't gotten rid 
> of all numeric_div_opt_error() calls yet, but if this seems acceptable, 
> I can work on the remaining ones.

I guess the immediate question is how much of a performance gap there
is now between the float and numeric implementations.

			regards, tom lane

Commits

  1. Fix inconsistent equalfuncs.c behavior for FuncCall.funcformat.

  2. Doc: fix discussion of how to get real Julian Dates.

  3. Doc: document EXTRACT(JULIAN ...), improve Julian Date explanation.

  4. Change return type of EXTRACT to numeric

  5. Improve our ability to regurgitate SQL-syntax function calls.

  6. Add more tests for EXTRACT of date type

  7. Expose internal function for converting int64 to numeric

  8. Change floating-point output format for improved performance.