PostgreSQL 쿼리 프로파일러: 계획과 쿼리를 일치시키는 방법

이미 사용하고 있는 많은 분들이 explain.tensor.ru - PostgreSQL 계획 시각화 서비스는 읽기 어려운 서버 로그 부분을 바꾸는 강력한 기능 중 하나를 인식하지 못할 수도 있습니다.

... 해당 계획 노드에 대한 상황별 힌트를 사용하여 아름답게 디자인된 쿼리로 변환합니다.

그의 두 번째 부분의 사본에서 PGConf.Russia 2020에서 보고 우리가 어떻게 이 일을 할 수 있었는지 말씀드리겠습니다.

일반적인 쿼리 성능 문제와 그 해결 방법을 다룬 첫 번째 부분의 기록은 기사에서 찾을 수 있습니다. "병든 SQL 쿼리에 대한 레시피".

먼저 채색을 시작하겠습니다. 더 이상 계획을 채색하지 않고 이미 채색했으며 이미 아름답고 이해할 수 있지만 요청이 있습니다.

형식이 지정되지 않은 "시트"를 사용하여 로그에서 가져온 요청은 매우 보기 흉하고 따라서 불편한 것처럼 보였습니다.


특히 개발자가 코드의 요청 본문을 한 줄에 "접착"하는 경우(물론 안티패턴이지만 발생합니다). 끔찍해!

좀 더 아름답게 그려보자.


그리고 이를 아름답게 그릴 수 있다면, 즉 요청 본문을 분해하고 다시 조립할 수 있다면 이 요청의 각 개체에 힌트(계획의 해당 지점에서 발생한 일)를 "첨부"할 수 있습니다.

쿼리 구문 트리

이렇게 하려면 먼저 요청을 구문 분석해야 합니다.


왜냐하면 우리는 시스템의 핵심은 NodeJS에서 실행됩니다., 그런 다음 이를 위한 모듈을 만들었습니다. GitHub에서 찾아보세요. 실제로 이는 PostgreSQL 파서 자체의 내부로 확장된 "바인딩"입니다. 즉, 문법은 단순히 바이너리로 컴파일되고 NodeJS에서 바인딩됩니다. 우리는 다른 사람의 모듈을 기초로 삼았습니다. 여기에는 큰 비밀이 없습니다.

요청 본문을 함수에 대한 입력으로 제공합니다. 출력에서 ​​JSON 개체 형식의 구문 분석된 구문 트리를 얻습니다.


이제 우리는 이 트리를 반대 방향으로 실행하고 원하는 들여쓰기, 색상 및 서식을 사용하여 요청을 조합할 수 있습니다. 아니요, 맞춤 설정할 수는 없지만 이것이 편리할 것 같았습니다.

쿼리 및 계획 노드 매핑

이제 첫 번째 단계에서 분석한 계획과 두 번째 단계에서 분석한 쿼리를 어떻게 결합할 수 있는지 살펴보겠습니다.

간단한 예를 들어보겠습니다. CTE를 생성하고 이를 두 번 읽는 쿼리가 있습니다. 그는 그러한 계획을 세웁니다.

CTE

잘 보면 버전 12까지(혹은 그것부터 키워드로 시작해서) MATERIALIZED) 형성 CTE는 기획자에게 절대적인 장벽입니다..


즉, 요청 어딘가에 CTE 생성이 있고 계획 어딘가에 노드가 있는 경우 CTE, 그러면 이들 노드는 확실히 서로 "싸움"하므로 즉시 결합할 수 있습니다.

별표 문제: CTE는 중첩될 수 있습니다.

중첩이 매우 불량하고 심지어 같은 이름의 것도 있습니다. 예를 들어, 내부에서 다음을 수행할 수 있습니다. CTE A ~을하다 CTE X, 그리고 내부도 같은 수준에 있습니다 CTE B 다시 할 CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

비교할 때 이것을 이해해야합니다. 이것을 “눈으로” 이해하는 것, 심지어 계획을 보는 것, 심지어 요청의 본문을 보는 것조차 매우 어렵습니다. CTE 생성이 복잡하고 중첩되어 있으며 요청이 크다면 완전히 무의식적입니다.

UNION

검색어에 키워드가 있는 경우 UNION [ALL] (두 샘플을 결합하는 연산자) 계획에서는 노드 중 하나에 해당합니다. Append, 또는 일부 Recursive Union.


위의 "위"에 있는 것 UNION - 이것은 "아래"에 있는 우리 노드의 첫 번째 자손입니다. 만약 통과한다면 UNION 한 번에 여러 블록을 "접착"한 다음 Append-여전히 하나의 노드만 있지만 두 개가 아닌 많은 자식을 갖게 됩니다. 각각 순서대로:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

별표 문제: 내부 재귀 샘플링 생성(WITH RECURSIVE)은 둘 이상일 수도 있습니다. UNION. 그러나 마지막 블록 다음의 가장 마지막 블록만 항상 재귀적입니다. UNION. 위의 모든 것은 하나이지만 다릅니다. UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

또한 그러한 예를 "돋보이게" 할 수 있어야 합니다. 이 예에서 우리는 UNION-요청에는 3개의 세그먼트가 있었습니다. 이에 따라 하나의 UNION 일치 Append-노드, 그리고 다른 노드 - Recursive Union.

데이터 읽기-쓰기

모든 것이 준비되었으므로 이제 요청의 어느 부분이 계획의 어느 부분에 해당하는지 알 수 있습니다. 그리고 이 작품들 속에서 우리는 '읽을 수 있는' 대상을 쉽고 자연스럽게 찾을 수 있습니다.

쿼리 관점에서는 테이블인지 CTE인지는 알 수 없지만 동일한 노드로 지정됩니다. RangeVar. 그리고 "가독성" 측면에서 이것은 상당히 제한된 노드 집합이기도 합니다.

• Seq Scan on [tbl]
• Bitmap Heap Scan on [tbl]
• Index [Only] Scan [Backward] using [idx] on [tbl]
• CTE Scan on [cte]
• Insert/Update/Delete on [tbl]

우리는 계획과 쿼리의 구조를 알고, 블록의 대응 관계를 알고, 객체의 이름을 알고 있습니다. 일대일 비교를 수행합니다.


다시 "별표가 있는" 작업. 요청을 받아 실행합니다. 별칭이 없습니다. 동일한 CTE에서 두 번만 읽습니다.


우리는 계획을 봅니다. 문제가 무엇입니까? 왜 별칭이 생겼나요? 우리는 그것을 주문하지 않았습니다. 그런 "숫자 번호"는 어디서 얻습니까?

PostgreSQL은 이를 자체적으로 추가합니다. 그것만 이해하면 된다 그냥 그런 별칭 우리에게는 계획과의 비교 목적상 의미가 없으며 단순히 여기에 추가됩니다. 그 사람에게 관심을 기울이지 말자.

초 "별표가 있는" 작업: 분할된 테이블에서 읽는 경우 노드를 얻습니다. Append 또는 Merge Append, 이는 많은 수의 "어린이"로 구성되며 각각은 어떻게 든 Scan'om 테이블 섹션에서 : Seq Scan, Bitmap Heap Scan 또는 Index Scan. 그러나 어떤 경우에도 이러한 "하위"는 복잡한 쿼리가 아닙니다. 이것이 이러한 노드를 구별하는 방법입니다. Append при UNION.


우리는 또한 그러한 매듭을 이해하고 "한 더미로" 모아 다음과 같이 말합니다.메가테이블에서 읽은 모든 내용은 여기 트리 아래에 있습니다.".

"간단한" 데이터 수신 노드

Values Scan 계획에 해당 VALUES 요청에.

Result 없는 요청입니다 FROM ~처럼 SELECT 1. 혹은 의도적으로 거짓 표현을 했을 때 WHERE-block(그러면 속성이 나타납니다. One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan 동일한 이름의 SRF에 "매핑"합니다.

그러나 중첩된 쿼리를 사용하면 모든 것이 더 복잡해집니다. 불행하게도 쿼리가 항상 다음과 같이 바뀌지는 않습니다. InitPlan/SubPlan. 때때로 그들은 다음과 같이 변합니다. ... Join 또는 ... Anti Join, 특히 다음과 같은 것을 쓸 때 WHERE NOT EXISTS .... 그리고 여기서 그것들을 결합하는 것이 항상 가능한 것은 아닙니다. 계획의 텍스트에는 계획의 노드에 해당하는 연산자가 없습니다.

다시 "별표가 있는" 작업: 일부 VALUES 요청에. 이 경우와 계획에서는 여러 노드를 얻게 됩니다. Values Scan.


"번호가 매겨진" 접미사는 서로 구별하는 데 도움이 됩니다. 해당 접미사는 해당 접미사가 발견된 순서대로 정확하게 추가됩니다. VALUES-요청을 위에서 아래로 차단합니다.

데이터 처리

요청 내용이 모두 정리된 것 같습니다. 이제 남은 것은 Limit.


그러나 여기서는 모든 것이 간단합니다. Limit, Sort, Aggregate, WindowAgg, Unique 요청의 해당 연산자(있는 경우)에 일대일로 "매핑"합니다. 여기에는 "별"이나 어려움이 없습니다.

JOIN

결합하고 싶을 때 어려움이 발생합니다 JOIN 그들 사이. 항상 가능한 것은 아니지만 가능합니다.


쿼리 파서의 관점에서 보면 노드가 있습니다. JoinExpr, 왼쪽과 오른쪽 정확히 두 명의 자식이 있습니다. 따라서 이는 JOIN "위"에 있는 내용이며 요청에서 JOIN "아래"에 작성된 내용입니다.

그리고 계획의 관점에서 볼 때 이들은 일부의 후손입니다. * Loop/* Join-마디. Nested Loop, Hash Anti Join,... - 그런 거요.

간단한 논리를 사용해 보겠습니다. 계획에서 서로 "결합"하는 테이블 A와 B가 있는 경우 요청에서 해당 테이블을 찾을 수 있습니다. A-JOIN-B또는 B-JOIN-A. 이 방법으로 결합하고, 반대 방향으로 결합하는 식으로 이러한 쌍이 부족할 때까지 계속합니다.

구문 트리를 살펴보고 계획을 세워 살펴보겠습니다. 유사하지 않습니다!


그래프 형태로 다시 그려보겠습니다. 아, 벌써 뭔가 보이는군요!


자식 B와 C를 동시에 갖는 노드가 있다는 점에 유의하세요. 어떤 순서인지는 신경 쓰지 않습니다. 그것들을 결합하고 노드의 그림을 뒤집어 봅시다.


다시 살펴보겠습니다. 이제 우리는 자식 A와 쌍 (B + C)을 가진 노드를 갖게 되었습니다. 이들과도 호환됩니다.


엄청난! 알고보니 우리 둘이네 JOIN 계획 노드와의 요청이 성공적으로 결합되었습니다.

아쉽게도 이 문제가 항상 해결되는 것은 아닙니다.


예를 들어 요청에 있는 경우 A JOIN B JOIN C, 그리고 계획에서는 우선 "외부" 노드 A와 C가 연결되었지만 요청에는 그러한 연산자가 없으며 강조할 것도 없고 힌트를 첨부할 것도 없습니다. 쓸 때 "쉼표"도 마찬가지예요 A, B.

그러나 대부분의 경우 거의 모든 노드는 "연결 해제"될 수 있으며 JavaScript 코드를 분석할 때 Google Chrome에서와 같이 문자 그대로 왼쪽에서 이러한 종류의 프로파일링을 얻을 수 있습니다. 각 줄과 각 문이 "실행"되는 데 걸린 시간을 확인할 수 있습니다.


그리고 이 모든 것을 더욱 편리하게 사용하실 수 있도록 수납공간을 마련해두었습니다. 아카이브에서 관련 요청과 함께 계획을 저장하고 나중에 찾거나 다른 사람과 링크를 공유할 수 있습니다.

읽을 수 없는 쿼리를 적절한 형식으로 가져오려면 다음을 사용하세요. 우리의 "노멀라이저".

출처 : habr.com