[데이터베이스] 23. Postgresql Explain

https://www.postgresql.org/docs/current/using-explain.html

 

대부분의 DBMS는 Explain 이라는 명령어를 통해서 SQL의 질의 수행 계획과 비용을 계산해볼 수 있다.

이를 통해 어떤 관계 대수 연산이 먼저 실행되는지도 알 수 있다.

이러한 질의 수행 계획은 DBMS가 알아서 계산하며, 인덱스를 추가하는 등의 방법으로 더 좋은 질의 계획을 만들도록 유도할 수 있다.

(인덱스를 추가한다고 항상 좋아지는 건 아니다)

 

 

EXPLAIN 키워드는 이렇게 수행할 SQL 앞에 EXPLAIN 키워드만 붙이면 끝이다.

 

 

data grip 에서 다른 테이블에 대해 수행했을 때는 이렇게 나타난다.

 

위 예시에서는 where 절 없이 단순히 모든 데이터를 조회하였으므로, 관계 대수 연산자 트리를 그리면 node가 하나만 존재하는 것과 같다.

결과를 보면 괄호 안에 cost 항목이 존재한다. '..' 을 기준으로 앞의 항목과 뒤의 항목이 서로 개별 항목인데, 앞의 항목은 쿼리를 수행하기 위해 사전에 준비하는데 걸리는 시간, 뒷 시간은 최종 걸린 total 비용을 말한다.

 

따라서 이 쿼리를 실행하는데 사전 준비 비용은 없으며, 445.00 의 총 비용이 들었음을 알 수 있다.

이때 비용의 단위는 디스크에서 page fetch 하는 양을 말하며, 측정할 때마다 샘플링을 하기 때문에 조금씩 변할 수 있다.

단순히 full scan 하는 것이기 때문에 사전 준비 비용이 들지 않은 것이다.

rows 는 가져온 행의 개수를 말하고, width 는 각 row의 크기를 말한다.

 

explain 에서 헷갈리지 않도록 주의할 점은, 이 비용은 '추정치' 일 뿐 실제로 수행시간을 측정한 것은 아니라는 것이다.

그저 몇 개의 page를 올려보내야 하는지에 대한 논리적인 비용을 추정해서 알려준다.

(실제로는 네트워크, 메모리 상황 등에 따라 비용과 수행 시간이 달라질 수 있기 때문)

 

 

 

이번엔 where 절을 추가해보았다.

비용은 크게 변하지 않았다. 똑같이 full scan 하면서 unique1 조건만 그때 그때 체크해서 거르면 되기 때문이다.

 

 

이번에는 조건을 추가하고, unique1 필드를 search key로 하는 인덱스를 걸었다고 해보자.

그러면 SQL을 수행할 때 인덱스를 스캔하면서 사전에 5.04 비용이 들고, 총 비용은 225.20의 비용이 들었다.

 

그리고 이 결과에서 각각의 -> 가 의미하는 것이 관계 대수 트리의 노드라고 생각하면 된다.

먼저 tenk1_unique1 필드에 대해 Bitmap Index Scan 을 걸어 unique1 < 100 조건을 처리하고, 그 다음 그 결과에 대해서 stringu1 필드에 대한 조건을 체크하며 tenk1 에 대해 인덱스 스캔을 한 것이다.

 

 

정렬을 하는 경우에 대한 쿼리 플랜을 보면 위와 같이 나온다.

정렬 키는 hundred, ten 이었고, hundered로 먼저 사전 정렬하였으며, 이때 hundred 에 대한 인덱스를 사용했고, 이 비용이 1574 였다.

Incremental Sort 비용은 2033 이었음을 알 수 있고, Limit 까지 처리하는데 최종적으로 39의 비용이 들었음을 알 수 있다.

비용이 39로 줄어든 이유는, 전체 정렬을 하는데는 2033의 비용이 들겠지만, 어차피 100개만 뽑으면 되니까 정렬하는 과정에서 100개를 뽑았다면 나머지는 더 이상 정렬하지 않을테니 그 비용은 빠지는 것이다.

(Incremental sort, 점진적 정렬을 하기 때문에 가능한 것으로 이해했다.)

 

 

EXPLAIN 키워드 앞에 ANALYZE 키워드를 붙이면 더 자세한 정보를 알 수 있다.

이때 알 수 있는 정보로 Planning Time 과 Execution Time 이 있다.

Planning Time은 계획을 추정하면서 걸린 시간, Execution Time 은 이 쿼리를 실제로 실행할 때 걸리는 시간..정도로 이해했다.

(GPT 말로는 planning time = 쿼리 플랜 작성하는데 걸린 시간, execution time 은 쿼리 플랜에 따라 실제로 실행하는데 걸린 시간이라고 한다.)

 

 

 

ANALYZE 뒤에 BUFFERS 라는 키워드를 추가하면 디스크엥서 읽어온 페이지에 대해 캐시 히트가 몇 번 발생했는지, 그래서 실제로는 몇개를 읽어왔는지 등에 대한 정보를 추가적으로 볼 수 있다.