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

On 18.03.21 09:28, Peter Eisentraut wrote:
> Which leads me to:  After retesting this now, with a new machine, the 
> performance of the numeric implementation is brutal compared to the 
> float implementation, for cases where we need numeric division, which is 
> milliseconds, seconds, and epoch.  In the first two cases, I imagine we 
> could rewrite this a bit to avoid a lot of the numeric work, but for the 
> epoch case (which is what started this thread), there isn't enough space 
> in int64 to make this work.  Perhaps int128 could be pressed into 
> service, optionally.  I think it would also help if we cracked open the 
> numeric APIs a bit to avoid all the repeated unpacking and packing for 
> each step.
> 
> So I think we need to do a bit more thinking and work here, meaning it 
> will have to be postponed.

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.

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.