Postgresql - 在大型数据库中使用数组的性能

Question

假设我们有一个包含600万条记录的表。其中有16个整数列和一些文本列。这是只读表，因此每个整数列都有一个索引。每条记录大约50-60字节。

表名为“Item”
服务器配置为: 12 GB RAM, 1.5 TB SATA, 4 CORES，全部用于PostgreSQL数据库。
在数据库中还有许多其他表，因此RAM无法覆盖整个数据库。

我想在"Item"表中添加一个名为"a_elements"的列（大型整数数组类型），每条记录在此列中最多不超过50-60个元素。

之后，我将在此列上创建GIN索引，并且典型的查询应该类似于这样：

select * from item where ...... and '{5}' <@ a_elements;

我还有第二种更传统的选择。

不要将列a_elements添加到表项中，而是创建具有两个列的elements表：

• id_item
• id_element

该表将有大约2亿记录。

我能够对这些表进行分区，因此元素表中的记录数量会减少到2000万，在item表中为50万。

第二个选项查询看起来像这样：

select item.* 
from item 
    left join elements on (item.id_item=elements.id_item) 
where .... 
and 5 = elements.id_element

我想知道在性能角度上哪个选项更好。 Postgres能否在单个查询中使用许多不同的GIN索引（选项1）？

我需要做出一个明智的决定，因为导入这些数据需要20天。

- user1573402

Answer 1

我认为你应该使用一个elements表:

• Postgres能够使用统计数据在执行查询之前预测将匹配多少行，因此它能够使用最佳的查询计划（如果您的数据不均匀，则更重要）;

• 您可以使用CLUSTER elements USING elements_id_element_idx本地化查询数据;

• 当Postgres 9.2发布时，您将能够利用仅索引扫描;

但我已经对1000万个元素进行了一些测试:

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

因此，另一方面，数组更小，索引也更小。在做出决定之前，我会进行一些2亿元素的测试。

- Tometzky

---

如果有许多列的类型为int或real，使用数组类型将会提供更好的读取性能吗？例如，将成千上万个字段放入一个int[]列中。 - Yong Wang