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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
