| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 13. 성능효율 팁 | Fast Forward | Next |
PostgreSQL는 주어진 쿼리로부터 쿼리 계획을 생성합니다. 쿼리의 구조와 데이터의 특성과 일치하는 올바른 쿼리 계획을 선택하는 것은 좋은 작업 성능을 얻기 위한 중요한 부분입니다. 그러므로 뛰어난 계획 선택을 시도하는 시스템은 복잡한planner를 가집니다. EXPLAIN 커맨드를 사용하면, 임의의 쿼리에 대해서 planner가 어떠한 쿼리 계획을 만들었는지 알 수 있습니다. Plan-reading은 광범한 튜토리얼을 가질 자격이 있는 기술입니다. 본 장에서는 일부 기본적인 정보만을 제공합니다.
쿼리 계획은 계획 노드의 트리 구조입니다. 최하층 노드는 테이블 스캔 노드로, 테이블에서 행 그 자체를 돌려줍니다. 시퀀셜 스캔, 인덱스 스캔, 비트 맵 인덱스 스캔이라고 하는 테이블 액세스 방법의 차이에 따라 스캔 노드의 종류에 차이가 있습니다. 쿼리가 조인, 집약(aggregation), 정렬(sorting)이나 행에 대한 연산을 요구하는 경우, 이러한 조작을 실행하기 위한 "atop"스캔 노드가 추가됩니다. 반복하지만, 이러한 연산을 실시할 때, 일반적으로 하나 이상의 가능성이 존재합니다. 그러므로, 다른 노드 종류가 출현 할 가능성이 있습니다. EXPLAIN의 출력은 계획 트리 내의 각 노드에 대한 하나의 출력을 가지며, 기본 노드 타입과 planner가 생성한 계획 노드의 실행의 코스트 추정치를 더합니다. 첫 번째 행(가장 상위 노드)에는 계획에 대한 전체 실행 코스트 추정치를 가지고 있습니다. planner는 이 수가 최소가 되도록 검색합니다.
무엇이 출력되는지 알아보기 위해, 일반적인 예제를 들어보겠습니다. [1]
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
EXPLAIN가 인용하는 수치는 다음과 같습니다.
초기 처리의 추정 코스트(출력용의 스캔이 개시 될 때까지 소비되는 시간, 예를 들면 소트 노드에서 정렬(sorting)하는데 걸리는시간).
예측된 전체 코스트(모든 행이 추출될 경우, 그렇지 않으나, LIMIT구를 사용한 쿼리는 Limit계획 노드의 입력 노드 총 코스트 계산을 하기 전에 정지할 것입니다).
계획 노드에 의해 예측된 출력 행의 수(실행이 완료됐을 경우만).
이 계획 노드가 출력하는 행의 (바이트 단위에서의) 추정 평균폭.
코스트는 planner의 코스트 파라미터(Section 17.6.2참조)로 결정되는 임의의 단위로 측정됩니다. 추출되는 디스크 페이지 단위로 코스트를 측정하는 것이 전통적인 방식입니다. 즉, seq_page_cost은 관습적으로1.0으로 설정되고, 다른 코스트 파라미터는 이와 관련하여 설정됩니다. 본 절의 예에서는 디폴트 코스트 파라미터를 실행하고 있습니다.
상위 레벨 노드의 코스트는 모든 자식 노드의 코스트를 포함한다는 사실은 중요합니다. 코스트는 planner가 관여하는 코스트만 반영하는 점도 또한 중요합니다. 특히, 코스트는 클라이언트에게 결과 행을 전송하는 데 걸린 시간을 고려하지 않습니다. 실제 경과 시간이 중요한 사실일 수 있음에도 불구하고 말입니다. 이것은 계획을 바꿈으로써 변경될 수 없기 때문에, planner는 이를 무시합니다. (모든 정확한 계획은 같은 행 집합을 출력한다고 믿고 있습니다).
출력 행은 계획 노드에 의해 처리되거나 스캔 된 행의 수를 나타내지 않기 때문에 다소 취급하기 어렵습니다. 해당 노드에 적용되는 모든WHERE구문 제약으로부터 예상된 선택도를 반영해, 보통에 비해 적은 행 수가 됩니다. 이상적으로는 최상위레벨 행은 실제로 쿼리에 의해 리턴되거나, 업데이트되거나 삭제된 대략적인 수치를 예측합니다.
예제로 돌아옵니다.
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
이것은 얻어진 그대로입니다. 이하를 실행하게 되면,
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
tenk1에는 358 디스크 페이지와 10000 행이 있다는 것을 알 것입니다.
그래서 코스트는 358 페이지를 읽음으로서 추정되어지고, seq_page_cost 하나(1.0 디폴트로)와
디폴트의 0.01의 10000 * cpu_tuple_cost을 더해서 계산됩니다.
지금부터는 WHERE 구문을 추가해서 쿼리를 변경하겠습니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
EXPLAIN 출력은 "필터"조건으로 적용되어지는 WHERE구문을 나타낸다는 것을 주의하십시요. 이는 계획 노드가 스캔하는 한 행에 대한 조건을 검사한다는 것을 의미하며, 조건을 통과할 때만 출력됩니다. WHERE구문으로 출력 행 수의 견적이 작아지고 있습니다. 그러나 여전히 10000행 모두를 스캔 할 필요가 있기 때문에, 코스트가 작아고 있진 않습니다. 사실상 WHERE 조건을 검사하는 추가적인 CPU 시간을 반영하기 때문에 코스트가 조금 상승했습니다.
이 쿼리가 선택하는 실제의 행수는 7000입니다. 그러나 행 추정은 근사치에 지나지 않습니다. 이 실험을 2회 실행했을 경우, 아마 다소 다른 추정치를 얻을 것입니다. 좀 더 말하면, 이것은ANALYZE커맨드 후에 변경될 것입니다. ,ANALYZE로 생성되는 통계 정보가 테이블의 무작위 표본으로부터 얻어지기 때문입니다.
Now, let's make the condition more restrictive: --> 그럼, 조건을 보다 제한해 보겠습니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
여기에서 planner는 2 단계의 계획을 사용할 것을 결정했습니다. 최하층의 계획 노드는 인덱스를 조회해서 인덱스 조건에 맞는 행의 장소를 검색합니다. 그런 다음, 상위 계획 노드가 실제로 테이블 자체로부터 이 행을 꺼냅니다. 개별적으로 행을 추출하는 것은 연속적으로 읽어들이는 것에 비해 매우 비쌉니다. 그러나 테이블의 모든 페이지를 읽어낼 필요는 없기 때문에, 시퀀셜 스캔보다는 저가가 됩니다 (2 단계 계획을 사용하는 이유는 최상위 계획 노드가 인덱스로 식별된 행의 위치를 읽어내기 전에 물리적인 순서로 정렬하기 위해서입니다. 또한 이로 인해 개별적으로 행을 추출하는 비용을 최소화시키기 위해서 입니다. 노드의 이름으로 언급된 "bitmap"는 정렬(sorting)를 실시하는 매커니즘입니다).
만약 WHERE조건이 선택하기에 충분하면, planner가 "단순한"인덱스 스캔 계획으로 전환할 가능성이 있습니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
이러한 경우, 테이블 행은 인덱스 순서로 꺼내집니다. 이는 읽어들이기 위해 높은 비용이 드는데, 행의 위치를 sorting하는 추가적인 비용이 적기 때문에 그만큼 가치 있지 않습니다. 단일 행만을 꺼내는 쿼리나, 인덱스 순서에 일치하는 ORDER BY조건을 요구하는 쿼리에서는 이런 종류의 계획 종류를 자주 볼 수 있습니다.
WHERE구문에 다른 조건을 추가합니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 < 3)
Filter: (stringu1 = 'xxx'::name)
추가된 구문 stringu1 = 'xxx'은 출력 행 추정치를 감소시키지만, 같은 행의 집합을 검사하기 때문에 코스트는 변함이 없습니다. 이 stringu1구문은 인덱스 조건으로 적용할 수 없는 것을 주의하십시요(인덱스가 unique1 열에만 있기 때문에). 대신에, 이는 인덱스에 의해 행의 필터로서 적용되어집니다. 그러므로, 코스트는 사실상 이러한 추가적인 검사를 반영하여 조금 올라갑니다.
WHERE구문에서 사용되는 일부 컬럼의 인덱스가 있다면, planner는 AND나 OR 을 인덱스의 조합으로 사용하기 위해 선택할 것입니다.
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
그러나 이는 양쪽의 인덱스를 조회할 필요가 있습니다. 그래서 이는 하나의 인덱스를 사용하는 것과 필터로서 다른 조건을 취급하는 것을 비교하여 우위를 가릴 필요가 없습니다. 포함되는 범위를 변경하면, 그에 따라 계획도 바뀔 것입니다.
지금까지 설명에 사용한 열을 사용하여 2개의 테이블을 조인해 봅시다.
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
이 nested-loop 조인에서는 외부 스캔에, 위의 예제와 같은 비트 맵 인덱스 스캔이 사용되고 있습니다. 그리고unique1 < 100 WHERE구문을 해당 노드에 적용하고 있기 때문에, 코스트와 출력행 수는 변하지 않습니다. 이 단계에서는 t1.unique2 = t2.unique2구문은 관계하고 있지 않고, 외부 스캔에 있어서의 출력행 수에 영향을 주고 있지 않습니다. 내부 스캔에서는 외부 스캔에 있어서의 현재 행의unique2의 값이 내부의 인덱스 스캔에 내던져져 t2.unique2 = constant 와 같은 인덱스 조건을 생성합니다. 따라서, 내부 스캔의 계획과 코스트는 예를 들면EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42와 같은 쿼리와 같습니다. 루프 노드의 코스트는 외부 스캔의 코스트와 각각의 외부의 행에 대해서 내부 스캔이 반복 되는 것에 의한 코스트(여기에서는 106 * 3.01)를 더해 조인 처리를 행하기 위한 조금의 CPU 시간을 더한 것이 됩니다.
이 예에서는 조인의 출력 행 수는 2개의 스캔의 출력 행 수와 동일하지만, 언제나 그런 것은 아닙니다. 대부분의 경우, 2개의 테이블에 관계하는 WHERE구문이 있어, 그WHERE구문은 입력 스캔 때가 아닌 조인을 실시할 때 적용되기 때문입니다. 예를 들면, WHERE ... AND t1.hundred < t2.hundred라고 하는 구를 추가하면, 조인 노드의 출력행수가 줄어들지만, 입력 스캔에는 영향을 주지 않습니다.
Section 17.6.1에 기재한 enable/disable 플래그를 사용해, planner가 가장 좋다고 생각하는 전략을 강제로 무시시키는 방법으로 다른 계획을 관찰할 수 있습니다 (매우 원시적인 툴이지만, 이용가치가 있습니다. Section 13.3도 참조해 주십시요).
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
이 계획에서는 인덱스 스캔을 사용해tenk1로부터 필요한 100행을 꺼내 메모리상의 해시 테이블에 저장하고,
그 후, tenk2를 시퀀셜 스캔을 하여, tenk2의 행 마다t1.unique2 = t2.unique2를 채울지에 대해 해시 테이블을 검사합니다.
tenk1를 읽어내, 해시 테이블을 설정하기 위한 코스트는 tenk2의 read를 시작할 때까지 행을 입수하지 않으므로,
완전하게 해시 조인의 초기 코스트가 됩니다.
또한, 이 조인의 추정 총시간은 해시 테이블을 10000회 검사하는 무거운 CPU 시간이 포함되어 있습니다.
그러나 이 계획형에서 해시 테이블의 설정은 한 번만 일어나기 때문에, 10000×232.35 분의 코스트는
들어가지 않는
것으로 주의해 주십시요.
EXPLAIN ANALYZE를 사용해, planner가 추정하는 코스트의 정밀도를 점검할 수 있습니다. 이 커맨드는 실제로 그 쿼리를 실행해, 계획 노드 마다 필요로 한 실제의 실행 시간을, 보통EXPLAIN가 가리키는 추정 코스트와 같은 값과 함께 표시합니다. 예를 들면, 이하와 같은 결과를 얻을 수 있습니다.
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 14.452 ms
"actual time"치는 실시간을 밀리 세컨드 단위로 나타낸 것이고, "cost"추정치는 임의의 단위로 나타내는 것에 주의해 주십시요. 이에 따라 이들은 그대로 비교할 수 없습니다. 주목해야 할 점은 실시간과 추정 코스트의 비율에 모순이 있는지 없는지입니다.
쿼리 계획안에는 한번 이상의 하위 계획노드를 실행하는 것이 가능합니다. 예를 들면, 내부 인덱스 스캔은 위에서 언급된 nested-plan에서 외부 행 하나마다 한번씩 실행되어집니다. 이러한 경우 "loops" 값은 노드를 실행하는 총회수를 기록하고, 표시되는 실제의 시간과 행 수는 한번 실행에 대한 평균이 됩니다. 이것은 표시된 추정 코스트와 비교할 수 있는 값을 작성하는 것으로 실시합니다. "loops"값을 곱해서 노드로 실제로 소비된 총 시간을 얻을 수 있습니다.
EXPLAIN ANALYZE로 표시되는 Total runtime(총실행 시간)은 결과행을 조작하기 위한 시간 외에, 수행자의 기동, 정지시간이나 포함됩니다. 해석이나 고쳐 써 계획 작성의 시간은 포함되지 않습니다. SELECT쿼리에서는 총실행 시간은 최상정도 계획 노드로 보고되는 총시간보다 보통 조금 커집니다. INSERT, UPDATE, 및 DELETE커맨드에서는 그 총실행 시간은 꽤 커질 가능성이 있습니다. 거기에는 결과행을 처리하는 시간이 포함되기 때문입니다. 이러한 커맨드에서는 최상정도 계획 노드의 시간은 기본적으로 새로운 행을 연산해, 오래된 행의 장소를 결정하기 위한 시간을 나타내, 거기에는 변경을 실시하는 시간은 포함되지 않습니다. 트리거가 발행되는 경우, 트리거의 발행에 걸리는 시간도 최상정도 계획 노드에는 포함되지 않고, 트리거마다 따로 따로 표시됩니다.
EXPLAIN의 결과를 시험을 실시한 것 이외의 추정에 이용해서는 안되는 것은 말할 것도 없습니다. 예를 들면, 작은 테이블의 결과는 거대한 테이블에 적용할 수 있다고는 가정할 수 없습니다. planner의 추정 코스트는 선형은 아니고, 그 때문에, 테이블의 대소에 따라서 다를 계획을 선택할 가능성이 있습니다. 극단적인 예이지만, 테이블이 1 디스크 페이지 밖에 차지하지 않는 경우, 인덱스를 사용할 수 있는 할 수 없는 관계없이, 거의 항상 시퀀셜 스캔 계획을 얻게 됩니다. planner는 어떠한 경우에서도 테이블을 처리하기 위해서 1 디스크 페이지 read를 실시하므로, 인덱스를 참조하기 위한 추가적 페이지 read를 실시하는 가치가 없는 것을 알고 있습니다.
| [1] |
본 절의 예는 8.2 개발판 소스의 regression 시험 데이터베이스로부터 VACUUM ANALYZE를 실행한 다음에 발췌한 것입니다. 실제로 이 예를 시험하면, 비슷한 결과를 얻게 되겠지만, 추정 코스트나 행 수는 다소 다르게 나타날 것입니다. ANALYZE에 의한 통계 정보는 정확한 것이 아닌 무작위 샘플링을 실시한 결과이기 때문입니다. |