PostgreSQL: วิเคราะห์ก่อน!

ฉันเพิ่งเขียนบทความเกี่ยวกับการเปรียบเทียบดัชนี Hash และ B- tree น่าเสียดาย ฉันทำผิดพลาดไป และตอนนี้ได้เวลาแก้ไขแล้ว

ในบทความนี้ ฉันจะแสดงให้คุณเห็น สคริปต์ 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;

คุณเห็นอะไรที่น่าสงสัยหรือไม่? ฉันก็เช่นกัน ก่อนที่ฉันจะพูดว่ามีอะไรผิดปกติที่นี่ เรามานึกถึงคำจำกัดความของดัชนีแฮชกันก่อน

ดัชนีแฮชเก็บรหัสแฮช 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));

      
                

                    
                    
                
            

• ffactorกำหนดว่าเมื่อใดที่ดัชนีควรจัดสรรพื้นที่เพิ่มเติมสำหรับข้อมูลที่บันทึกไว้
• maxbucketแสดงจำนวนถังที่จัดสรรในปัจจุบัน (รายการรหัสแฮชที่เรียงลำดับพร้อมตัวชี้ไปยังแถวในตาราง)

ที่น่าสนใจคือ ในกรณีของเรา ดัชนีที่สร้างขึ้นมีค่าเริ่มต้นffactorเท่ากับ307 ( 75% จากสูงสุด 409 ) โดยmaxbucketเท่ากับ639 แต่ทำไมมีที่เก็บข้อมูลจำนวนมากถ้าเราจัดทำดัชนีเพียง 10.000 แถว

ตอนนี้ใช้pageinspectส่วนขยาย มาดูกันว่ามีกี่แถว (ทูเพิล) ในแต่ละบัคเก็ต

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

      
                

                    
                    
                
            

นี่คือโค้ดที่น่าสนใจ

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

      
                

                    
                    
                
            

น่าเสียดายที่ทั้งสองตารางจะมีจำนวนบัคเก็ตที่ไม่มีประสิทธิภาพในดัชนีแฮชอยู่ดี

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

      
                

                    
                    
                
            

กลับไปที่จุดเริ่มต้น เรามาแก้ไขขั้นตอนการเปรียบเทียบดัชนี Hash และ B-tree โดยเพิ่มanalyzeหลังจากเติมข้อมูลในตารางแล้ว นี่จะเป็น แผนภูมิ ที่ถูกต้องซึ่งแสดงผลลัพธ์สำหรับ 10,000 แถวที่มีความยาวต่างกัน

ผลลัพธ์ตอนนี้สมเหตุสมผลมากขึ้น! และเป็นแรงบันดาลใจมากขึ้นเพราะตอนนี้ดัชนีแฮชใช้หน่วยความจำน้อยกว่าดัชนี B-tree ถึง 30 เท่า ( สำหรับ 10,000 สตริงที่มีความยาว 1,024 อักขระ )

analyzeเป็นเครื่องมืออันทรงพลังที่ช่วยให้ PostgreSQL ทำงานได้อย่างมีประสิทธิภาพ ดังนั้นจึงเป็นการดีกว่าที่จะเรียกใช้บ่อยๆ โดยเฉพาะเมื่อคุณทำการเปรียบเทียบ นอกจากนี้ ฉันขอแนะนำให้คุณตรวจสอบดัชนีของคุณและอาจทำดัชนีใหม่ ( หลังจากวิเคราะห์แล้ว )!

จากการวิจัยข้างต้น ฉันคิดว่าฉันจะลองบทความ "Hash vs. B-tree index" เป็นครั้งที่สอง

ติดตามฉันเพื่อรับการแจ้งเตือนเกี่ยวกับบทความที่กำลังจะมาถึงของฉัน