PostgreSQL Antipatterns: вредные JOIN и OR

Бойтесь операций, buffers приносящих…
На примере небольшого запроса рассмотрим некоторые универсальные подходы к оптимизации запросов на PostgreSQL. Пользоваться ими или нет — выбирать вам, но знать о них стоит.

В каких-то последующих версиях PG ситуация может измениться с «поумнением» планировщика, но для 9.4/9.6 она выглядит примерно одинаково, как примеры тут.

Возьму вполне реальный запрос:

SELECT   TRUE FROM   "Документ" d INNER JOIN   "ДокументРасширение" doc_ex     USING("@Документ") INNER JOIN   "ТипДокумента" t_doc ON     t_doc."@ТипДокумента" = d."ТипДокумента" WHERE   (d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND   d."$Черновик" IS NULL AND   d."Удален" IS NOT TRUE AND   doc_ex."Состояние"[1] IS TRUE AND   t_doc."ТипДокумента" = 'ПланРабот' LIMIT 1;

Посмотрим на получившийся план:

[посмотреть на explain.tensor.ru]

144ms и почти 53K buffers — то есть больше 400MB данных! И нам повезет, если все они окажутся в кэше к моменту нашего запроса, иначе он станет в разы дольше при вычитывании с диска.

Алгоритм важнее всего!

Чтобы как-то оптимизировать любой запрос, надо сначала понять, что же он вообще должен делать.
Оставим пока за рамками этой статьи разработку самой структуры БД, и договоримся, что мы можем относительно «дешево» переписать запрос и/или накатить на базу какие-то нужные нам индексы.

Итак, запрос:
— проверяет существование хоть какого-то документа
— в нужном нам состоянии и определенного типа
— где автором или исполнителем является нужный нам сотрудник

JOIN + LIMIT 1

Достаточно часто разработчику проще написать запрос, где сначала делается соединение большого количества таблиц, а потом из всего этого множества остается одна-единственная запись. Но проще для разработчика — не значит эффективнее для БД.
В нашем случае таблиц было всего 3 — а какой эффект…

Давайте для начала избавимся от соединения с таблицей «ТипДокумента», а заодно подскажем базе, что у запись типа у нас уникальна (мы-то это знаем, а вот планировщик пока не догадывается):

WITH T AS (   SELECT     "@ТипДокумента"   FROM     "ТипДокумента"   WHERE     "ТипДокумента" = 'ПланРабот'   LIMIT 1 ) ... WHERE   d."ТипДокумента" = (TABLE T) ...

Да, если таблица/CTE состоит из единственного поля единственной же записи, то в PG можно писать даже так, вместо

d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)

«Ленивые» вычисления в запросах PostgreSQL

BitmapOr vs UNION

В некоторых случаях Bitmap Heap Scan будет стоить нам очень дорого — например, в нашей ситуации, когда достаточно много записей подпадает под требуемое условие. Получили мы его из-за OR-условия, превратившегося в BitmapOr-операцию в плане.
Вернемся к исходной задаче — надо найти запись, соответствующую любому из условий — то есть незачем искать все 59K записей по обоим условиям. Есть способ отработать одно условие, а ко второму перейти только когда по первому ничего не нашлось. Нам поможет такая конструкция:

(   SELECT     ...   LIMIT 1 ) UNION ALL (   SELECT     ...   LIMIT 1 ) LIMIT 1

«Внешний» LIMIT 1 гарантирует, что поиск завершится при нахождении первой же записи. И если она найдется уже в первом блоке, выполнение второго осуществляться не будет (never executed в плане).

«Прячем под CASE» сложные условия

В исходном запросе есть крайне неудобный момент — проверка состояния по связанной таблице «ДокументРасширение». Независимо от истинности остальных условий в выражении (например, d.«Удален» IS NOT TRUE), это соединение выполняется всегда и «стоит ресурсов». Больше или меньше их будет потрачено — зависит от объема этой таблицы.
Но можно модифицировать запрос так, чтобы поиск связанной записи происходил бы только когда это действительно необходимо:

SELECT   ... FROM   "Документ" d WHERE   ... /*index cond*/ AND   CASE     WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (       SELECT         "Состояние"[1] IS TRUE       FROM         "ДокументРасширение"       WHERE         "@Документ" = d."@Документ"     )   END

Раз из связываемой таблицы нам не нужно для результата ни одно из полей, то мы имеем возможность превратить JOIN в условие по подзапросу.
Оставим индексируемые поля «за скобками» CASE, простые условия от записи вносим в WHEN-блок — и теперь «тяжелый» запрос выполняется только при переходе в THEN.

Моя фамилия «Итого»

Собираем результирующий запрос со всеми описанными выше механиками:

WITH T AS (   SELECT     "@ТипДокумента"   FROM     "ТипДокумента"   WHERE     "ТипДокумента" = 'ПланРабот' )   (     SELECT       TRUE     FROM       "Документ" d     WHERE       ("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND       CASE         WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (           SELECT             "Состояние"[1] IS TRUE           FROM             "ДокументРасширение"           WHERE             "@Документ" = d."@Документ"         )       END     LIMIT 1   ) UNION ALL   (     SELECT       TRUE     FROM       "Документ" d     WHERE       ("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND       CASE         WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (           SELECT             "Состояние"[1] IS TRUE           FROM             "ДокументРасширение"           WHERE             "@Документ" = d."@Документ"         )       END     LIMIT 1   ) LIMIT 1;

Подгоняем [под] индексы

Наметанный глаз заметил, что индексируемые условия в подблоках UNION чуть разнятся — это потому, что у нас уже есть подходящие индексы на таблице. А если бы их не было — то стоило бы создать: Документ(Лицо3, ТипДокумента) и Документ(ТипДокумента, Сотрудник).

Что в плане?

[посмотреть на explain.tensor.ru]

К сожалению, нам не повезло, и в первом UNION-блоке ничего не нашлось, поэтому второй все-таки пошел на выполнение. Но даже при этом — всего 0.037ms и 11 buffers!
Мы ускорили запрос и сократили «прокачку» данных в памяти в несколько тысяч раз, воспользовавшись достаточно простыми методиками — неплохой результат при небольшой копипасте. 🙂