PostgreSQL 안티패턴: 사전 히트 헤비 조인

우리는 "간단해 보이는" PostgreSQL 쿼리의 성능을 향상시키기 위해 잘 알려지지 않은 방법에 대한 연구에 전념하는 일련의 기사를 계속합니다.

• 드문 레코드가 JOIN 중간에 도달합니다.
• Sisyphean JOIN 배열
• 유해한 JOIN 및 OR
• CTE 가입 CTE

제가 JOIN을 별로 안 좋아한다고 생각하지 마세요... :)

그러나 요청이 없을 경우 요청이 있을 때보다 훨씬 더 생산적인 것으로 판명되는 경우가 많습니다. 그래서 오늘 우리는 시도해 볼 것입니다 리소스 집약적인 JOIN 제거 - 사전을 사용합니다.

PostgreSQL 12부터 아래에 설명된 일부 상황은 다음과 같은 이유로 약간 다르게 재현될 수 있습니다. 기본 비구체화 CTE. 이 동작은 키를 지정하여 되돌릴 수 있습니다. MATERIALIZED.

제한된 어휘에 담긴 많은 "사실"

매우 실제적인 애플리케이션 작업을 살펴보겠습니다. 목록을 표시해야 합니다. 들어오는 메시지 또는 보낸 사람과의 활성 작업:

25.01 | Иванов И.И. | Подготовить описание нового алгоритма.
22.01 | Иванов И.И. | Написать статью на Хабр: жизнь без JOIN.
20.01 | Петров П.П. | Помочь оптимизировать запрос.
18.01 | Иванов И.И. | Написать статью на Хабр: JOIN с учетом распределения данных.
16.01 | Петров П.П. | Помочь оптимизировать запрос.

추상적인 세계에서 작업 작성자는 조직의 모든 직원에게 균등하게 분배되어야 하지만 실제로는 일반적으로 작업은 상당히 제한된 수의 사람들에게서 나옵니다. - 상위 계층의 "경영진" 또는 인접 부서(분석가, 디자이너, 마케팅 등)의 "하청업체".

1000명으로 구성된 우리 조직에서 오직 20명의 작성자(보통 그보다 더 적음)만이 각 특정 수행자에 대한 작업을 설정하고 이 주제 지식을 활용하자"전통적인" 쿼리 속도를 높이기 위해.

스크립트 생성기

-- сотрудники
CREATE TABLE person AS
SELECT
  id
, repeat(chr(ascii('a') + (id % 26)), (id % 32) + 1) "name"
, '2000-01-01'::date - (random() * 1e4)::integer birth_date
FROM
  generate_series(1, 1000) id;

ALTER TABLE person ADD PRIMARY KEY(id);

-- задачи с указанным распределением
CREATE TABLE task AS
WITH aid AS (
  SELECT
    id
  , array_agg((random() * 999)::integer + 1) aids
  FROM
    generate_series(1, 1000) id
  , generate_series(1, 20)
  GROUP BY
    1
)
SELECT
  *
FROM
  (
    SELECT
      id
    , '2020-01-01'::date - (random() * 1e3)::integer task_date
    , (random() * 999)::integer + 1 owner_id
    FROM
      generate_series(1, 100000) id
  ) T
, LATERAL(
    SELECT
      aids[(random() * (array_length(aids, 1) - 1))::integer + 1] author_id
    FROM
      aid
    WHERE
      id = T.owner_id
    LIMIT 1
  ) a;

ALTER TABLE task ADD PRIMARY KEY(id);
CREATE INDEX ON task(owner_id, task_date);
CREATE INDEX ON task(author_id);

특정 실행기에 대한 마지막 100개 작업을 보여드리겠습니다.

SELECT
  task.*
, person.name
FROM
  task
LEFT JOIN
  person
    ON person.id = task.author_id
WHERE
  owner_id = 777
ORDER BY
  task_date DESC
LIMIT 100;

[explain.tensor.ru 참조]

그것은 밝혀 그 총 시간의 1/3 및 판독값의 3/4 데이터 페이지는 각 출력 작업에 대해 작성자를 100번 검색하기 위해 만들어졌습니다. 하지만 우리는 이 수백 명 중 단지 20가지 다른 - 이 지식을 활용하는 것이 가능한가요?

hstore-사전

사용하자 hstore 유형 "사전" 키-값을 생성하려면 다음을 수행하세요.

CREATE EXTENSION hstore

작성자의 ID와 이름을 사전에 입력하면 이 키를 사용하여 추출할 수 있습니다.

-- формируем целевую выборку
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- формируем словарь для уникальных значений
, dict AS (
  SELECT
    hstore( -- hstore(keys::text[], values::text[])
      array_agg(id)::text[]
    , array_agg(name)::text[]
    )
  FROM
    person
  WHERE
    id = ANY(ARRAY(
      SELECT DISTINCT
        author_id
      FROM
        T
    ))
)
-- получаем связанные значения словаря
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> key
FROM
  T;

[explain.tensor.ru 참조]

개인에 대한 정보를 얻는 데 소비됨 2배 더 적은 시간과 7배 더 적은 데이터 읽기! "어휘" 외에도 이러한 결과를 얻는 데 도움이 된 것은 다음과 같습니다. 대량 기록 검색 다음을 사용하여 단일 패스로 테이블에서 = ANY(ARRAY(...)).

테이블 항목: 직렬화 및 역직렬화

하지만 하나의 텍스트 필드뿐만 아니라 사전의 전체 항목을 저장해야 한다면 어떻게 될까요? 이 경우 PostgreSQL의 기능이 도움이 될 것입니다. 테이블 항목을 단일 값으로 처리:

...
, dict AS (
  SELECT
    hstore(
      array_agg(id)::text[]
    , array_agg(p)::text[] -- магия #1
    )
  FROM
    person p
  WHERE
    ...
)
SELECT
  *
, (((TABLE dict) -> author_id::text)::person).* -- магия #2
FROM
  T;

여기서 무슨 일이 일어나고 있는지 살펴 보겠습니다.

1. 우리는 전체 개인 테이블 항목에 대한 별칭인 p 그리고 그것들을 배열로 모았습니다.
2. 이 녹음 배열이 다시 전송되었습니다. 텍스트 문자열 배열(person[]::text[])로 변환하여 hstore 사전에 값 배열로 배치합니다.
3. 당사는 관련 기록을 수신하면 키로 사전에서 가져옴 텍스트 문자열로.
4. 텍스트가 필요해요 테이블 유형 값으로 변환 사람(각 테이블에 대해 동일한 이름의 유형이 자동으로 생성됩니다).
5. 다음을 사용하여 입력된 레코드를 열로 "확장"합니다. (...).*.

JSON 사전

그러나 위에서 적용한 트릭은 "캐스팅"을 수행하는 해당 테이블 유형이 없으면 작동하지 않습니다. 똑같은 상황이 발생하며, 이를 사용하려고 하면 "실제" 테이블이 아닌 CTE 행.

이 경우 그들은 우리를 도울 것입니다 json 작업을 위한 함수:

...
, p AS ( -- это уже CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- теперь это уже json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- и внутри json для каждой строки
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- извлекли из словаря как json
      ) AS j(name text, birth_date date) -- заполнили нужную нам структуру
  ) j;

대상 구조를 설명할 때 소스 문자열의 모든 필드를 나열할 수는 없고 실제로 필요한 필드만 나열할 수 있다는 점에 유의해야 합니다. "기본" 테이블이 있는 경우 다음 함수를 사용하는 것이 더 좋습니다. json_populate_record.

우리는 여전히 사전에 한 번 접근하지만 json-[de]직렬화 비용이 상당히 높습니다.따라서 "정직한" CTE 스캔 결과가 더 나쁠 경우에만 이 방법을 사용하는 것이 합리적입니다.

테스트 성능

그래서 우리는 데이터를 사전으로 직렬화하는 두 가지 방법을 얻었습니다. hstore/json_object. 또한 키와 값의 배열 자체는 내부 또는 외부 텍스트로 변환하는 두 가지 방법으로 생성할 수도 있습니다. array_agg(i::텍스트) / array_agg(i)::텍스트[].

순수 합성 예제를 사용하여 다양한 유형의 직렬화의 효율성을 확인해 보겠습니다. 다른 수의 키를 직렬화:

WITH dict AS (
  SELECT
    hstore(
      array_agg(i::text)
    , array_agg(i::text)
    )
  FROM
    generate_series(1, ...) i
)
TABLE dict;

평가 스크립트: 직렬화

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  hstore(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              TABLE dict
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  ,   LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

PostgreSQL 11에서는 최대 약 2^12개 키의 사전 크기 JSON으로의 직렬화에는 시간이 덜 걸립니다. 이 경우 가장 효과적인 방법은 json_object와 "내부" 유형 변환을 조합하는 것입니다. array_agg(i::text).

이제 각 키의 값을 8번 읽어 보겠습니다. 결국 사전에 액세스하지 않는다면 사전이 왜 필요한가요?

평가 스크립트: 사전에서 읽기

WITH T AS (
  SELECT
    *
  , (
      SELECT
        regexp_replace(ea[array_length(ea, 1)], '^Execution Time: (d+.d+) ms$', '1')::real et
      FROM
        (
          SELECT
            array_agg(el) ea
          FROM
            dblink('port= ' || current_setting('port') || ' dbname=' || current_database(), $$
              explain analyze
              WITH dict AS (
                SELECT
                  json_object(
                    array_agg(i::text)
                  , array_agg(i::text)
                  )
                FROM
                  generate_series(1, $$ || (1 << v) || $$) i
              )
              SELECT
                (TABLE dict) -> (i % ($$ || (1 << v) || $$) + 1)::text
              FROM
                generate_series(1, $$ || (1 << (v + 3)) || $$) i
            $$) T(el text)
        ) T
    ) et
  FROM
    generate_series(0, 19) v
  , LATERAL generate_series(1, 7) i
  ORDER BY
    1, 2
)
SELECT
  v
, avg(et)::numeric(32,3)
FROM
  T
GROUP BY
  1
ORDER BY
  1;

그리고... 이미 대략 2^6 키를 사용하면 json 사전에서 읽는 것이 여러 번 손실되기 시작합니다. hstore에서 읽는 중, jsonb의 경우 2^9에서도 같은 일이 발생합니다.

최종 결론:

• 꼭 해야 한다면 여러 반복 레코드로 JOIN — 테이블의 "사전"을 사용하는 것이 더 좋습니다
• 당신의 사전이 예상된다면 작아서 많이 읽지 못할 것입니다 - json[b]를 사용할 수 있습니다.
• 다른 모든 경우에 hstore + array_agg(i::text) 더 효과적일 것이다

출처 : habr.com