PostgreSQL: Analisis dulu!

Saya baru-baru ini menulis sebuah artikel tentang membandingkan indeks Hash dan B-tree . Sayangnya, saya membuat kesalahan, dan sekarang saatnya memperbaikinya.

Pada artikel ini, saya akan menunjukkan kepada Anda skrip PL/pgSQL dengan masalah signifikan yang sulit ditangkap. Kemudian, saya akan menunjukkan kisah debug saya, termasuk beberapa internal PostgreSQL. Dan yang tak kalah pentingnya, pelajaran yang dipelajari dan nasihat bagi Anda untuk menghindari kesalahan yang sama.

Mari kita lihat prosedur yang salah .

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;

Apakah Anda melihat sesuatu yang mencurigakan? Saya juga tidak . Sebelum saya mengatakan apa yang salah di sini, mari kita ingat kembali definisi indeks Hash.

Indeks hash menyimpan kode hash 32-bit yang berasal dari nilai kolom yang diindeks.

Apakah Anda mengikuti sekarang ke mana saya pergi?

Jika indeks hash hanya menyimpan indeks hash dari nilai yang diindeks, mengapa ukuran indeks berbeda untuk string dengan panjang berbeda? Mengapa ini terjadi?

Kami akan memulai penelitian kami dengan hanya menjalankan bagian di bawah ini dari skrip awal kami dan memeriksa beberapa statistik menggunakan pageinspectekstensi.

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

      
                

                    
                    
                
            

• ffactormenentukan kapan indeks harus mengalokasikan lebih banyak ruang untuk data yang disimpan.
• maxbucketmenunjukkan jumlah ember yang dialokasikan saat ini (daftar kode hash yang diurutkan dengan penunjuk ke baris dalam tabel).

Menariknya, dalam kasus kami, indeks yang dibuat memiliki default ffactorsama dengan 307 ( 75% dari maksimum 409 ) maxbucketsama dengan 639 . Tapi mengapa begitu banyak ember jika kita hanya mengindeks 10.000 baris?

Sekarang, dengan menggunakan pageinspectekstensi, mari kita lihat berapa banyak baris (tuple) yang ada di setiap keranjang.

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

      
                

                    
                    
                
            

Ini adalah potongan kode yang menarik.

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

Mari jalankan kode ini dan lihat apakah kita menemukan perbedaan.

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

      
                

                    
                    
                
            

Sayangnya, kedua tabel akan memiliki jumlah ember yang tidak efisien dalam indeks 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));

      
                

                    
                    
                
            

Kembali ke awal, mari kita perbaiki prosedur untuk membandingkan indeks Hash dan B-tree dengan menambahkan analyzesetelah mengisi tabel. Ini akan menjadi bagan yang benar yang mewakili hasil untuk 10.000 baris dengan panjang tertentu yang berbeda.

Hasilnya sekarang lebih masuk akal! Dan mereka jauh lebih menginspirasi karena sekarang indeks Hash membutuhkan memori 30x lebih sedikit daripada indeks B-tree ( untuk 10.000 string dengan panjang 1024 karakter ).

analyzeadalah instrumen ampuh yang membantu PostgreSQL melakukan pekerjaannya secara efisien. Jadi lebih baik sering menelepon daripada jarang, terutama saat Anda melakukan benchmark . Selain itu, saya mendorong Anda untuk memeriksa indeks Anda dan mungkin mengindeks ulang ( setelah menganalisis, tentu saja )!

Berdasarkan penelitian di atas, saya rasa saya akan melakukan upaya kedua pada artikel "Hash vs. B-tree index".

Ikuti saya untuk diberitahu tentang artikel saya yang akan datang.