[PostgreSQL] PostgreSQL의 쿼리 플랜에 대해서 알아보자. (Query Plan)

데이터베이스 엔진은 쿼리를 수행할 때 어떤 방식을 사용하고 어떤 순서로 쿼리를 수행 할지에 대한 계획을 세운다.

 

PSQL에서는 쿼리 실행 계획(Query Execution Plan) 또는 쿼리 플랜(Query Plan)이라고 한다.

 

 

사용하는 키워드

- EXPLAIN : SQL구문을 이용해 쿼리 플랜 확인

- ANALYZE : 실행 시간을 포함한 구체적인 실행 계획을 분석

 

 

우선 쿼리 플랜을 실습하기 위해 간단한 테이블을 만들어보자.

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


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

 

좀 복잡해 보일 수 있는데 간단히 보면 아래와 같다.

인덱스 게시물에서 설명하겠지만, PRIMARY KEY로 선언하면 해당 컬럼에 인덱스가 자동 생성된다.

그래서 id 컬럼에 인덱스가 걸려있다고 생각하면 된다.

 

 

 

---

 

1. Sequencial Scan

 

테이블의 모든 데이터를 하나씩 확인하는 방법
주로 인덱스가 없는 Column을 조건으로 검색할 경우 사용됨.

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

 Result

 

 

 

 

2. Index Scan (1)

 

인덱스를 탐색하는 방식
인덱스가 만들어진 Column을 조건문으로 조회하는 방식.

EXPLAIN ANALYZE
SELECT * FROM post WHERE id = 5432;

Result

 

 

 

 

3. Index Scan (2)

 

범위 탐색에도 Index Scan이 적용될 수 있다.

EXPLAIN ANALYZE
SELECT * FROM post WHERE id < 5432;

Result

 

 

 

 

4. Index Scan (3)

 

ORDER BY [COLUMN] DESC로 검색하면

인덱스가 안걸릴 수 도 있겠다라고 생각하겠지만

인덱스를 역으로 찾는 방식으로 스캔할 수 있다.

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

Result

 

 

 

 

5. Index Scan (4)

 

많은 데이터를 가져올때는 Seq Scan이 효과적이다.

때문에 쿼리 플랜은 더욱 효과적인 방법을 찾는다.

EXPLAIN ANALYZE
SELECT id FROM post WHERE id > 5432;

Result

 

 

 

 

6. Index Only Scan

 

index로 걸린 컬럼만 조회한다면

쿼리 플랜은 실제 컬럼에 직접 접근하지 않고

인덱스에 있는 값만 가져오므로

Index Only Scan이 된다.

EXPLAIN ANALYZE
SELECT id FROM post;

 Result

 

 

 

7. Bitmap Scan

 

Index Scan과 Sequential Scan이 조합된 방식
인덱스를 돌리기엔 애매하게 많고, 순차를 돌리기엔 애매하게 적을 때
인덱스 스캔에서 발생할 수 있는 과도한 랜덤IO를 방지하고 인덱스 성능도 가져올 수 있다.
무겁고 경직된 BTree가 아니라 효율이 좋다.

CREATE INDEX idx_author ON post(author);

 

 

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

Result

 

 

 

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

Result

 

 

 

 

 

REFERENCES

PostgreSQL : Documentation: 13: PostgreSQL 13.7 Documentation

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