PostgreSQL: まず分析してください!

Dec 20 2022

最近、ハッシュインデックスと B ツリーインデックスの比較に関する記事を書きました。残念ながら、私は間違いを犯しました。今こそそれを正す時です。

この記事では、見つけにくい重大な問題がある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;

10.000 行

不審な点はありますか？私もそうしませんでした。ここで何が問題なのかを説明する前に、ハッシュインデックスの定義を一緒に思い出してみましょう。

ハッシュインデックスは、インデックス付きの列の値から派生した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));

      
                
                    
                    
       Partial results from hash_metapage_info

ffactorインデックスが保存されたデータにより多くのスペースをいつ割り当てるかを決定します。
maxbucket割り当てられたバケットの現在の数を示します (テーブル内の行へのポインターを含む、並べ替えられたハッシュコードのリスト)。

興味深いことに、私たちの場合、作成されたインデックスのデフォルトffactorは307 (最大値 409 の 75% ) でmaxbucket、639です。しかし、インデックスを 10.000 行しか付けないのに、なぜこれほど多くのバケットが必要なのでしょうか?

次に、pageinspect拡張機能を使用して、各バケットに含まれる行 (タプル) の数を見てみましょう。

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

      
                
                    
                    
       Partial results from hash_page_stats

ここに興味深いコードがあります。

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

      
                
                    
                    
       Results from pg_stat_user_tables

残念ながら、両方のテーブルのハッシュインデックスのバケット数は非効率的です。

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

      
                
                    
                    
       Partial results from hash_metapage_info

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

      
                
                    
                    
       Partial results from hash_metapage_info

analyze最初に戻って、テーブルを埋めた後に追加して、ハッシュと B-tree インデックスを比較する手順を修正しましょう。これは、与えられた長さが異なる 10.000 行の結果を表す正しいグラフになります。

分析で10.000行

結果はより意味のあるものになりました！また、ハッシュインデックスはB ツリーインデックスよりも30 分の 1 のメモリしか必要としないため ( 1024 文字の長さの 10.000 文字列の場合) 、はるかに刺激的です。

analyzeは、PostgreSQL がその作業を効率的に行うのに役立つ強力なツールです。したがって、特にベンチマークを行っている場合は、めったに呼び出すよりも頻繁に呼び出す方がよいでしょう。さらに、インデックスを確認し、場合によってはインデックスを再作成することをお勧めします (もちろん、分析後)。