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

Введение

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

НЮансы работы 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
)

  ->  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.5кк?»
• «мы не меняли алгоритм, мы только код читаемым сделали, почему всё повисло?»
• «он декларативный, мы сказали что хотим, а как не говорили, почему всё сломалось?»

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

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

Вывод:

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

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

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

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

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

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

Так же стоит обратить внимание на большой «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. Радует только, что они почти никогда не выбиваются в лидеры по лайкам.

Конец

Комментарии (9)

• kir_vesp

  1 февраля 2017 в 10:17

  0

  ↑

  ↓

  По первому же примеру: ман не главное, ведь даже при чтении функции очевидно, что она проводит тупо выборку нужного формата и возвращает её, это логически приводит к тому, что все остальные условия будут работать уже с полученной выборкой.

  
  

  Взяли результат из сложной функции → фильтранули его → «ай-яй-яй, всё сломалось, но мы же ничего не меняли в логике!». Бинго! Вы вынесли одно из условий в следующий этап обработки.

  
  

  ИМХО, тут скорее на лицо проблема построения логических цепочек. Т.к. даже для меня, человека весьма далёкого от подобных сложных запросов и не знающего о функциях и их работе в SQL, очевидно, что тут получается сначала огромный результат по всем продуктам (!) и только потом он изменяется.

  • Loriowar

    1 февраля 2017 в 11:04

    0

    ↑

    ↓

    Об этом и хотелось сказать. Что в случае 2 табличек и 3 условий, всё довольно очевидно. А когда объектов десяток или больше и если не знать кто с кем как связан, то легко можно вынести такой кусок куда подальше и быть уверенным, что всё будет хорошо. По личному ощущению, после 100 строк sql-запроса становится довольно сложно в нём ориентироваться. Плюс, рискну огрести, но всё-таки: по рассказу постфактум всё может быть довольно понятно, но когда в ходе разработки на очередном этапе всё поломалось, то найти причину не так просто. Конечно потом ударишь себя ладонью в лоб, и скажешь как же мы могли этого не заметить, но это будет позже.

    • kir_vesp

      1 февраля 2017 в 11:25 (комментарий был изменён)

      0

      ↑

      ↓

      Согласен, но всё-таки, если параметр WHERE стоит за пределами выражения, это должно наводить на мысль…)

      • Loriowar

        1 февраля 2017 в 11:28

        0

        ↑

        ↓

        Таки да. Но если в WHERE ещё десятка полтора условий и значимых для конкретной части только 1–2шт, то они там и теряются. Всё познаётся в сравнении. В sql нужно быть безумно внимательным, иначе неизменно получается хрень.

        • kir_vesp

          1 февраля 2017 в 11:38

          0

          ↑

          ↓

          Хм, а дробить это сразу, а функции можно или это сильно будет сказываться на производительности? Просто мне всё-таки хочется понять, почему Вы сказали, что практики «чистого кода» не сильно применимы к SQL (ну, да, это вообще не ЯП, но всё-таки).

          • Loriowar

            1 февраля 2017 в 11:48

            0

            ↑

            ↓

            Скажем так, имею в виду, что к чистому sql-запросу практики для императивного программирования очень редко применимы (крайний пример: огромный метод в классе на том же Ruby — это великое зло и все будут жаждать разбить его, а огромный запрос, который выбирает за милисекунды нужные данные — это необходимое зло, с которым мало кто что-то делает, максимум подтащат красивое соглашение об оформлении кода). Если бы это был PL/SQL, то там другой коленкор, но всё равно скорость превалирует над красотой. В вышеописанном случае: есть небольшая портянка на 150 строк. Любые попытки разбить запрос на отдельные части ведут к проседанию производительности. Может быть можно его иначе написать и будет более читаемо, но вряд ли трудозатраты окупятся. Как раз об этом и хочется поговорить ещё с кем-либо.

• potapuff

  1 февраля 2017 в 11:07

  0

  ↑

  ↓

  Поправьте меня с:

  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)
  

  но, ANY (specification_parts.ids) — это ересь. При соединениях сравнения выполняются построчно, потому внутри ANY (specification_parts.ids) все-равно всегда будет ровно одна строка. А значит, условие полностью равносильно с… AND specification_history.specification_id = specification_parts.ids
  

  • Loriowar

    1 февраля 2017 в 11:30

    0

    ↑

    ↓

    В specification_parts.ids лежит Array из PG. В этом вся засада. И именно поэтому он ids, а не id.

• masai

  1 февраля 2017 в 12:02

  0

  ↑

  ↓

  Да и статья — это, как и билет на экзамене

  Выучишь —, а попадётся другое.