PostgreSQL: Hãy phân tích nó trước!

Gần đây tôi đã viết một bài báo về việc so sánh các chỉ mục Hash và B-tree . Thật không may, tôi đã phạm sai lầm, và bây giờ là lúc để sửa sai.

Trong bài viết này, tôi sẽ chỉ cho bạn tập lệnh PL/pgSQL với một vấn đề nghiêm trọng khó phát hiện. Sau đó, tôi sẽ cho bạn xem câu chuyện sửa lỗi của mình, bao gồm một số nội dung bên trong PostgreSQL. Và cuối cùng nhưng không kém phần quan trọng là bài học kinh nghiệm và lời khuyên cho bạn để tránh những sai lầm tương tự.

Hãy xem xét thủ tục sai .

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;

Bạn có thấy điều gì khả nghi không? Tôi cũng không . Trước khi tôi nói điều gì sai ở đây, chúng ta hãy cùng nhau nhắc lại định nghĩa của chỉ mục Hash.

Các chỉ mục băm lưu trữ mã băm 32 bit lấy từ giá trị của cột được lập chỉ mục.

Bây giờ bạn có đang theo dõi nơi tôi đang đi không?

Nếu chỉ mục băm chỉ lưu trữ chỉ mục băm của các giá trị được lập chỉ mục, thì tại sao kích thước chỉ mục lại khác nhau đối với các chuỗi có độ dài khác nhau? Tại sao điều này xảy ra?

Chúng tôi sẽ bắt đầu nghiên cứu của mình bằng cách chỉ chạy phần bên dưới từ tập lệnh ban đầu của chúng tôi và kiểm tra một số thống kê bằng cách sử dụng pageinspecttiện ích mở rộng.

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

      
                

                    
                    
                
            

• ffactorxác định khi nào một chỉ mục sẽ phân bổ nhiều không gian hơn cho dữ liệu đã lưu.
• maxbuckethiển thị số lượng nhóm được phân bổ hiện tại (danh sách các mã băm được sắp xếp có con trỏ tới các hàng trong bảng).

Thật thú vị, trong trường hợp của chúng tôi, chỉ mục được tạo có giá trị mặc định ffactorbằng 307 ( 75% từ mức tối đa 409 ) với giá trị maxbucketbằng 639 . Nhưng tại sao có quá nhiều nhóm nếu chúng ta chỉ lập chỉ mục 10.000 hàng?

Bây giờ, bằng cách sử dụng pageinspecttiện ích mở rộng, hãy xem có bao nhiêu hàng (bộ) trong mỗi nhóm.

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

      
                

                    
                    
                
            

Đây là một đoạn mã thú vị.

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

Hãy chạy mã này và xem liệu chúng ta có nhận thấy bất kỳ sự khác biệt nào không.

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

      
                

                    
                    
                
            

Thật không may, dù sao cả hai bảng sẽ có số lượng nhóm không hiệu quả trong chỉ mục băm.

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

      
                

                    
                    
                
            

Quay lại từ đầu, hãy sửa quy trình so sánh các chỉ mục Hash và B-tree bằng cách thêm analyzesau khi điền vào các bảng. Đây sẽ là biểu đồ chính xác biểu thị kết quả cho 10.000 hàng có độ dài nhất định khác nhau.

Các kết quả bây giờ có ý nghĩa hơn! Và chúng truyền cảm hứng hơn nhiều vì giờ đây, chỉ mục Hash chiếm ít bộ nhớ hơn 30 lần so với chỉ mục B-tree ( đối với 10.000 chuỗi có độ dài 1024 ký tự ).

analyzelà một công cụ mạnh mẽ giúp PostgreSQL thực hiện công việc của mình một cách hiệu quả. Vì vậy, tốt hơn là nên gọi nó thường xuyên hơn là hiếm khi, đặc biệt là khi bạn đang thực hiện các điểm chuẩn . Hơn nữa, tôi khuyến khích bạn kiểm tra các chỉ mục của mình và có thể lập chỉ mục lại chúng ( tất nhiên là sau khi phân tích )!

Dựa trên nghiên cứu trên, tôi nghĩ rằng tôi sẽ thử lần thứ hai với bài viết “Chỉ mục băm so với chỉ mục cây B”.

Theo dõi tôi để được thông báo về các bài viết sắp tới của tôi.