Комментарии 14
А почему индексированный hstore сравнивается с текстовым json, а не с jsonb? У вас до сих пор PostgreSQL <= 9.4?
0
Он, скорее, не «индексированный», а «более упакованный». Добавил в итоговый график.
0
На сколько я помню, табличка с json-ами занимает чуть меньше чем табличка с jsonb (в частных случаях может быть наоборот), но при этом с jsonb больше возможностей и он работает быстрее.
В целом, для большинства применений jsonb выглядит выигрышнее hstore: асимптотика похожая, но для jsonb не нужны расширения, да и возможностей без конвертации в строку больше.
0
У hstore есть небольшое преимущество — встроенная реализация akeys(). Ее можно эмулировать через ARRAY(json_each), но это менее удобно. Зато у json — json_to_recordset / json_populate_recordset.
Но иерархические структуры необходимы достаточно редко (кроме случая JSON уже на входе, конечно), так что в целом — паритет.
Но иерархические структуры необходимы достаточно редко (кроме случая JSON уже на входе, конечно), так что в целом — паритет.
0
Это, конечно, увлекательное упражнение. Но первая мысль, которая приходит в голову — зачем Nested Loop, почему бы не прочитать
Решил я воспроизвести пример (на 12) и только собрался отключить индексное сканирование, как таблицы проанализировались и планировщик сам догадался до такого решения:
То есть без всяких костылей мы читаем 116 страниц вместо 142 в решении с hstore.
Ммм?
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.
Ммм?
0
На v12 планировщик еще немного поумнел, потому в начале статьи я и оставил дисклаймер. :)
Замечание вполне справедливо, но достаточно person расширить до 10k, 100k,… — и в какой-то момент Seq Scan заведомо проиграет. Я же рассматриваю ситуацию именно джойна с узким срезом из словаря. А таким срезом бывает и несколько активных контрагентов из миллионной выборки.
Заменим task на email, person на counteragent, умножим размеры таблиц в 100 раз — и…
Замечание вполне справедливо, но достаточно person расширить до 10k, 100k,… — и в какой-то момент Seq Scan заведомо проиграет. Я же рассматриваю ситуацию именно джойна с узким срезом из словаря. А таким срезом бывает и несколько активных контрагентов из миллионной выборки.
Заменим task на email, person на counteragent, умножим размеры таблиц в 100 раз — и…
0
На 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;
и получить то же самое, а скорее всего и лучше?
+2
Ровно пара проблем:
1. Если IN внезапно развернется в Join, то и DISTINCT будет иметь значение. Поэтому = ANY(ARRAY(DISTINCT)) дает более стабильный результат.
2. CTE join CTE если развернется во что-то похожее на Nested Loop — будут тормоза.
1. Если IN внезапно развернется в Join, то и DISTINCT будет иметь значение. Поэтому = ANY(ARRAY(DISTINCT)) дает более стабильный результат.
2. CTE join CTE если развернется во что-то похожее на Nested Loop — будут тормоза.
0
1. Ну может, но DISTINCT там все равно не нужен, потому что =ANY сам по себе не пропустит дубликаты. А в варианте с DISTINCT вы на ровном месте получили лишний HashAggregate в плане.
2. В отсутствие индексов (CTE ведь у нас) у Nested Loop нет никаких шансов. Hash Join будет гарантированно, что нам и надо.
Если других проблем нет, то делаю вывод: не надо тут hstore/json использовать (:
2. В отсутствие индексов (CTE ведь у нас) у Nested Loop нет никаких шансов. Hash Join будет гарантированно, что нам и надо.
Если других проблем нет, то делаю вывод: не надо тут hstore/json использовать (:
+1
1. Давайте воспроизведу:
1.1. выбор по массиву из 2 разных элементов (всего 2)
сам Index Only Scan = 0.028ms
https://explain.tensor.ru/archive/explain/1d3bbda6cd190d55307661517a579e77:0:2020-01-29
1.2. выбор по массиву из 2 разных элементов (всего 2000)
сам Index Only Scan = 0.202ms
https://explain.tensor.ru/archive/explain/47953182a2ae3cd7f5d7be96fc1f8072:0:2020-01-29
Понятно, что HashAggregate тоже сколько-то «скушает», но и скармливать много дублей в массиве ключей — небесплатно.
1.3. то же через IN
HashAggregate на месте, но мы проиграли еще 300ms суммарно к предущему результату
https://explain.tensor.ru/archive/explain/3779beff9df45843f8dce9d5fe40cab7:0:2020-01-29
2. Вот кусок из реального плана нашел — таки иногда Nested Loop между CTE все-таки бывает, и тогда бывает больно:
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 все-таки бывает, и тогда бывает больно:
0
Понятно, что HashAggregate тоже сколько-то «скушает», но и скармливать много дублей в массиве ключей — небесплатно.
Понятное дело небесплатно. Но что с чем вы сравниваете? В 1.3 же совсем другой план получился. Давайте сравнивать 1.2 с таким же запросом, только заменим UNION ALL на UNION.
Я увеличил в примере все числа в 10 раз, чтобы цифры были заметнее, и вот что получил в среднем:
- UNION ALL — 28.8 мс,
- UNION — 35.3 мс.
Без DISTINCT все-таки лучше.
0
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN