PostgreSQL 안티패턴: 유해한 JOIN 및 OR

버퍼를 가져오는 작업에 주의하세요...
예를 들어 작은 쿼리를 사용하여 PostgreSQL에서 쿼리를 최적화하기위한 몇 가지 보편적 인 접근 방식을 살펴 보겠습니다. 당신이 그것들을 사용하든 아니든 당신에게 달려 있지만, 그들에 대해 아는 것이 좋습니다.

일부 후속 버전의 PG에서는 스케줄러가 더 똑똑해지면 상황이 변경 될 수 있지만 9.4/9.6의 경우 여기의 예에서와 거의 동일하게 보입니다.

매우 실제적인 요청을 들어보겠습니다.

SELECT
  TRUE
FROM
  "Документ" d
INNER JOIN
  "ДокументРасширение" doc_ex
    USING("@Документ")
INNER JOIN
  "ТипДокумента" t_doc ON
    t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
  (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
  d."$Черновик" IS NULL AND
  d."Удален" IS NOT TRUE AND
  doc_ex."Состояние"[1] IS TRUE AND
  t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;

테이블 및 필드 이름에 대해필드와 테이블의 "러시아어" 이름은 다르게 취급될 수 있지만 이는 취향의 문제입니다. 왜냐하면 여기 텐서에서 외국 개발자는 없으며 PostgreSQL에서는 상형 문자로도 이름을 지정할 수 있습니다. 따옴표로 둘러싸여 있습니다, 그런 다음 불일치가 없도록 객체의 이름을 명확하고 명확하게 지정하는 것을 선호합니다.
결과 계획을 살펴보겠습니다.

[explain.tensor.ru 참조]

144ms 및 거의 53K 버퍼 - 즉, 400MB 이상의 데이터입니다! 그리고 요청 시점에 모든 항목이 캐시에 있으면 운이 좋을 것입니다. 그렇지 않으면 디스크에서 읽을 때 시간이 몇 배 더 오래 걸릴 것입니다.

알고리즘이 가장 중요합니다!

요청을 어떻게든 최적화하려면 먼저 요청이 수행해야 하는 작업을 이해해야 합니다.
데이터베이스 구조 자체의 개발에 대해서는 지금은 이 기사의 범위를 벗어나서 상대적으로 "저렴하게" 할 수 있다는 점에 동의하겠습니다. 요청을 다시 작성 그리고/또는 우리에게 필요한 것들을 바닥에 굴려 놓을 수도 있습니다 색인.

따라서 요청은 다음과 같습니다.
— 적어도 일부 문서의 존재 여부를 확인합니다.
- 우리가 필요로 하는 특정 유형의 조건에서
- 저자나 수행자가 우리에게 필요한 직원인 경우

가입 + 제한 1

많은 수의 테이블이 처음 조인된 다음 전체 세트에서 하나의 레코드만 남게 되는 쿼리를 개발자가 작성하는 것이 더 쉬운 경우가 많습니다. 그러나 개발자에게 더 쉽다고 해서 데이터베이스가 더 효율적이라는 의미는 아닙니다.
우리의 경우에는 테이블이 3개뿐이었습니다. 그 효과는 무엇입니까?

먼저 "Document Type" 테이블과의 연결을 제거하고 동시에 데이터베이스에 다음을 알려줍니다. 우리의 유형 레코드는 독특합니다 (우리는 이것을 알고 있지만 스케줄러는 아직 모릅니다):

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
  LIMIT 1
)
...
WHERE
  d."ТипДокумента" = (TABLE T)
...

예, 테이블/CTE가 단일 레코드의 단일 필드로 구성된 경우 PG에서는 다음과 같이 작성할 수도 있습니다.

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

PostgreSQL 쿼리의 지연 평가

비트맵또는 UNION

어떤 경우에는 비트맵 힙 스캔에 많은 비용이 들 수 있습니다. 예를 들어 꽤 많은 레코드가 필수 조건을 충족하는 상황에서는 그렇습니다. 우리는 그것을 얻었습니다 왜냐하면 OR 조건이 BitmapOr로 바뀌었습니다.- 계획대로 운영 중.
원래 문제로 돌아가 보겠습니다. 이에 해당하는 레코드를 찾아야 합니다. 어느 누구에게 즉, 두 조건 모두에서 59K 레코드를 모두 검색할 필요가 없습니다. 한 가지 조건을 해결하는 방법이 있으며, 첫 번째에서 아무것도 발견되지 않았을 때만 두 번째로 이동. 다음 디자인이 도움이 될 것입니다.

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"외부" LIMIT 1은 첫 번째 레코드가 발견되면 검색이 종료되도록 보장합니다. 그리고 첫 번째 블록에서 이미 발견된 경우 두 번째 블록은 실행되지 않습니다(한번도 처형된 적이 없다 에 관하여).

"사건에서 어려운 조건을 숨기기"

원래 쿼리에는 관련 테이블 "DocumentExtension"에 대해 상태를 확인하는 매우 불편한 순간이 있습니다. 표현의 다른 조건이 참인지 여부에 관계없이(예: d.“삭제”는 사실이 아닙니다.), 이 연결은 항상 실행되며 "리소스 비용이 발생"합니다. 그 중 어느 정도는 소비됩니다. 이 테이블의 크기에 따라 다릅니다.
그러나 관련 레코드 검색이 실제로 필요한 경우에만 발생하도록 쿼리를 수정할 수 있습니다.

SELECT
  ...
FROM
  "Документ" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
      SELECT
        "Состояние"[1] IS TRUE
      FROM
        "ДокументРасширение"
      WHERE
        "@Документ" = d."@Документ"
    )
  END

링크 된 테이블에서 우리에게 한 번 결과에는 필드가 필요하지 않습니다., 그런 다음 JOIN을 하위 쿼리의 조건으로 바꿀 수 있는 기회가 있습니다.
인덱스 된 필드를 "케이스 브래킷 외부"로두고 레코드에서 when 블록으로 간단한 조건을 추가하겠습니다. 이제 "무거운"쿼리는 통과 할 때만 실행됩니다.

내 성은 "토탈"이에요

결과 쿼리를 위에서 설명한 모든 메커니즘으로 수집합니다.

WITH T AS (
  SELECT
    "@ТипДокумента"
  FROM
    "ТипДокумента"
  WHERE
    "ТипДокумента" = 'ПланРабот'
)
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "Документ" d
    WHERE
      ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
      CASE
        WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
          SELECT
            "Состояние"[1] IS TRUE
          FROM
            "ДокументРасширение"
          WHERE
            "@Документ" = d."@Документ"
        )
      END
    LIMIT 1
  )
LIMIT 1;

[to] 인덱스 조정

훈련된 눈은 UNION 하위 블록의 인덱스 조건이 약간 다르다는 것을 알아차렸습니다. 이는 이미 테이블에 적합한 인덱스가 있기 때문입니다. 그리고 만약 존재하지 않는다면, 만들어 볼 가치가 있을 것입니다: 문서(Person3, 문서 유형) и 문서(문서 유형, 직원).
행 조건에서 필드의 순서에 대해기획자 입장에서는 물론 쓸 수도 있겠지만 (A, B) = (constA, constB)과 (B, A) = (constB, constA). 하지만 녹음할 때 인덱스의 필드 순서대로, 이러한 요청은 나중에 디버깅하는 것이 더 편리합니다.
계획에는 무엇이 있나요?

[explain.tensor.ru 참조]

불행하게도 우리는 운이 좋지 않았고 첫 번째 UNION 블록에서는 아무 것도 발견되지 않았기 때문에 두 번째 UNION 블록이 여전히 실행되었습니다. 하지만 그럼에도 불구하고 - 단지 0.037ms 및 11개 버퍼!
요청 속도를 높이고 메모리의 데이터 펌핑을 줄였습니다. 수천 번, 매우 간단한 기술을 사용하여 약간의 복사-붙여넣기로 좋은 결과를 얻을 수 있습니다. 🙂

출처 : habr.com