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

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

On Tue, Aug 21, 2012 at 11:27 PM, Wells Oliver <wellsoliver@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