假设我们有一个包含 600 万条记录的表。有 16 个整数列和少量文本列。它是只读表,因此每个整数列都有一个索引。
每条记录大约 50-60 字节。
表名称为“项目”
服务器为:12 GB RAM、1.5 TB SATA、4 核。所有 postgres 服务器。
该数据库中有更多的表,因此 RAM 无法覆盖所有数据库。
我想向表“Item”添加一列“a_elements”(大整数的数组类型)
每条记录在此列中的元素不超过 50-60 个。
之后,我将在此列上创建索引 GIN,典型的查询应如下所示:
select * from item where ...... and '{5}' <@ a_elements;
我还有第二种更经典的选择。
不要将列 a_elements 添加到表项,而是创建具有两列的表元素:
该表将包含大约 2 亿条记录。
我能够对此表进行分区,因此表元素中的记录数将减少到 2000 万,表项中的记录数将减少到 500K。
第二个选项查询如下所示:
select item.*
from item
left join elements on (item.id_item=elements.id_item)
where ....
and 5 = elements.id_element
我想知道从性能角度来看什么选择会更好。
postgres 是否能够在单个查询中使用带有索引 GIN(选项 1)的许多不同索引?
我需要做出明智的决定,因为导入这些数据需要 20 天的时间。
我认为你应该使用elements
table:
Postgres将能够在执行查询之前使用统计信息来预测有多少行将匹配,因此它将能够使用最佳的查询计划(如果您的数据分布不均匀,则这一点更为重要);
您将能够使用本地化查询数据CLUSTER elements USING elements_id_element_idx
;
当 Postgres 9.2 发布时,您将能够利用仅索引扫描;
但我对 10M 元素做了一些测试:
create table elements (id_item bigint, id_element bigint);
insert into elements
select (random()*524288)::int, (random()*32768)::int
from generate_series(1,10000000);
\timing
create index elements_id_item on elements(id_item);
Time: 15470,685 ms
create index elements_id_element on elements(id_element);
Time: 15121,090 ms
select relation, pg_size_pretty(pg_relation_size(relation))
from (
select unnest(array['elements','elements_id_item', 'elements_id_element'])
as relation
) as _;
relation | pg_size_pretty
---------------------+----------------
elements | 422 MB
elements_id_item | 214 MB
elements_id_element | 214 MB
create table arrays (id_item bigint, a_elements bigint[]);
insert into arrays select array_agg(id_element) from elements group by id_item;
create index arrays_a_elements_idx on arrays using gin (a_elements);
Time: 22102,700 ms
select relation, pg_size_pretty(pg_relation_size(relation))
from (
select unnest(array['arrays','arrays_a_elements_idx']) as relation
) as _;
relation | pg_size_pretty
-----------------------+----------------
arrays | 108 MB
arrays_a_elements_idx | 73 MB
因此,另一方面,数组较小,索引也较小。在做出决定之前我会做一些 200M 元素测试。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)