PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Бид "энгийн мэт санагдах" 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;

[express.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;

[express.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. энэ бичлэгийн массивыг өөрчилсөн hstore толь бичигт утгын массив болгон байрлуулахын тулд текстийн мөрийн массив (person[]::text[]) руу оруулна.
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 Scan нь муу харагдаж байгаа тохиолдолд л энэ аргыг ашиглах нь үндэслэлтэй юм.

Туршилтын гүйцэтгэл

Тиймээс бидэнд өгөгдлийг толь бичигт цуваа болгох хоёр арга бий hstore/json_object. Нэмж дурдахад, түлхүүрүүд болон утгуудын массивыг дотоод болон гадаад текст болгон хөрвүүлэх замаар хоёр аргаар үүсгэж болно. array_agg(i::text) / array_agg(i)::text[].

Цэвэр синтетик жишээн дээр янз бүрийн төрлийн цуваажуулалтын үр нөлөөг шалгацгаая - өөр өөр тооны товчлууруудыг цуваа болгох:

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-д мөн адил зүйл тохиолддог.

Эцсийн дүгнэлтүүд:

• Хэрэв та үүнийг хийх хэрэгтэй бол Олон давтагдах бичлэгтэй НЭГДээрэй - хүснэгтийн "толь бичиг" ашиглах нь дээр
• Хэрэв таны толь бичиг хүлээж байгаа бол жижиг бөгөөд та үүнээс нэг их уншихгүй - та json[b] ашиглаж болно
• бусад бүх тохиолдолд hstore + array_agg(i::текст) илүү үр дүнтэй байх болно

Эх сурвалж: www.habr.com