Re: 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