foo's questions

Vamos fazer algumas suposições:

Tenho uma tabela assim:

 a | b
---+---
 a | -1
 a | 17
  ...
 a | 21
 c | 17
 c | -3
  ...
 c | 22

Fatos sobre meu set:

• O tamanho de toda a tabela é de ~ 10 ¹⁰ linhas.

• Eu tenho ~ 100k linhas com valor ana coluna a, semelhante para outros valores (por exemplo c).

• Isso significa ~ 100k valores distintos na coluna 'a'.

• A maioria das minhas consultas lerá todos ou a maioria dos valores de um determinado valor em a, por exemplo select sum(b) from t where a = 'c'.

• A tabela é escrita de forma que os valores consecutivos estejam fisicamente próximos (ou está escrito em ordem, ou assumimos que CLUSTERfoi usado naquela tabela e coluna a).

• A tabela raramente é atualizada, estamos apenas preocupados com a velocidade de leitura.

• A tabela é relativamente estreita (digamos ~25 bytes por tupla, + 23 bytes de sobrecarga).

Agora a pergunta é: que tipo de índice devo usar? Meu entendimento é:

• BTree Meu problema aqui é que o índice BTree será enorme, pois até onde eu sei, ele armazenará valores duplicados (é necessário, pois não pode assumir que a tabela está classificada fisicamente). Se o BTree for enorme, acabo tendo que ler tanto o índice quanto as partes da tabela para as quais o índice aponta. (Podemos usar fillfactor = 100para diminuir um pouco o tamanho do índice.)

• BRIN Meu entendimento é que posso ter um pequeno índice aqui às custas de ler páginas inúteis. Usar um pequeno pages_per_rangesignifica que o índice é maior (o que é um problema com o BRIN já que preciso ler todo o índice), ter um grande pages_per_rangesignifica que vou ler muitas páginas inúteis. Existe uma fórmula mágica para encontrar um bom valor pages_per_rangeque leve em conta esses trade-offs?

• GIN/GiST Não tenho certeza se eles são relevantes aqui, pois são usados ​​principalmente para pesquisa de texto completo, mas também ouvi dizer que eles são bons em lidar com chaves duplicadas. Um GINou GiSTíndice ajudaria aqui?

Outra questão é, o Postgres usará o fato de que uma tabela é CLUSTERed (supondo que não haja atualizações) no planejador de consulta (por exemplo, por busca binária pelas páginas inicial/final relevantes)? Um pouco relacionado, posso apenas armazenar todas as minhas colunas em um BTree e descartar a tabela completamente (ou conseguir algo equivalente, acredito que sejam índices clusterizados no SQL Server)? Existe algum índice híbrido BTree/BRIN que ajudaria aqui?

Prefiro evitar o uso de matrizes para armazenar meus valores, pois minha consulta acabará menos legível dessa maneira (eu entendo que isso reduziria o custo dos 23 bytes por sobrecarga de tupla, reduzindo o número de tuplas).