🥇PostgreSQL Antipatterns: ağır JOIN-a qarşı lüğətimizlə döyüşək

Məqalələr silsiləsini davam etdiririk ki, burada PostgreSQL-də "görünən dərəcədə sadə" sorğuların performansını artırmaq üçün az bilinən üsulları incələyirik:

Mən JOIN-u bu qədər sevmədiyimi düşünməyin… 🙂

Amma çox zaman ondan olmadan sorğu daha məhsuldar olur. Ona görə də bu gün cəhd edəcəyik resurs tələb edən JOIN-dan — lüğət vasitəsilə xilas olmaq.

PostgreSQL Antipattern-ləri: ağır JOIN-ə sözlük vururuq

PostgreSQL 12-dən başlayaraq, aşağıda təsvir olunan vəziyyətlərin bir hissəsi bir qədər fərqli baş verə bilər defolt olaraq CTE-nin materiallaşmaması səbəbindən. Bu davranışı əvvəlki halına qaytarmaq üçün Ulduzlu tapşırıq.

Məhdud lüğətə dair çox sayda "fakt"

Abstrakt dünyada tapşırıq müəlliflərinin təşkilatımızdakı bütün əməkdaşlar arasında bərabər paylanması lazım idi, amma reallıqda

tapşırıqlar adətən nisbətən məhdud sayda insanlardan gəlir — "baş idarəçidən" yuxarıda iyerarxiya üzrə və ya "yan müdafiəçilərdən" qonşu bölmələrdən (analitiklər, dizaynerlər, marketinq, …). Gəlin qəbul edək ki, təşkilatımızdakı 1000 nəfərdən yalnız 20 müəllif (adətən daha az) hər konkret icraçıya tapşırıq verirlər

və bu əşya bilikdəndən istifadə edək, "ənənəvi" sorğunu sürətləndirmək.

Skripti-generasiya

-- əməkdaşlar
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);

-- verilmiş paylanma ilə tapşırıqlar
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əcəyik:

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 Antipattern-ləri: ağır JOIN-ə sözlük vururuq
[explain.tensor.ru-dan baxın]

Yani 1/3 bütün vaxt və 3/4 oxuma verilənlərin səhifələri yalnız müəllifi 100 dəfə axtarmaq üçün yaradıldı — hər bir çıxarılan tapşırıq üçün. Amma biz bilirik ki, bu yüzdə yalnız 20 fərqli — bu bilgini istifadə edə bilmirikmi?

hstore-lüğət

İstifadə edəcəyik hstore tipi «lük» açar-dəyər cədvəlini yaradmaq üçün:

HSTORE genişlətməsi yaradaq

Sözlüyə müəllifin ID və adını daxil etməyimiz kifayətdir ki, daha sonra bu açar üzrə çıxara bilək:

-- hədəf seçimi yaradırıq
WITH T AS (
  SELECT
    *
  FROM
    task
  WHERE
    owner_id = 777
  ORDER BY
    task_date DESC
  LIMIT 100
)
-- unikal dəyərlər üçün lüğət yaradırıq
, 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
    ))
)
-- lüğətin əlaqəli dəyərlərini əldə edirik
SELECT
  *
, (TABLE dict) -> author_id::text -- hstore -> açar
FROM
  T;

PostgreSQL Antipattern-ləri: ağır JOIN-ə sözlük vururuq
[explain.tensor.ru-dan baxın]

Şəxslər haqqında məlumat almaq üçün sərf olunan vaxt 2 dəfə daha azdır və 7 dəfə daha az məlumat oxunur! «Sözləşdirmə»dən başqa, bu nəticələri əldə etməyə bizə kömək etdi müxtəlif qeydlərin kütləvi çıxarılması cədvəldən bir keçid ilə = ANY(ARRAY(...)).

Cədvəl qeydləri: serializasiya və deserializasiya

Amma əgər lüğətimizdə yalnız bir mətn sahəsi saxlamaq deyil, bütöv bir qeydi saxlamağa ehtiyac varsa, bu halda PostgreSQL-in cədvəl qeydini tək bir dəyər kimi işləməsi bizə kömək edəcək:

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

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

Biz p-ni person cədvəlinin tam qeydi üçün alias olaraq götürdük və onlardan bir massiv topladıq.
Bu massiv qeydləri mətn sətirləri massivinə (person[]::text[]) çevirdik ki, bunu hstore-lüğətində dəyərlər massivi olaraq qoya bilək.
Əlaqəli qeydi əldə edəndə onu lüğətdən açar üzrə mətn sətiri kimi çıxardıq.
Mətni cədvəl tipi dəyərinə çevirməliyik person (hər cədvəl üçün avtomatik olaraq eyni adda tip yaradılır).
Tipli qeydi sütunlara "yaydıq" (...).*.

json-lüğət

Amma yuxarıdakı kimi tətbiq etdiyimiz cür bir fənd çalışmayacaq, əgər "rahat" cədvəl tipi yoxdursa ki, "yayılma" etsin. Eynilə, əgər serializasiya üçün verilənlərin mənbəyi olaraq CTE sətirini, yoxsa "gerçək" cədvəli işə salmağa çalışsaq, eyni vəziyyət yaranacaq..

Bu halda bizə json ilə işləmək üçün funksiyalar:

...
, p AS ( -- bu artıq CTE
  SELECT
    *
  FROM
    person
  WHERE
    ...
)
, dict AS (
  SELECT
    json_object( -- indi bu artıq json
      array_agg(id)::text[]
    , array_agg(row_to_json(p))::text[] -- və hər sətir üçün json-un içində
    )
  FROM
    p
)
SELECT
  *
FROM
  T
, LATERAL(
    SELECT
      *
    FROM
      json_to_record(
        ((TABLE dict) ->> author_id::text)::json -- lüğətdən json kimi çıxardıq
      ) AS j(name text, birth_date date) -- lazım olan strukturu doldurduq
  ) j;

Qeyd etmək lazımdır ki, hədəf strukturu təsvir edilərkən biz əsas sətirdən lazım olan bütün sahələri sadalamaq yerinə, yalnız həqiqətən lazım olanları göstərə bilərik. Əgər bizdə "doğma" cədvəl varsa, daha yaxşıdır ki, funkisiyadan istifadə edək json_populate_record.

Bizim lüğətə daxil olmaq hələ də bir dəfəlikdir, amma json-[de]serialization üçün xərclər kifayət qədər yüksəkdir, buna görə bu üsuldan yalnız bəzi hallarda istifadə etmək məsləhətdir, çünki "ədalətli" CTE Scan özünü pis göstərir.

Performansı sınaqdan keçiririk

Deməli, məlumatları lüğət şəklinə salmağın iki yolu var — hstore / json_object. Bundan əlavə, açar və dəyərlər yarımcığı da iki yolla, daxili və ya xarici mətbəxlə yaradılır: array_agg(i::text) / array_agg(i)::text[].

Fərqli lüğət türlərinin effektivliyini tamamilə sintetik bir misalda yoxlayaq — fərqli açar saylarını lüğətə çeviririk:

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

Qiymətləndirmə skripti: serializasiya

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 Antipattern-ləri: ağır JOIN-ə sözlük vururuq

PostgreSQL 11-də lüğət ölçüsü 2^12 açara qədər json şəklində serializasiya daha az vaxt tələb edir. Bununla yanaşı, ən effektiv kombinasiya json_object və "daxili" tip çevirməsidir array_agg(i::text).

İndi gəlin hər açarın dəyərini 8 dəfə oxumağa çalışaq — çünki əgər lüğətə müraciət etməsək, o zaman onun anlamı nədir?

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

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 < (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 Antipattern-ləri: ağır JOIN-ə sözlük vururuq

Və… artıq təxminən 2^6 açarda json lüğətindən oxumaq kəskin ziyan görməyə başlayır hstore-dan oxumağında, jsonb üçün eynisi 2^9-da baş verir.

Sonuncu nəticələr:

əgər JOIN ilə mütəmadi təkrarlanan qeydlər edirsinizsə — lüğət şəklində çevrilmiş cədvəllərlə işləmək daha yaxşıdır

əgər lüğətiniz gözlənildiyindən kiçik olsa və ondan az oxuyacaqsınızsa — json[b] istifadə etmək olar

digər hallarda hstore + array_agg(i::text) daha effektiv olacaq

Mənbə: habr.com