PostgreSQL Antipatterns: скованные одной цепью EXISTS

Я уже как-то рассказывал про особенности вычисления условий в SQL вообще и в PostgreSQL, в частности. Сегодня продолжим тему и попробуем написать и пооптимизировать простой запрос — у кого из сотрудников есть на выполнении «суперприоритетные» задачи.

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

все картинки планов кликабельны

Пока все выглядит неплохо, но…

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

Читать стали в 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
  );

Обратите внимание, что 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
  );

Правильные индексы — залог здоровья базы

А теперь зайдем на задачу совсем с другой стороны. Если мы точно знаем, что тех 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
  );

В 2 раза быстрее и в 1.5 раза меньше пришлось читать!

Но ведь, наверное, вычитать сразу вообще все подходящие task сразу — будет еще быстрее?..

SELECT DISTINCT
  person
FROM
  task
WHERE
  priority >= 8;

Далеко не всегда, и точно не в этом случае — потому что вместо 100 чтений первых попавшихся записей, на приходится вычитывать больше 400!

---

А чтобы не гадать, какой из вариантов запроса будет более эффективен, а знать это уверенно — пользуйтесь explain.tensor.ru.