안녕하세요. 어느덧 4월이 찾아왔습니다.
2024년이 시작된 것이 엊그제 같은데 정말 많은 일들이 있었네요 ㄷㄷㄷ
이번에는 PostgreSQL EXPLAIN 명령어에 대해서 조금 더 자세히 공부해보려고 합니다.
EXPLAIN 명령어를 공부한 적이 있었지만 지금 보니 많이 까먹었더라고요 ㅜㅠ
이참에 자세히 공부해보겠습니다!
0. EXPLAIN 명령어 옵션에는 어떤 것들이 있을까요?
이전에는 ANALYZE 옵션만 사용했는데 이번 기회에 다른 파라미터들도 사용하면서 공부해보려고 합니다.
PostgreSQL 데이터베이스에 테스트 데이터를 채운 상태로 각 옵션을 활성화/비활성화 시켰을 때의 결과값을 비교해보도록 하겠습니다.
- 예전에 진행했던 DB 테이블 로테이션 실험을 참고하여 테스트 데이터를 만들었습니다.
- 참고: 데이터베이스 테이블 rotation - DB에 데이터가 계속해서 쌓여서 용량이 꽉차면 어떡하죠...?
- 스키마
CREATE TABLE messages(
id UUID PRIMARY KEY,
date TIMESTAMP NOT NULL, -- message 데이터가 생성된 시각을 의미합니다.
content VARCHAR(128) -- message 데이터의 내용을 의미합니다.
);
- 데이터 갯수: 1,225,842개
1. ANALYZE 옵션(기본값 false)
ANALYZE 옵션은 쿼리를 직접 실행하면서 시간을 측정해줍니다.
다음 쿼리를 이용해서 ANALYZE 옵션을 사용했을 때와 사용하지 않았을 때를 비교해보겠습니다.
SELECT id, date, content FROM messages WHERE date <= '2024-04-01T09:42:57Z' AND date >= '2024-04-01T09:42:56Z';
일단 출력된 결과값이 4줄 늘었는데요 ㅋㅋㅋ
어떤 것들이 추가되었는지 하나씩 살펴보겠습니다.
1.1. actual time
ANALYZE 옵션을 추가할 때 보이는 actual time은 쿼리가 실행되는데 걸린 시간(밀리초 단위)을 의미합니다.
원래는 cost만 출력되다가 ANALYZE 옵션을 추가하면서 actual time이 추가되기 때문에
cost와 actual time이 서로 비례할 것이라고 생각하기 쉽습니다.
하지만 cost는 정해지지 않은 임의의 단위를 사용하고, actual time은 밀리초 단위를 사용하기 때문에
둘 사이의 연관성은 거의 없다고 봐도 됩니다!
1.2. Workers Launched
`Workers Launched`는 요청 받은 쿼리를 실행한 워커의 수를 나타냅니다.
※ 워커란?
PostgreSQL에서는 병렬 처리를 활용하여 쿼리의 실행 속도를 높일 수 있는데 요청 받은 쿼리를 몇 개의 프로세스가 실행하는지 보여주는 값이 Workers Planned, Workers Launched 값입니다.
다음 링크를 참고하면 언제 병렬 처리가 실행될 수 있고, 작업 별로 어떻게 병렬적으로 처리되는지 설명되어 있습니다.
- https://www.postgresql.org/docs/current/parallel-query.html
2. VERBOSE(기본값 false)
VERBOSE 옵션은 쿼리 실행 계획에 다음과 같은 추가적인 정보를 보여줍니다.
- 쿼리 결과에 대한 column
- ANALYZE 옵션과 같이 사용할 때 워커 별 실행 시간
3. COSTS(기본값 true)
COSTS 옵션은 쿼리 실행 계획의 비용 관련 정보를 표시합니다.
기본적으로 COSTS 옵션은 true 이기 때문에 여러분이 보던 EXPLAIN 결과는 주로 COSTS 옵션이 true였을 거에요.
COSTS 옵션을 false로 하는 경우가 드물겠지만 다음과 같이 결과값이 차이납니다.
4. SETTINGS(기본값 false)
문서를 보면 SETTINGS 옵션은 쿼리 실행 시 적용된 PostgreSQL 설정 값을 보여준다고 설명되어 있는데...!
실행해보니 결과값이 달라지지 않았습니다 ㅜ
쿼리가 너무 단순해서 그런가 싶은데... 이번 테스트에서는 유의미한 차이를 못 찾았습니다.
5. GENERIC_PLAN(기본값 false)
GENERIC_PLAN 옵션을 사용하면 EXPLAIN 명령어에 $1과 같은 변수를 사용할 수 있게 만들어줍니다.
그리고 결과는 변수와 무관한 부분에 대해서 COST를 계산합니다.
- PostgreSQL 버전 16부터 지원하는 옵션입니다.
- 테스트 중 unrecognized 에러가 떠서 기존에 사용하고 있던 버전 14에서는 지원하지 않는다는 것을 알게 되었습니다 ㅜ
※ ANALYZE 옵션과는 같이 사용할 수 없는 GENERIC_PLAN 옵션
혹시 $1, $2가 있으면 ANALYZE 옵션을 사용할 때 어떤 결과가 나오는지 궁금하셨을까요?
아쉽게도 ANALYZE 옵션은 쿼리를 직접 실행하기 때문에 $1, $2 같은 변수를 사용할 수 없습니다!
6. BUFFERS(기본값 false)
BUFFERS 옵션은 쿼리 실행 과정에서 사용된 버퍼에 대한 정보를 보여줍니다.
제가 보여드린 예시에는 shared hit, dirtied가 있는데 어떤 블락인지 (shared, local), 각 블락에서 어떤 조작이 있었는지 (hit, read, dirtied, written) 알려줍니다.
shared 블락은 정규 테이블과 인덱스가 포함되어 있는 블락이고,
local 블락은 정렬, 해싱 등에 사용되는 임시 테이블과 인덱스가 포함되어 있는 블락입니다.
hit은 쿼리를 실행하면서 읽은 블락이 캐시에 포함되어 있는 수를 의미합니다.
read는 쿼리를 실행하면서 실제로 블락을 읽은 횟수를 의미합니다.
dirtied는 쿼리를 실행하면서 이전에는 수정되지 않았던 블락 중에서 수정된 블락 수를 의미합니다.
written은 쿼리를 실행하면서 dirtied 블락들을 디스크에 다시 쓴 횟수를 의미합니다.
7. WAL(기본값 false)
WAL (Write-Ahead Logging) 옵션은 쿼리 실행 중 생성된 WAL 레코드의 양과 관련 정보를 나타냅니다.
- PostgreSQL은 데이터의 무결성과 복구를 위해 WAL 시스템을 사용하는데, 이는 데이터 변경 사항을 먼저 로그로 기록한 후 실제 데이터베이스 파일에 적용하는 방식입니다.
- 테스트 쿼리로는 WAL 옵션으로 인한 변화를 볼 수 없었습니다...
8. TIMING(기본값 true)
TIMING 옵션은 쿼리의 각 단계에서 소요된 시간을 보여줍니다.
ANALYZE 옵션이 사용될 때에만 동작하며 actual time이 필요하지 않을 때 비활성화 시키면 됩니다(그럴 때가 있을까...?).
9. SUMMARY(기본값 false)
SUMMARY 옵션은 쿼리 실행에 대한 요약 정보를 제공합니다.
요약 정보라고 해서 특별한 것은 아니었고 쿼리 실행 계획을 계산하는데 걸린 시간을 알려주더라고요.
.ANALYZE 옵션을 사용하면 자동으로 SUMMARY 옵션을 사용하게 되는데
아래 작성되어 있떤 Planning Time, Execution Time이 바로 SUMMARY 옵션으로 인한 요약이었습니다.
10. FORMAT(기본값 text)
FORMAT 옵션을 이용하면 EXPLAIN 명령어의 출력 형식을 지정할 수 있습니다.
기본적으로 텍스트 형식으로 결과가 출력되지만, FORMAT 옵션을 사용하여 JSON, XML, YAML 등 다양한 형식으로 결과를 받아볼 수 있습니다.
'데이터베이스 > PostgresQL' 카테고리의 다른 글
PostgresQL 에러 pq: cached plan must not change result type 에러 작업 (0) | 2024.10.26 |
---|---|
데이터베이스 테이블 rotation - DB에 데이터가 계속해서 쌓여서 용량이 꽉차면 어떡하죠...? (0) | 2023.10.06 |
PostgreSQL 계정 생성하기 (0) | 2022.11.13 |
PostgreSQL constraint - DB가 할 수 있는건 DB한테 맡기자! (0) | 2022.10.21 |
PostgreSQL autoincrement - Oracle DB는 SERIAL이 없어요? (0) | 2022.10.15 |