Как писать кривые запросы с неоптимальным планом и заставить задуматься СУБД

Всё просто. Тут можно найти «Основы разбора запросов для чайников» в случае PostgreSQL и замечательные невыдуманные примеры из продакшена о том, как не надо писать запросы на PostgreSQL и MySQL и что бывает, если их так всё-таки писать.

Введение

Расскажу про несколько очевидных вещей, которые довольно хорошо описаны в манах и документации, которую читают, обычно, после вступления на десяток грабель, либо после отстрела определённого числа конечностей или после иного членовредительства.

Частей будет несколько, логически они слабо связаны друг с другом, но все они встретились в ходе решения различных потребностей бизнеса и так или иначе удовлетворяли его потребности.

НЮансы работы WITH

Та самая штука WITH, которая очень похожа на синтаксический сахар без большой смысловой нагрузки и ассоциируется (у непосвящённых) с разбиением большой портянки на отдельные методы в духе Мартина и Фаулера. Здесь главная особенность заключается в том, что это нифига не аналог метода/функции, особенно когда дело доходит до оптимизации запроса.

Сразу прошу прощения у читателя, но далее в тексте будут только куски запросов, имеющих принципиальное значение, запросы целиком публиковаться не будут. Во-первых, чтобы не утомлять особенностями структуры данных и во-вторых, чтобы я нечаянно не запостил что-нибудь приватно-корпоративное. Если куски будут абсолютно не читаемы, пожалуйста, не бейте сильно, а предложите как их облагородить. Спасибо.

Как делать не надо.

LEFT JOIN specifications_history AS specification_history
       ON specification_history.id               = specification_detail.entity_history_id
      AND specification_history.specification_id = ANY(specification_parts.ids)
LEFT JOIN specification_revision_details AS specification_section_detail
       ON specification_section_detail.specification_revision_id = specification_revision.id
      AND specification_section_detail.entity_type               = 1002
LEFT JOIN specification_sections_history AS specification_section_history
       ON specification_section_history.id = specification_section_detail.entity_history_id
LEFT JOIN specification_revision_details AS section_item_detail
       ON section_item_detail.specification_revision_id = specification_revision.id
      AND section_item_detail.entity_type               = 1003
LEFT JOIN section_items_history AS section_item_history
       ON section_item_history.id = section_item_detail.entity_history_id

WITH revision_products AS (
  SELECT DISTINCT specification_revision.id              AS revision_id,
                  specification_history.specification_id AS specification_id,
                  section_item_history.product_id        AS product_id
  FROM specification_revisions AS specification_revision
  INNER JOIN specification_revision_details AS specification_detail
          ON specification_detail.specification_revision_id = specification_revision.id
         AND specification_detail.entity_type               = 1001
  INNER JOIN specifications_history AS specification_histor
          ON specification_history.id = specification_detail.entity_history_id
  INNER JOIN specification_revision_details AS specification_section_detail
          ON specification_section_detail.specification_revision_id = specification_revision.id
         AND specification_section_detail.entity_type               = 1002
  INNER JOIN specification_sections_history AS specification_section_history
          ON specification_section_history.id = specification_section_detail.entity_history_id
  INNER JOIN specification_revision_details AS section_item_detail
          ON section_item_detail.specification_revision_id = specification_revision.id
         AND section_item_detail.entity_type               = 1003
  INNER JOIN section_items_history AS section_item_history
          ON section_item_history.id = section_item_detail.entity_history_id
  WHERE section_item_history.product_id IS NOT NULL
)

Тут произошло следующее: из основного тела запроса, в котором было немало LEFT JOIN их унесли в WITH и превратили в INNER JOIN. Куску выдали благозвучное название, дабы таким образом повысить читаемость основного тела, а все детали реализации утащили подальше. Практики чистого кода в лучшем виде. С читаемостью, действительно, стало получше. В основном теле запроса осталось 5 джоинов вместо 10. Вот только скорость выполнения запроса тут же упала с 75мс до 95сек. В Explain появились интересные вещи вида:

  ->  Unique  (cost=796821.66..848031.33 rows=5120967 width=12) (actual time=80769.666..94946.622 rows=315260 loops=1)
        ->  Sort  (cost=796821.66..809624.07 rows=5120967 width=12) (actual time=80769.663..90662.993 rows=37659600 loops=1)
              Sort Key: specification_revision_1.id, specification_history.specification_id, section_item_history.product_id
              Sort Method: external merge  Disk: 809888kB

То есть кто-то взял 37 лямов строк и начал их бодро сортировать в 1 гиге памяти. Тут же возникли вопросы:

• «а откуда у нас 37кк строк, когда в наибольшей из таблиц их 1.5кк?»
• «мы не меняли алгоритм, мы только код читаемым сделали, почему всё повисло?»
• «он декларативный, мы сказали что хотим, а как не говорили, почему всё сломалось?»

Ответ: перенос джоинов из основного тела в WITH сделал ровно то, что описано в документации:

Если коротко и грубо: запросы из WITH выполняются один раз и, чаще всего, не оптимизируются, то есть место их использования не влияет на план выполнения.

То есть мы левелапнули кусок запроса до уровня самостоятельной части, забыв добавить к нему важные условий из WHERE, которые урезают выборку по самые помидоры. В результате переджойнили всю базу, а затем отдали этого монстрика в основное тело, где взяли из него десяток строк.

В конкретном вышеописанном случае в WHERE было условие вида «product_id = 1234», которое и задавало основное ограничение по данным. Если бы это условие утащили в WITH, то всё продолжило бы работать примерно с той же скоростью. Однако, так можно сделать только в случае статического значения для правой части условия. Если айдишних получается, например, в ходе рекурсивного запроса, то в WITH такое условие не утащишь и идея с разделением запроса на куски будет безбожно тормозить.

Вывод:

• нужно читать документацию;
• не все практики разработки одинаково полезны в разных областях этой самой разработки.

Визуализация explain

Думаю, все в курсе про explain.depesz.com. Там красиво показывают что не так с запросом.

По сути, это просто раскраска для дефолтного вывода команды explain, зато это очень наглядно и особенно помогает в начале, пока не знаешь на что смотреть… хотя чего врать, и не в начале помогает, просто красиво и удобно.

Здесь хочется сказать пару слов по каждой из колонок и пояснить как они влияют на результат выполнения. Таки да, это написано там же в хелпе, но редко кто читает хелп, пока не припрёт.

• # — просто порядковый номер операции в ходе выполнения запроса
• Exclusive — время на выполнения конкретной операции (в миллисекундах)
• Inclusive — время на выполнение всего конвейера команд (например, на картинке выше, для выполнения uniq нужно сделать, как минимум, сортировку)
• Rows X — на сколько промахнулся Акелла планировщик, когда ванговал количество строк, которые должна вернуть операция (таки да, это важно для последующего принятия решений о том, как же дальше выполнять запрос)

Советы начинающим оптимизаторам

Если всё тормозит и не знаете с чего начать, то вот пара советов. Нужно взять раскрашенный explain (желательно вместе с analyze) из предыдущего пункта и посмотреть на него. Чаще всего, проблема (читай, 80%+ времени выполнения), сосредоточена в одной из операций, описанных в плане выполнения. То есть по Exclusive/Inclusive найти самое тёмное и стрёмное место. Опять же, в примере выше видно, что операция uniq длится 94 секунды из суммарных 95 секунд, за которые выполняется запрос. Там же видим, что в uniq почти всё время занимает sort, который идёт 90 сек. Здесь же видно проблему в виде количества строк, алгоритма сортировки и используемой памяти. Осталось всего ничего: понять «кто виноват и что делать». Здесь уже поможет только знание структуры данных целевой БД и требования к результатам запроса. Может будет достаточно переставить пару строк или добавить доп.условие, а может понадобится полностью переписать запрос, так как в исходном виде единственное что у него может хорошо получаться — это тормозить.

Так же стоит обратить внимание на большой «Rows X». Это говорит о промахе предсказанного и фактического результата выборки и, чаще всего, обусловлено недостаточным объёмом статистики о таблицах. Сие может приводить к неоптимальному плану выполнения запроса. Например: хотим выбрать одну строку из таблицы с 1 миллионом элементов; если планировщик решит, что результат выборки будет не 1 строка, а ~200 000, то он не будет искать по индексу, а пойдёт фулл сканом, так как это оптимальная стратегия для такого соотношения результирующих строк и размера таблицы. Выводы о скорости делайте сами.

Стандартные грабли

Вот, что чаще всего встречалось на практике и что было причиной непотребного поведения запросов:

• непонимание структуры данных и джоин данных через неведомые обходные и окольные пути или, ещё лучше, джоин лишних данных; крайний такой случай был в MySQL; вот немного облегчённый пример, который передаёт суть проблемы:

  SELECT ordered_products.*
  FROM products,
       products AS ordered_products
  GROUP BY ordered_products.id

  С одной стороны, просто указали лишнюю таблицу внутри FROM и никак ей не воспользовались. С другой стороны, мы получили неявное соединение двух таблиц через CROSS JOIN и результирующих строк (актуально, как минимум, для версии MySQL 5.5). В моём случае в таблице products было 40к строк, но конца выполнения запроса я так и не дождался. На сколько я знаю, Oracle умеет делать join elimination, но, в любом случае, лучше не надеяться на фичи СУБД, а думать головой.

  Бонус: как это сделать в ActiveRecord и всё повесить

  Product.joins(", (#{Product.table_name}) AS ordered_products").
          select('ordered_products.*').
          group("ordered_products.#{Product.primary_key}")
  

• Любовь к OUTER JOIN. Они порождают, как минимум, геометрический рост строк в промежуточных результатах и может легко оказаться, что на некоторых входных данных запрос будет тормозить, а СУБД захлёбываться от объёма данных. Крайний пример был на запросе, упомянутом выше по тесту (который с WITH). Он отлично работал при строгом ограничении по product_id. Этот же запрос хорошо работал с массивом из 5-15 айдишников и время выполнения запроса росло линейно, но далее, каждый следующий айдишник в массиве увеличивал время выполнение запроса в 2-3 раза. Проблема как раз была в множестве OUTER JOIN, которые мультипликативно увеличивали число обрабатываемых строк и с некоторого момента их количество становилось возмутительно большим, а план выполнения нельзя было показывать несовершеннолетним разработчикам.
• В продолжении предыдущего пункта: некоторые любят ставить FULL OUTER JOIN вместо LEFT/RIGHT, которых хватает в подавляющем большинстве случаев (проверенно на обитателях хабра, с которыми обсуждали запрос из предыдущей статьи о собеседованиях). Проблема всё та же: генерация лишних данных и увеличение потребления ресурсов. Из личного: FULL OUTER JOIN недавно реально понадобился в продакшене впервые за 2 года… счастлив был, как ребёнок.
• Чудесная магия с функциями, например, из PostgreSQL, когда вместо пары джоинов в декларативном стиле пытаются сделать то же самое, но в императивном стиле через массивы и прочие структуры данных вкупе с функциями, их преобразующими. Примера, к сожалению, не найду, поэтому придётся поверить на слово. Помню лишь, что такие вещи периодически мелькают на stackoverflow. Радует только, что они почти никогда не выбиваются в лидеры по лайкам.

Конец

Всем спасибо. Если у вас есть свои замечательные примеры того, как делать не надо, то, пожалуйста, не молчите, «вредных советов» и «живых примеров» много не бывает. Да и статья — это, как и билет на экзамене, лишь повод поговорить.

Автор: Loriowar

Источник