- PVSM.RU - https://www.pvsm.ru -
Если писать SQL-запросы без анализа алгоритма, который они должны реализовать, ни к чему хорошему с точки зрения производительности это обычно не приводит.
Такие запросы любят «кушать» процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот — чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN…
Само по себе соединение таблиц не вредно и не полезно — это просто инструмент, но и пользоваться им надо уметь.
Сначала возьмем совсем простой пример.
Есть «словарик» на 100 записей (например, это регионы РФ):
CREATE TABLE tbl_dict AS
SELECT
generate_series(0, 100) k;
ALTER TABLE tbl_dict ADD PRIMARY KEY(k);
… и к нему прилагается таблица связанных «фактов» на 100K записей:
CREATE TABLE tbl_fact AS
SELECT
(random() * 100)::integer k
, (random() * 1000)::integer v
FROM
generate_series(1, 100000);
CREATE INDEX ON tbl_fact(k);
Теперь попытаемся подсчитать сумму значений по каждому «региону».
SELECT
d.k
, sum(f.v)
FROM
tbl_fact f
NATURAL JOIN
tbl_dict d
GROUP BY
1;
Само чтение данных заняло только 18% времени, остальное — обработка:
[посмотреть на explain.tensor.ru] [1]
А все потому, что Hash Join и Hash Aggregate пришлось обрабатывать по 100K записей из-за нашего желания группировать по полю связанной таблицы.
Но ведь значение этого поля равно значению поля в агрегируемой таблице! То есть нам никто не мешает сначала сгруппировать «факты», а уже потом делать соединение:
SELECT
d.k
, f.sum
FROM
(
SELECT
k
, sum(v)
FROM
tbl_fact
GROUP BY
1
) f
NATURAL JOIN
tbl_dict d;
[посмотреть на explain.tensor.ru] [2]
Безусловно, метод не универсален, но для нашего случая «обычного JOIN» выигрыш по времени в 2 раза с минимальной модификацией запроса — просто за счет «обнулившегося» Hash Join, которому на вход вместо 100K записей пришло только 100.
Теперь усложним задачу: у нас есть 3 таблицы, связанные одним идентификатором — основная и две вспомогательные с некими прикладными данными, по которым мы будем фильтровать.
Маленькое, но очень важное замечание: пусть на основе «прикладных» знаний целевой задачи нам уже заведомо известно, что условия будут выполняться на первой таблице — почти всегда (для определенности — 3:4), а на второй — очень редко (1:8).
Мы хотим отобрать из основной и первой вспомогательной таблицы 100 первых по id записей с четными значениями идентификатора, для которых выполняются условия на всех таблицах. Всего записей в таблицах у нас пусть будет снова по 100K.
CREATE TABLE base(
id
integer
PRIMARY KEY
, val
integer
);
INSERT INTO base
SELECT
id
, (random() * 1000)::integer
FROM
generate_series(1, 100000) id;
CREATE TABLE ext1(
id
integer
PRIMARY KEY
, conda
boolean
);
INSERT INTO ext1
SELECT
id
, (random() * 4)::integer <> 0 -- 3:4
FROM
generate_series(1, 100000) id;
CREATE TABLE ext2(
id
integer
PRIMARY KEY
, condb
boolean
);
INSERT INTO ext2
SELECT
id
, (random() * 8)::integer = 0 -- 1:8
FROM
generate_series(1, 100000) id;
SELECT
base.*
, ext1.*
FROM
base
NATURAL JOIN
ext1
NATURAL JOIN
ext2
WHERE
id % 2 = 0 AND
conda AND
condb
ORDER BY
base.id
LIMIT 100;
[посмотреть на explain.tensor.ru] [3]
200мс и больше 2GB данных прокачано — не очень хорошо для 100 записей!
Используем следующие подходы, чтобы добиться ускорения:
SELECT
base.*
, ext1.*
FROM
base
, LATERAL( -- подзапрос делается заведомо после отбора по base
SELECT
*
FROM
ext1
WHERE
id = base.id AND
conda -- частое условие
LIMIT 1
) ext1
WHERE
CASE
WHEN base.id % 2 = 0 THEN
EXISTS( -- подзапрос делается только при прохождении первичного условия
SELECT
NULL
FROM
ext2
WHERE
id = base.id AND
condb -- редкое условие
LIMIT 1
)
END
ORDER BY
base.id -- сортировка пойдет строго по PK, потому что больше не по чему
LIMIT 100;
[посмотреть на explain.tensor.ru] [5]
Запрос, конечно, стал посложнее, но выигрыш в 13 раз по времени и в 350 по «прожорливости» стоит того!
Снова напомню, что использовать стоит не все способы и не всегда, но знать — лишним не будет.
Автор: Kilor
Источник [6]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/343891
Ссылки в тексте:
[1] [посмотреть на explain.tensor.ru]: https://explain.tensor.ru/archive/explain/f2dbfa8818913c7ca6a8a2e6c5137be4:0:2020-01-20
[2] [посмотреть на explain.tensor.ru]: https://explain.tensor.ru/archive/explain/211c2a9e9bb2425fda450f90f1aa5d08:0:2020-01-20
[3] [посмотреть на explain.tensor.ru]: https://explain.tensor.ru/archive/explain/11aff76b79f671a307edb86e3608ca98:0:2020-01-20
[4] PGConf.Russia: https://pgconf.ru/2020/262963
[5] [посмотреть на explain.tensor.ru]: https://explain.tensor.ru/archive/explain/4a201a1ccbc44b066cb4a60c6a9f8b15:0:2020-01-20
[6] Источник: https://habr.com/ru/post/484670/?utm_campaign=484670&utm_source=habrahabr&utm_medium=rss
Нажмите здесь для печати.