PostgreSQL: Önce onu analiz edin!

Geçenlerde Hash ve B-tree dizinlerini karşılaştırma hakkında bir makale yazdım . Ne yazık ki bir hata yaptım ve şimdi düzeltme zamanı.

Bu yazıda, yakalanması zor önemli bir sorunu olan PL/pgSQL betiğini size göstereceğim . Ardından, PostgreSQL'in bazı dahili bileşenleri de dahil olmak üzere hata ayıklama hikayemi size göstereceğim. Ve son olarak, aynı hatalardan kaçınmanız için öğrenilen dersler ve tavsiyeler.

Yanlış prosedüre bakalım .

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;

Şüpheli bir şey görüyor musun? Ben de yapmadım . Burada neyin yanlış olduğunu söylemeden önce Hash indeksinin tanımını birlikte hatırlayalım.

Hash indeksleri , indekslenmiş sütunun değerinden türetilen 32 bitlik bir hash kodunu saklar.

Şimdi nereye gittiğimi takip ediyor musun?

Karma dizini yalnızca dizine alınmış değerlerin karma dizinlerini saklıyorsa, dizin boyutu farklı uzunluklara sahip dizeler için neden farklı olsun? Bu neden oluyor?

İlk betiğimizden sadece aşağıdaki kısmı çalıştırarak araştırmamıza başlayacağız ve pageinspectuzantıyı kullanarak bazı istatistikleri kontrol edeceğiz.

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

      
                

                    
                    
                
            

• ffactorbir indeksin kaydedilen veriler için ne zaman daha fazla alan ayırması gerektiğini belirler.
• maxbucketgeçerli tahsis edilen bölüm sayısını gösterir (tablodaki satırlara yönelik işaretçilerle birlikte sıralanmış karma kodların bir listesi).

İlginç bir şekilde, bizim durumumuzda, oluşturulan dizin varsayılan ffactorolarak 307'ye ( maksimum 409'dan %75 ) ve 639'amaxbucket eşittir . Ancak, yalnızca 10.000 satırı dizine eklersek neden bu kadar çok kova var?

Şimdi, pageinspectuzantıyı kullanarak, her kovada kaç satır (demet) olduğunu görelim.

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

      
                

                    
                    
                
            

İşte ilginç bir kod parçası.

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

Bu kodu çalıştıralım ve herhangi bir farklılık yakalayıp yakalamadığımıza bakalım.

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

      
                

                    
                    
                
            

Ne yazık ki, her iki tablo da karma dizinlerde verimsiz sayıda kovaya sahip olacaktır.

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

      
                

                    
                    
                
            

Başa dönelim, Hash ve B-tree indekslerini karşılaştırma prosedürünü analyzetabloları doldurduktan sonra ekleyerek düzeltelim. Bu, farklı verilen uzunluklara sahip 10.000 satır için sonuçları temsil eden doğru grafik olacaktır .

Sonuçlar artık daha anlamlı! Ve çok daha ilham vericiler çünkü artık Hash indeksi , B-tree indeksinden ( 1024 karakter uzunluğunda 10.000 dizi için ) 30 kat daha az bellek kullanıyor .

analyzePostgreSQL'in işini verimli bir şekilde yapmasına yardımcı olan güçlü bir araçtır. Bu nedenle, özellikle kıyaslama yaparken, nadiren aramaktansa sık sık çağırmak daha iyidir . Ayrıca, indekslerinizi kontrol etmenizi ve belki yeniden indekslemenizi tavsiye ederim (elbette analiz ettikten sonra )!

Yukarıdaki araştırmaya dayanarak, “Hash vs. B-tree index” makalesinde ikinci bir girişimde bulunmayı düşünüyorum.

Gelecek yazılarımdan haberdar olmak için beni takip edin.