[PG] 쿼리 실행 계획 분석하기 - Table Scan

데이터베이스에 날릴 쿼리를 최적화하기 위해서는, 데이터베이스가 실제로 쿼리를 실행하는 방식과 해당 쿼리의 성능을 알고 있어야 한다. 그러기 위해서 데이터베이스의 쿼리 실행 계획(Query execution plan)을 보고 분석할 수 있어야 한다.

PostgreSQL의 실행 계획을 분석하기 위해서 PostgreSQL가 테이블을 조회하는 방식인 테이블 스캔(Table Scan) 방식에 대해서 제일 먼저 알아보고 정리하려고 한다.

테스트 데이터

테스트할 테이블은 다음 쿼리로 생성했다. 게시판의 게시물에 해당하는 테이블이고, 사용자 테이블은 생성하지 않은 관계로 외래 키 참조는 생략했다.

CREATE TABLE post (
    id serial PRIMARY KEY,
    title varchar(255),
    author varchar(255),
    created_at timestamp
);

데이터는 다음 쿼리로 생성했다. 'title{i}'라는 서로 다른 제목을 갖는 100만 개의 게시물이 생성되고, 사용자는 총 100명으로 'author{0-100}'의 이름을 갖는다. 100명의 사용자가 10,000개씩 게시물을 작성한 상황이다. 생성 날짜는 모두 다르게 설정했다.

DO $$
DECLARE
    i INTEGER := 1;
BEGIN
    WHILE i < 1000000 LOOP
        INSERT INTO post(title, author, created_at)
            VALUES(CONCAT('title', i), CONCAT('author', i % 100), now() + i * INTERVAL '1 second');
        i := i + 1;
    END LOOP;
END $$;

Query Plan이란?

데이터베이스 엔진은 쿼리를 수행할 때 어떤 방식을 사용하고 어떤 순서로 쿼리를 수행 할지에 대한 계획을 세우게 되는데, 이것을 쿼리 실행 계획(Query execution plan) 또는 쿼리 플랜(Query plan)이라고 부른다.

PostgreSQL과 MySQL 모두 EXPLAIN 이라는 SQL구문을 이용해 쿼리 플랜을 확인할 수 있다. 그리고 성능 분석을 할 때는 보통 ANALYZE 문까지 붙여서 실행 시간을 포함한 구체적인 실행 계획을 분석한다.

자세한 실행 계획에 대한 정보는 공식 문서에서 확인할 수 있다.

Table Scan

PostgreSQL은 다음 5가지 스캔 방식을 사용한다.

• Sequential Scan
• Index Scan
• Index Only Scan
• Bitmap Scan
• TID Scan

1. Sequential Scan

먼저 Sequential Scan은 테이블의 모든 데이터를 하나씩 확인하는 방법이다. 주로 인덱스가 없는 column을 조건으로 검색할 경우에 사용된다.

EXPLAIN ANALYZE
SELECT * FROM post WHERE title = 'title5432';

위 쿼리는 2단계의 실행 계획으로 이루어졌다. 각 단계는 4번째 줄의 Seq Scan과 1번째 줄의 Gather이다. 화살표로 인덴트된 실행 계획은 child plan으로, 해당 단계에서 실행된 결과를 상위 단계에서 처리한다는 뜻으로, 안쪽 인덴트에서 바깥으로 실행계획의 흐름을 따라가야 한다.

title은 인덱스가 없는 column이기 때문에 테이블의 모든 데이터를 조회하는 방법밖에 없다. 위 결과를 보자면 PostgreSQL은 2개의 워커 프로세스를 생성해 병렬적으로 테이블을 순차 스캔했으며, 각 워커 프로세스가 찾아낸 row를 종합해서 쿼리를 수행했음을 알 수 있다. (Parallel Seq Scan)

실행 계획에서 또 다른 중요한 키워드는 Rows Removed by Filter이다. 이 결과는 테이블의 row를 조회했으나, 우리가 찾는(title = 'title543332') row가 아니어서 버려진 row의 수를 나타낸다. 이 수가 적을수록 최적화된 쿼리라고 할 수 있다.

3번의 루프(loop=3)를 돌면서 333,333개의 row를 버렸으니 1개의 row를 찾기 위해 100만 개의 row를 모두 다 조회했다는 뜻이다.

마지막 줄에서는 실제 실행에 소요된 시간을 확인할 수 있다.

2. Index Scan

Index Scan은 말그대로 인덱스를 탐색하는 방식을 말한다. 인덱스가 만들어진 column을 조건문으로 조회할 때 데이터베이스가 선택하는 방식이다.

EXPLAIN ANALYZE
SELECT * FROM post WHERE id = 5432;

id column은 PK로 지정했기 때문에 테이블을 생성할 때 인덱스가 자동으로 생성되고 정렬된다. 그렇기 때문에 id로 조회할 때 Index Scan을 이용해서 필요한 row만 가져올 수 있다. 그렇기 때문에 실행시간도 스캔 방법 중에 가장 빠르다.

EXPLAIN ANALYZE
SELECT * FROM post WHERE id < 5432;

범위 조회에서도 Index Scan을 사용하는 것을 확인할 수 있다.

EXPLAIN ANALYZE
SELECT * FROM post WHERE id < 5432 ORDER BY id DESC;

인덱스는 항상 정렬되어있기 때문에 역순으로 조회가 가능하다. 따라서 Index Scan Backward 방식을 이용해서 id를 역순으로 조회하여 쿼리를 효율적으로 처리하는 것을 확인할 수 있다.

재밌는 것은 인덱스가 있는 column에서도 Sequential Scan 방식이 사용될 수 있다는 것이다.

EXPLAIN ANALYZE
SELECT id FROM post WHERE id > 5432;

100만 개의 row 중에 5432개의 row를 제외한 99만 4568개의 row를 가져오는 쿼리이다.  데이터베이스 엔진이 이 수를 계산해서 인덱스를 조회하고 데이터에 접근하는 것보다 바로 데이터를 조회하는 것이 더 낫다고 판단하고, Index Scan 대신 Sequential Scan을 사용했다.

이처럼 데이터베이스 엔진은 인덱스가 있다고 Index Scan을 사용하는 것이 아니라, 해당 쿼리에 적합하다고 판단되는 방식을 이용하는 것을 확인할 수 있다.

3. Index Only Scan

Index Only Scan은 인덱스에 필요한 데이터가 있는 경우 사용되는 방식이다. 인덱스에 필요한 값이 있기 때문에, 실제 데이터를 fetch하지 않아도 된다.

EXPLAIN ANALYZE
SELECT id FROM post;

위 쿼리처럼 필요한 컬럼 값이 인덱스에 이미 존재할 때 사용된다.

4. Bitmap Scan

Bitmap Scan은 Index Scan과 Sequential Scan이 조합된 방식이다. 데이터베이스가 Bitmap Scan을 이용하도록 author 컬럼을 인덱스로 생성한 후 author로 검색을 해보았다.

CREATE INDEX idx_author ON post(author);

EXPLAIN ANALYZE
SELECT id FROM post WHERE id < 600000 AND author = 'author54';

데이터베이스에서 첫 번째로 Bitmap Index Scan이 일어나고, 두 번째로 Bitmap Heap Scan이 일어난 것을 확인할 수 있다.

Bitmap Index Scan 단계에서 인덱스 자료구조를 스캔하며 조건에 해당하는 인덱스의 데이터를 이용해서 bitmap을 생성한다. 이 bitmap에는 인덱스에 해당하는 테이블의 row에 접근할 수 있는 정보가 담겨 있다.

Bitmap Heap Scan 단계에서는 앞서 생성된 bitmap을 스캔하면서 조건에 맞는 실제 데이터를 가져오게 된다.

위 예시에서는 author 인덱스를 이용해서 author가 'author54'인 10,000개의 row에 접근할 수 있는 bitmap을 생성하고, id가 600,000이하인 6,000개의 row를 결과로 가져왔다.

여기서도 재밌는 것은 가져와야 할 데이터가 줄어들면(이 상황에서는 id 의 범위 조건이 줄어들면) 데이터베이스가 Index Scan을 사용한다는 것이다.

EXPLAIN ANALYZE
SELECT id FROM post WHERE id < 200000 AND author = 'author54';

id 조건 범위를 200,000으로 줄였더니 데이터베이스가 Parallel Index Scan을 사용한다. 데이터베이스가 조건문에 사용할 column의 인덱스 유무 뿐만 아니라 실제로 가져와야 할 데이터의 크기도 고려해서 Table Scan 방법을 정한다는 것을 확인할 수 있다.

5. TID Scan

TID Scan은 데이터베이스가 실제 테이블의 데이터를 식별하기 위해 사용하는 TID라는 것을 이용한 쿼리를 사용할 때 사용된다.

EXPLAIN ANALYZE
SELECT id FROM post WHERE ctid = '(1, 1)';

이 TID는 Tuple Indecator의 줄임말인데 특정 row의 실제 위치를 나타내는 6byte 값으로, 4byte는 페이지 번호, 2byte는 페이지 내의 튜플 인덱스를 나타낸다고 한다(PostgreSQL 버전의 rowid개념으로 보인다).

공부를 하다가 알게 된 사실이기도 하고 사용해본 적도 없어서 실제 실무에서 TID 번호로 쿼리를 날리는 상황이 있을까 하는 궁금증을 갖게 되는 Table Scan 방식이다.

Conclusion

쿼리 최적화를 위한 쿼리 실행 계획 분석에 있어서 가장 먼저 PostgreSQL의 Table Scan 방식들에 대해서 알아보았다. 데이터베이스는 똑같은 쿼리라도 해당 테이블의 인덱스, 쿼리로 가져올 범위 등을 계산해서 적절한 쿼리 플랜을 짜게 된다는 것을 알았다. 그렇기 때문에 인덱스를 생성하거나 쿼리를 작성할 때 항상 쿼리 플랜을 분석하고 데이터베이스의 행동 방식을 이해하기 위해 노력해야겠다.

References

• https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/
• https://www.postgresql.org/docs/10/using-explain.html
• https://severalnines.com/database-blog/overview-various-scan-methods-postgresql