Re: Let's drop two obsolete features which are bear-traps for novices

Hi,

This is Feng from Vitesse. Performance different between Money and
Numeric is *HUGE*. For TPCH Q1, the performance difference is 5x for
stock postgres, and ~20x for vitesse.

Stock postgres, for my laptop, TPCH 1G, Q1, use money type ~ 9s, use
Numeric (15, 2) is ~53s.

Kevin,
test=# do $$ begin perform sum('10000.01'::numeric) from
generate_series(1,10000000); end; $$;

This may not reflect the difference of the two data type. One aggregate
is not where most of the time is spent. TPCH Q1 has many more computing.

On Mon, Nov 3, 2014 at 4:54 AM, Michael Banck <michael(dot)banck(at)credativ(dot)de>
wrote:

> Am Sonntag, den 02.11.2014, 12:41 -0500 schrieb Tom Lane:
> > BTW, after reflecting a bit more I'm less than convinced that this
> > datatype is completely useless. Even if you prefer to store currency
> > values in numeric columns, casting to or from money provides a way to
> > accept or emit values in whatever monetary format the LC_MONETARY locale
> > setting specifies. That seems like a useful feature, and it's one you
> > could not easily duplicate using to_char/to_number (not to mention that
> > those functions aren't without major shortcomings of their own).
>
> 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.
>
>
> Michael
>
> [1]
> http://vitesse-timing-on.blogspot.de/2014/10/running-tpch-on-postgresql-part-1.html
> [2] http://vitessedata.com/benchmark/
>
> --
> Michael Banck
> Projektleiter / Berater
> Tel.: +49 (2161) 4643-171
> Fax: +49 (2161) 4643-100
> Email: michael(dot)banck(at)credativ(dot)de
>
> credativ GmbH, HRB Mönchengladbach 12080
> USt-ID-Nummer: DE204566209
> Hohenzollernstr. 133, 41061 Mönchengladbach
> Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
>
>
>
> --
> 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
>