모든 것이 어떻게 시작되었는지 기억하십니까? 모든 게 처음이었고 또 다시 그랬어

PostgreSQL 쿼리를 최적화하는 방법과 그 결과에 대해 설명합니다.
왜 그래야만 했나요? 예, 지난 4년 동안 모든 것이 시계가 똑딱거리는 것처럼 조용하고 침착하게 작동했기 때문입니다.
비문으로.

실제 사건을 기반으로 합니다.
이름은 모두 변경되었으며 우연의 일치는 무작위입니다.

특정 결과를 얻었을 때 모든 것이 시작된 시작의 원동력이 무엇인지 기억하는 것은 항상 흥미 롭습니다.

그래서 그 결과 일어난 일은 기사에 간략하게 설명되어 있습니다.PostgreSQL 성능을 향상시키는 방법 중 하나로 합성".

이전 이벤트 체인을 재현하는 것은 아마도 흥미로울 것입니다.
기록에는 정확한 시작 날짜인 2018-09-10 18:02:48이 저장되었습니다.
또한 이야기에는 모든 것이 시작된 요청이 있습니다.
문제요청SELECT
p.parameter_id로 "PARAMETER_ID",
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS 고객_부품번호,
w. "LRM"은 LRM으로,
w. "LOTID"는 로티드로서,
w.RTD_값으로서 "RTD_VALUE",
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS 사용_이름,
s.“SPENT_DATE” AS 사용_날짜,
extract("SPENT_DATE"의 연도) AS 연도,
("SPENT_DATE"의 월)을 월로 추출합니다.
s."REPORT_NAME" AS 보고서_이름,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
wdata w에서,
s를 보냈다,
오후티르피,
지출_pd sp,
PD PD
WHERE s.“SPENT_ID” = w.“SPENT_ID”
AND p."PARAMETER_ID" = w."PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
AND s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'
및 s.“SPENT_DATE” = (SELECT MAX(s2.“SPENT_DATE”)
사용한 s2에서,
w데이터 w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
AND w2.“LRM” = w.“LRM”);

문제에 대한 설명은 예상대로 표준입니다. “모든 것이 나쁩니다. 문제가 무엇인지 말해 보세요.”
나는 즉시 3인치 드라이브 시절의 일화를 떠올렸습니다.

라머가 해커에게 다가옵니다.
- 아무것도 작동하지 않습니다. 문제가 있는 곳을 알려주십시오.
- DNA에...

하지만 물론 이것이 성능사고를 해결하는 방법은 아닙니다. “그들은 우리를 이해하지 못할 수도 있습니다" (와 함께). 우리는 그것을 알아내야 합니다.
글쎄, 파헤쳐 보자. 결과적으로 뭔가가 쌓일 수도 있습니다.

조사가 시작되었습니다

따라서 EXPLAIN에 의지하지 않고도 육안으로 즉시 볼 수 있는 것은 무엇입니까?
1) JOIN은 사용되지 않습니다. 특히 연결 수가 둘 이상인 경우 이는 좋지 않습니다.
2) 그러나 더욱 나쁜 것은 집계와 관련된 하위 쿼리입니다. 이건 정말 나빠.
물론 이것은 나쁘다. 그러나 이것은 한편에만 해당됩니다. 반면에, 문제에는 분명히 해결방안과 개선할 수 있는 요구사항이 있기 때문에 매우 좋습니다.
점쟁이에게 가지 마세요 (C).
쿼리 계획은 그다지 복잡하지는 않지만 매우 시사적입니다.
실행 계획

평소와 같이 가장 흥미롭고 유용한 것은 시작과 끝입니다.
중첩 루프(비용=935.84..479763226.18행=3322너비=135)(실제 시간=31.536..8220420.295행=8111656루프=1)
계획 시간: 3.807ms
실행 시간: 8222351.640ms
완료 시간은 2시간 이상입니다.

시간이 걸린 잘못된 가설

가설 1 - 최적화 프로그램이 실수를 하고 잘못된 계획을 세웁니다.

실행 계획을 시각화하기 위해 사이트를 사용합니다. https://explain.depesz.com/. 그러나 사이트에는 흥미롭거나 유용한 내용이 표시되지 않았습니다. 얼핏 보면 실제로 도움이 될 만한 것은 아무것도 없습니다. 전체 스캔이 최소화될 수 있습니까? 계속하세요.

가설 2 - Autovacuum 측에서 베이스에 영향을 미치려면 브레이크를 제거해야 합니다.

그러나 autovacuum 데몬은 잘 작동하며 오래 걸리는 프로세스가 없습니다. 심각한 부하가 없습니다. 우리는 다른 것을 찾아야 합니다.

가설 3 - 통계가 오래되어 모든 것을 다시 계산해야 합니다.

다시 말하지만 그렇지 않습니다. 통계는 최신입니다. Autovacuum에 문제가 없다는 점을 고려하면 이는 놀라운 일이 아닙니다.

최적화를 시작해보자

메인 테이블 'wdata'는 확실히 작지 않습니다. 거의 3만 개의 레코드가 있습니다.
그리고 Full Scan이 따르는 것이 바로 이 테이블입니다.

해시 조건: ((w."SPENT_ID" = s."SPENT_ID") AND ((하위 계획 1) = s."SPENT_DATE"))
-> 서열 스캔 wdata w에서 (비용=0.00..574151.49행=26886249 너비=46) (실제 시간=0.005..8153.565행=26873950 루프=1)
우리는 표준적인 일을 합니다: "자, 색인을 만들면 모든 것이 날아갈 것입니다."
"SPENT_ID" 필드에 색인을 생성했습니다.
그 결과 :
인덱스를 이용한 쿼리 실행 계획

글쎄요, 도움이 됐나요?
그것은 : 8 222 351.640ms (2시간 조금 넘게)
그것은되었다 : 6 985ms(거의 431.575시간)
일반적으로 동일한 사과, 측면도.
고전을 기억합시다.
“같은 것이 있는데 날개가 없나요? 추구할 것이다."

원칙적으로 이것은 좋은 결과라고 할 수 있습니다. 글쎄, 좋지는 않지만 수용 가능합니다. 최소한 고객에게 얼마나 많은 일이 이루어졌는지, 그리고 그 일이 왜 좋았는지 설명하는 대규모 보고서를 제공하십시오.
하지만 아직 최종 결정은 멀었습니다. 아주 멀리.

이제 가장 흥미로운 점은 우리가 계속해서 최적화하고 요청을 다듬을 것이라는 점입니다.

XNUMX단계 - JOIN 사용

이제 다시 작성된 요청은 다음과 같습니다(글쎄 적어도 더 아름다워):
JOIN을 사용한 쿼리SELECT
p.parameter_id로 "PARAMETER_ID",
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS 고객_부품번호,
w. "LRM"은 LRM으로,
w. "LOTID"는 로티드로서,
w.RTD_값으로서 "RTD_VALUE",
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS 사용_이름,
s.“SPENT_DATE” AS 사용_날짜,
extract("SPENT_DATE"의 연도) AS 연도,
("SPENT_DATE"의 월)을 월로 추출합니다.
s."REPORT_NAME" AS 보고서_이름,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN은 s ON w.“SPENT_ID”=s.”“SPENT_ID”를 소비했습니다.
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN sent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
WHERE
s.“SPENT_DATE” >= '2018-07-01' AND s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (최대 선택(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN은 s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”를 소비했습니다.
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
계획 시간: 2.486ms
실행 시간: 1223680.326ms

자, 첫 번째 결과입니다.
그것은 : 6ms(거의 985시간).
그것은되었다 : 1 223 680.326ms(20분 남짓).
좋은 결과. 원칙적으로 여기서도 멈출 수 있습니다. 하지만 너무 흥미롭지 않아서 멈출 수 없습니다.
Mbo

XNUMX단계 - 상관된 하위 쿼리 제거

변경된 요청 텍스트:
상관 하위 쿼리 없음SELECT
p.parameter_id로 "PARAMETER_ID",
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS 고객_부품번호,
w. "LRM"은 LRM으로,
w. "LOTID"는 로티드로서,
w.RTD_값으로서 "RTD_VALUE",
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS 사용_이름,
s.“SPENT_DATE” AS 사용_날짜,
extract("SPENT_DATE"의 연도) AS 연도,
("SPENT_DATE"의 월)을 월로 추출합니다.
s."REPORT_NAME" AS 보고서_이름,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN은 ON s.“SPENT_ID” = w.“SPENT_ID”를 소비했습니다.
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN sent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM 소비된 s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
w2.“LRM”으로 그룹화
) md on w.“LRM” = md.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
계획 시간: 2.291ms
실행 시간: 165021.870ms

그것은 : 1 223 680.326ms(20분 남짓).
그것은되었다 : 165 021.870ms(2분 남짓).
이것은 이미 꽤 좋습니다.
그러나 영국인이 말했듯이 "그러나 항상 그러나" 너무 좋은 결과는 자동으로 의심을 불러일으킬 것입니다. 여기에 뭔가 문제가 있습니다.

상관된 하위 쿼리를 제거하기 위해 쿼리를 수정하는 것에 대한 가설은 정확합니다. 하지만 최종 결과가 정확하려면 약간 조정해야 합니다.
결과적으로 첫 번째 중간 결과는 다음과 같습니다.
상관 하위 쿼리 없이 편집된 쿼리SELECT
p.parameter_id로 "PARAMETER_ID",
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS 고객_부품번호,
w. "LRM"은 LRM으로,
w. "LOTID"는 로티드로서,
w.RTD_값으로서 "RTD_VALUE",
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS 사용_이름,
s.“SPENT_DATE” AS 사용_날짜,
추출(s.“SPENT_DATE”의 연도) AS 연도,
(s.“SPENT_DATE”의 월)을 월로 추출하고,
s."REPORT_NAME" AS 보고서_이름,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN은 ON s.“SPENT_ID” = w.“SPENT_ID”를 소비했습니다.
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN sent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
내부 조인( SELECT w2.“LRM”, MAX(s2.“SPENT_DATE”) AS “SPENT_DATE”
FROM 소비된 s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
w2.“LRM”으로 그룹화
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” AND md.“LRM” = w.“LRM”
WHERE
s."SPENT_DATE" >= '2018-07-01' AND s."SPENT_DATE" <= '2018-09-30';
계획 시간: 3.192ms
실행 시간: 208014.134ms

따라서 우리가 최종적으로 만족할 수 있는 첫 번째 결과는 고객에게 보여주는 것이 부끄러운 일이 아닙니다.
다음으로 시작됨: 8 222 351.640 ms (2시간 이상)
우리는 1ms(223분 조금 넘는 시간)를 달성했습니다.
결과(중간): 208 014.134ms(3분 남짓).

훌륭한 결과.

합계

우리는 거기서 멈출 수도 있었습니다.
그러나…
식욕은 식사와 함께 제공됩니다. 걷는 사람은 길을 마스터할 것이다. 모든 결과는 중간입니다. 멈추고 죽었습니다. 등.
최적화를 계속해 보겠습니다.
좋은 생각이에요. 특히 고객이 신경 쓰지 않았다는 점을 고려하면 더욱 그렇습니다. 그리고 심지어 그것을 위해 강력하게.

이제 데이터베이스를 재설계해야 할 때입니다. 쿼리 구조 자체는 더 이상 최적화할 수 없습니다(나중에 밝혀진 것처럼 모든 것이 실제로 실패하는지 확인하는 옵션이 있지만). 그러나 데이터베이스 설계 최적화 및 개발을 시작하는 것은 이미 매우 유망한 아이디어입니다. 그리고 가장 중요한 것은 흥미 롭습니다. 다시 한 번, 당신의 젊음을 기억하십시오. 저는 바로 DBA가 된 것이 아니라 프로그래머(BASIC, 어셈블러, C, 더블 플러스 C, Oracle, plsql)로 성장했습니다. 물론 별도의 회고록에 대한 흥미로운 주제입니다 ;-).
그러나 산만해지지 말자.

따라서,

아니면 파티셔닝이 도움이 될까요?
스포일러 - "예, 성능 최적화를 포함하여 도움이 되었습니다."

그러나 그것은 완전히 다른 이야기입니다 ...

계속…

출처 : habr.com