PostgreSQL Antipatterns: มาเข้าร่วมกับพจนานุกรมกัน

เรายังคงเขียนบทความต่อเนื่องเกี่ยวกับการศึกษาวิธีที่ไม่ค่อยมีใครรู้จักในการปรับปรุงประสิทธิภาพของข้อความค้นหา PostgreSQL ที่ "ดูเหมือนเรียบง่าย":

• บันทึกที่หายากจะถึงกลาง JOIN
• อาร์เรย์ Sisyphean JOIN
• เข้าร่วมและหรือที่เป็นอันตราย
• CTE เข้าร่วม CTE

อย่าคิดว่าฉันไม่ชอบเข้าร่วมมากนัก... :)

แต่บ่อยครั้งหากไม่มีสิ่งนี้ คำขอจะมีประสิทธิผลมากกว่าคำขอนั้นอย่างมาก ดังนั้นวันนี้เราจะลอง กำจัดการเข้าร่วมที่ต้องใช้ทรัพยากรมาก - การใช้พจนานุกรม

ตั้งแต่ 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;

[ดูที่ expand.tensor.ru]

แต่กลับกลายเป็นว่า เวลาทั้งหมด 1/3 และการอ่าน 3/4 หน้าข้อมูลถูกสร้างขึ้นเพื่อค้นหาผู้เขียน 100 ครั้งเท่านั้น - สำหรับงานเอาต์พุตแต่ละรายการ แต่เรารู้ว่าในบรรดาร้อยคนเหล่านี้ ที่แตกต่างกันเพียง 20 - สามารถใช้ความรู้นี้ได้หรือไม่?

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;

[ดูที่ expand.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. นี้ อาร์เรย์ของการบันทึกถูกสร้างใหม่ ไปยังอาร์เรย์ของสตริงข้อความ (person[]::text[]) เพื่อวางไว้ในพจนานุกรม hstore เป็นอาร์เรย์ของค่า
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]serialization ต้นทุนค่อนข้างสูงดังนั้นจึงสมเหตุสมผลที่จะใช้วิธีนี้เฉพาะในบางกรณีเท่านั้นเมื่อการสแกน CTE ที่ "ซื่อสัตย์" แสดงให้เห็นว่าแย่ลง

การทดสอบประสิทธิภาพ

ดังนั้นเราจึงมีสองวิธีในการเรียงลำดับข้อมูลลงในพจนานุกรม - hstore/json_object. นอกจากนี้ อาร์เรย์ของคีย์และค่ายังสามารถสร้างได้สองวิธี ด้วยการแปลงภายในหรือภายนอกเป็นข้อความ: array_agg(i::ข้อความ) / array_agg(i)::ข้อความ[].

มาตรวจสอบประสิทธิภาพของการทำให้เป็นอนุกรมประเภทต่างๆ โดยใช้ตัวอย่างสังเคราะห์ล้วนๆ - เรียงลำดับหมายเลขคีย์ที่แตกต่างกัน:

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 :: ข้อความ) จะมีประสิทธิภาพมากขึ้น

ที่มา: will.com