PostgreSQL 실행계획 분석하기 2편 (Table Scan)

개요

• 실행계획을 정확히 분석하기 위해서는 PostgreSQL의 옵티마이저가 어떤 방식으로 테이블을 스캔하는지 알아야 한다.
• PostgreSQL에서 자주 사용되는 테이블 스캔 방식은 아래 4가지이다.
  • Seq Scan
  • Index Scan
  • Index Only Scan
  • Bitmap Scan
• 이외에도 Tid Scan, CTE Scan, Subquery Scan, Function Scan, Values Scan, Foreign Scan 등이 있지만 가장 자주 사용되는 위의 4가지 스캔방식에 대해서만 정리해 보려고 한다.

필수 사전 지식

• PostgreSQL 옵티마이저
• PostgreSQL 실행계획 분석하기 1편 (실행계획 읽는 방법)

테이블

• 아래와 같이 테이블을 하나 만들었다.

CREATE TABLE scan_test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN,
    score DECIMAL(5,2),
    data JSON
);

Seq Scan (Sequential Scan)

• Seq Scan는 테이블을 Full Scan 하면서 레코드를 읽는 방식이다.
• Seq Scan을 하는 경우는 크게 아래 3가지다.
  • 적절한 인덱스가 없는 경우
  • 테이블 크기에 비교해서 추출 비율이 큰 경우 (예를들어 1만건의 데이터중 9000건을 조회하는 경우)
  • 테이블의 크기가 적은 경우

 

적절한 인덱스가 없는 경우

데이터 세팅 (10000건)

• generate_series를 통해서 임의의 데이터 10000건을 생성했다.

INSERT INTO scan_test_table (name, created_at, is_active, score, data)
SELECT
    'Name ' || i,
    CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 100),
    i % 2 = 0,
    i::numeric / 100.0, 
    json_build_object('key', 'Value ' || i)
FROM generate_series(1, 10000) AS s(i);

• 아래 실행 계획을 보면, 현재 인덱스가 없기 때문에 Seq Scan을 통해서 테이블을 스캔하는 것을 확인할 수 있다.

 

 

테이블 크기에 비교해서 추출 비율이 큰 경우

• 테이블 크기가 1만건이고 1만건중 9000건을 조회한다고 가정하자. 참고로 PostgreSQL의 경우 PK에 대해서 인덱스가 자동으로 생성되어 있기 때문에 id에 대한 인덱스는 이미 생성되어 있다.

 

• 1만건 중에서 9000을 조회하기 때문에 옵티마이저는 인덱스를 사용하는것 보다 Seq Scan으로 테이블 Full Scan을 하는 것이 유리하다고 판단한다.

 

테이블의 크기가 적은 경우

데이터 세팅 (100건)

truncate scan_test_table;

INSERT INTO scan_test_table (name, created_at, is_active, score, data)
SELECT
    'Name ' || i,
    CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 100),
    i % 2 = 0,
    i::numeric / 100.0, 
    json_build_object('key', 'Value ' || i)
FROM generate_series(1, 100) AS s(i);

• 테이블에 데이터가 적은 경우, 인덱스가 있다고 하더라도 Seq Scan을 선택하기도 한다.
• 아래 사진을 보면, 인덱스를 만들었음에도 데이터가 100건 밖에 없기 때문에 Seq Scan이 선택된 것을 확인할 수 있다.

 

Index Scan

• 인덱스를 통해서 테이블을 스캔하는 방식을 말한다.
• 좀 더 정확히는 인덱스 Leaf 블록에 저장된 키를 이용해서 테이블 레코드를 액세스 하는 방식이다.
• where 절의 조회조건이 인덱스 사용이 유리할때 선택되는 스캔방식이다. 인덱스 사용이 유리하다는 것은 보통 아래 2가지 경우이다.
  • 크기가 큰 테이블에서 소량의 데이터를 추출할 경우
  • 인덱스 컬럼에 대한 정렬 상태가 좋은 경우 (블록 엑세스 횟수가 적기 때문)
• Index Scan는 레코드의 정렬 상태에 따라 테이블 블록 액세스 횟수가 크게 차이가 많이 나기 때문에 정렬 상태가 중요하다.

데이터 세팅 (10000건)

truncate scan_test_table;

INSERT INTO scan_test_table (name, created_at, is_active, score, data)
SELECT
    'Name ' || i,
    CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 100),
    i % 2 = 0,
    i::numeric / 100.0, 
    json_build_object('key', 'Value ' || i)
FROM generate_series(1, 10000) AS s(i);

• 아래와 같이 where 절에서 name을 기준으로 조회하는데, idx_scan_test_table_name라는 인덱스가 name을 기준으로 만들어져 있기 때문에 해당 인덱스를 통해서 테이블을 스캔하는 것을 확인할 수 있다.

Index Only Scan

• 인덱스에 포함된 데이터만으로 select를 할 수 있는 경우 선택되는 스캔방식이다.
• 커버링 인덱스라는 것이 바로 이 Index Only Scan을 가능하게 하는 인덱스이다.
• 아래와 같이 select에 name 만 조회하고 있기 때문에 인덱스만 탐색해도 결과를 찾을 수 있다.

Bitmap Scan

• 인덱스를 사용하여 여러 행을 대상으로 하는 비트맵을 생성하고, 이를 사용하여 필요한 행을 효율적으로 읽어오는 방식이다.
• Bitmap Scan은 테이블 랜덤 액세스 횟수를 줄이기 위한 스캔 방식이다.
• Bitmap Scan이 선택되는 경우는 인덱스 칼럼의 정렬상태가 불량할 때 주로 선택된다.
• 즉, 인덱스 컬럼의 Correlation 값에 따라 Index Scan과 Bitmap Scan 중 하나가 선택되는 것이다.

Correlation 값은 -1 ~ 1 사이의 값을 갖는다. 완전히 정렬되어 있는 경우 1, 역순으로 정렬되어 있는 경우 -1이다. 따라서 정렬이 안되어있는 값일 경우, -1과 1에서 멀리 떨어진. 즉 0과 가까울수록 정렬이 잘 안 되어있다고 생각할 수 있다.

• 먼저 아래와 같이 데이터를 넣어보자

데이터 세팅 (10000건)

• score 칼럼을 랜덤 하게 생성해서 정렬상태를 불량하게 만들었다.

truncate scan_test_table;

INSERT INTO scan_test_table (name, created_at, is_active, score, data)
SELECT
    'Name ' || i,
    CURRENT_TIMESTAMP - INTERVAL '1 day' * (i % 100),
    i % 2 = 0,
    ROUND((RANDOM() * 100)::numeric, 2), -- 정렬이 불량하도록 유도
    json_build_object('key', 'Value ' || i)
FROM generate_series(1, 10000) AS s(i);

• 그다음 score의 Correlation 값을 조회했다.

select tablename, attname, correlation
from pg_stats
where tablename = 'scan_test_table' and attname = 'score';

• 위와 같이 Correlation 값이 0과 매우 가깝다. 즉, 정렬이 잘 안 되어있다는 의미이다.
• 이제 실행계획을 살펴보자.

• 위 실행계획에서 알 수 있듯이 Bitmap Scan은 Bitmap Index Scan과 Bitmap Heap Scan으로 구성되어 있다.
• Bitmap Index Scan에서 인덱스를 스캔해서 테이블 위치 정보를 담는 비트맵을 생성한다.
• Bitmap Heap Scan에서 실제 테이블 데이터에 접근한다.

• 위 실행계획에서 Bitmap Index Scan 과정에서 인덱스 테이블 액세스 횟수가 3인것을 확인할 수 있다.
• 또한, 테이블 액세스 수가 99이고, 결론적으로 102번의 액세스가 발생한 것을 확인할 수 있다.
• Bitmap Scan은 데이블 랜덤 액세스를 줄이는 효과적인 스캔방법이라고 언급했다.
• 그럼 우선 위에서 저장한 10000건의 데이터가 몇 개의 테이블 블록에 저장되는지 확인해 보자.

• relpages가 테이블 블록의 개수이다.
• 즉 10000개의 레코드가 104개의 테이블 블록에 저장되어 있다는 의미이다.
• Bitmap Scan은 테이블 블록을 블록 번호순으로 정렬한다.
• 그렇기 때문에 테이블 블록은 최대 1번씩만 방문한다. 즉 104개의 테이블 블록에 데이터가 저장되어 있다면, 테이블블록은 104번 이상 액세스하지 않는다는 의미이다.
• 이제 테이블액세스 관점에서 실행계획을 다시 한번 분석해 보자.

• 데이터를 세팅할 때, score 값은 1~100 사이의 랜덤 한 값을 골고루 분포시켰다. 데이터 건수는 10000건이다.
• 따라서 1~100 사이에 값들이 104개의 테이블블록 골고루 분포되어 있을 것이다.
• 그렇기 때문에 between 1 and 5 조건으로 검색하면 104개의 테이블 중 거의 대부분의 테이블에 접근할 것이다.
• 띠라서 between 1 and 5를 하면 104개의 테이블 블록 중 99개의 테이블블록에 액세스 한 걸 확인할 수 있다.
• 그리고 shared hit = 3이라는 것은 인덱스 블록을 3번 액세스 했다는 의미이다.
• 이번엔 between 1 and 100을 해보자. 이것은 사실상 전체 데이터를 조회하는 것이다.

• 전체 데이터를 조회했기 때문에 104개의 테이블블록을 전부 액세스 한 것을 확인할 수 있다.
• 또한, 테이블블록은 최대 1번만 액세스 하기 때문에 104를 넘지 않는 것도 확인할 수 있다.
• 그렇다면 Index Scan과 테이블 블록 액세스 수의 차이를 비교해 보자.

• set enable_bitmapscan = off를 통해서 Index Scan을 유도했다.
• 이전과 마찬가지로 WHERE score between 1 and 5로 조회했다.
• Bitmap Scan의 경우, 3(인덱스액세스) + 99(테이블액세스)이었다.
• 하지만 Index Scan의 경우, 위와 같이 shared hit=404라는 값은 3(인덱스액세스) + 401(테이블액세스)인 것을 알 수 있다.
• 따라서 Bitmap Scan 은 테이블 액세스를 최소화하는 스캔방식이라는 것을 확인할 수 있었다.

정리

• 실행계획을 분석하는 데 있어서 스캔방식을 읽을 수 있는 것은 매우 중요하다.
• 옵티마이저가 스캔방식을 선택하는 기준을 다시 정리해 보면 아래와 같다.
• Seq Scan
  • 조회에 유리한 인덱스가 없는 경우
  • 테이블 크기 대비 추출 범위가 큰 경우
  • 테이블의 크기가 작은 경우
• Index Scan
  • 적절한 인덱스가 존재하는 경우
  • 테이블 크기 대비 추출 범위가 작은 경우
  • 인덱스 컬럼에 대한 정렬 상태가 좋은 경우
• Index Only Scan
  • 인덱스에 포함된 데이터만으로 select를 할 수 있는 경우 선택된다.
• Bitmap Scan
  • 조회에 유리한 인덱스가 있으나, 인덱스에 포함된 칼럼의 정렬상태가 불량할 때 선택된다.
• 지금까지 배운 내용을 도식화해서 정리하면 아래와 같다.물론, 옵티마이저가 스캔방식을 선택하는 과정을 훨씬 다양한 요소를 체크한다.

• 따라서 위 그림처럼 동작하지 않을 확률도 매우 높다.

References

• PostgreSQL 9.6 성능이야기, 저자 김시연, 최두원
• https://www.postgresql.org/docs/10/using-explain.html