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>
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when
 extracting epoch
In-reply-to: <901bcfb8-36c8-27b2-b148-042392992eeb@2ndquadrant.com>
References: 
 <CA+hUKGK+K8NyS57tU841m1tSF6ha3W0udN2dw_Hbc=D_0OPbmA@mail.gmail.com>
 <ee683994-8f7c-aded-be43-543e66e68b94@2ndquadrant.com>
 <a3be61d9-f44b-7fce-3dc8-d700fdfb6f48@2ndquadrant.com>
 <CAFj8pRC8cgoiQMT235cky4Pa40gj=s0m3PftmaJLSt9MAiwj_Q@mail.gmail.com>
 <be30c07c-3f7b-7c86-f1c5-d0858046b26f@2ndquadrant.com>
 <1551357.1599435992@sss.pgh.pa.us>
 <097a4242-0748-98e3-06ab-ded1d366cd76@2ndquadrant.com>
 <1893468.1599658711@sss.pgh.pa.us>
 <38fc499a-ae52-efb6-aad6-82f812d6782d@2ndquadrant.com>
 <20200917055327.GU2873@paquier.xyz> <20200930071543.GL1996@paquier.xyz>
 <2fb99632-9302-6509-78ce-3aaed3fd7f40@2ndquadrant.com>
 <3ff4be02-1e2b-564f-9b53-3dc561da8218@pgmasters.net>
 <3013402.1615829712@sss.pgh.pa.us>
 <8fae8673-0d5b-12d9-7342-daed0d590594@2ndquadrant.com>
 <901bcfb8-36c8-27b2-b148-042392992eeb@2ndquadrant.com>
Comments: In-reply-to Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
	message dated "Fri, 19 Mar 2021 20:37:04 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4158030.1616184417.1@sss.pgh.pa.us>
Date: Fri, 19 Mar 2021 16:06:57 -0400
Message-ID: <4158031.1616184417@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/4158031.1616184417%40sss.pgh.pa.us>
Precedence: bulk

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