Vamos dizer que temos uma tabela com 6 milhões de registros. Existem 16 colunas inteiras e poucas colunas de texto. É uma tabela somente de leitura, portanto, cada coluna inteira possui um índice. Cada registro é em torno de 50 a 60 bytes.

O nome da tabela é "Item"
O servidor é: 12 GB de RAM, 1,5 TB SATA, 4 CORES. Todo servidor para postgres.
Existem muitas outras tabelas neste banco de dados, de modo que a RAM não cobre todo o banco de dados.

Eu quero adicionar à tabela "Item" uma coluna "a_elements" (tipo de matriz de inteiros grandes) Cada registro não teria mais de 50-60 elementos nesta coluna.

Depois disso, eu criaria o índice GIN nessa coluna e a consulta típica deveria se parecer com isso:

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

Eu também tenho segunda opção, mais clássica.

Não adicione coluna a_elements ao item de tabela, mas crie elementos de tabela com duas colunas:

id_itemid_element

Esta tabela teria cerca de 200 milhões de registros.

Eu sou capaz de fazer o particionamento nessas tabelas, então o número de registros reduziria para 20 mln nos elementos da tabela e 500 K no item da tabela.

A segunda consulta de opção é assim:

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

Eu me pergunto qual opção seria melhor no ponto de vista do desempenho. O postgres é capaz de usar muitos índices diferentes com o índice GIN (opção 1) em uma única consulta?

Eu preciso tomar uma boa decisão porque a importação desses dados me levará 20 dias.