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