From: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> |
---|---|
To: | Wells Oliver <wellsoliver(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Performance implications of numeric? |
Date: | 2012-08-22 04:47:58 |
Message-ID: | 5034647E.60501@ringerc.id.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-08-22 05:15:49 | Re: Amazon High I/O instances |
Previous Message | Craig Ringer | 2012-08-22 04:33:15 | Re: Grant problem and how to prevent users to execute OS commands? |