Re: PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

On 08/05/2014 10:44 PM, Shaun Thomas wrote:
> On 08/05/2014 12:56 AM, Mark Kirkwood wrote:
>
>> The moral of the story for this case is that mapping Oracle to Postgres
>> datatypes can require some careful thought. Using 'native' types (like
>> integer, float8 etc) will generally give vastly quicker performance.
>
> We've seen a lot of this ourselves. Oracle's NUMERIC is a native type,
> whereas ours is emulated.

I'm not sure what you mean by "native" vs "emulated" here.

PostgreSQL's NUMERIC is binary-coded decimal with mathematical
operations performed in software.

According to the docs, my impression is that Oracle's NUMBER is stored
more like a decfloat:

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i22289

but my Oracle expertise is admittedly lacking.

New Intel hardware supports IEEE 754:2008 decimal floating point in
hardware, and I'm quite interested in implementing DECFLOAT(n) for
PostgreSQL to take advantage of that.

A DECFLOAT type would also be more compatible with things like the C#
"Decimal" type than our current NUMERIC is.

> At least you used INT though. I've seen too many Oracle shops using
> NUMERIC in PostgreSQL because it's there, and suffering major
> performance hits because of it.

In retrospect it might be a bit of a loss that the numeric type format
doesn't reserve a couple of bits for short-value flags, so we could
store and work with native integers for common values.

There's NumericShort and NumericLong, but no NumericNative or
NumericInt32 or whatever. OTOH, by the time you handle alignment and
padding requirements and the cost of deciding which numeric format the
input is, it might not've been much faster. Presumably it was looked at
during the introduction of NumericShort.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services