🥇 Antipattern PostgreSQL: ayo mulai GABUNG dengan kamus

Kami melanjutkan rangkaian artikel yang ditujukan untuk mempelajari cara-cara yang kurang diketahui untuk meningkatkan kinerja kueri PostgreSQL yang “tampaknya sederhana”:

Jangan berpikir bahwa saya tidak terlalu suka BERGABUNG... :)

Namun seringkali tanpanya, permintaan tersebut ternyata jauh lebih produktif dibandingkan dengan permintaan tersebut. Jadi hari ini kami akan mencoba singkirkan GABUNG yang intensif sumber daya - menggunakan kamus.

Dimulai dengan PostgreSQL 12, beberapa situasi yang dijelaskan di bawah ini mungkin direproduksi sedikit berbeda karena CTE non-materialisasi default. Perilaku ini dapat dikembalikan dengan menentukan kuncinya MATERIALIZED.

Banyak “fakta” dalam kosakata yang terbatas

Mari kita ambil tugas aplikasi yang sangat nyata - kita perlu menampilkan daftar pesan masuk atau tugas aktif dengan pengirim:

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

Di dunia abstrak, penulis tugas harus didistribusikan secara merata di antara seluruh karyawan organisasi kita, tetapi dalam kenyataannya tugas biasanya datang dari sejumlah orang yang terbatas - “dari manajemen” ke atas hierarki atau “dari subkontraktor” dari departemen tetangga (analis, desainer, pemasaran, ...).

Mari kita asumsikan bahwa dalam organisasi kita yang beranggotakan 1000 orang, hanya 20 penulis (biasanya bahkan lebih sedikit) yang menetapkan tugas untuk setiap pelaku tertentu dan Mari kita gunakan pengetahuan mata pelajaran iniuntuk mempercepat kueri "tradisional".

Pembuat 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 penjelasan.tensor.ru]

Ternyata bahwa 1/3 total waktu dan 3/4 pembacaan halaman data dibuat hanya untuk mencari penulis 100 kali - untuk setiap tugas keluaran. Tapi kita tahu itu di antara ratusan ini hanya 20 yang berbeda - Apakah mungkin menggunakan pengetahuan ini?

kamus toko-h

Mari manfaatkan tipe toko untuk menghasilkan nilai kunci "kamus":

CREATE EXTENSION hstore

Kita hanya perlu memasukkan ID penulis dan namanya ke dalam kamus sehingga kita dapat mengekstraknya 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 penjelasan.tensor.ru]

Dihabiskan untuk memperoleh informasi tentang orang 2 kali lebih sedikit waktu dan 7 kali lebih sedikit data yang dibaca! Selain “kosa kata”, yang juga membantu kami mencapai hasil tersebut adalah pengambilan catatan massal dari tabel dalam sekali jalan menggunakan = ANY(ARRAY(...)).

Entri Tabel: Serialisasi dan Deserialisasi

Namun bagaimana jika kita perlu menyimpan tidak hanya satu kolom teks, namun seluruh entri dalam kamus? Dalam hal ini, kemampuan PostgreSQL akan membantu kita memperlakukan entri tabel 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 kita lihat apa yang terjadi di sini:

Kami mengambil p sebagai alias untuk entri tabel orang lengkap dan mengumpulkan serangkaian dari mereka.
ini susunan rekaman disusun kembali ke array string teks (orang[]::teks[]) untuk menempatkannya di kamus hstore sebagai array nilai.
Ketika kami menerima catatan terkait, kami diambil dari kamus dengan kunci sebagai string teks.
Kami membutuhkan teks berubah menjadi nilai tipe tabel orang (untuk setiap tabel, tipe dengan nama yang sama dibuat secara otomatis).
“Perluas” rekaman yang diketik ke dalam kolom menggunakan (...).*.

kamus json

Namun trik seperti yang kami terapkan di atas tidak akan berhasil jika tidak ada tipe tabel yang sesuai untuk melakukan “casting”. Situasi yang persis sama akan muncul, dan jika kita mencoba menggunakannya baris CTE, bukan tabel "nyata"..

Dalam hal 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 dicatat bahwa ketika menjelaskan struktur target, kita tidak dapat membuat daftar semua bidang dari string sumber, tetapi hanya bidang yang benar-benar kita perlukan. Jika kita memiliki tabel “asli”, lebih baik menggunakan fungsinya json_populate_record.

Kami masih mengakses kamus sekali, tapi biaya serialisasi json-[de]cukup tinggi, oleh karena itu, masuk akal untuk menggunakan metode ini hanya dalam beberapa kasus ketika CTE Scan yang “jujur” menunjukkan hasil yang lebih buruk.

Menguji kinerja

Jadi, kami punya dua cara untuk membuat serial data ke dalam kamus - hstore/json_object. Selain itu, susunan kunci dan nilai itu sendiri juga dapat dihasilkan dengan dua cara, dengan konversi internal atau eksternal ke teks: array_agg(i::teks) / array_agg(i)::teks[].

Mari kita periksa efektivitas berbagai jenis serialisasi menggunakan contoh sintetik murni - membuat serial sejumlah kunci yang berbeda:

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

Skrip evaluasi: serialisasi

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, hingga kira-kira ukuran kamus sebanyak 2^12 kunci serialisasi ke json membutuhkan waktu lebih sedikit. Dalam hal ini, yang paling efektif adalah kombinasi konversi tipe json_object dan “internal”. array_agg(i::text).

Sekarang mari kita coba membaca nilai setiap kunci sebanyak 8 kali - lagipula, jika Anda tidak mengakses kamus, mengapa diperlukan?

Skrip evaluasi: membaca dari 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 kira-kira dengan kunci 2^6, membaca dari kamus json mulai hilang beberapa kali membaca dari hstore, untuk jsonb hal yang sama terjadi di 2^9.

Kesimpulan akhir:

jika Anda perlu melakukannya GABUNG dengan beberapa catatan berulang — lebih baik menggunakan “kamus” tabel

jika kamus Anda diharapkan kecil dan Anda tidak akan membaca banyak darinya - Anda dapat menggunakan json[b]

dalam semua kasus lainnya hstore + array_agg(i::teks) akan lebih efektif

Sumber: www.habr.com

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Banyak “fakta” dalam kosakata yang terbatas

kamus toko-h

Entri Tabel: Serialisasi dan Deserialisasi

kamus json

Menguji kinerja

Tambah komentar Membatalkan balasan

PostgreSQL Antipatterns: Dictionary Hit Heavy JOIN

Banyak “fakta” ​​dalam kosakata yang terbatas

kamus toko-h

Entri Tabel: Serialisasi dan Deserialisasi

kamus json

Menguji kinerja

Tambah komentar Membatalkan balasan

Banyak “fakta” dalam kosakata yang terbatas