PostgreSQL: analizzalo prima!

Di recente ho scritto un articolo sul confronto tra gli indici Hash e B-tree . Sfortunatamente, ho commesso un errore e ora è il momento di rimediare.

In questo articolo, ti mostrerò lo script PL/pgSQL con un problema significativo che è difficile da rilevare. Quindi, ti mostrerò la mia storia di debug, inclusi alcuni degli interni di PostgreSQL. E, ultimo ma non meno importante, la lezione appresa e i consigli per evitare gli stessi errori.

Diamo un'occhiata alla procedura sbagliata .

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;

Vedi qualcosa di sospetto? Nemmeno io . Prima di dire cosa c'è che non va qui, ricordiamo insieme la definizione dell'indice Hash.

Gli indici hash memorizzano un codice hash a 32 bit derivato dal valore della colonna indicizzata.

Stai seguendo ora dove sto andando?

Se l'indice hash memorizza solo indici hash di valori indicizzati, perché la dimensione dell'indice differirebbe per stringhe con lunghezze diverse? Perché succede questo?

Inizieremo la nostra ricerca eseguendo solo la parte sottostante dal nostro script iniziale e controlleremo alcune statistiche utilizzando l' pageinspectestensione.

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 un indice deve allocare più spazio per i dati salvati.
• maxbucketmostra il numero corrente di bucket allocati (un elenco di codici hash ordinati con puntatori alle righe nella tabella).

Interessante, nel nostro caso, l'indice creato ha un default ffactorpari a 307 ( 75% dal massimo 409 ) con maxbucketpari a 639 . Ma perché così tanti bucket se indicizziamo solo 10.000 righe?

Ora, usando l' pageinspectestensione, vediamo quante righe (tuple) ci sono in ogni bucket.

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

      
                

                    
                    
                
            

Ecco un pezzo di codice interessante.

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

Eseguiamo questo codice e vediamo se rileviamo differenze.

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

      
                

                    
                    
                
            

Sfortunatamente, entrambe le tabelle avrebbero comunque un numero inefficiente di bucket negli indici hash.

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

      
                

                    
                    
                
            

Tornando all'inizio, fissiamo la procedura per confrontare gli indici Hash e B-tree aggiungendo analyzedopo aver riempito le tabelle. Questo sarà il grafico corretto che rappresenta i risultati per 10.000 righe con diverse lunghezze date.

I risultati ora hanno più senso! E sono molto più stimolanti perché ora l'indice Hash richiede 30 volte meno memoria rispetto all'indice B-tree ( per 10.000 stringhe con una lunghezza di 1024 caratteri ).

analyzeè un potente strumento che aiuta PostgreSQL a svolgere il proprio lavoro in modo efficiente. Quindi è meglio chiamarlo frequentemente che raramente, specialmente quando stai facendo benchmark . Inoltre, ti incoraggio a controllare i tuoi indici e magari reindicizzarli ( dopo averli analizzati, ovviamente )!

Sulla base della ricerca di cui sopra, penso che farò un secondo tentativo con l'articolo "Hash vs. B-tree index".

Seguimi per essere avvisato dei miei prossimi articoli.