On 11/3/14, 1:34 PM, Alvaro Herrera wrote:
> David Fetter wrote:
>> On Mon, Nov 03, 2014 at 01:54:09PM +0100, Michael Banck wrote:
>>> As an additional datapoint, Vitesse Data changed the DB schema from
>>> NUMERIC to MONEY for their TPCH benchmark for performance reasons: "The
>>> modification to data types is easy to understand -- money and double
>>> types are faster than Numeric (and no one on this planet has a bank
>>> account that overflows the money type, not any time soon)."[1] And
>>> "Replaced NUMERIC fields representing currency with MONEY"[2].
>>>
>>> Not sure whether they modified/optimized PostgreSQL with respect to the
>>> MONEY data type and/or how much performance that gained, so CCing CK Tan
>>> as well.
>>
>> How does our NUMERIC type's performance compare to other systems'
>> precise types? I realize that some of those systems might have
>> restrictions on publishing numbers they don't authorize, but they
>> might have pushed some authorized numbers if those numbers make them
>> look good.
>
> There is a real advantage of money over numeric in the performance
> front. I haven't measured it, but suffice to say that money uses
> integer operations which map almost directly to CPU instructions,
> whereas numeric needs to decode from our varlena base-10000 digit
> format, operate on digits at a time, then encode back. No matter how
> much you optimize numeric, it's never going to outperform stuff that
> runs practically on bare electrons. As far as I recall, some TPCH
> queries run aggregations on currency columns.
>
> Now, whether this makes a measurable difference or not in TPCH terms, I
> have no idea.
The performance of our numeric vs Oracle's was a common complaint when I was at EnterpriseDB (in 2007).
Perhaps numeric's performance could be greatly improved in cases where the precision is low enough to map to an
int/bigint.That would get us closer to eliminating money as well as give other uses a big win. Of course, how to do
thatand not break pg_upgrade is a huge question...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com