ProHoster > Blog > İdarə > PostgreSQL Antipatterns: ağır QOŞULUŞ-u lüğətlə vuraq

PostgreSQL Antipatterns: ağır QOŞULUŞ-u lüğətlə vuraq

"Sadə görünən" PostgreSQL sorğularının performansını yaxşılaşdırmaq üçün az tanınan yolların öyrənilməsinə həsr olunmuş məqalələr silsiləsini davam etdiririk:

Elə düşünməyin ki, mən QOŞULMAĞI çox sevmirəm... :)

Ancaq tez-tez onsuz, sorğu onunla müqayisədə əhəmiyyətli dərəcədə daha məhsuldar olur. Beləliklə, bu gün cəhd edəcəyik resurs tələb edən JOIN-dan xilas olun - lüğətdən istifadə etməklə.

PostgreSQL Antipatterns: ağır QOŞULUŞ-u lüğətlə vuraq

PostgreSQL 12-dən başlayaraq, aşağıda təsvir edilən bəzi vəziyyətlər aşağıdakılara görə bir qədər fərqli şəkildə təkrarlana bilər. default qeyri-materializasiya CTE. Bu davranış açarı təyin etməklə geri qaytarıla bilər MATERIALIZED.

Məhdud lüğətdə çoxlu "faktlar"

Çox real tətbiq tapşırığını götürək - siyahı göstərməliyik gələn mesajlar və ya göndərənlərlə aktiv tapşırıqlar:

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

Mücərrəd dünyada tapşırıq müəllifləri təşkilatımızın bütün işçiləri arasında bərabər paylanmalıdır, lakin əslində tapşırıqlar, bir qayda olaraq, kifayət qədər məhdud sayda insandan gəlir - iyerarxiya üzrə “idarəetmədən” və ya qonşu şöbələrdən (analitiklər, dizaynerlər, marketinq, ...) “subpodratçılardan”.

Gəlin qəbul edək ki, 1000 nəfərlik təşkilatımızda hər bir konkret ifaçı üçün yalnız 20 müəllif (adətən ondan da az) vəzifələr qoyur və Gəlin bu mövzuda biliklərdən istifadə edək"ənənəvi" sorğunu sürətləndirmək üçün.

Skript generatoru

-- сотрудники
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);

Müəyyən bir icraçı üçün son 100 tapşırığı göstərək:

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;

PostgreSQL Antipatterns: ağır QOŞULUŞ-u lüğətlə vuraq
[express.tensor.ru saytına baxın]

O çıxır ki, 1/3 ümumi vaxt və 3/4 oxunuş məlumat səhifələri yalnız müəllifi 100 dəfə axtarmaq üçün hazırlanmışdır - hər bir çıxış tapşırığı üçün. Amma biz bilirik ki, bu yüzlərlədir cəmi 20 fərqli - Bu biliklərdən istifadə etmək mümkündürmü?

hstore lüğəti

Gəlin yararlanaq hstore növü "Lüğət" açar-dəyəri yaratmaq üçün:

CREATE EXTENSION hstore

Sadəcə olaraq, müəllifin şəxsiyyət vəsiqəsini və adını lüğətə daxil etməliyik ki, bu açardan istifadə edərək çıxara bilək:

-- формируем целевую выборку
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;

PostgreSQL Antipatterns: ağır QOŞULUŞ-u lüğətlə vuraq
[express.tensor.ru saytına baxın]

Şəxslər haqqında məlumat əldə etməyə xərclənir 2 dəfə az vaxt və 7 dəfə az məlumat oxunur! “Lüğətdən” əlavə, bu nəticələrə nail olmağımıza kömək edən də oldu toplu qeydlərin axtarışı masadan tək keçid istifadə edərək = ANY(ARRAY(...)).

Cədvəl qeydləri: Seriyadan çıxarma və seriyadan çıxarma

Bəs biz yalnız bir mətn sahəsini deyil, lüğətdə bütöv bir girişi saxlamalı olsaq nə etməli? Bu halda PostgreSQL-in bacarığı bizə kömək edəcək Cədvəl girişini tək dəyər kimi qəbul edin:

...
, 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;

Gəlin burada nə baş verdiyinə baxaq:

  1. aldıq p tam şəxs cədvəli girişinin ləqəbi kimi və onlardan bir sıra topladı.
  2. Bu yazılar silsiləsi yenidən işlənmişdir hstore lüğətində dəyərlər massivi kimi yerləşdirmək üçün mətn sətirləri (person[]::text[]) massivinə.
  3. Müvafiq qeydi aldıqda, biz açarla lüğətdən çıxarılır mətn sətri kimi.
  4. Bizə mətn lazımdır cədvəl tipli dəyərə çevrilir şəxs (hər bir cədvəl üçün avtomatik olaraq eyni adlı bir növ yaradılır).
  5. İstifadə edərək yazılmış qeydi sütunlara "genişləndirin" (...).*.

json lüğəti

Ancaq yuxarıda tətbiq etdiyimiz belə bir hiylə işləməyəcək, əgər "tökmə" üçün müvafiq cədvəl növü yoxdur. Tam olaraq eyni vəziyyət yaranacaq və istifadə etməyə çalışsaq CTE sıra, "real" cədvəl deyil.

Bu halda onlar bizə kömək edəcəklər json ilə işləmək üçün funksiyalar:

...
, 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;

Qeyd etmək lazımdır ki, hədəf strukturunu təsvir edərkən mənbə sətirinin bütün sahələrini deyil, yalnız həqiqətən ehtiyac duyduğumuz sahələri sadalaya bilərik. Əgər "doğma" cədvəlimiz varsa, funksiyadan istifadə etmək daha yaxşıdır json_populate_record.

Biz hələ də lüğətə bir dəfə daxil oluruq, amma json-[de]serializasiya xərcləri olduqca yüksəkdir, buna görə də, bu metoddan yalnız bəzi hallarda “dürüst” CTE Scan özünü daha pis göstərdiyi hallarda istifadə etmək məqsədəuyğundur.

Test performansı

Beləliklə, məlumatları lüğətə seriallaşdırmaq üçün iki yolumuz var - hstore/json_object. Bundan əlavə, açarların və dəyərlərin massivləri də mətnə ​​daxili və ya xarici çevrilmə ilə iki yolla yaradıla bilər: array_agg(i::text) / array_agg(i)::text[].

Sırf sintetik bir nümunədən istifadə edərək müxtəlif növ seriallaşdırmanın effektivliyini yoxlayaq - müxtəlif nömrəli düymələri sıralayın:

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

Qiymətləndirmə skripti: seriallaşdırma

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 Antipatterns: ağır QOŞULUŞ-u lüğətlə vuraq

PostgreSQL 11-də təxminən 2^12 düymədən ibarət lüğət ölçüsünə qədər json-a serializasiya daha az vaxt aparır. Bu halda, ən təsirli olan json_object və "daxili" tipli çevrilmə birləşməsidir array_agg(i::text).

İndi gəlin hər bir açarın dəyərini 8 dəfə oxumağa çalışaq - axır ki, lüğətə daxil deyilsinizsə, o zaman niyə lazımdır?

Qiymətləndirmə skripti: lüğətdən oxumaq

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;

PostgreSQL Antipatterns: ağır QOŞULUŞ-u lüğətlə vuraq

Və... artıq təxminən 2^6 düymə ilə json lüğətindən oxumaq dəfələrlə itirməyə başlayır hstore-dan oxu, jsonb üçün eyni şey 2^9-da olur.

Yekun nəticələr:

  • etmək lazımdırsa Çox təkrarlanan qeydlərlə QOŞULUN — cədvəlin “lüğətindən” istifadə etmək daha yaxşıdır
  • lüğətiniz gözlənilirsə kiçik və ondan çox oxumayacaqsan - json[b] istifadə edə bilərsiniz
  • bütün digər hallarda hstore + array_agg(i::mətn) daha təsirli olacaq

Mənbə: www.habr.com

Добавить комментарий