PostgreSQL: Analysieren Sie es zuerst!

Ich habe kürzlich einen Artikel über den Vergleich von Hash- und B-Tree-Indizes geschrieben . Leider habe ich einen Fehler gemacht, und jetzt ist es an der Zeit, es richtig zu machen.

In diesem Artikel zeige ich Ihnen das PL/pgSQL -Skript mit einem schwerwiegenden Problem, das schwer zu erkennen ist. Dann zeige ich Ihnen meine Debugging-Geschichte, einschließlich einiger PostgreSQL-Interna. Und zu guter Letzt die gelernte Lektion und Ratschläge für Sie, um dieselben Fehler zu vermeiden.

Schauen wir uns das falsche Verfahren an.

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;

Sehen Sie etwas Verdächtiges? Ich auch nicht . Bevor ich sage, was hier falsch ist, erinnern wir uns gemeinsam an die Definition des Hash-Index.

Hash-Indizes speichern einen 32-Bit-Hash-Code , der vom Wert der indizierten Spalte abgeleitet wird.

Folgst du mir jetzt, wohin ich gehe?

Wenn der Hash-Index nur Hash-Indizes von indizierten Werten speichert, warum würde sich dann die Indexgröße für Zeichenfolgen mit unterschiedlichen Längen unterscheiden? Warum passiert das?

Wir beginnen unsere Recherche, indem wir nur den folgenden Teil unseres ursprünglichen Skripts ausführen und einige Statistiken mithilfe der pageinspectErweiterung überprüfen.

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

      
                

                    
                    
                
            

• ffactorlegt fest, wann ein Index mehr Speicherplatz für gespeicherte Daten zuweisen soll.
• maxbucketzeigt die aktuelle Anzahl der zugewiesenen Buckets (eine Liste sortierter Hash-Codes mit Zeigern auf Zeilen in der Tabelle).

Interessanterweise hat der erstellte Index in unserem Fall einen Standardwert ffactorvon 307 ( 75 % vom Maximum 409 ) mit maxbucketeinem Wert von 639 . Aber warum so viele Buckets, wenn wir nur 10.000 Zeilen indizieren?

Lassen Sie uns nun mithilfe der pageinspectErweiterung sehen, wie viele Zeilen (Tupel) sich in jedem Bucket befinden.

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

      
                

                    
                    
                
            

Hier ist ein interessantes Stück Code.

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

Lassen Sie uns diesen Code ausführen und sehen, ob wir Unterschiede feststellen.

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

      
                

                    
                    
                
            

Leider hätten beide Tabellen ohnehin eine ineffiziente Anzahl von Buckets in Hash-Indizes.

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

      
                

                    
                    
                
            

Zurück zum Anfang, lassen Sie uns das Verfahren zum Vergleichen von Hash- und B-Tree-Indizes korrigieren, indem wir analyzenach dem Füllen der Tabellen hinzufügen. Dies ist das richtige Diagramm, das die Ergebnisse für 10.000 Zeilen mit unterschiedlichen angegebenen Längen darstellt.

Die Ergebnisse machen jetzt mehr Sinn! Und sie sind viel inspirierender, da der Hash-Index jetzt 30- mal weniger Speicher benötigt als der B-Tree-Index ( für 10.000 Zeichenfolgen mit 1024 Zeichen Länge ).

analyzeist ein leistungsstarkes Instrument, das PostgreSQL dabei hilft, seine Arbeit effizient zu erledigen. Rufen Sie es also besser häufig als selten auf, insbesondere wenn Sie Benchmarks durchführen . Außerdem ermutige ich Sie, Ihre Indizes zu überprüfen und möglicherweise neu zu indizieren ( nach der Analyse natürlich )!

Basierend auf den obigen Recherchen denke ich, dass ich einen zweiten Versuch mit dem Artikel „Hash vs. B-Tree-Index“ machen werde.

Folgen Sie mir, um über meine kommenden Artikel benachrichtigt zu werden.