Русский
Русский
English
Статистика
Реклама

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

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

CREATE TABLE task ASSELECT  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) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 10  );


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

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

EXISTS + IN


тут к нам пришли, и попросили к супер отнести не только priority = 10, но еще и 8 и 9:

SELECT  *FROM  generate_series(0, 99) pidWHERE  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) pidWHERE  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) pidWHERE  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) pidWHERE  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) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority >= 8  );



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

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

SELECT DISTINCT  personFROM  taskWHERE  priority >= 8;



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

А чтобы не гадать, какой из вариантов запроса будет более эффективен, а знать это уверенно пользуйтесь explain.tensor.ru.
Источник: habr.com
К списку статей
Опубликовано: 14.12.2020 14:08:42
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Postgresql

Программирование

Sql

Администрирование баз данных

Sql tips and tricks

Exists

Case

Union

Distinct

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru