Я уже как-то рассказывал про особенности вычисления условий в SQL вообще и в PostgreSQL, в частности. Сегодня продолжим тему и попробуем написать и пооптимизировать простой запрос — у кого из сотрудников есть на выполнении «суперприоритетные» задачи.
Слово «есть» в SQL превращается в
![](https://habrastorage.org/webt/wu/p0/sa/wup0satqenaq7mlykvleuz5vkme.png)
все картинки планов кликабельны
Пока все выглядит неплохо, но…
… тут к нам пришли, и попросили к «супер» отнести не только
![](https://habrastorage.org/webt/6l/oo/tw/6lootwotqvrzlc1gnz9ggzmz2ro.png)
Читать стали в 1.5 раза больше, ну и на времени выполнения это сказалось.
Давайте попробуем воспользоваться нашим знанием, что встретить запись с
![](https://habrastorage.org/webt/xr/uw/co/xruwcoe5ec1h7d1d6mwkkmzphgo.png)
Обратите внимание, что PostgreSQL 12 уже достаточно умен, чтобы после 100 поисков по значению 8 делать последующие
На предыдущих версиях аналогичного результата можно добиться, «спрятав под CASE» последующие запросы:
То же самое, но чуть быстрее можно получить, если воспользоваться «хаком»
![](https://habrastorage.org/webt/9k/dg/ev/9kdgevvwagsyp2jsxtvyneolzxg.png)
А теперь зайдем на задачу совсем с другой стороны. Если мы точно знаем, что тех
![](https://habrastorage.org/webt/pb/mb/l1/pbmbl1zuwcinhbm963e6cpy3-r4.png)
В 2 раза быстрее и в 1.5 раза меньше пришлось читать!
Но ведь, наверное, вычитать сразу вообще все подходящие
![](https://habrastorage.org/webt/yh/3h/-y/yh3h-y3vkfzopg9ychpe2-gkn5i.png)
Далеко не всегда, и точно не в этом случае — потому что вместо 100 чтений первых попавшихся записей, нам приходится вычитывать больше 400!
А чтобы не гадать, какой из вариантов запроса будет более эффективен, а знать это уверенно — пользуйтесь explain.tensor.ru.
CREATE TABLE task AS
SELECT
id
, (random() * 100)::integer person -- всего 100 сотрудников
, least(trunc(-ln(random()) / ln(2)), 10)::integer priority -- каждый следующий приоритет в 2 раза менее вероятен
FROM
generate_series(1, 1e5) id; -- 100K задач
CREATE INDEX ON task(person, priority);
Слово «есть» в SQL превращается в
EXISTS
— вот с самого простого варианта и начнем:SELECT
*
FROM
generate_series(0, 99) pid
WHERE
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 10
);
![](https://habrastorage.org/webt/wu/p0/sa/wup0satqenaq7mlykvleuz5vkme.png)
все картинки планов кликабельны
Пока все выглядит неплохо, но…
EXISTS + IN
… тут к нам пришли, и попросили к «супер» отнести не только
priority = 10
, но еще и 8 и 9:SELECT
*
FROM
generate_series(0, 99) pid
WHERE
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority IN (10, 9, 8)
);
![](https://habrastorage.org/webt/6l/oo/tw/6lootwotqvrzlc1gnz9ggzmz2ro.png)
Читать стали в 1.5 раза больше, ну и на времени выполнения это сказалось.
OR + EXISTS
Давайте попробуем воспользоваться нашим знанием, что встретить запись с
priority = 8
много вероятнее, чем с 10:SELECT
*
FROM
generate_series(0, 99) pid
WHERE
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 8
) OR
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 9
) OR
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 10
);
![](https://habrastorage.org/webt/xr/uw/co/xruwcoe5ec1h7d1d6mwkkmzphgo.png)
Обратите внимание, что PostgreSQL 12 уже достаточно умен, чтобы после 100 поисков по значению 8 делать последующие
EXISTS
-подзапросы только для «ненайденных» предыдущими — всего 13 по значению 9, и лишь 4 — по 10.CASE + EXISTS + ...
На предыдущих версиях аналогичного результата можно добиться, «спрятав под CASE» последующие запросы:
SELECT
*
FROM
generate_series(0, 99) pid
WHERE
CASE
WHEN
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 8
) THEN TRUE
ELSE
CASE
WHEN
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 9
) THEN TRUE
ELSE
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 10
)
END
END;
EXISTS + UNION ALL + LIMIT
То же самое, но чуть быстрее можно получить, если воспользоваться «хаком»
UNION ALL + LIMIT
:SELECT
*
FROM
generate_series(0, 99) pid
WHERE
EXISTS(
(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 8
LIMIT 1
)
UNION ALL
(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 9
LIMIT 1
)
UNION ALL
(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority = 10
LIMIT 1
)
LIMIT 1
);
![](https://habrastorage.org/webt/9k/dg/ev/9kdgevvwagsyp2jsxtvyneolzxg.png)
Правильные индексы — залог здоровья базы
А теперь зайдем на задачу совсем с другой стороны. Если мы точно знаем, что тех
task
-записей, которые мы хотим найти, в разы меньше, чем остальных — так сделаем подходящий частичный индекс. Заодно сразу перейдем от «точечного» перечисления 8, 9, 10
к >= 8
:CREATE INDEX ON task(person) WHERE priority >= 8;
SELECT
*
FROM
generate_series(0, 99) pid
WHERE
EXISTS(
SELECT
NULL
FROM
task
WHERE
person = pid AND
priority >= 8
);
![](https://habrastorage.org/webt/pb/mb/l1/pbmbl1zuwcinhbm963e6cpy3-r4.png)
В 2 раза быстрее и в 1.5 раза меньше пришлось читать!
Но ведь, наверное, вычитать сразу вообще все подходящие
task
сразу — будет еще быстрее?..SELECT DISTINCT
person
FROM
task
WHERE
priority >= 8;
![](https://habrastorage.org/webt/yh/3h/-y/yh3h-y3vkfzopg9ychpe2-gkn5i.png)
Далеко не всегда, и точно не в этом случае — потому что вместо 100 чтений первых попавшихся записей, нам приходится вычитывать больше 400!
А чтобы не гадать, какой из вариантов запроса будет более эффективен, а знать это уверенно — пользуйтесь explain.tensor.ru.