🥇PostgreSQL 反模式：让我们用字典进行重型 JOIN

我们继续致力于研究鲜为人知的方法来提高“看似简单”的 PostgreSQL 查询性能的系列文章：

别以为我那么不喜欢JOIN...:)

但通常情况下，如果没有它，请求的效率会比有它时高得多。所以今天我们会尝试 摆脱资源密集型 JOIN - 使用字典。

从 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;

[看看 explain.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;

[看看 explain.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;

让我们看看这里发生了什么：

我们拿了 p 作为完整 person 表条目的别名 并组装了一系列。
此 录音阵列被重新制作 到文本字符串数组 (person[]::text[]) 以将其作为值数组放入 hstore 字典中。
当我们收到相关记录时，我们 通过键从字典中提取 作为文本字符串。
我们需要文字 转成表类型值 person（为每个表自动创建一个同名的类型）。
使用以下命令将键入的记录“扩展”为列 (...).*.

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]序列化成本相当高因此，只有在“诚实的”CTE 扫描表现更差的情况下才使用此方法是合理的。

测试性能

因此，我们有两种方法将数据序列化到字典中 - hstore/json_object。此外，键和值数组本身也可以通过两种方式生成，内部或外部转换为文本： array_agg(i::text) / array_agg(i)::text[].

让我们使用一个纯粹的合成示例来检查不同类型序列化的有效性 - 序列化不同数量的密钥:

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 处。

最终结论：

如果你需要这样做 JOIN 多个重复记录 — 最好使用表格的“字典”

如果你的字典是预期的 小，你不会从中读到太多内容 - 你可以使用 json[b]

在所有其他情况下 hstore + array_agg(i::text) 会更有效

来源： habr.com

PostgreSQL 反模式：字典 Hit Heavy JOIN

有限的词汇中有很多“事实”

hstore字典

表条目：序列化和反序列化

json字典

测试性能

添加评论取消回复

PostgreSQL 反模式：字典 Hit Heavy JOIN

有限的词汇中有很多“事实”

hstore字典

表条目：序列化和反序列化

json字典

测试性能

添加评论 取消回复

添加评论取消回复