PostgreSQL: Analise primeiro!

Recentemente, escrevi um artigo sobre a comparação de índices Hash e B-tree . Infelizmente, cometi um erro e agora é hora de consertar.

Neste artigo, mostrarei o script PL/pgSQL com um problema significativo e difícil de detectar. Em seguida, mostrarei minha história de depuração, incluindo alguns dos componentes internos do PostgreSQL. E por último, mas não menos importante, a lição aprendida e o conselho para você evitar os mesmos erros.

Vejamos o procedimento errado .

CREATE OR REPLACE FUNCTION test(length integer, count numeric)
RETURNS TABLE (
    sample_length integer,
    unique_ratio decimal, -- in percentage
    hash_index_size bigint, -- in kilobytes
    btree_index_size bigint, -- in kilobytes
    column_size bigint, -- in kilobytes
    hash_select_query decimal, -- in milliseconds
    btree_select_query decimal, -- in milliseconds
    hash_insert_query decimal, -- in milliseconds
    btree_insert_query decimal -- in milliseconds
) AS
$$
DECLARE
    strings varchar[];
BEGIN
    CREATE TABLE IF NOT EXISTS hash_table(example varchar);
    CREATE TABLE IF NOT EXISTS btree_table(example varchar);

    INSERT INTO hash_table (SELECT random_string(length) FROM generate_series(1, count));
    INSERT INTO btree_table (SELECT example FROM hash_table);

    CREATE INDEX IF NOT EXISTS hash_index ON hash_table USING hash(example);
    CREATE INDEX IF NOT EXISTS btree_index ON btree_table USING btree(example);

    ANALYSE hash_table;
    ANALYSE btree_table;

    strings := array_agg(random_string(length)) FROM generate_series(1, 100);

    RETURN QUERY
    SELECT (SELECT length(example) FROM hash_table LIMIT 1),
        round(count(DISTINCT example)::decimal / count(*) * 100, 2) AS unique_ratio,
       pg_relation_size('hash_index') / 1024 AS hash_index_size,
       pg_relation_size('btree_index') / 1024 AS btree_index_size,
       pg_table_size('hash_table') / 1024 AS column_size,
       benchmark('SELECT example FROM hash_table WHERE example = $1', strings) AS hash_select_query,
       benchmark('SELECT example FROM btree_table WHERE example = $1', strings) AS btree_select_query,
       benchmark('INSERT INTO hash_table VALUES($1)', strings) AS hash_insert_query,
       benchmark('INSERT INTO btree_table VALUES($1)', strings) AS btree_insert_query
    FROM hash_table;

    DROP TABLE IF EXISTS hash_table;
    DROP TABLE IF EXISTS btree_table;
END
$$ LANGUAGE plpgsql;

Você vê algo suspeito? Nem eu . Antes de dizer o que há de errado aqui, vamos relembrar juntos a definição do índice Hash.

Os índices de hash armazenam um código de hash de 32 bits derivado do valor da coluna indexada.

Você está seguindo agora onde eu estou indo?

Se o índice de hash armazena apenas índices de hash de valores indexados, por que o tamanho do índice seria diferente para strings com comprimentos diferentes? Por que isso está acontecendo?

Iniciaremos nossa pesquisa executando apenas a parte abaixo de nosso script inicial e verificaremos algumas estatísticas usando a pageinspectextensão.

CREATE EXTENSION pageinspect;

CREATE TABLE IF NOT EXISTS hash_table(example varchar);

-- For the research, we will use 10.000 strings with a length of 1024 characters.
INSERT INTO hash_table (
  SELECT random_string(1024) FROM generate_series(1, 10000)
);

CREATE INDEX IF NOT EXISTS hash_index ON hash_table USING hash(example);

SELECT * FROM hash_metapage_info(get_raw_page('hash_index', 0));

      
                

                    
                    
                
            

• ffactordetermina quando um índice deve alocar mais espaço para dados salvos.
• maxbucketmostra o número atual de baldes alocados (uma lista de códigos hash classificados com ponteiros para linhas na tabela).

Curiosamente, em nosso caso, o índice criado tem um padrão ffactorigual a 307 ( 75% do máximo 409 ) com maxbucketigual a 639 . Mas por que tantos baldes se indexamos apenas 10.000 linhas?

Agora, usando a pageinspectextensão, vamos ver quantas linhas (tuplas) existem em cada bucket.

SELECT (hash_page_stats(get_raw_page('hash_index', generate_series))).* 
FROM generate_series(1, 10);

      
                

                    
                    
                
            

Aqui está um pedaço de código interessante.

ffactor = HashGetTargetPageUsage(rel) / item_width;
/* keep to a sane range */
if (ffactor < 10)
  ffactor = 10;

Vamos executar este código e ver se detectamos alguma diferença.

CREATE TABLE hash1_table(example varchar);
CREATE TABLE hash2_table(example varchar);

INSERT INTO hash1_table (SELECT random_string(1024) FROM generate_series(1, 10000));
INSERT INTO hash2_table (SELECT example FROM hash_table1);

SELECT relname, n_tup_ins, n_live_tup, n_ins_since_vacuum 
FROM pg_stat_user_tables WHERE relname IN ('hash1_table', 'hash2_table');

      
                

                    
                    
                
            

Infelizmente, ambas as tabelas teriam um número ineficiente de baldes em índices de hash de qualquer maneira.

SELECT * FROM hash_metapage_info(get_raw_page('hash1_table_idx', 0))
UNION ALL
SELECT * FROM hash_metapage_info(get_raw_page('hash2_table_idx', 0));

      
                

                    
                    
                
            

CREATE TABLE hash_table(example varchar);

INSERT INTO hash_table (
    SELECT random_string(1024) FROM generate_series(1, 10000)
);

ANALYZE hash_table;

CREATE INDEX hash_table_idx ON hash_table USING hash(example);

SELECT * FROM hash_metapage_info(get_raw_page('hash_table_idx', 0));

      
                

                    
                    
                
            

De volta ao início, vamos corrigir o procedimento para comparar os índices Hash e B-tree adicionando analyzeapós o preenchimento das tabelas. Este será o gráfico correto representando resultados para 10.000 linhas com diferentes comprimentos fornecidos.

Os resultados agora fazem mais sentido! E eles são muito mais inspiradores porque agora o índice Hash ocupa 30 vezes menos memória do que o índice B-tree ( para 10.000 strings com 1024 caracteres de comprimento ).

analyzeé um poderoso instrumento que ajuda o PostgreSQL a fazer seu trabalho de forma eficiente. Portanto, é melhor chamá-lo com frequência do que raramente, especialmente quando você está fazendo benchmarks . Além disso, encorajo você a verificar seus índices e talvez reindexá-los ( depois de analisar, é claro )!

Com base na pesquisa acima, acho que farei uma segunda tentativa no artigo “Hash vs. B-tree index”.

Siga-me para ser notificado dos meus próximos artigos.