Though this may be the kiss of death, I favor a 64 bit float version of money. It's more terse than numeric and a *lot* faster when performing numeric operations because it would use a cpu intrinsic operand.


- Luke

Msg is shrt cuz m on ma treo

-----Original Message-----
From: D'Arcy J.M. Cain [mailto:darcy@druid.net]
Sent: Thursday, September 28, 2006 11:02 AM Eastern Standard Time
To: D'Arcy J.M. Cain
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] New version of money type

On Thu, 14 Sep 2006 10:35:03 -0400
"D'Arcy J.M. Cain" <darcy@druid.net> wrote:
> For years I have been promising that a 64 bit version of the money type
> was on the way. Here it is. So far it compiles and I have done some
> basic testing on it and it seems to work fine. Note that the currency
> symbol is also dropped on output as well but it is accepted on input.
>
> darcy=# select '$92,233,720,368,547,758.07'::money;
> money
> ----------------------------
> 92,233,720,368,547,758.07

There has been plenty of discussion back and forth but still no ruling
from core. Is money out in the next release in which case I can
convert this to a contrib module or will this improvement be accepted
for the next release.

--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, 28 Sep 2006 11:09:17 -0400
"Luke Lonergan" <LLonergan@greenplum.com> wrote:
> Though this may be the kiss of death, I favor a 64 bit float version of money. It's more terse than numeric and a

I assume you mean "...64 bit INT version..."

--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

* Luke Lonergan (LLonergan@greenplum.com) wrote:
> Though this may be the kiss of death, I favor a 64 bit float version of money. It's more terse than numeric and a *lot* faster when performing numeric operations because it would use a cpu intrinsic operand.

What about just having a numeric64, or changing numeric to support
moving to 64bit sizes when necessary and supported by the platform?
Exactly how much faster would it *really* be? Have you tested it? At
what point does it become a 'winning' change?

I'm not sure about 'money' in general but these claims of great
performance improvments over numeric just don't fly so easily with me.
numeric isn't all *that* much slower than regular old integer in the
tests that I've done.

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFG/vorzgMPqB3kigRAg8aAJ45C5HENonF6SZ6uVHe9kmcQVukRQCf XZLX
tSCQ5guhzfUDfBycc+Fbgqg=
=o6wH
-----END PGP SIGNATURE-----

Stephen,

On 9/28/06 9:44 AM, "Stephen Frost" <sfrost@snowman.net> wrote:

> I'm not sure about 'money' in general but these claims of great
> performance improvments over numeric just don't fly so easily with me.
> numeric isn't all *that* much slower than regular old integer in the
> tests that I've done.

Part of the problem is the *size* of Numeric. I've just looked for
something that describes the size of a Numeric and I saw an old post that
says:
10 + x/2 bytes

So, a minimum of 10 bytes (compared to the 8 proposed for money64) plus
scale (x) divided by two.

Currently on the TPC-H benchmark, Postgres requires 1.7 times the amount of
internal database storage as what is in the ASCII data file representation.
Oracle and MSFT SQLServer are almost 1:1. Part of this fluff is the 24
bytes of tuple header, part of it is in the Numeric.

- Luke



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Thu, 28 Sep 2006 12:44:24 -0400
Stephen Frost <sfrost@snowman.net> wrote:
> I'm not sure about 'money' in general but these claims of great
> performance improvments over numeric just don't fly so easily with me.
> numeric isn't all *that* much slower than regular old integer in the
> tests that I've done.

Numeric has been shown to be as good or better than money in I/O
operations. Where money shines is in internal calculations.

--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

* D'Arcy J.M. Cain (darcy@druid.net) wrote:
> On Thu, 28 Sep 2006 12:44:24 -0400
> Stephen Frost <sfrost@snowman.net> wrote:
> > I'm not sure about 'money' in general but these claims of great
> > performance improvments over numeric just don't fly so easily with me.
> > numeric isn't all *that* much slower than regular old integer in the
> > tests that I've done.
>
> Numeric has been shown to be as good or better than money in I/O
> operations. Where money shines is in internal calculations.

Which may be an area which could be improved on for numeric, or even a
numeric64 type added for it. I'm not entirely sure there's a huge
amount to gain there either though...

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFHBZwrzgMPqB3kigRAiekAKCINd/N9fYqZK8OUJHR1uZhLQK6HwCdGnjv
mZ/Iu3flHZNoEx5phprsrxs=
=PoOk
-----END PGP SIGNATURE-----

D'Arcy,

On 9/28/06 10:12 AM, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:

> Numeric has been shown to be as good or better than money in I/O
> operations.

What exactly does that mean in the context of a Datum: "I/O operations"?

- Luke



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, Sep 28, 2006 at 11:39:31AM -0700, Luke Lonergan wrote:
> D'Arcy,
>
> On 9/28/06 10:12 AM, "D'Arcy J.M. Cain" <darcy@druid.net> wrote:
>
> > Numeric has been shown to be as good or better than money in I/O
> > operations.
>
> What exactly does that mean in the context of a Datum: "I/O operations"?

Converting to/from text format for when dealing with client
applications. Numeric can convert faster than plain integers sometimes.
Numeric isn't that slow really...

Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFHBobIB7bNG8LQkwRAlExAJ9AT+dIy2tblqwR1BlH+X 0Z59cVQgCggI/8
VNZVtxhNnJU3BN9cmZl3gdw=
=tGzC
-----END PGP SIGNATURE-----

Martijn,

On 9/28/06 11:53 AM, "Martijn van Oosterhout" <kleptog@svana.org> wrote:

> Converting to/from text format for when dealing with client
> applications. Numeric can convert faster than plain integers sometimes.
> Numeric isn't that slow really...

Got it - so the performance benefits of the fixed point versus Numeric are:

- Smaller size of fixed point (less than half)
- Faster arithmetic operations

These should be quantified, so that we can evaluate Money64 as a proposal
and to understand Numeric better.

- Luke



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Thu, Sep 28, 2006 at 11:57:10AM -0700, Luke Lonergan wrote:
> Got it - so the performance benefits of the fixed point versus Numeric are:
>
> - Smaller size of fixed point (less than half)
> - Faster arithmetic operations
>
> These should be quantified, so that we can evaluate Money64 as a proposal
> and to understand Numeric better.

However, none of this seems to deal with the major problems with the
money type right now:

- Only supports one currency (dollars)
- Only supports one scale (yen has no decimal normally, but what if you
want to track hundredths of a dollar-cent?)

My question, what is this Money64 type buying you over just storing an
integer in your database? There should be some value-add somewhere, but
what is it?

I've written applications tracking money using just an integer, if
there were a special money type, I'd expect it to do something more.

Have a ncie day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFFHCWQIB7bNG8LQkwRAiy5AJ9MH9qvMTjQYki5Wz+zYU NVT5KbcQCaAsWe
3gKQXmBdMflpkhbkpcWFqPs=
=FFzC
-----END PGP SIGNATURE-----