Re: [GENERAL] Performance implications of numeric?
On Tue, Aug 21, 2012 at 11:27 PM, Wells Oliver wellsoli...@gmail.com wrote: We have a lot of tables which store numeric data. These tables all use the numeric type, where the values are 95% integer values. We used numeric because it eliminated the need for casting during division to yield a floating point value. I'm curious as to whether this would have performance and/or disk size implications. Would converting these columns to integer (or double precision on the handful that require the precision) and forcing developers to use explicit casting be worth the time? Thanks for any clarification. Calculations against numeric are several orders of magnitude slower than native binary operations. Fortunately the time the database spends doing these types of calculations is often a tiny fraction of overall execution time and I advise giving numeric a whirl unless you measure a big performance hit. Let's put it this way: native binary types are a performance hack that come with all kinds of weird baggage that percolate up and uglify your code: your example given is a classic case in point. Database integer types are not in fact integers but a physically constrained approximation of them. Floating point types are even worse. Another example: I just found out for the first time (after many years of programming professionally) that -2147483648 / -1 raises a hardware exception: this is exactly the kind of thing that makes me think that rote use of hardware integer types is a terribly bad practice. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance implications of numeric?
On 08/23/2012 12:48 AM, Wells Oliver wrote: Hey, thanks for your feedback. Just to clarify: pg_total_relation_size returns bytes, correct? Please reply to the list, not directly to me. Yes, pg_total_relation_size returns bytes. The documentation (http://www.postgresql.org/docs/9.1/static/functions-admin.html) doesn't seem to explicitly say that for pg_total_relation_size though it does for pg_relation_size and other functions. Use pg_size_pretty to convert bytes to human values for display. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance implications of numeric?
We have a lot of tables which store numeric data. These tables all use the numeric type, where the values are 95% integer values. We used numeric because it eliminated the need for casting during division to yield a floating point value. I'm curious as to whether this would have performance and/or disk size implications. Would converting these columns to integer (or double precision on the handful that require the precision) and forcing developers to use explicit casting be worth the time? Thanks for any clarification. -- Wells Oliver wellsoli...@gmail.com
Re: [GENERAL] Performance implications of numeric?
On 08/22/2012 12:27 PM, Wells Oliver wrote: We have a lot of tables which store numeric data. These tables all use the numeric type, where the values are 95% integer values. We used numeric because it eliminated the need for casting during division to yield a floating point value. I'm curious as to whether this would have performance and/or disk size implications. Yes, and yes, though the gap seems to have shrunk a lot since I first started using Pg. It's easy to concoct fairly meaningless micro-benchmarks, but you should really try it with some real queries you run on your real schema. Take a copy of your data, convert it, and run some tests. Use `pg_total_relation_size` to compare the numeric and int versions of the relations after `CLUSTER`ing them to debloat and reindex them. Would converting these columns to integer (or double precision on the handful that require the precision) and forcing developers to use explicit casting be worth the time? Without knowing your workload and your constraints, that's a how blue is the sky question. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
