PostgreSQL : analysez-le d'abord !

J'ai récemment écrit un article sur la comparaison des index Hash et B-tree . Malheureusement, j'ai fait une erreur, et maintenant il est temps de la corriger.

Dans cet article, je vais vous montrer le script PL/pgSQL avec un problème important difficile à détecter. Ensuite, je vous montrerai mon histoire de débogage, y compris certains des éléments internes de PostgreSQL. Et enfin, la leçon apprise et les conseils pour éviter les mêmes erreurs.

Regardons la mauvaise procédure.

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;

Vous voyez quelque chose de suspect ? Moi non plus . Avant de dire ce qui ne va pas ici, rappelons ensemble la définition de l'index Hash.

Les index de hachage stockent un code de hachage 32 bits dérivé de la valeur de la colonne indexée.

Suivez-vous maintenant où je vais?

Si l'index de hachage stocke uniquement les index de hachage des valeurs indexées, pourquoi la taille de l'index serait-elle différente pour les chaînes de longueurs différentes ? Pourquoi cela se produit-il ?

Nous commencerons nos recherches en exécutant uniquement la partie ci-dessous à partir de notre script initial et en vérifiant certaines statistiques à l'aide de l' pageinspectextension.

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

      
                

                    
                    
                
            

• ffactordétermine quand un index doit allouer plus d'espace pour les données enregistrées.
• maxbucketaffiche le nombre actuel de compartiments alloués (une liste de codes de hachage triés avec des pointeurs vers les lignes de la table).

Fait intéressant, dans notre cas, l'index créé a un défaut ffactorégal à 307 ( 75% du maximum 409 ) avec maxbucketégal à 639 . Mais pourquoi tant de buckets si nous n'indexons que 10 000 lignes ?

Maintenant, en utilisant l' pageinspectextension, voyons combien de lignes (tuples) se trouvent dans chaque seau.

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

      
                

                    
                    
                
            

Voici un morceau de code intéressant.

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

Exécutons ce code et voyons si nous détectons des différences.

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

      
                

                    
                    
                
            

Malheureusement, les deux tables auraient de toute façon un nombre inefficace de compartiments dans les index de hachage.

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

      
                

                    
                    
                
            

Revenons au début, corrigeons la procédure pour comparer les index Hash et B-tree en ajoutant analyzeaprès avoir rempli les tables. Ce sera le bon graphique représentant les résultats pour 10 000 lignes avec différentes longueurs données.

Les résultats ont maintenant plus de sens ! Et ils sont bien plus inspirants car désormais l'index Hash prend 30 fois moins de mémoire que l'index B-tree ( pour 10.000 chaînes de 1024 caractères ).

analyzeest un instrument puissant qui aide PostgreSQL à faire son travail efficacement. Il est donc préférable de l'appeler fréquemment que rarement, surtout lorsque vous faites des benchmarks . De plus, je vous encourage à vérifier vos index et peut-être à les réindexer ( après analyse, bien sûr ) !

Sur la base des recherches ci-dessus, je pense que je vais faire une deuxième tentative à l'article "Hash vs. B-tree index".

Suivez-moi pour être informé de mes prochains articles.