Postgresql - производительность использования массива в большой базе данных

Скажем, у нас есть таблица с 6 миллионами записей. Есть 16 целых столбцов и несколько столбцов текста. Это таблица только для чтения, поэтому каждый целочисленный столбец имеет индекс. Каждая запись составляет около 50-60 байт.

Название таблицы - "Item"
Сервер: 12 ГБ оперативной памяти, 1,5 ТБ SATA, 4 CORES. Весь сервер для postgres.
В этой базе данных еще много таблиц, поэтому операционная система не распространяется на всю базу данных.

Я хочу добавить в таблицу "Item" столбец "a_elements" (тип массива больших целых чисел) Каждая запись будет содержать не более 50-60 элементов в этом столбце.

После этого я создам индекс GIN в этом столбце, и типичный запрос должен выглядеть так:

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

У меня есть второй, более классический вариант.

Не добавляйте столбец a_elements в элемент таблицы, но создавайте элементы таблицы с двумя столбцами:

id_item
id_element

В этой таблице будет около 200 млн записей.

Я могу сделать разбиение на эти таблицы, поэтому количество записей уменьшится до 20 миллионов в элементах таблицы и 500 K в элементе таблицы.

Второй вариант запроса выглядит следующим образом:

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

Интересно, какой вариант будет лучше с точки зрения производительности. Может ли postgres использовать множество разных индексов с индексом GIN (вариант 1) в одном запросе?

Мне нужно принять правильное решение, потому что импорт этих данных займет у меня 20 дней.

Ответ 1

Я думаю, вы должны использовать таблицу elements:

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 элементов, прежде чем принимать решение.