Índice de hash: ser ou não ser?

Esta única consulta SQL pode ajudá-lo a encontrar índices B-Tree que podem se tornar índices Hash, economizando dinheiro em contas de armazenamento!

Recentemente, comparamos os índices Hash e B-Tree e aprendemos uma lição sobre um melhor benchmarking . Neste artigo, quero ajudá-lo a encontrar candidatos adequados para índices Hash em seus projetos.

Compartilhei minha história sobre por que essa oportunidade pode levar a uma promoção ou aumento de salário, então experimente.

Sem mais delongas, vamos ver a consulta. Coloquei alguns comentários para descrever melhor o que ele faz em particular.

DO $$
DECLARE
    count bigint;
    unique_count bigint;
    row record;
BEGIN
    CREATE TEMPORARY TABLE hash_index_candidates (
        table_name text,
        index_name text, -- b-tree index to migrate
        column_name text,
        unique_ratio decimal,
        btree_index_size text,
        potential_hash_index_size text, -- assumption based value
        count bigint
    ); -- stores temporary results for the current session
    FOR row IN (
        SELECT
            t.table_name AS table_name,
            t.index_name AS index_name,
            t.column_name AS column_name,
            pg_size_pretty(t.size) AS index_size
        FROM
            (
                SELECT
                    tab.relname AS table_name,
                    cls.relname AS index_name,
                    pa.attname AS column_name,
                    pg_relation_size(cls.relname :: text) AS size
                FROM pg_stat_user_indexes idx
                JOIN pg_index pi ON pi.indexrelid = idx.indexrelid
                JOIN pg_class cls ON cls.oid = idx.indexrelid
                JOIN pg_class tab ON tab.oid = idx.relid
                JOIN pg_am am ON am.oid = cls.relam
                JOIN pg_attribute pa ON cls.oid = pa.attrelid
                JOIN pg_type pt ON pa.atttypid = pt.oid
                WHERE amname = 'btree' -- b-tree type
                AND indnatts = 1 -- index covers single column
                AND indislive = true -- check index "health"
                AND indisvalid = true -- check index "health"
                AND indpred IS NULL -- index has no conditions
                AND typname IN ('varchar', 'text', 'char')
                AND indisprimary = false -- index is not primary
                AND typlen = -1 -- type has unlimited bytes length
                AND indisunique = false -- index is not unique
            ) t
        WHERE t.size > 104857600 -- index size is more than 100MB
    ) LOOP
        EXECUTE format('SELECT COUNT(%I) FROM %I', row.column_name, row.table_name ) INTO count;
        EXECUTE format('SELECT COUNT(DISTINCT %I) FROM %I', row.column_name, row.table_name) INTO unique_count;
        INSERT INTO hash_index_candidates
        VALUES
        (
            row.table_name,
            row.index_name,
            row.column_name,
            round(unique_count :: decimal / (count :: decimal + 1) * 100, 2),
            row.index_size,
            pg_size_pretty(count / 300 * 8192), -- assumes 300 tuples per hash bucket
            count
        );
    END LOOP;
END
$$ LANGUAGE plpgsql;

SELECT * FROM hash_index_candidates WHERE unique_ratio > 95; -- fetch results

Mas espere um segundo antes de fazer isso . Primeiro, devemos verificar se o candidato satisfaz a regra geral que criamos aqui:

• Os valores têm alta cardinalidade . Idealmente, seus códigos de hash também têm alta cardinalidade;
• Os valores são consultados apenas com operadores de igualdade ;
• Os comprimentos dos valores devem ter pelo menos 25 caracteres.

Podemos ter uma visão melhor das estatísticas armazenadas do PostgreSQL para automatizá-lo, mas isso pode exigir uma configuração específica do seu banco de dados, que talvez você ainda não tenha. Então eu forneci algo que pode ajudar a maioria de nós.

Como observação: podemos pular a regra 3 se o tamanho resultante do índice for menor do que com B-Tree. A consulta SQL abrange a regra 1 .

É isso!

Obrigado pelo seu tempo e, por favor, deixe-me saber se isso ajudou você.