| PostgreSQL 8.3.3문서 | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 13. 성능효율 팁 | Fast Forward | Next |
명시적인JOIN구문을 사용해 쿼리 planner를 어느 정도 제어할 수 있습니다. 어째서 이런 것이 문제가 되는지, 우선 그 배경을 볼 필요가 있습니다.
단순한 쿼리해 예를 들면
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
그럼, planner는 자유롭게 주어진 테이블을 임의의 순서로 조인할 수 있습니다. 예를 들면, WHERE조건의a.id = b.id를 사용해 우선 A와 B를 조인해, 다른WHERE조건을 사용해 그 조인 테이블에 C를 조인한다고 하는 계획을 세울 수 있습니다. 혹은 B와 C를 조인해, 그 결과에 A를 조인할 수도 있습니다. 혹은 A와 C를 조인해, 그 결과에 B를 조인할 수도 있겠지요. 그러나 그러면 효율이 좋지 않습니다. 왜냐하면, 조인의 최적화를 실시하기 위해서 적용할 수 있는 조건이WHERE구문에 없기 때문에, A와 C의 전직적이 만들어지기 때문입니다 (PostgreSQL의 수행자에서는 조인은 모두 2개의 테이블의 사이에 행해지기 때문에, 이와 같이 해 1개하나 결과를 만들어 가지 않으면 안됩니다). 중요한 것은 이러한 다른 조인의 방법은 의미적으로는 같은 결과이지만, 실행 코스트는 크게 다를 가능성이 있다고 하는 것입니다. 그러니까, planner는 가장 효율의 좋은 플랜을 찾기 위해서 가능한 플랜을 모두 검사합니다.
조인의 대상이 겨우 2, 3개의 테이블이라면 걱정하는 만큼 조인의 종류는 많지 않습니다. 그러나 테이블수가 증가하면 가능한 조인의 수는 지수함수적으로 증가해 갑니다. 10 정도 이상으로 테이블이 증가하면, 모든 가능성을 해들 보고 부수어에 탐색하는 것은 이미 실용적인 것은 없어집니다. 6이나 7개의 테이블조차도, 계획을 작성할 시간이 무시할 수 없게 됩니다. 테이블의 수가 너무 많을 때는 PostgreSQL의 planner는 해 해의 탐색으로부터, 한정된 가능성만을 탐색하는 유전적확률적인 탐색으로 바뀝니다 (변환의 threshold은geqo_threshold실행시 파라미터로 설정됩니다). 유전적 탐색은 짧은 시간에 탐색을 실시하지만, 반드시 최적인 계획을 찾아낸다고는 할 수 없습니다.
외부 조인이 포함되는 것 같은 쿼리에서는 planner에는 보통의(내부) 조인으로부터 선택의 여지가 작아집니다. 예를 들면, 다음과 같은 쿼리를 생각합니다.
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
이 쿼리의 검색 조건은 전술의 예와 표면적으로는 비슷한 것처럼 생각되지만, B와 C의 조인 결과의 행에 적합하지 않는 A의 각 행이 출력되지 않으면 안 되기 때문에, 의미적으로는 다릅니다. 따라서, 여기에서는 planner에는 조인 순서에 관해서 선택의 여지가 없습니다. 우선 B와 C를 조인해, 그 결과에 A를 조인해야 합니다. 그러한 이유로, 이 쿼리에서는 계획을 세우는데 필요로 하는 시간은 전의 예보다 짧아집니다. 그 외의 경우, planner가 안전한 조인 순서를 복수 결정할 수 있을 가능성이 있습니다. 예를 들어, 이하를 생각해 보겠습니다.
SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
이 경우, A를 먼저 B와 조인해도 C와 조인해도 유효합니다. 현시점에서는 FULL JOIN만이 완전하게 조인 순서를 제한합니다. LEFT JOIN나 RIGHT JOIN를 포함한, 대부분의 실행 환경에서는 일부 확장으로 재조정할 수 있습니다.
명시적인 내부 조인 구문(INNER JOIN, CROSS JOIN, 장식이 없는 JOIN)은 의미적으로는 FROM내의 입력 릴레이션의 열거와 같습니다. 따라서, 조인 순서를 제약할 필요는 없습니다.
대부분의 종류의JOIN는 완전하게 조인 순서를 제약하지만, PostgreSQL쿼리 planner에, 모든JOIN구문에 대해서 우선 조인 순서를 제한시킬 수 있습니다. 예를 들면, 이하의 3개의 쿼리는 논리적으로 동일합니다.
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id; SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id; SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
그러나 planner에JOIN의 차례를 지키도록 전했을 경우, 2번째와 3번째의 쿼리는 최초의 것보다 짧은 시간에 계획을 세울 수 있습니다. 이 효과는 3개의 테이블만 있을 때는 신경쓸 정도의 것이 아니지만, 많은 테이블을 조인할 때는 구원자 역할을 해 줄 것입니다.
planner를 강제적으로 명시적인JOIN에 잠재하는 조인 순서에 따르게 하려면, join_collapse_limit실행시 파라미터를 1으로 설정해 주십시요 (이하로 다른 취할 수 있는 값에 대해 설명합니다).
검색 시간을 절약하기 위해서, 조인 순서를 완전하게 속박 할 필요는 없습니다. 왜냐하면, 단순한FROM리스트의 항목내에JOIN연산자를 사용해도 상관없기 때문입니다. 예를 들면, 다음의 예입니다.
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
With join_collapse_limit = 1, this forces the planner to join A to B before joining them to other tables, but doesn't constrain its choices otherwise. In this example, the number of possible join orders is reduced by a factor of 5. --> join_collapse_limit = 1으로 했을 경우, planner는 강제적으로 다른 테이블과 조인하기 전에 A와 B를 조인하지만, 그 이외에 대해서는 특히 구속은 없습니다. 이 예에서는 조인 순서의 후보는 5분의 1으로 감소합니다.
이러한 방법으로 planner의 검색에 제약을 더하는 것은 계획 작성 시간의 단축과 planner에 대한 뛰어난 쿼리 계획을 방향지을 수 있습니다. 이 둘은 모두 유용한 기법입니다. planner가 뒤떨어진 조인 순서를 디폴트로 선택한다면, JOIN구문 경유로 보다 좋은 차례를 선택하도록 강제할 수 있습니다. 다만, 보다 좋은 차례를 이해한다고 가정해야합니다. 이것은 실험해 볼 것을 권합니다.
쿼리 작성 시간에 영향을 주는 밀접하게 관련된 문제는 하위 쿼리에서 부모 쿼리로 충돌되는 것입니다. 예를 들면, 이하를 생각해 보겠습니다.
SELECT *
FROM x, y,
(SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;
이런 상황은 조인을 포함하는 뷰를 사용함으로써 발생됩니다. 뷰의 SELECT 룰은 뷰 참조 공간에 추가될 것입니다. 영향 받기 쉬운 쿼리는 위와 같습니다. 일반적으로 planner는 부모 쿼리에서 하위 쿼리가 충돌하도록 시도할 것입니다.
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
이것은 보통, 하위 쿼리의 계획을 별도 작성하는 것보다 뛰어난 계획을 작성합니다 (예를 들면, 외부의WHERE조건은 X를 A에 조인하게 되어, 우선 A가 많은 행이 제거됩니다. 이것에 의해, 하위 쿼리의 완전한 논리적 출력이 불필요하게 됩니다). 그러나 동시에 계획 작성 시간이 증가합니다. 이 경우, 2개의 3방법의 조인 문제로부터 5방법의 조인 문제가 됩니다. 후보 수는 지수함수적으로 증가하기 위해, 이것은 큰 차이가 됩니다. planner는 대규모 조인 검색 문제로 숨이 막히지 않게, 만약from_collapse_limit개의FROM항목이 부모 쿼리로 발생해 버리는 경우는 하위 쿼리를 충돌하지 않도록 해서 억제합니다. 이 실행시 파라미터의 값을 상하로 조정하여 계획 작성 시간과 계획의 질을 trade off 할 수 있습니다.
양자는 거의 같은 것을 실시하기 때문에, from_collapse_limit라고join_collapse_limit는 비슷한 이름을 가집니다. 다른 한쪽은 부선택도의"flatten out"를 planner가 언제 실시하는지를 제어하고, 다른 한쪽은 명시적인 조인의 flatten out를 언제 실시하는지를 제어합니다. 보통, join_collapse_limit를from_collapse_limit와 같은 값으로 설정(명시적인 내부 조인과 부쿼리의 동작을 같게 한다)하던지 join_collapse_limit를 1으로 설정(명시적인 조인으로 조인 순서를 제어하고 싶은 경우)하는 두 방법을 실시합니다. 그러나 계획 작성 시간과 실행 시간 사이의 trade off를 충분히 실시하는 경우는 이것들을 다른 값으로 설정해도 상관하지 않습니다.