PostgreSQL 안티패턴: CTE x CTE

업무상 개발자가 요청을 작성하고 ''라고 생각하는 상황을 처리해야 합니다.베이스는 스마트해서 모든 것을 자체적으로 처리할 수 있습니다!«

어떤 경우에는(부분적으로는 데이터베이스 기능에 대한 무지, 부분적으로는 조기 최적화로 인해) 이러한 접근 방식으로 인해 "프랑켄슈타인"이 나타납니다.

먼저 그러한 요청의 예를 들어 보겠습니다.

-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
  SELECT DISTINCT ON (key_a, key_b)
    key_a a
  , key_b b
  , fld1 bind_fld1
  , fld2 bind_fld2
  FROM
    tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
  SELECT
    a
  , max(bind_fld1) bind_fld1
  , min(bind_fld2) bind_fld2
  FROM
    cte_bind
  GROUP BY
    a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
  SELECT
    cte_bind.a
  , cte_bind.b
  , cte_max.bind_fld1
  , cte_max.bind_fld2
  FROM
    cte_bind
  INNER JOIN
    cte_max
      ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;

요청 품질을 실질적으로 평가하기 위해 임의의 데이터 세트를 만들어 보겠습니다.

CREATE TABLE tbl AS
SELECT
  (random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
  generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);

그것은 밝혀졌다 데이터를 읽는 데 XNUMX/XNUMX도 채 걸리지 않았습니다. 쿼리 실행:

[explain.tensor.ru 참조]

한조각씩 분해해서

요청을 자세히 살펴보고 당황해 봅시다.

1. 재귀적 CTE가 없는데 왜 여기에 WITH RECURSIVE가 있나요?
2. 어쨌든 원본 샘플에 연결되어 있는 경우 최소/최대 값을 별도의 CTE로 그룹화하는 이유는 무엇입니까?
   +25% 시간
3. 이전 CTE를 반복하기 위해 끝에 무조건 'SELECT * FROM'을 사용하는 이유는 무엇입니까?
   +14% 시간

이 경우 연결에 Nested Loop가 아닌 Hash Join이 선택되었다는 점은 매우 운이 좋았습니다. 왜냐하면 CTE 스캔 패스 하나만이 아니라 10K를 받았기 때문입니다!

CTE 스캔에 대해 조금여기서 우리는 그것을 기억해야 합니다. CTE 스캔은 Seq 스캔과 유사합니다. - 즉, 색인을 생성하지 않고 전체 검색만 수행합니다. 10K x 0.3ms = 3000ms cte_max의 사이클용 또는 1K x 1.5ms = 1500ms cte_bind로 반복할 때!
사실, 그 결과 무엇을 얻고 싶었나요? 네, 보통 "5층" 쿼리를 분석하는 XNUMX분 정도에 이런 질문이 나옵니다.

우리는 각각의 고유한 키 쌍에 대해 출력을 원했습니다. key_a별 그룹의 최소/최대.
그럼 이걸로 써보자 창 기능:

SELECT DISTINCT ON(key_a, key_b)
	key_a a
,	key_b b
,	max(fld1) OVER(w) bind_fld1
,	min(fld2) OVER(w) bind_fld2
FROM
	tbl
WINDOW
	w AS (PARTITION BY key_a);

[explain.tensor.ru 참조]

두 옵션 모두에서 데이터를 읽는 데 약 4-5ms가 걸리므로 모든 시간이 늘어납니다. -32 % - 이것은 가장 순수한 형태이다. 기본 CPU에서 부하가 제거됨, 그러한 요청이 충분히 자주 실행되는 경우.

일반적으로 베이스에 "둥근 것은 들고 네모는 굴린다"고 강요해서는 안 됩니다.

출처 : habr.com