我不明白Postgresql(9.2)如何计算列大小(kb),我有以下表格:
Table d2:
Column | Type |
---------+---------------|
id | serial |
n | numeric(17,2) |
Table d4:
Column | Type |
---------+---------------|
id | serial |
n | numeric(19,4) |
Table d18:
Column | Type |
---------+---------------|
id | serial |
n | numeric(35,18)|
Table b1:
Column | Type |
---------+---------------|
id | serial |
n | numeric(16,2) |
Table b2:
Column | Type |
---------+---------------|
id | serial |
n | numeric(4,2) |
我用下面的代码填充它们,以便每个表有10000行;
$tests = array(2, 4, 18);
foreach($tests AS $n)
{
$m = number_format(999999999999999.66549865, $n, '.', '');
$prp_name = "insert_$n";
$prp = pg_prepare($db, $prp_name, "INSERT INTO d_$n (n) VALUES ($1)");
for($i = 0; $i < 10000; $i++)
{
pg_execute($db, $prp_name, array($m));
}
}
$prp = pg_prepare($db, 'insert_b1', "INSERT INTO b1 (n) VALUES ($1)");
$m = 16512.67;
for($i = 0; $i < 10000; $i++)
{
pg_execute($db, 'insert_b1', array($m));
}
$prp = pg_prepare($db, 'insert_b2', "INSERT INTO b2 (n) VALUES ($1)");
$m = 99.36;
for($i = 0; $i < 10000; $i++)
{
pg_execute($db, 'insert_b2', array($m));
}
现在,我不明白的是为什么会这样:
SELECT pg_size_pretty(pg_total_relation_size('d2')) AS size_d2;
size_d2
---------
752 kB
SELECT pg_size_pretty(pg_total_relation_size('d4')) AS size_d4;
size_d4
---------
752 kB
SELECT pg_size_pretty(pg_total_relation_size('d18')) AS size_d18;
size_d18
----------
752 kB
SELECT pg_size_pretty(pg_total_relation_size('b1')) AS size_b1;
size_b1
---------
440 kB
SELECT pg_size_pretty(pg_total_relation_size('b2')) AS size_b2;
size_b2
---------
680 kB
因此,d_*表具有相同的大小,即使精度(以及存储的数据长度)非常不同;
表b1比b2小,即使具有更大的精度.
所有桌子在pg_total_relazion_size
分钟前都已冲洗(真空、分析).
我无法在Postgresql's datatypes documentations中找到答案,所以我会在这里问:How does the size in kb grow in relation of the precision of numeric columns?
我做这些测试是为了决定在CMS数据库中存储货币类型的精度/刻度,我希望所有商品的价格只有1个精度/刻度值(不是总数,刻度必须是2位小数).
我可以存储的小数位数越多,对用户来说就越好(因此,当客户要求为特定项目存储12个小数位数时,我没有限制),但我想了解这一决定将如何影响数据库大小和性能.