프로호스터 > 블로그 > 관리 > PostgreSQL 안티패턴: SQL에서 조건 평가

PostgreSQL 안티패턴: SQL에서 조건 평가

SQL은 C++도 아니고 JavaScript도 아닙니다. 따라서 논리식 계산은 다르게 발생하며 이는 전혀 동일하지 않습니다.

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

PostgreSQL 쿼리 실행 계획을 최적화하는 중 동등한 조건을 임의로 "재배열"할 수 있습니다., 개별 레코드에 대해 일부를 계산하지 말고 적용된 인덱스의 조건과 연결하십시오... 간단히 말해서, 가장 쉬운 방법은 다음과 같이 가정하는 것입니다. 통제할 수 없다 어떤 순서로 계산할지(그리고 계산할지 여부) 공평한 조건들

그러므로 그래도 우선순위를 관리하고 싶다면 구조화해야 합니다. 이러한 조건을 불평등하게 만들어라 조건문 사용 표현 и 연산자.

PostgreSQL 안티패턴: SQL에서 조건 평가
데이터와 그에 따른 작업이 기본입니다 우리의 VLSI 콤플렉스따라서 이에 대한 작업이 올바르게 수행될 뿐만 아니라 효율적으로 수행되는 것이 매우 중요합니다. 표현식 계산 시 오류가 발생할 수 있는 부분과 효율성을 향상시킬 가치가 있는 구체적인 예를 살펴보겠습니다.

#0: RTFM

시작 문서의 예:

평가 순서가 중요한 경우 구문을 사용하여 캡처할 수 있습니다. CASE. 예를 들어, 이는 문장에서 XNUMX으로 나누는 것을 방지하는 방법입니다. WHERE 신뢰할 수 없는:

SELECT ... WHERE x > 0 AND y/x > 1.5;

안전한 옵션:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

이런 식으로 사용되는 디자인 CASE 표현식이 최적화되지 않도록 보호하므로 필요한 경우에만 사용해야 합니다.

#1: 트리거 조건

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

모든 것이 좋아 보이는데... 투자를 약속하는 사람은 아무도 없습니다. SELECT 첫 번째 조건이 거짓이면 실행되지 않습니다. 그걸로 고치자 중첩된 IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

이제 주의 깊게 살펴보겠습니다. 트리거 기능의 전체 본문이 "래핑"되어 있습니다. IF. 이는 다음을 사용하여 절차에서 이 조건을 제거하는 것을 방해하는 것이 없음을 의미합니다. WHEN-정황:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

이 접근 방식은 조건이 false인 경우 서버 리소스를 절약하는 것이 보장됩니다.

#2: OR/AND 체인

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

그렇지 않으면 둘 다로 끝날 수 있습니다. EXISTS "사실"이겠지만, 둘 다 충족될 것이다.

그러나 그 중 하나가 "참"이라는 것을 확실히 안다면 훨씬 더 자주 (또는 "거짓"입니다. AND-체인) - 두 번째 항목이 다시 실행되지 않도록 어떻게든 "우선순위를 높이는" 것이 가능합니까?

가능하다는 것이 밝혀졌습니다. 알고리즘 접근 방식이 기사 주제에 가깝습니다. PostgreSQL 안티패턴: 드문 레코드가 JOIN 중간에 도달합니다..

CASE 아래에 이 두 조건을 모두 "밀어넣어" 보겠습니다.

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

이 경우에는 정의하지 않았습니다. ELSE-값, 즉 두 조건이 모두 거짓인 경우 CASE 돌아올 것이다 NULL, 이는 다음과 같이 해석됩니다. FALSE в WHERE-정황.

이 예는 취향과 색상에 따라 다른 방법으로 결합할 수 있습니다.

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: 조건을 작성하지 않는 방법

우리는 이 트리거의 "이상한" 작동 이유를 분석하는 데 이틀을 보냈습니다. 그 이유를 살펴보겠습니다.

출처 :

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

문제 #1: 부등식은 NULL을 존중하지 않습니다.

모든 것이 있다고 상상해 봅시다. OLD- 필드에는 의미가 있었습니다. NULL. 무슨 일이 일어날 것?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

그리고 조건을 해결하는 관점에서 보면 NULL 동등한 FALSE, 상술 한 바와 같이.

결정: 연산자 사용 IS DISTINCT FROM 부터 ROW-연산자, 전체 레코드를 한 번에 비교:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

문제 #2: 동일한 기능을 다르게 구현함

비교해 보겠습니다.

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

여기에 추가 투자가 필요한 이유는 무엇입니까? SELECT? 기능 to_regclass? 왜 다른가요?..

수정하자:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

문제 #3: bool 연산의 우선순위

소스 형식을 지정해 보겠습니다.

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

이런... 사실 처음 두 조건 중 하나라도 참이면 전체 조건이 다음과 같이 바뀌는 것으로 나타났습니다. TRUE, 불평등을 고려하지 않고. 그리고 이것은 우리가 원했던 것이 전혀 아닙니다.

수정하자:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

문제 #4(소형): 한 필드에 대한 복잡한 OR 조건

사실 3번은 세 가지 조건이 있었기 때문에 문제가 있었습니다. 하지만 그 대신 메커니즘을 사용하여 하나를 사용할 수 있습니다. coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

그래서 우리는 NULL “우리가 잡겠다”, 그리고 어렵다 OR 괄호로 울타리를 칠 필요가 없습니다.

전체로

우리가 얻은 것을 기록해 봅시다:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

그리고 이 트리거 기능은 다음에서만 사용할 수 있다고 생각한다면 UPDATE- 가용성으로 인한 트리거 OLD/NEW 상위 수준 조건에서 이 조건은 일반적으로 다음 위치에 배치될 수 있습니다. WHEN-조건, #1에 표시된 대로...

출처 : habr.com

코멘트를 추가