Kilor Jan 27 2020 at 17:33

PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN

6 min

20K

Тензор corporate blogPostgreSQL*SQL*Algorithms*Database Administration*

Comments 14

rrrad Jan 28 2020 at 09:03

А почему индексированный hstore сравнивается с текстовым json, а не с jsonb? У вас до сих пор PostgreSQL <= 9.4?

Kilor Jan 28 2020 at 09:04

Он, скорее, не «индексированный», а «более упакованный». Добавил в итоговый график.

rrrad Jan 28 2020 at 10:20

На сколько я помню, табличка с json-ами занимает чуть меньше чем табличка с jsonb (в частных случаях может быть наоборот), но при этом с jsonb больше возможностей и он работает быстрее.

В целом, для большинства применений jsonb выглядит выигрышнее hstore: асимптотика похожая, но для jsonb не нужны расширения, да и возможностей без конвертации в строку больше.

Kilor Jan 28 2020 at 10:30

У hstore есть небольшое преимущество — встроенная реализация akeys(). Ее можно эмулировать через ARRAY(json_each), но это менее удобно. Зато у json — json_to_recordset / json_populate_recordset.
Но иерархические структуры необходимы достаточно редко (кроме случая JSON уже на входе, конечно), так что в целом — паритет.

rrrad Jan 28 2020 at 14:35

Гм… Не использую hstore, но судя по описанию, аналог для jsonb — jsonb_object_keys

Kilor Jan 28 2020 at 14:53

Почти. json_object_keys возвращает setof text, как skeys. А akeys возвращает сразу массив ключей.

erogov Jan 28 2020 at 21:07

Это, конечно, увлекательное упражнение. Но первая мысль, которая приходит в голову — зачем Nested Loop, почему бы не прочитать person целиком и не соединить хешированием? Тогда вместо накрутки буферов из-за индексного сканирования получим константу.
Решил я воспроизвести пример (на 12) и только собрался отключить индексное сканирование, как таблицы проанализировались и планировщик сам догадался до такого решения:

habr=# EXPLAIN (analyze, costs off, timing off, buffers)
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;

                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Limit (actual rows=100 loops=1)
   Buffers: shared hit=116
   ->  Sort (actual rows=100 loops=1)
         Sort Key: task.task_date DESC
         Sort Method: quicksort  Memory: 34kB
         Buffers: shared hit=116
         ->  Hash Left Join (actual rows=116 loops=1)
               Hash Cond: (task.author_id = person.id)
               Buffers: shared hit=116
               ->  Bitmap Heap Scan on task (actual rows=116 loops=1)
                     Recheck Cond: (owner_id = 777)
                     Heap Blocks: exact=107
                     Buffers: shared hit=109
                     ->  Bitmap Index Scan on task_owner_id_task_date_idx (actual rows=116 loops=1)
                           Index Cond: (owner_id = 777)
                           Buffers: shared hit=2
               ->  Hash (actual rows=1000 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 62kB
                     Buffers: shared hit=7
                     ->  Seq Scan on person (actual rows=1000 loops=1)
                           Buffers: shared hit=7

То есть без всяких костылей мы читаем 116 страниц вместо 142 в решении с hstore.
Ммм?

Kilor Jan 28 2020 at 21:22

На v12 планировщик еще немного поумнел, потому в начале статьи я и оставил дисклаймер. :)
Замечание вполне справедливо, но достаточно person расширить до 10k, 100k,… — и в какой-то момент Seq Scan заведомо проиграет. Я же рассматриваю ситуацию именно джойна с узким срезом из словаря. А таким срезом бывает и несколько активных контрагентов из миллионной выборки.

Заменим task на email, person на counteragent, умножим размеры таблиц в 100 раз — и…

erogov Jan 28 2020 at 22:59

На v12 планировщик еще немного поумнел

У меня нет под рукой 11, но 10-ка выдает точно такой же план после ANALYZE. Так что тестовые данные так себе.

но достаточно person расширить до 10k, 100k,…

Ну хорошо, допустим, что на больших таблицах эффект будет иметь место. Но я не понимаю, зачем все эти приплясывания вокруг hstore/json, если можно сделать просто

WITH T AS (
  SELECT *
  FROM task
  WHERE owner_id = 777
  ORDER BY task_date DESC
  LIMIT 100
)
, dict AS (
  SELECT *
  FROM person
  WHERE id IN (
      SELECT author_id FROM T -- DISTINCT тут не нужен
    )
)
SELECT *
FROM T LEFT JOIN dict ON T.author_id = dict.id;

и получить то же самое, а скорее всего и лучше?

Kilor Jan 28 2020 at 23:13

Ровно пара проблем:
1. Если IN внезапно развернется в Join, то и DISTINCT будет иметь значение. Поэтому = ANY(ARRAY(DISTINCT)) дает более стабильный результат.
2. CTE join CTE если развернется во что-то похожее на Nested Loop — будут тормоза.

erogov Jan 28 2020 at 23:45

1. Ну может, но DISTINCT там все равно не нужен, потому что =ANY сам по себе не пропустит дубликаты. А в варианте с DISTINCT вы на ровном месте получили лишний HashAggregate в плане.
2. В отсутствие индексов (CTE ведь у нас) у Nested Loop нет никаких шансов. Hash Join будет гарантированно, что нам и надо.

Если других проблем нет, то делаю вывод: не надо тут hstore/json использовать (:

Kilor Jan 29 2020 at 00:27

1. Давайте воспроизведу:

CREATE TABLE tbl(a integer PRIMARY KEY);
INSERT INTO tbl
SELECT generate_series(1, 100000);
ANALYZE tbl;

1.1. выбор по массиву из 2 разных элементов (всего 2)

explain (analyze)
SELECT
  *
FROM
  tbl
WHERE
  a = ANY(ARRAY(
    SELECT 1
  UNION ALL
    SELECT 2
  ));

сам Index Only Scan = 0.028ms
https://explain.tensor.ru/archive/explain/1d3bbda6cd190d55307661517a579e77:0:2020-01-29

1.2. выбор по массиву из 2 разных элементов (всего 2000)

explain (analyze)
SELECT
  *
FROM
  tbl
WHERE
  a = ANY(ARRAY(
    SELECT 1 FROM generate_series(1, 1000)
  UNION ALL
    SELECT 2 FROM generate_series(1, 1000)
  ));

сам Index Only Scan = 0.202ms
https://explain.tensor.ru/archive/explain/47953182a2ae3cd7f5d7be96fc1f8072:0:2020-01-29

Понятно, что HashAggregate тоже сколько-то «скушает», но и скармливать много дублей в массиве ключей — небесплатно.

1.3. то же через IN

explain (analyze)
SELECT
  *
FROM
  tbl
WHERE
  a IN (
    SELECT 1 FROM generate_series(1, 1000)
  UNION ALL
    SELECT 2 FROM generate_series(1, 1000)
  );

HashAggregate на месте, но мы проиграли еще 300ms суммарно к предущему результату
https://explain.tensor.ru/archive/explain/3779beff9df45843f8dce9d5fe40cab7:0:2020-01-29

2. Вот кусок из реального плана нашел — таки иногда Nested Loop между CTE все-таки бывает, и тогда бывает больно:

erogov Jan 29 2020 at 12:34

Понятно, что HashAggregate тоже сколько-то «скушает», но и скармливать много дублей в массиве ключей — небесплатно.

Понятное дело небесплатно. Но что с чем вы сравниваете? В 1.3 же совсем другой план получился. Давайте сравнивать 1.2 с таким же запросом, только заменим UNION ALL на UNION.
Я увеличил в примере все числа в 10 раз, чтобы цифры были заметнее, и вот что получил в среднем:

UNION ALL — 28.8 мс,
UNION — 35.3 мс.

Без DISTINCT все-таки лучше.

Kilor Jan 29 2020 at 13:55

Согласен, в среднем HashAggregate стоит дороже выигрыша от передачи меньшего массива.
У меня получилось примерно так:

UNION ALL — 72.134ms
UNION — 84.905ms