PostgreSQL 안티패턴: "하나만 있어야 합니다!"

SQL에서는 실행해야 하는 "방법"이 아니라 달성하려는 "무엇"을 설명합니다. 그러므로 "들은 대로 쓰여진다"라는 스타일로 SQL 쿼리를 개발하는 문제가 그 자리를 대신합니다. SQL의 계산 조건 기능.

오늘은 매우 간단한 예를 사용하여 이것이 사용 맥락에서 어떤 결과를 가져올 수 있는지 살펴보겠습니다. GROUP/DISTINCT и LIMIT 그들과 함께.

이제 요청에 썼다면 “먼저 이 표지판들을 연결한 다음, 중복된 것들을 모두 버리세요. 하나만 남아야 해 각 키에 대해 복사" - 연결이 전혀 필요하지 않은 경우에도 이것이 정확히 작동하는 방식입니다.

때로는 운이 좋아서 "그냥 작동"하기도 하고, 때로는 성능에 불쾌한 영향을 미치기도 하고, 때로는 개발자의 관점에서 전혀 예상치 못한 효과를 주기도 합니다.

글쎄요, 그렇게 훌륭하지는 않을 수도 있지만...

“달콤한 커플”: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

그들이 원하는 것이 무엇인지는 분명할 것이다. 충족된 조건과 관련된 Y의 레코드가 있는 레코드 X를 선택합니다.. 다음을 통해 요청을 작성했습니다. JOIN — pk 값을 여러 번 얻었습니다(정확히 Y에 적합한 항목이 몇 개나 나타났는지). 제거하는 방법? 틀림없이 DISTINCT!

각 X 레코드에 대해 수백 개의 관련 Y 레코드가 있고 중복 항목이 영웅적으로 제거되면 특히 "기쁘다".

어떻게 고치는 지? 우선 문제가 다음과 같이 수정될 수 있다는 점을 인식하세요. "Y에 충족된 조건과 연관된 AT LEAST ONE이 있는 X 레코드를 선택하세요." -결국 Y 레코드 자체에서는 아무것도 필요하지 않습니다.

중첩된 존재

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

일부 PostgreSQL 버전은 EXISTS에서는 나타나는 첫 번째 항목을 찾는 것으로 충분하지만 이전 버전은 그렇지 않다는 것을 이해합니다. 그러므로 나는 항상 표시하는 것을 선호합니다 LIMIT 1 이내 EXISTS.

측면 조인

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

동일한 옵션을 사용하면 필요한 경우 발견된 관련 Y 레코드에서 일부 데이터를 즉시 반환할 수 있습니다. 유사한 옵션이 기사에서 논의됩니다. "PostgreSQL 안티패턴: 드문 레코드가 JOIN 중간에 도달합니다.".

“더 많은 비용을 지불해야 하는 이유”: DISTINCT [ON] + LIMIT 1

이러한 쿼리 변환의 또 다른 이점은 다음과 같이 레코드 중 하나 또는 몇 개가 필요한 경우 레코드 검색을 쉽게 제한할 수 있다는 것입니다.

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

이제 요청을 읽고 DBMS가 수행할 작업이 무엇인지 이해하려고 노력합니다.

• 표지판을 연결하다
• X.pk 고유
• 나머지 항목 중에서 하나를 선택하세요.

그래서 무엇을 얻었나요? "한 번만 입력하세요" 고유한 것 중에서 - 고유하지 않은 것을 취하면 결과가 어떻게든 바뀔까요?.. "차이가 없다면 왜 더 많은 비용을 지불합니까?"

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

그리고 정확히 같은 주제 GROUP BY + LIMIT 1.

"물어볼 게 있어요": 암시적 GROUP + LIMIT

비슷한 일이 다른 곳에서 발생합니다. 비어 있지 않은지 확인 요청이 진행됨에 따라 서명 또는 CTE:

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

집계 함수(count/min/max/sum/...)은 명시적인 지침 없이도 전체 세트에서 성공적으로 실행됩니다. GROUP BY. 오직 LIMIT 그들은 그다지 친절하지 않습니다.

개발자는 생각할 수 있다 “거기에 기록이 있다면 LIMIT만 있으면 됩니다.”. 하지만 그러지 마세요! 베이스의 경우 다음과 같습니다.

• 그들이 원하는 것을 세어보세요 모든 기록에 의하면
• 그들이 요구하는 만큼 많은 대사를 줘

대상 조건에 따라 다음 대체 중 하나를 수행하는 것이 적절합니다.

• (count + LIMIT 1) = 0 에 NOT EXISTS(LIMIT 1)
• (count + LIMIT 1) > 0 에 EXISTS(LIMIT 1)
• count >= N 에 (SELECT count(*) FROM (... LIMIT N))

"그램 단위로 매달아야 할 양": DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

순진한 개발자는 요청 실행이 중지될 것이라고 진심으로 믿을 수 있습니다. 처음으로 발견된 다른 값 중 1달러를 찾자마자.

미래에는 새로운 노드 덕분에 이것이 작동할 수도 있고 작동할 것입니다. 인덱스 건너뛰기 스캔, 구현이 현재 진행 중이지만 아직은 아닙니다.

지금은 먼저 모든 기록이 검색됩니다, 고유하며 해당 중에서만 요청한 금액이 반환됩니다. 우리가 다음과 같은 것을 원했다면 특히 슬프다 $ 1 = 4, 테이블에는 수십만 개의 레코드가 있습니다.

슬픔을 헛되이 보내지 않기 위해 재귀 쿼리를 활용해보자 PostgreSQL Wiki의 "DISTINCT는 가난한 사람들을 위한 것입니다.":

출처 : habr.com