PostgreSQL 쿼리 계획을 훨씬 더 편리하게 이해

반년 전 우리는 제시 explain.tensor.ru - 공공의 쿼리 계획 구문 분석 및 시각화 서비스 PostgreSQL에.

지난 몇 달 동안 우리는 그에 대해 PGConf.Russia 2020에서 보고, 요약 준비 SQL 쿼리 속도 향상에 대한 문서 제공되는 권장 사항을 기반으로 하지만 가장 중요한 것은 귀하의 피드백을 수집하고 실제 사용 사례를 살펴봤다는 것입니다.

이제 사용할 수 있는 새로운 기능에 대해 알려드릴 준비가 되었습니다.

다양한 계획 형식 지원

요청과 함께 로그에서 계획

콘솔에서 직접 다음 행부터 시작하여 전체 블록을 선택합니다. 쿼리 텍스트, 모든 선행 공백 포함:

        Query Text: INSERT INTO  dicquery_20200604  VALUES ($1.*) ON CONFLICT (query)
                           DO NOTHING;
        Insert on dicquery_20200604  (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: dicquery_20200604_pkey
          Tuples Inserted: 1
          Conflicting Tuples: 0
          Buffers: shared hit=9 read=1 dirtied=1
          ->  Result  (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)

... 아무것도 분리하지 않고 계획의 필드에 직접 복사한 모든 항목을 던집니다.

출력에서 분해 계획에 대한 보너스도 얻습니다. 컨텍스트 탭, 여기서 우리의 요청은 모든 영광으로 제시됩니다.

JSON 및 YAML

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;

"[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "pg_class",
      "Alias": "pg_class",
      "Startup Cost": 0.00,
      "Total Cost": 1336.20,
      "Plan Rows": 13804,
      "Plan Width": 539,
      "Actual Startup Time": 0.006,
      "Actual Total Time": 1.838,
      "Actual Rows": 10266,
      "Actual Loops": 1,
      "Shared Hit Blocks": 646,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 5.135,
    "Triggers": [
    ],
    "Execution Time": 2.389
  }
]"

외부 따옴표가 있더라도 pgAdmin이 복사한 것처럼 동일한 필드에 넣지 않아도 출력은 아름답습니다.

고급 시각화

계획시간 / 실행시간

이제 쿼리를 실행할 때 추가 시간이 소요된 위치를 더 잘 확인할 수 있습니다.

입출력 타이밍

때때로 리소스 측면에서 너무 많이 읽고 쓰지 않은 것처럼 보이지만 실행 시간이 어떤 이유로 부적절하게 큰 것 같은 상황에 대처해야 합니다.

여기서 말해야 합니다.아, 아마도 그 순간 서버의 디스크가 너무 과부하되어서 읽는 데 너무 오래 걸렸을 것입니다!"하지만 어쩐지 그다지 정확하지 않아...

그러나 절대적으로 안정적으로 결정할 수 있습니다. 사실 PG 서버의 구성 옵션 중에는 다음이 있습니다. track_io_timing:

시간 지정 I/O 작업을 활성화합니다. 이 설정은 운영 체제가 현재 시간을 지속적으로 쿼리해야 하므로 일부 플랫폼에서 속도가 크게 느려질 수 있으므로 기본적으로 비활성화되어 있습니다. pg_test_timing 유틸리티를 사용하여 플랫폼에서 타이밍의 오버헤드를 추정할 수 있습니다. I/O 통계는 pg_stat_database 뷰를 통해 얻을 수 있으며, EXPLAIN 출력에서(BUFFERS 매개변수가 사용된 경우) 그리고 pg_stat_statements 보기를 통해.

이 옵션은 로컬 세션 내에서도 활성화할 수 있습니다.

SET track_io_timing = TRUE;

이제 가장 중요한 부분은 실행 트리의 모든 변환을 고려하여 이 데이터를 이해하고 표시하는 방법을 배웠다는 것입니다.

여기에서 총 실행 시간의 0.790ms 중 0.718ms는 한 페이지의 데이터를 읽는 데, 0.044ms는 데이터를 쓰는 데, 그리고 0.028ms만이 다른 모든 유용한 활동에 사용되었음을 알 수 있습니다!

PostgreSQL 13의 미래

새로운 기능에 대한 전체 개요는 다음을 참조하세요. 자세한 기사에서, 특히 계획 변경에 대해 이야기하고 있습니다.

계획 버퍼

스케줄러에 할당된 자원에 대한 계산은 pg_stat_statements와 관련되지 않은 다른 패치에 반영됩니다. BUFFERS 옵션이 있는 EXPLAIN은 계획 단계에서 사용된 버퍼 수를 보고합니다.

 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

증분 정렬

많은 키(k1, k2, k3…)로 정렬해야 하는 경우 플래너는 이제 데이터가 이미 여러 첫 번째 키(예: k1 및 k2)로 정렬되어 있음을 알 수 있습니다. 이 경우 모든 데이터를 새로 재정렬할 수는 없고 k1과 k2의 같은 값으로 연속적인 그룹으로 나누고 k3 키로 "재정렬"합니다.

따라서 전체 정렬은 더 작은 크기의 여러 연속 정렬로 나뉩니다. 이렇게 하면 필요한 메모리 양이 줄어들고 모든 정렬이 완료되기 전에 첫 번째 데이터를 반환할 수도 있습니다.

 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

UI/UX 개선

스크린샷은 어디에나 있습니다!

이제 각 탭에서 탭의 스크린샷을 클립보드로 가져오기 탭의 전체 너비와 깊이 - "시력" 오른쪽 상단:

실제로 이 출판물에 실린 대부분의 사진은 이런 방식으로 입수했습니다.

노드에 대한 권장 사항

더 많은 것이 있을 뿐만 아니라 각각에 대해 할 수 있습니다. 글을 자세히 읽어보세요링크를 따라:

아카이브에서 제거

어떤 사람들은 능력을 요구했습니다. "절대" 삭제 아카이브에 게시되지 않은 계획도 해당 아이콘을 클릭하십시오.

글쎄, 우리가 가지고 있다는 것을 잊지 말자 지원 그룹의견과 제안을 작성할 수 있습니다.

출처 : habr.com