PostgreSQL: 먼저 분석하세요!

나는 최근 에 해시와 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;

수상한 점이 보이시나요? 나도 마찬가지 였다. 여기서 잘못된 점을 말하기 전에 해시 인덱스의 정의를 함께 떠올려 봅시다.

해시 인덱스 는 인덱싱된 열의 값에서 파생된 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 ( 최대 409에서 75% )이고 maxbucket639 입니다 . 하지만 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));

      
                

                    
                    
                
            

처음으로 돌아가서 analyze테이블을 채운 후 추가하여 Hash 및 B-tree 인덱스를 비교하는 절차를 수정하겠습니다. 이것은 주어진 길이가 다른 10,000개의 행에 대한 결과를 나타내는 올바른 차트입니다.

이제 결과가 더 이해가 됩니다! 그리고 이제 해시 인덱스가 B-트리 인덱스보다 30배 적은 메모리를 사용 하기 때문에 훨씬 더 고무적 입니다(1024자 길이의 10.000개 문자열 ).

analyzePostgreSQL이 작업을 효율적으로 수행하도록 도와주는 강력한 도구입니다. 따라서 특히 벤치마크 를 수행할 때 드물게 호출하는 것보다 자주 호출하는 것이 좋습니다 . 또한 색인을 확인하고 다시 색인화할 것을 권장합니다( 물론 분석 후 )!

위의 연구를 바탕으로 “Hash vs. B-tree index” 글에서 두 번째 시도를 해볼 생각입니다.

내 다가오는 기사에 대한 알림을 받으려면 나를 따르십시오.