Хэш-индекс: быть или не быть?

Этот единственный запрос SQL может помочь вам найти индексы B-Tree, которые могут стать индексами Hash, экономя ваши деньги на счетах за хранение!

Недавно мы сравнили индексы Hash и B-Tree и усвоили урок по улучшению бенчмаркинга. В этой статье я хочу помочь вам найти подходящих кандидатов для хэш-индексов в ваших проектах.

Я поделился своей историей о том, почему эта возможность может привести к продвижению по службе или повышению зарплаты, так что попробуйте.

Без дальнейших церемоний, давайте посмотрим на запрос. Я оставил несколько комментариев, чтобы лучше описать, что он делает в частности.

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

В одном из моих проектов я нашел несколько индексов, которые освободили бы около 1,2 ГБ, если бы я перенес их на хэш-индексы.

Но подождите секунду, прежде чем сделать это. Во-первых, мы должны проверить, удовлетворяет ли кандидат эмпирическому правилу, которое мы здесь придумали:

Значения имеют высокую кардинальность. В идеале их хэш-коды также должны иметь высокую кардинальность;
Значения запрашиваются только с помощью равенства операторов;
Длина значений должна быть не менее 25 символов.

Наиболее важной частью является использование индекса. Если вы уверены, что ваше поле в индексе запрашивается только операторами равенства, переходите к миграции.

У нас может быть лучшее представление о сохраненной статистике из PostgreSQL для ее автоматизации, но для этого может потребоваться определенная конфигурация вашей базы данных, которой у вас еще может не быть. Поэтому я предоставил кое-что, что может помочь большинству из нас.

В качестве примечания: мы можем пропустить правило 3, если результирующий размер индекса меньше, чем при использовании B-Tree. Запрос SQL охватывает правило 1.

Вот и все!

Спасибо за потраченное время. Пожалуйста, дайте мне знать, помогло ли вам это.

Хэш-индекс: быть или не быть?

Вопросы по теме