Início / dba / Perguntas / 249030
Accepted
Morris de Oryx
Asked: 2019-09-18 22:42:47 +0800 CST

Por que um índice de hash do Postgres 11 é tão grande?

  • 772

Postgres 11.4 no RDS e 11.5 em casa.

Estou analisando os índices de hash mais de perto hoje porque estou tendo problemas com um índice citext sendo ignorado . E acho que não entendo por que um índice de hash é tão grande. Está levando cerca de 50 bytes/linha quando eu esperava que levasse 10 bytes + alguma sobrecarga.

Eu tenho um banco de dados de exemplo com uma tabela chamada tabela record_changes_log_detail que tem 7.733.552 registros, então ~ 8M. Dentro dessa tabela há um campo citext chamado old_value que é a fonte do índice de hash:

CREATE INDEX record_changes_log_detail_old_value_ix_hash
    ON record_changes_log_detail
    USING hash (old_value);

Aqui está uma verificação do tamanho do índice:

select
'record_changes_log_detail_old_value_ix_hash' as index_name,
pg_relation_size ('record_changes_log_detail_old_value_ix_hash') as bytes,
pg_size_pretty(pg_relation_size ('record_changes_log_detail_old_value_ix_hash')) as pretty

Isso retorna 379.322.368 bytes, ou cerca de 362 MB. Eu cavei um pouco na fonte , e esta bela peça um pouco mais.

Parece que uma entrada de índice de hash para uma linha é um TID emparelhado com a própria chave de hash. E algum tipo de contador de índice dentro da página. São dois inteiros de 4 bytes e, suponho, um inteiro de 1 ou 2 bytes. Como um cálculo ingênuo, 10 bytes * 7.733.552 = 77.335.520. O índice real é aproximadamente 5x maior que isso. Concedido, você precisa de espaço para a própria estrutura do índice, mas não deve levar o custo aproximado por linha de ~ 10 bytes a ~ 50, não é?

Aqui estão os detalhes do índice, lidos usando a extensão pageinspect e depois girados manualmente para legibilidade.

select * 
from hash_metapage_info(get_raw_page('record_changes_log_detail_old_value_ix_hash',0));


magic   105121344
version 4
ntuples 7733552
ffactor 307
bsize   8152
bmsize  4096
bmshift 15
maxbucket   28671
highmask    32767
lowmask 16383
ovflpoint   32
firstfree   17631
nmaps   1
procid  17269
spares  {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,17631,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
mapp    {28673,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

select *
from hash_page_stats(get_raw_page('record_changes_log_detail_old_value_ix_hash',1));

live_items  2
dead_items  0
page_size   8192
free_size   8108
hasho_prevblkno 28671
hasho_nextblkno 4294967295
hasho_bucket    0
hasho_flag  2
hasho_page_id   65408
postgresql index

1 respostas

  1. Best Answer

    Eu recebo um índice recém-construído de 256 MB com esse tamanho de tabela. Seu índice foi criado recentemente? A tabela foi recentemente analisada antes da compilação (o índice é pré-dimensionado com base nas linhas estimadas na tabela). Como é sua distribuição de duplicatas?

    As coisas são armazenadas com alinhamento mínimo de 8 bytes, portanto, uma tupla de índice de hash tem 16 bytes, mesmo que caiba em 10 (ou 12, ou o que for). E as páginas de hash estão, em média, apenas pela metade. Os baldes são divididos em uma sequência pré-determinada, deve-se dividir o balde cuja vez é a próxima, não o que estiver mais cheio.

    select *
from hash_page_stats(get_raw_page('record_changes_log_detail_old_value_ix_hash',1));

live_items  2
dead_items  0
page_size   8192
free_size   8108

    Você não vai aprender muito olhando apenas uma página, mas essa página é estranhamente deficiente em tuplas. Talvez você tenha uma distribuição de dados patológicos.

    O microgerenciamento do banco de dados até esse nível raramente vale a pena.

    • 4

relate perguntas