🥇PostgreSQL Antipatterns: hãy nhấn mạnh THAM GIA với từ điển

Chúng tôi tiếp tục loạt bài viết dành cho việc nghiên cứu các cách ít được biết đến để cải thiện hiệu suất của các truy vấn PostgreSQL “có vẻ đơn giản”:

Đừng nghĩ là tôi không thích THAM GIA đến thế... :)

Nhưng thường khi không có nó, yêu cầu sẽ hiệu quả hơn đáng kể so với khi có nó. Vì vậy hôm nay chúng ta sẽ thử thoát khỏi THAM GIA sử dụng nhiều tài nguyên - sử dụng từ điển.

Bắt đầu với PostgreSQL 12, một số tình huống được mô tả bên dưới có thể được mô phỏng hơi khác do CTE không hiện thực hóa mặc định. Hành vi này có thể được hoàn nguyên bằng cách chỉ định khóa MATERIALIZED.

Rất nhiều “sự thật” trong vốn từ vựng hạn chế

Hãy thực hiện một nhiệm vụ ứng dụng rất thực tế - chúng ta cần hiển thị một danh sách tin nhắn đến hoặc các tác vụ đang hoạt động với người gửi:

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

Trong thế giới trừu tượng, tác giả nhiệm vụ phải được phân bổ đều cho tất cả nhân viên trong tổ chức của chúng ta, nhưng trên thực tế nhiệm vụ thường đến từ một số lượng người khá hạn chế - “từ quản lý” lên cấp bậc cao hơn hoặc “từ các nhà thầu phụ” từ các bộ phận lân cận (nhà phân tích, nhà thiết kế, tiếp thị, ...).

Hãy chấp nhận rằng trong tổ chức 1000 người của chúng tôi, chỉ có 20 tác giả (thậm chí còn ít hơn) đặt ra nhiệm vụ cho từng người biểu diễn cụ thể và Hãy sử dụng kiến thức môn học nàyđể tăng tốc truy vấn "truyền thống".

Trình tạo tập lệnh

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

Hãy hiển thị 100 nhiệm vụ cuối cùng cho một người thực thi cụ thể:

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;

[xem giải thích.tensor.ru]

Nó chỉ ra rằng 1/3 tổng thời gian và 3/4 bài đọc các trang dữ liệu được tạo ra chỉ để tìm kiếm tác giả 100 lần - cho mỗi tác vụ đầu ra. Nhưng chúng ta biết rằng trong số hàng trăm người này chỉ có 20 khác nhau - Kiến thức này có thể sử dụng được không?

hstore-từ điển

Hãy sử dụng loại cửa hàng để tạo khóa-giá trị "từ điển":

CREATE EXTENSION hstore

Chúng ta chỉ cần đưa ID của tác giả và tên của anh ta vào từ điển để có thể trích xuất bằng khóa này:

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

[xem giải thích.tensor.ru]

Dành cho việc thu thập thông tin về người Thời gian ít hơn 2 lần và đọc dữ liệu ít hơn 7 lần! Ngoài “từ vựng”, điều cũng giúp chúng tôi đạt được những kết quả này là truy xuất bản ghi hàng loạt từ bảng chỉ trong một lần sử dụng = ANY(ARRAY(...)).

Các mục trong bảng: Tuần tự hóa và Giải tuần tự hóa

Nhưng điều gì sẽ xảy ra nếu chúng ta cần lưu không chỉ một trường văn bản mà cả toàn bộ mục trong từ điển? Trong trường hợp này, khả năng của PostgreSQL sẽ giúp chúng ta coi một mục trong bảng là một giá trị duy nhất:

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

Chúng ta hãy xem những gì đang xảy ra ở đây:

Chúng tôi đã lấy p làm bí danh cho mục nhập đầy đủ trong bảng người và tập hợp một loạt chúng.
Điều này mảng bản ghi âm đã được đúc lại vào một mảng các chuỗi văn bản (person[]::text[]) để đặt nó vào từ điển hstore dưới dạng một mảng các giá trị.
Khi chúng tôi nhận được hồ sơ liên quan, chúng tôi lấy từ từ điển bằng phím dưới dạng một chuỗi văn bản.
Chúng tôi cần văn bản biến thành một giá trị loại bảng người (đối với mỗi bảng, một loại cùng tên sẽ được tạo tự động).
“Mở rộng” bản ghi đã nhập thành các cột bằng cách sử dụng (...).*.

từ điển json

Nhưng thủ thuật như chúng tôi đã áp dụng ở trên sẽ không hoạt động nếu không có loại bảng tương ứng để thực hiện việc “truyền”. Chính xác thì tình huống tương tự sẽ xảy ra và nếu chúng ta cố gắng sử dụng một hàng CTE, không phải bảng "thực".

Trong trường hợp này họ sẽ giúp chúng tôi các hàm làm việc với 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;

Cần lưu ý rằng khi mô tả cấu trúc đích, chúng ta không thể liệt kê tất cả các trường của chuỗi nguồn mà chỉ những trường mà chúng ta thực sự cần. Nếu chúng ta có bảng “gốc” thì tốt hơn nên sử dụng hàm json_populate_record.

Chúng ta vẫn truy cập từ điển một lần, nhưng chi phí tuần tự hóa json-[de] khá cao, do đó, chỉ hợp lý khi chỉ sử dụng phương pháp này trong một số trường hợp khi CTE Scan “trung thực” cho thấy bản thân tệ hơn.

Kiểm tra hiệu suất

Vì vậy, chúng tôi có hai cách để tuần tự hóa dữ liệu vào từ điển - hstore/json_object. Ngoài ra, bản thân các mảng khóa và giá trị cũng có thể được tạo theo hai cách, với chuyển đổi bên trong hoặc bên ngoài thành văn bản: mảng_agg(i::text) / array_agg(i)::text[].

Hãy kiểm tra tính hiệu quả của các loại tuần tự hóa khác nhau bằng một ví dụ hoàn toàn tổng hợp - tuần tự hóa các số khóa khác nhau:

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

Kịch bản đánh giá: tuần tự hóa

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;

Trên PostgreSQL 11, kích thước từ điển tối đa là 2^12 khóa việc tuần tự hóa sang json mất ít thời gian hơn. Trong trường hợp này, hiệu quả nhất là sự kết hợp giữa chuyển đổi kiểu json_object và kiểu “internal” array_agg(i::text).

Bây giờ chúng ta hãy thử đọc giá trị của mỗi khóa 8 lần - xét cho cùng, nếu bạn không truy cập từ điển thì tại sao lại cần đến nó?

Kịch bản đánh giá: đọc từ từ điển

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;

Và... đã xấp xỉ rồi với 2^6 phím, việc đọc từ từ điển json bắt đầu bị mất nhiều lần đọc từ hstore, đối với jsonb, điều tương tự cũng xảy ra ở 2^9.

Kết luận cuối cùng:

nếu bạn cần làm điều đó THAM GIA với nhiều bản ghi lặp lại — tốt hơn nên sử dụng “từ điển” của bảng

nếu từ điển của bạn được mong đợi nhỏ và bạn sẽ không đọc được nhiều từ nó - bạn có thể sử dụng json[b]

trong tất cả các trường hợp khác hstore + array_agg(i::text) sẽ hiệu quả hơn

Nguồn: www.habr.com

Antipatterns của PostgreSQL: hãy nhấn mạnh vào THAM GIA bằng từ điển

Rất nhiều “sự thật” trong vốn từ vựng hạn chế

hstore-từ điển

Các mục trong bảng: Tuần tự hóa và Giải tuần tự hóa

từ điển json

Kiểm tra hiệu suất

Thêm một lời nhận xét Hủy bỏ trả lời