Re: double and numeric conversion

Hello all,

I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value?

Best regards,

Theo

--
Theo Schlossnagle
http://omniti.com/is/theo-schlossnagle

Theo Schlossnagle wrote:
> Hello all,
>
> I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value?
>
>
>

float8_numeric() ? Although it uses sprintf too, by the look of it.

cheers

andrew

Theo Schlossnagle <jesus(at)omniti(dot)com> writes:
> I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value?

If you're worried about micro-optimization, why are you using NUMERIC at
all? It's no speed demon.

Although you might be able to shave some cycles with a dedicated code
path for this conversion, binary to decimal is fundamentally not cheap.

regards, tom lane

On Mar 1, 2010, at 4:35 PM, Tom Lane wrote:

> Theo Schlossnagle <jesus(at)omniti(dot)com> writes:
>> I'm writing some extension and I have a hot code path that has a lot of double (C type) data and needs to output NUMERIC tuple data. The current methods I can find in the code to convert sprintf the double to a buffer and then invoke the numeric_in function on them. I've profile my stuff and I'm spending (wasting) all my time in that conversion. Is there a more efficient method of converting a double into a postgres numeric value?
>
> If you're worried about micro-optimization, why are you using NUMERIC at
> all? It's no speed demon.
>
> Although you might be able to shave some cycles with a dedicated code
> path for this conversion, binary to decimal is fundamentally not cheap.

I feared that was the case. I spent an hour or so coding that last night and the speedups for me were worth it, I see a 2 fold speedup in conversion operations (or a 50% reduction in CPU cycles per conversion). The integer ones were trivial, the double one has the imperfect issue of reasonably guessing the dscale, but seems to work in my tests.

I didn't look deeply at the postgres internals to see if there was a way to do double -> numeric and integer-types -> numeric without intermediary string format. If that sort of thing is easy to leverage, I'd be happy to share the code.

--
Theo Schlossnagle
http://omniti.com/is/theo-schlossnagle

Theo Schlossnagle wrote:
> I didn't look deeply at the postgres internals to see if there was a way to do double -> numeric and integer-types -> numeric without intermediary string format. If that sort of thing is easy to leverage, I'd be happy to share the code.
>
I think your code could be valuable for postgres on the fact alone that
it is almost twice as fast, and probably easy to integrate and unit
test. We make heavy use of the numeric data type, so I'm very interested!

regards
Yeb Havinga

2010/3/3 Yeb Havinga <yebhavinga(at)gmail(dot)com>:
> Theo Schlossnagle wrote:
>>
>> I didn't look deeply at the postgres internals to see if there was a way
>> to do double -> numeric and integer-types -> numeric without intermediary
>> string format.  If that sort of thing is easy to leverage, I'd be happy to
>> share the code.
>>
>
> I think your code could be valuable for postgres on the fact alone that it
> is almost twice as fast, and probably easy to integrate and unit test. We
> make heavy use of the numeric data type, so I'm very interested!

I did some test and numeric->double is about 5% faster than
numeric->string->double (on my PC)

Regards
Pavel Stehule

>
> regards
> Yeb Havinga
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Pavel Stehule wrote:
> 2010/3/3 Yeb Havinga <yebhavinga(at)gmail(dot)com>:
>
>> Theo Schlossnagle wrote:
>>
>>> I didn't look deeply at the postgres internals to see if there was a way
>>> to do double -> numeric and integer-types -> numeric without intermediary
>>> string format. If that sort of thing is easy to leverage, I'd be happy to
>>> share the code.
>>>
>>>
>> I think your code could be valuable for postgres on the fact alone that it
>> is almost twice as fast, and probably easy to integrate and unit test. We
>> make heavy use of the numeric data type, so I'm very interested!
>>
>
> I did some test and numeric->double is about 5% faster than
> numeric->string->double (on my PC)
>
numeric_to_double_no_overflow() also uses string as intermediate format.

Theo's conversions are the converse, from double to numeric, and do not
use string as intermediate format (if I understand it correct). (where
float8_numeric
http://doxygen.postgresql.org/backend_2utils_2adt_2numeric_8c.html#2de7f65c8de4b65dad441e77ea1bf402
does)

regards
Yeb Havinga

2010/3/3 Yeb Havinga <yebhavinga(at)gmail(dot)com>:
> Pavel Stehule wrote:
>>
>> 2010/3/3 Yeb Havinga <yebhavinga(at)gmail(dot)com>:
>>
>>>
>>> Theo Schlossnagle wrote:
>>>
>>>>
>>>> I didn't look deeply at the postgres internals to see if there was a way
>>>> to do double -> numeric and integer-types -> numeric without
>>>> intermediary
>>>> string format.  If that sort of thing is easy to leverage, I'd be happy
>>>> to
>>>> share the code.
>>>>
>>>>
>>>
>>> I think your code could be valuable for postgres on the fact alone that
>>> it
>>> is almost twice as fast, and probably easy to integrate and unit test. We
>>> make heavy use of the numeric data type, so I'm very interested!
>>>
>>
>> I did some test and numeric->double is about 5% faster than
>> numeric->string->double (on my PC)
>>
>
> numeric_to_double_no_overflow() also uses string as intermediate format.
>
> Theo's conversions are the converse, from double to numeric, and do not use
> string as intermediate format (if I understand it correct). (where
> float8_numeric
> http://doxygen.postgresql.org/backend_2utils_2adt_2numeric_8c.html#2de7f65c8de4b65dad441e77ea1bf402
> does)
>

aha - it is reason why time similar

Pavel

> regards
> Yeb Havinga
>
>

I can't release all of it, but the functions to convert uint64_t, int64_t and double to numeric Datum are the meat and I can expose those...

https://labs.omniti.com/pgsoltools/trunk/contrib/scratch/pg_type_to_numeric.c

As I mentioned, the dscale on the double_to_numeric is imperfect resulting in things like: 1.23 turning into 1.2300 in the numeric returned. This are significantly faster (as expected) than the type -> string -> numeric conversions.

On Mar 3, 2010, at 5:01 AM, Yeb Havinga wrote:

> Theo Schlossnagle wrote:
>> I didn't look deeply at the postgres internals to see if there was a way to do double -> numeric and integer-types -> numeric without intermediary string format. If that sort of thing is easy to leverage, I'd be happy to share the code.
>>
> I think your code could be valuable for postgres on the fact alone that it is almost twice as fast, and probably easy to integrate and unit test. We make heavy use of the numeric data type, so I'm very interested!
>
> regards
> Yeb Havinga
>
>

--
Theo Schlossnagle
http://omniti.com/is/theo-schlossnagle

if (p1 > buf)
++ * --p1;
else {

....

++ * --p1; ???

does it even compile ?

On 3 Mar 2010, at 17:41, Grzegorz Jaskiewicz wrote:

> if (p1 > buf)
> ++ * --p1;
> else {
>
> ....
>
>
> ++ * --p1; ???
>
> does it even compile ?

Oh, I can see, that it is *(--p1)++ ,mea culpa.

Which doesn't change the fact, that the code is rather messy imo.