Search Postgresql Archives

Re: Decimal vs. Bigint memory usage

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Sep 29, 2011 at 15:15, Gregor Vollmer
<vollmer@xxxxxxxxxxxxxxxxxxxx> wrote:
> We do not do any arithmetic on the columns, only saving and retrieval, is
> decimal as fast as bigint in that case?

It's slightly slower because numeric is passed around by pointer, not
by value. Bigint is by-value in 64-bit Postgres versions. But that's
probably only noticeable if you're processing lots of values in a
single query, or running CREATE INDEX.

Personally I'd choose bigint for efficiency reasons. But always keep
in mind that arithmetic works differently on integers and numerics:

db=# select 1::bigint/10 as x;
 x
---
 0
db=# select 1::numeric/10 as x;
           x
------------------------
 0.10000000000000000000

> How does decimal store the number internally, is it a fixed size through-out
> all rows and how does it compare to bigint?

Numeric (decimal) is always variable-length. The specification in
column type doesn't affect storage. Bigint is always 8 bytes.

For numbers with less than 8 digits, numeric is slightly smaller than
bigint. For larger numbers, bigint is smaller.

create table dec (i numeric);
insert into dec values(0), (1), (11), (101), (1001), (10001),
(100001), (1000001), (10000001), (100000001), (1000000001),
(10000000001), (100000000001), (1000000000001);

select pg_column_size(i), i::text from dec;
 pg_column_size |       i
----------------+---------------
              3 | 0
              5 | 1
              5 | 11
              5 | 101
              5 | 1001
              7 | 10001
              7 | 100001
              7 | 1000001
              7 | 10000001
              9 | 100000001
              9 | 1000000001
              9 | 10000000001
              9 | 100000000001
             11 | 1000000000001

Regards,
Marti

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux