🥇PostgreSQL Antipatterns: mari kita lakukan yang berat JOIN dengan kamus

Kami meneruskan siri artikel yang dikhaskan untuk mengkaji cara yang kurang diketahui untuk meningkatkan prestasi pertanyaan PostgreSQL yang "nampak mudah":

Jangan fikir saya tidak begitu suka JOIN... :)

Tetapi selalunya tanpa itu, permintaan itu ternyata jauh lebih produktif daripada dengannya. Jadi hari ini kita akan cuba singkirkan SERTAI intensif sumber - menggunakan kamus.

Bermula dengan PostgreSQL 12, beberapa situasi yang diterangkan di bawah mungkin dihasilkan semula sedikit berbeza disebabkan oleh CTE bukan kewujudan lalai. Tingkah laku ini boleh dikembalikan dengan menentukan kunci MATERIALIZED.

Banyak "fakta" dalam perbendaharaan kata yang terhad

Mari kita lakukan tugas aplikasi yang sangat nyata - kita perlu memaparkan senarai mesej masuk atau tugas aktif dengan penghantar:

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

Dalam dunia abstrak, pengarang tugas harus diagihkan sama rata di kalangan semua pekerja organisasi kita, tetapi pada hakikatnya tugas datang, sebagai peraturan, daripada bilangan orang yang agak terhad - "daripada pengurusan" ke atas hierarki atau "daripada subkontraktor" dari jabatan jiran (penganalisis, pereka bentuk, pemasaran, ...).

Mari kita terima bahawa dalam organisasi kami yang terdiri daripada 1000 orang, hanya 20 pengarang (biasanya lebih sedikit) menetapkan tugas untuk setiap pelaku tertentu dan Jom gunakan ilmu subjek iniuntuk mempercepatkan pertanyaan "tradisional".

Penjana skrip

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

Mari tunjukkan 100 tugas terakhir untuk pelaksana tertentu:

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;

[lihat explain.tensor.ru]

Ia ternyata bahawa 1/3 jumlah masa dan 3/4 bacaan halaman data dibuat hanya untuk mencari pengarang 100 kali - untuk setiap tugas output. Tetapi kita tahu bahawa antara ratusan ini hanya 20 berbeza - Adakah mungkin untuk menggunakan pengetahuan ini?

hstore-kamus

Jom guna jenis hstore untuk menjana nilai kunci "kamus":

CREATE EXTENSION hstore

Kami hanya perlu meletakkan ID pengarang dan namanya dalam kamus supaya kami boleh mengekstrak menggunakan kunci ini:

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

[lihat explain.tensor.ru]

Dibelanjakan untuk mendapatkan maklumat tentang orang 2 kali lebih sedikit masa dan 7 kali lebih sedikit data dibaca! Selain "perbendaharaan kata", perkara yang turut membantu kami mencapai keputusan ini ialah pengambilan rekod pukal daripada jadual dalam satu pas menggunakan = ANY(ARRAY(...)).

Entri Jadual: Pensirilan dan Penyahserikatan

Tetapi bagaimana jika kita perlu menyimpan bukan hanya satu medan teks, tetapi keseluruhan entri dalam kamus? Dalam kes ini, keupayaan PostgreSQL akan membantu kami anggap entri jadual sebagai nilai tunggal:

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

Mari lihat apa yang berlaku di sini:

Kami mengambil p sebagai alias kepada entri jadual orang penuh dan mengumpulkan pelbagai daripadanya.
Ini susunan rakaman telah disusun semula kepada tatasusunan rentetan teks (orang[]::text[]) untuk meletakkannya dalam kamus hstore sebagai tatasusunan nilai.
Apabila kami menerima rekod berkaitan, kami ditarik dari kamus dengan kunci sebagai rentetan teks.
Kami memerlukan teks bertukar menjadi nilai jenis jadual orang (untuk setiap jadual jenis nama yang sama dicipta secara automatik).
“Kembangkan” rekod yang ditaip ke dalam lajur menggunakan (...).*.

kamus json

Tetapi helah seperti yang kami gunakan di atas tidak akan berfungsi jika tidak ada jenis jadual yang sepadan untuk melakukan "pemutus". Persis situasi yang sama akan timbul, dan jika kita cuba untuk menggunakan baris CTE, bukan jadual "sebenar"..

Dalam kes ini mereka akan membantu kita fungsi untuk bekerja dengan 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;

Perlu diingatkan bahawa apabila menerangkan struktur sasaran, kita tidak boleh menyenaraikan semua medan rentetan sumber, tetapi hanya yang benar-benar kita perlukan. Jika kita mempunyai jadual "asli", maka lebih baik menggunakan fungsi tersebut json_populate_record.

Kami masih mengakses kamus sekali, tetapi json-[de]kos siri adalah agak tinggi, oleh itu, adalah munasabah untuk menggunakan kaedah ini hanya dalam beberapa kes apabila Imbasan CTE “jujur” menunjukkan dirinya lebih teruk.

Menguji prestasi

Jadi, kami mendapat dua cara untuk mensirikan data ke dalam kamus − hstore/json_object. Di samping itu, tatasusunan kunci dan nilai itu sendiri juga boleh dijana dalam dua cara, dengan penukaran dalaman atau luaran kepada teks: array_agg(i::text) / array_agg(i)::text[].

Mari kita semak keberkesanan pelbagai jenis siri menggunakan contoh sintetik semata-mata - sirikan nombor kunci yang berbeza:

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

Skrip penilaian: bersiri

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;

Pada PostgreSQL 11, sehingga lebih kurang saiz kamus 2^12 kekunci siri ke json mengambil masa yang lebih singkat. Dalam kes ini, yang paling berkesan ialah gabungan json_object dan penukaran jenis "dalaman". array_agg(i::text).

Sekarang mari cuba baca nilai setiap kunci 8 kali - lagipun, jika anda tidak mengakses kamus, maka mengapa ia diperlukan?

Skrip penilaian: membaca daripada kamus

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;

Dan... sudah lebih kurang dengan kekunci 2^6, bacaan daripada kamus json mula hilang beberapa kali membaca dari hstore, untuk jsonb perkara yang sama berlaku pada 2^9.

Kesimpulan akhir:

jika anda perlu melakukannya SERTAI dengan berbilang rekod berulang — lebih baik menggunakan "kamus" jadual

jika kamus anda dijangka kecil dan anda tidak akan membaca banyak daripadanya - anda boleh menggunakan json[b]

dalam semua kes lain hstore + array_agg(i::text) akan lebih berkesan

Sumber: www.habr.com

Antipattern PostgreSQL: Kamus Hit Heavy JOIN

Banyak "fakta" dalam perbendaharaan kata yang terhad

hstore-kamus

Entri Jadual: Pensirilan dan Penyahserikatan

kamus json

Menguji prestasi

Tambah komen Batal reply