PostgreSQL: najpierw przeanalizuj!

Niedawno napisałem artykuł o porównywaniu indeksów Hash i B-tree . Niestety popełniłem błąd i teraz czas go naprawić.

W tym artykule pokażę skrypt PL/pgSQL z istotnym problemem, który trudno wychwycić. Następnie pokażę ci moją historię debugowania, w tym niektóre elementy wewnętrzne PostgreSQL. I wreszcie, wyciągnięta lekcja i porady, aby uniknąć tych samych błędów.

Przyjrzyjmy się niewłaściwej procedurze.

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;

Czy widzisz coś podejrzanego? Ja też nie . Zanim powiem, co tu jest nie tak, przypomnijmy sobie razem definicję indeksu Hash.

Indeksy skrótu przechowują 32-bitowy kod skrótu pochodzący z wartości indeksowanej kolumny.

Czy śledzisz teraz, dokąd zmierzam?

Jeśli indeks skrótu przechowuje tylko indeksy skrótu indeksowanych wartości, dlaczego rozmiar indeksu miałby się różnić dla łańcuchów o różnych długościach? Dlaczego tak się dzieje?

Rozpoczniemy nasze badania od uruchomienia tylko poniższej części z naszego początkowego skryptu i sprawdzenia niektórych statystyk za pomocą pageinspectrozszerzenia.

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

      
                

                    
                    
                
            

• ffactorokreśla, kiedy indeks powinien przeznaczyć więcej miejsca na zapisane dane.
• maxbucketpokazuje aktualną liczbę przydzielonych kubełków (lista posortowanych kodów skrótu ze wskaźnikami do wierszy w tabeli).

Co ciekawe, w naszym przypadku utworzony indeks ma wartość domyślną ffactorrówną 307 ( 75% z maksimum 409 ) przy wartości maxbucketrównej 639 . Ale po co tyle wiader, skoro indeksujemy tylko 10 000 wierszy?

Teraz, korzystając z pageinspectrozszerzenia, zobaczmy, ile wierszy (krotek) znajduje się w każdym zasobniku.

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

      
                

                    
                    
                
            

Oto ciekawy fragment kodu.

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

Uruchommy ten kod i zobaczmy, czy wyłapiemy jakieś różnice.

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

      
                

                    
                    
                
            

Niestety, obie tabele i tak miałyby nieefektywną liczbę segmentów w indeksach mieszania.

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

      
                

                    
                    
                
            

Wróćmy do początku, poprawmy procedurę porównywania indeksów Hash i B-drzewa, dodając analyzepo wypełnieniu tabel. To będzie prawidłowy wykres przedstawiający wyniki dla 10 000 wierszy o różnych podanych długościach.

Wyniki mają teraz większy sens! I są o wiele bardziej inspirujące, ponieważ teraz indeks Hash zajmuje 30x mniej pamięci niż indeks B-drzewa ( dla 10.000 łańcuchów o długości 1024 znaków ).

analyzeto potężne narzędzie, które pomaga PostgreSQL wydajnie pracować. Dlatego lepiej jest wywoływać to często niż rzadko, zwłaszcza gdy wykonujesz testy porównawcze . Ponadto zachęcam do sprawdzenia swoich indeksów i być może ponownego ich indeksowania ( oczywiście po przeanalizowaniu )!

Na podstawie powyższych badań myślę, że podejmę drugą próbę artykułu „Hash vs. B-tree index”.

Śledź mnie, aby otrzymywać powiadomienia o moich nadchodzących artykułach.