PostgreSQL
PostgreSQL은 쿼리의 실행 계획을 수립합니다. 쿼리 구조와 데이터의 속성에 맞는 올바른 계획을 선택하는 것이 좋은 성능을 내기 위해 매우 중요하기 때문에, 시스템에는 좋은 계획을 선택하기 위한 플래너가 포함되어 있습니다.
EXPLAIN 명령을 사용하면 플래너가 어떤 쿼리 계획을 생성하는지 확인할 수 있습니다. 이 쿼리 계획을 읽는 것은 어느 정도 경험이 필요한 기술이지만 이 섹션에서는 기본적인 내용을 다루려고 합니다.이 예제에서는 사람이 읽기 편하고 간결한
EXPLAIN의 기본 "텍스트" 출력 형식을 사용합니다. 추가 분석을 위해 EXPLAIN의 출력을 프로그램에 제공하려면 기계가 읽을 수 있는 출력 형식(XML, JSON 또는 YAML) 중 하나를 사용해야 합니다.EXPLAIN 기초
쿼리 계획의 구조는 트리 형식입니다. 트리의 맨 아래에 있는 노드는 스캔 노드로, 테이블의 행을 반환합니다. 테이블 액세스 방식에 따라 순차 스캔, 인덱스 스캔, 비트맵 인덱스 스캔 등 다양한 유형의 스캔 노드가 있습니다. 또한 테이블이 아닌 데이터 소스(예를 들면
VALUES 절)에는 고유한 스캔 노드 유형이 있습니다. 쿼리에 조인, 집계, 정렬 또는 기타 작업이 필요한 경우, 이러한 작업을 수행하기 위해 스캔 노드 위에 추가 노드가 있습니다. 다시 말하지만, 이러한 작업을 수행하는 데는 일반적으로 두 가지 이상의 가능한 방법이 있으므로 여기에도 다른 노드 유형이 나타날 수 있습니다.
EXPLAIN의 출력에는 계획 트리의 각 노드에 대해 기본 노드 유형과 플래너가 노드를 실행하기 위해 만든 비용 추정치가 표시됩니다. 노드의 추가 속성을 표시하기 위해 노드의 요약 줄에서 들여쓰기된 추가 줄이 표시될 수 있습니다. 첫 번째 줄(최상위 노드의 요약 줄)에는 계획의 예상 총 실행 비용이 표시되며, 플래너는 이 수치를 최소화하려고 합니다.다음은 결과물이 어떻게 보이는지 보여드리기 위한 간단한 예시입니다.
이 쿼리에는
WHERE 절이 없기 때문에 테이블의 모든 행을 스캔해야 하므로 플래너는 간단한 순차 스캔(Seq Scan) 계획을 사용하기로 선택했습니다. 괄호 안에 따옴표로 묶인 숫자의 의미는 다음과 같습니다.cost=0.00: 예상 시작 비용. 출력 단계를 시작하기 전에 소요되는 시간입니다.
458.00: 예상 총 비용. 이는 계획 노드가 완료될 때까지 실행된다는 가정 하에 계산된 것입니다.
rows=10000: 이 계획 노드에서 출력되는 예상 행 수입니다. 노드가 완료될 때까지 실행된다고 가정합니다.
width=244: 이 계획 노드에서 출력되는 행의 예상 평균 너비(바이트)입니다.
비용(Cost)은 플래너의 비용 매개변수에 정의된 가상의 단위로 측정됩니다(Section 20.7.2 참조). 요청을 처리하기 위해 읽어야 하는 디스크 페이지 단위로 비용을 측정하는 것이 일반적인 관행입니다. seq_page_cost는 일반적으로
1.0으로 설정되고 다른 비용 매개변수는 이를 기준으로 설정됩니다. 이 섹션의 예제는 기본 비용 매개변수로 실행됩니다.상위 노드의 비용에는 모든 하위 노드의 비용이 포함된다는 점을 이해하는 것이 중요합니다. 또한 비용에는 플래너가 신경 쓰는 부분만 반영된다는 점을 인식하는 것도 중요합니다. 특히 비용에는 결과 행을 클라이언트에 전송하는 데 소요되는 시간이 고려되지 않지만 실제 쿼리를 실행하고 결과를 전달받는 총 시간에는 클라이언트 전송 시간도 중요한 요소가 될 수 있습니다. 하지만 이 시간은 플래너가 실행 계획을 선택하는데 중요하지 않기 때문에 무시합니다.
rows 값은 계획 노드에서 처리하거나 스캔한 행의 수가 아니라 노드에서 연산한 결과의 수라는 점도 주의해야 합니다. 이 값은 노드에서 적용되는 WHERE 절 조건에 의해 필터링된 결과로, 연산을 하기 위해 스캔된 데이터 수보다 적은 경우가 많습니다. 최상위 노드에 적힌 rows 값은 쿼리에서 실제로 반환, 업데이트 또는 삭제된 행의 수를 대략적으로 추정하는 것이 이상적입니다.다시 예시로 돌아와서
이 숫자는 매우 간단하게 도출됩니다. 만약 아래 쿼리를 실행한다면
우리는
tenk1에 358개의 디스크 페이지와 10000개의 행이 있다는 것을 알 수 있습니다. (*역자주: 위 쿼리를 실행하면 ‘tenk1’이라는 테이블 정보가 출력됩니다. relpages 에는 사용한 디스크 페이지의 개수, reltuples에는 행의 개수가 표시됩니다.) 예상 비용은 (읽은 디스크 페이지 * seq_page_cost) + (스캔한 행 * cpu_tuple_cost)로 계산됩니다. 기본적으로 seq_page_cost는 1.0이고 cpu_tuple_cost는 0.01이므로 예상 비용은 (358 * 1.0) + (10000 * 0.01) = 458입니다.WHERE
이제
WHERE 조건을 추가하여 쿼리를 수정해 보겠습니다.Seq Scan plan 노드에 WHERE 절의 조건이 “Filter”라고 표현된 것을 볼 수 있습니다. WHERE 절로 인해 예상되는 출력 행의 개수(rows)가 줄어들었습니다. 그러나 Seq Scan은 여전히 10000개의 행을 모두 방문해야 하므로 총 비용은 감소하지 않았으며, 실제로는 WHERE 조건을 확인하는 데 소요되는 추가 CPU 시간을 반영하여 약간 증가했습니다. (정확히 말하면 10000 * cpu_operator_cost만큼 증가했습니다).rows 값은 대략적인 추정치를 의미합니다. 실제 쿼리가 출력하는 행 수인 7000개입니다. ANALYZE에서 생성된 통계는 테이블의 무작위 샘플에서 가져온 것이므로 ANALYZE 명령이 실행될 때마다 변경될 수 있습니다.이제 조건을 더 제한적으로 만들어 보겠습니다.
여기서 플래너는 2단계 계획을 사용하기로 결정했습니다. 하위 계획 노드가 인덱스를 방문하여 인덱스 조건과 일치하는 행의 위치를 찾은 다음 상위 계획 노드가 실제로 테이블에서 해당하는 행을 가져옵니다. 행을 개별적으로 가져오는 것은 순차적으로 읽는 것보다 훨씬 비용이 많이 들지만, 테이블의 모든 페이지를 방문할 필요가 없기 때문에 순차 스캔보다 효율적입니다. (두 개의 계획 수준을 사용하는 이유는 상위 계획 노드가 인덱스에 의해 식별된 행 위치를 읽기 전에 디스크에 저장된 물리적 순서로 정렬하여 개별 가져오기 비용을 최소화하기 때문입니다. 노드 이름에 언급된 "Bitmap"은 정렬을 수행하는 메커니즘입니다.)
이제
WHERE 절에 다른 조건을 추가해 보겠습니다.stringu1 = 'xxx'라는 조건을 추가하면 출력 행 수 추정치는 줄어들지만 여전히 동일한 행 집합을 방문해야 하므로 총 비용은 줄어들지 않습니다. unique1 컬럼에만 인덱스가 있기 때문에 stringu1는 인덱스를 적용할 수 없습니다. unique1 컬럼에 있는 인덱스를 이용해 1차로 데이터를 필터링하고, 이렇게 줄어든 데이터에 stringu1 필터를 적용합니다. 이 추가 필터 작업을 반영하기 위해 비용이 약간 증가했습니다.어떤 경우에는 플래너가 '간단한' 인덱스 스캔 계획을 선호할 수도 있습니다.
이 계획에서는 테이블 행을 디스크에서 가져올 때, 디스크에 저장된 물리적인 순서가 아니라 인덱스 순서대로 가져오기 때문에 읽기 비용이 훨씬 더 많이 들지만 가져와야 하는 행 수가 너무 적어서 행 위치를 정렬하는 데 드는 추가 비용을 들일 필요까지는 없습니다. 이 계획은 단일 행만 가져오는 쿼리에서 가장 자주 볼 수 있습니다.
ORDER BY
인덱스 순서와 일치하는
ORDER BY 조건이 있는 쿼리에도 위의 Index Scan이 자주 사용되는데, ORDER BY의 순서와 인덱스의 정렬 순서가 동일할 때 인덱스 순서대로 데이터를 뽑아오면 추가 정렬이 필요하지 않기 때문입니다. 위 예에서 ORDER BY unique1를 쿼리에 추가해도 동일한 실행 계획을 출력합니다. 이미 unique1 의 오름차순으로 인덱스가 정렬되어 있기 때문입니다.플래너는 여러 가지 방법으로
ORDER BY 절을 구현할 수 있습니다. 위의 예는 이러한 정렬 절을 암시적으로 구현할 수 있음을 보여줍니다. 플래너는 명시적인 sort 단계를 추가할 수도 있습니다.인덱스 조합으로 WHERE절의 다양한 조건 처리하기
WHERE에서 참조하는 여러 열에 별도의 인덱스가 있는 경우 플래너는 인덱스의 AND 또는 OR 조합을 사용하도록 선택할 수 있습니다.그러나 이렇게 하려면 두 인덱스를 모두 방문해야 하므로 하나의 인덱스만 사용하고 다른 조건을 필터로 처리하는 것보다 반드시 효율적인 것은 아닙니다. WHERE 절의 조건 범위를 변경하면 그에 따라 실행 계획도 변경될 수 있습니다.
LIMIT
다음은
LIMIT를 추가한 예시입니다.이것은 위와 동일한 쿼리이지만 모든 행을 검색할 필요가 없도록
LIMIT를 추가하고 플래너가 수행해야 할 작업을 변경했습니다. Index Scan 노드의 cost와 rows가 마치 완료될 때까지 실행되는 것처럼 표시되는 것을 볼 수 있습니다. 그러나 Limit 노드는 해당 행의 5분의 1만 검색한 후에 중지될 것으로 예상되므로 총 비용은 5분의 1에 불과하며 이것이 쿼리의 실제 예상 비용입니다. 이 계획은 이전 계획에 Limit 노드를 추가하는 것보다 선호되는데, 만약 이전 계획대로 하게 되면 비트맵 스캔의 시작 비용이 일단 들어가기 때문에 총 비용이 25가 넘게 됩니다.JOIN: Nested Loop
unique1, unique2 컬럼을 사용하여 두 테이블을 조인해 보겠습니다.이 계획에는 두 개의 테이블 스캔을 입력으로 하는
Nested Loop 조인 노드가 있습니다. 노드 요약 줄의 들여쓰기는 실행 계획의 트리 형태 구조를 보여줍니다. 트리 구조의 첫 번째 자식 노드는 앞서 본 것과 유사한 Bitmap 스캔입니다. cost와 rows는 해당 노드에 unique1 < 10을 적용하기 때문에 SELECT ... WHERE unique1 < 10 쿼리를 실행할 때와 동일합니다. (*역자주: 이렇게 조인을 수행할 때 먼저 처리되는 테이블을 외부 테이블 (Outer table) 또는 구동 테이블(Driving table)이라고 합니다. 다른 테이블은 내부 테이블(Inner table)이라고 부릅니다. 이 예시에서는 t1이 외부 테이블, t2를 내부 테이블입니다.) t1.unique2 = t2.unique2 조건은 아직 관련이 없으므로 첫 번째 Bitmap Scan의 rows에 영향을 미치지 않습니다. Nested Loop 조인 노드는 첫 번째 자식 노드에서 얻은 데이터를 이용해 두 번째 자식 노드를 실행합니다. 첫 번째 자식 노드에서 얻은 t1.unique2 값을 사용해 SELECT ... WHERE t2.unique2 = constant 케이스와 유사한 계획 및 비용을 얻을 수 있습니다. (예상 비용은 실제로 위에서 본 것보다 약간 낮은데, 이는 t2에서 인덱스 스캔을 반복하는 동안 캐싱을 하기 때문입니다.)최종적인
Nested Loop 노드의 비용은 외부 테이블의 스캔 비용과 외부 테이블 스캔 결과에 대한 내부 스캔의 반복 수행(여기서는 10 * 7.91), 그리고 조인 처리를 위한 약간의 CPU 시간을 기준으로 계산됩니다. (*역자주: 구동 테이블 필터링 결과 데이터가 10개가 있고, 이 데이터 10개에 대해 내부 테이블을 스캔하면서 조인을 하기 때문에 비용이 10 * 7.91가 됩니다.)이 예에서 조인의 출력 행 수는 10으로, 두 자식 노드의
rows 값의 곱(*역자주: 각각 10과 1입니다.)과 동일합니다. 하지만 입력 스캔이 아닌 조인 지점에서만 적용할 수 있는 조건이 있을 때에는 이와 같이 간단하게 계산되진 않습니다. 다음 예제에서 살펴봅시다.
t1.hundred < t2.hundred 조건은 조인 노드에서 적용됩니다. 이렇게 하면 조인 노드의 예상 출력 행 수가 줄어들지만 입력 스캔은 변경되지 않습니다.여기서 플래너는
Materialize 노드를 배치했습니다. 이 노드는 데이터를 읽으면서 메모리에 데이터를 저장한 다음, 이후 실행을 할 때에는 메모리에서 데이터를 반환합니다. 외부 테이블의 스캔 결과가 10개이기 때문에, Nested Loop 조인을 할 때 원래는 t2가 10번 스캔되어야 하지만 Materialize 노드 덕분에 t2 인덱스 스캔은 한 번만 수행됩니다.아우터 조인(*역자주: LEFT JOIN, RIGHT JOIN 등)을 처리할 때에는 "
Join Filter" 조건과 일반 "Filter" 조건이 모두 첨부 된 조인 노드도 볼 수 있습니다. Join Filter 조건은 아우터 조인의 ON 절에서 가져오고, Join Filter 조건에 실패한 행은 여전히 null 행으로 출력될 수 있습니다. 그러나 일반 Filter 조건은 외부 조인 규칙 다음에 적용되므로 무조건 행을 제거하는 역할을 합니다. 이너 조인에서는 두 유형의 필터를 구분하는 의미가 없지만, 아우터 조인에서는 구분이 필요합니다.JOIN: Hash Join
쿼리의 선택 범위를 조금만 변경해도 매우 다른 조인 실행 계획을 얻을 수 있습니다.
여기서 플래너는 한 테이블의 행을 인메모리 해시 테이블에 입력한 후 다른 테이블을 스캔하고 해시 테이블에서 각 행의 일치 여부를 조사하는 해시 조인을 사용하기로 선택했습니다. 들여쓰기를 잘 보고 실행 계획을 구조를 파악해보세요.
tenk1의 Bitmap Scan은 해시 테이블을 구성하는 Hash 노드에 대한 입력입니다. 이렇게 만들어진 해시 테이블은 Hash Join 노드로 반환되고, tenk2를 스캔하면서 결합 키가 해시값에 존재하는지 확인하면서 결합을 수행합니다. (*역자주: 해시 테이블은 워킹 메모리에 저장되므로 작은 테이블을 이용해 만드는 것이 일반적입니다. 이 예에서는 t1에 t1.unique1 < 100 조건을 반영한 예상 행수가 101개, t2의 행 수는 10000개로 t1이 t2보다 작아 Hash 노드에 t1이 사용되었습니다.)JOIN: Merge Join
또 다른 가능한 조인 유형은
Merge Join입니다.병합 조인을 사용하려면 조인 키로 입력 데이터를 정렬해야 합니다. (*역자주: 이런 특성 때문에 Sort Merge라고도 부릅니다.) 이 계획에서
tenk1 데이터는 인덱스 스캔을 사용하여 읽습니다. onek은 행이 많기 때문에 인덱스 스캔을 하는 대신 순차적으로 스캔하고 정렬을 합니다. (데이터가 많은 경우 순차 스캔 및 정렬을 하는 것이 인덱스 스캔보다 나은 경우가 많습니다. 인덱스는 디스크의 물리적 저장 위치를 기준으로 정렬 되어있는 것이 아니기 때문에 디스크에서 실제로 데이터를 가지고 오는데 시간이 더 걸리기 때문입니다.)변형 계획을 살펴보는 한 가지 방법은 Section 20.7.1.에 설명된 활성화/비활성화 플래그를 사용하여 플래너가 가장 효율적이라고 생각하는 실행 전략을 무시하도록 하는 것입니다. 예를 들어, 앞의 예에서 테이블
onek를 처리하는 데 순차 스캔 및 정렬이 최선의 방법이라고 확신하지 못한다면 다음을 시도해 볼 수 있습니다.이는 플래너가 인덱스 스캔으로
onek를 정렬하는 것이 순차 스캔 후 정렬하는 것보다 약 12% 더 비싸다고 생각한다는 것을 보여줍니다. 물론 다음 질문은 그것이 옳은지 여부입니다. 아래에서 설명하는 것처럼 EXPLAIN ANALYZE을 사용하여 이를 조사할 수 있습니다.EXPLAIN ANALYZE
플래너의 추정치의 정확성을 확인하기 위해
EXPLAIN의 ANALYZE 옵션을 사용할 수 있습니다. 이 옵션을 사용하면 EXPLAIN이 실제로 쿼리를 실행한 다음 각 계획 노드에 누적된 실제 행 수와 실제 실행 시간을 일반 EXPLAIN이 표시하는 것과 동일한 추정치와 함께 표시합니다.예를 들어 다음과 같은 결과를 얻을 수 있습니다.
'실제 시간'은 실시간으로 밀리초 단위로 표시되는 반면,
cost 추정치는 임의의 단위로 표시되므로 일치하지 않을 수 있다는 점에 유의하세요. 가장 중요하게 살펴봐야 할 것은 예상 행 수가 현실에 상당히 근접한지 여부입니다. 이 예제에서는 추정치가 모두 정확했지만 실제로는 매우 드문 경우입니다.일부 쿼리 계획에서는 하위 계획 노드가 두 번 이상 실행될 수 있습니다. 예를 들어, 위의
Nested Loop 노드에서 내부 테이블의 인덱스 스캔은 외부 테이블의 한 행당 한 번씩 실행됩니다. 이러한 경우, loops 값은 노드의 총 실행 횟수를 보고하며, 표시되는 실제 시간과 행 값은 실행당 평균값입니다. 이는 비용 추정치가 표시되는 방식과 비교 가능한 수치를 만들기 위한 것입니다. loops 값을 곱하면 노드에서 실제로 소요된 총 시간을 알 수 있습니다. 위의 예에서는 tenk2에서 인덱스 스캔을 실행하는 데 총 0.220밀리초가 소요되었습니다.경우에 따라
EXPLAIN ANALYZE는 계획 노드 실행 시간 및 행 수 외에 추가적인 실행 통계를 표시합니다. 예를 들어 정렬 및 해시 노드는 아래와 같은 추가 정보를 제공합니다.정렬 노드에는 사용된 정렬 방법(특히 정렬이 인메모리인지 디스크인지)과 필요한 메모리 또는 디스크 공간의 양이 표시됩니다. 해시 노드에는 해시 버킷과 배치의 수와 해시 테이블에 사용된 최대 메모리 양이 표시됩니다. (배치 수가 1을 초과하는 경우 디스크 공간 사용량도 포함되지만 이 예시에서는 표시되지 않았습니다).
또 다른 유형의 추가 정보는 필터 조건에 의해 제거된 행 수입니다.
이 카운트는 조인 노드에 적용되는 필터 조건에 특히 유용할 수 있습니다. '
Rows Removed' 줄은 필터 조건에 의해 스캔된 행(조인 노드의 경우 잠재적 조인 쌍)이 하나 이상있는 경우에만 표시됩니다.EXPLAIN에는 더 많은 실행 시간 통계를 얻기 위해 ANALYZE과 함께 사용할 수 있는 BUFFERS 옵션이 있습니다.BUFFERS에서 제공하는 숫자는 쿼리에서 가장 I/O 집약적인 부분을 식별하는 데 도움이 됩니다.EXPLAIN ANALYZE에 표시되는 Planning time은 구문 분석된 쿼리에서 쿼리 계획을 생성하고 이를 최적화하는 데 걸린 시간입니다. 구문 분석이나 재작성을 하는데 들어간 시간은 포함되지 않습니다. Execution time에는 실행기 시작 및 종료 시간과 실행된 트리거를 실행하는 시간이 포함되지만 구문 분석, 재작성 또는 계획 시간은 포함되지 않습니다.주의 사항
측정된 실행 시간이 동일한 쿼리의 일반적인 실행 시간과 차이가 날 수 있는 두 가지 중요한 요인이 있습니다. 첫째,
EXPLAIN ANALYZE를 실행할 때에는 클라이언트에 출력 데이터가 전달되지 않기 때문에 네트워크 전송 비용과 I/O 변환 비용이 포함되지 않습니다. 둘째, 특히 gettimeofday() 운영 체제 호출이 느린 시스템에서는 EXPLAIN ANALYZE에 추가되는 측정 오버헤드가 상당할 수 있습니다. pg_test_timing 도구를 사용하여 시스템에서 타이밍 오버헤드를 측정할 수 있습니다.EXPLAIN 결과를 실제 테스트하는 상황과 크게 다른 상황으로 추정해서는 안 됩니다. 예를 들어 작은 크기의 테이블에 대한 결과를 큰 테이블에 적용한다고 가정해서는 안 됩니다. 플래너의 비용 추정치는 선형적이지 않으므로 더 크거나 작은 테이블에 대해서는 완전히 다른 실행 계획을 선택할 수 있습니다. 극단적인 예로, 디스크 페이지를 하나만 차지하는 테이블에서는 인덱스 사용 가능 여부에 관계없이 거의 항상 순차 스캔 계획이 선택됩니다. 플래너가 어떤 경우든 테이블을 처리하는 데 디스크 페이지 읽기 한 번이 소요된다는 것을 알고 있다면, 인덱스를 보기 위해 굳이 디스크의 다른 페이지를 읽는 실행 계획을 세우지 않습니다.실제 값과 예상 값이 잘 일치하지 않는 경우가 있지만 실제로는 아무런 문제가 없습니다. 이러한 경우 중 하나는
LIMIT 또는 이와 유사한 효과로 인해 계획 노드 실행이 짧게 중단되는 경우입니다. 예를 들어, 앞서 사용한 LIMIT 쿼리에서와 같이 말이죠.Index Scan 노드의 cost와 rows는 인덱스 스캔이 처음부터 끝까지 실행되는 것처럼 표시됩니다. 그러나 실제로는 Limit 노드가 2개를 얻은 후 행 요청을 중단했기 때문에 실제 행 수는 2개에 불과하고 실행 시간은 비용 추정치보다 적습니다. 이는 추정 오류는 아니며 추정치와 실제 값이 표시되는 방식에 차이가 있을 뿐입니다.Editor 선미’s comment
이 글의 원문은 PostgreSQL 공식문서 “Chapter 14. Performance Tips - 14.1. Using Explain”입니다. PostgreSQL 버전 16을 기준으로 작성되었습니다.
이 글은 데이터 분석가를 위한 번역 작업으로
SELECT 쿼리의 실행 계획 중에서도 빈번하게 표시되는 실행 계획 위주로 번역을 했습니다. UPDATE, DELETE 등 데이터 조작 기능의 실행 계획이나 Merge Join과 같이 활용 빈도가 낮은 실행 계획에 대한 일부 내용은 생략되어 있습니다. 전체 내용을 파악하고 싶은 분들은 꼭 원문을 읽어주세요.흔쾌히 번역을 허가해준 PostgreSQL CoC(Code of Conduct)팀 감사합니다. 이해를 돕기 위해 역자의 의역이 섞여있으니 만약 번역과 원문 간에 차이가 있는 경우 원문을 우선적으로 생각해주세요. 번역 오류는 contact@datarian.io로 제보해주세요.
