Re: Reducing the overhead of NUMERIC data

On Wed, Nov 02, 2005 at 12:53:07PM -0600, Jim C. Nasby wrote:
> > This is one of those issues where we need to run tests and take input.
> > We cannot decide this sort of thing just by debate alone. So, I'll leave
> > this as a less potentially fruitful line of enquiry.
>
> Is it worth comming up with some script that users can run against a
> table to provide us with real data?

Like I said, I have a few columns of numeric(12,4). They're costs in
cent, to 4 decimal places. The test is (column = trunc(column)).

Sample data:
col1 | col2 | col3 | col4
---------+---------+--------+---------
21.0000 | 10.1818 | 0.0000 | 21.0000
22.0000 | 11.2727 | 0.0000 | 22.0000
22.0000 | 6.0909 | 0.0000 | 22.0000

For each column (across 17 million rows):

Col 1: 83% trailing zeros
Col 2: 49%
Col 3: 94%
Col 4: 83%

AIUI, I currently get the four decimal places for free, and the idea is
to store them explicitly.

Fact is, things that cost fractions of cents are not that common, in
this database anyway. As for the argument in general, this table is so
wide that any gain will vanish into the slack at the end of a block so
it won't actually matter...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.