PostgreSQL: сначала проанализируйте!

Недавно я написал статью о сравнении индексов Hash и B-tree . К сожалению, я допустил ошибку, и теперь пришло время ее исправить.

В этой статье я покажу вам сценарий PL/pgSQL с серьезной проблемой, которую трудно отловить. Затем я покажу вам свою историю отладки, в том числе кое-что из внутреннего устройства PostgreSQL. И последнее, но не менее важное: выученный урок и совет, как избежать тех же ошибок.

Давайте посмотрим на неправильную процедуру.

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;

Вы видите что-нибудь подозрительное? Я тоже . Прежде чем я скажу, что здесь не так, давайте вместе вспомним определение хеш-индекса.

Хэш-индексы хранят 32-битный хэш-код , полученный из значения индексированного столбца.

Теперь ты следишь, куда я иду?

Если хеш-индекс хранит только хэш-индексы индексированных значений, почему размер индекса может различаться для строк разной длины? Почему это происходит?

Мы начнем наше исследование, запустив только приведенную ниже часть из нашего исходного скрипта и проверив некоторую статистику с помощью pageinspectрасширения.

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));

      
                

                    
                    
                
            

• ffactorопределяет, когда индекс должен выделять больше места для сохраненных данных.
• maxbucketпоказывает текущее количество выделенных сегментов (список отсортированных хэш-кодов с указателями на строки в таблице).

Интересно, что в нашем случае созданный индекс имеет значение по умолчанию, ffactorравное 307 ( 75% от максимального 409 ) с maxbucketравным 639 . Но зачем так много сегментов, если мы индексируем только 10 000 строк?

Теперь, используя pageinspectрасширение, посмотрим, сколько строк (кортежей) в каждом сегменте.

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

      
                

                    
                    
                
            

Вот интересный фрагмент кода.

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

Давайте запустим этот код и посмотрим, поймаем ли мы какие-либо различия.

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');

      
                

                    
                    
                
            

К сожалению, обе таблицы в любом случае будут иметь неэффективное количество сегментов в хеш-индексах.

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));

      
                

                    
                    
                
            

Вернемся к началу, давайте исправим процедуру сравнения индексов Hash и B-tree, добавив analyzeпосле заполнения таблиц. Это будет правильная диаграмма, представляющая результаты для 10 000 строк с разной заданной длиной.

Результаты теперь имеют больше смысла! И они гораздо более вдохновляющие, потому что теперь хеш-индекс занимает в 30 раз меньше памяти , чем индекс B-дерева ( для 10 000 строк длиной 1024 символа ).

analyze— мощный инструмент, помогающий PostgreSQL эффективно выполнять свою работу. Так что лучше вызывать его часто, чем редко, особенно когда вы делаете тесты . Кроме того, я рекомендую вам проверить свои индексы и, возможно, переиндексировать их ( конечно, после анализа )!

Основываясь на приведенном выше исследовании, я думаю, что сделаю вторую попытку статьи «Hash vs. B-tree index».

Подпишитесь на меня, чтобы получать уведомления о моих следующих статьях.