Комментарии 22
Вот она, главная боль sql — то, что задумывалось (и старается быть) декларативным описанием желаемого результата, хочешь, не хочешь, а выкидывает иногда такие коленца, что надо забыть об абстракциях, и вместо простого "чо хочу" приходится объяснять ему на пальцах, как не надо. И как хорошо, что подобные кудрявости далеко не на каждом запросе встают — читаемость после такой подкапотной оптимизации резко падает, и через полгода даже чтобы просто прочитать и понять приходится расчехлять бубен.
Что там Балу говорил Маугли про счастье? "Простые запросы", кажется? Даже жаль иногда, что этот принцип не совместим с реальностью...
Больше того, иногда очень хочется помочь оптимизатору, хотя бы указав с какой таблицы или индекса начинать. И это тоже боль — чувствовать себя беспомощным не имея хинтов в Постгресе.
Но такие танцы с бубном действительно что то очень странное)
Просто в вашей практике хинты не были нужны. Когда решение тиражируется на десятки и сотни инстансов, а на местах нет грамотных DBA, то хинты зачастую это единственный выход. Бывает что сбор статистики по какой-то причине вообще отключен.
Кроме того, бывает что система разворачивается в секретном контуре и доступ к ней имеют только федеральные агенты, которые однако ничего не смыслят в IT.
Ещё вариант — нет доступа к исходникам, а есть запрос, который нужно оптимизнуть. Тогда можно сохранить для него специальный план. Был такой случай, когда в топ AWR на огромном федеральном проекте постоянно вылезал запрос, выполняемый ядром Oracle Application Server, и даже у самих сотрудников Oracle не было возможности это починить иначе чем сохраненным планом.
Вообще, все дело в наличии актуальной статистики. Иногда, однако, в течение дня характер и распределение данных меняются кардиально. Например, система принимает и обрабатывает миллионы бизнес объектов, и в какой-то момент запускается операция обработки полученных данных (консолидация, аналитика, matching и т.п.), а статистика собиралась 8 часов назад, т.к. на больших БД это довольно длительная и затратная операция. Результат — оптимизатор выбирает неверный алгоритм соединения таблиц или неверный индекс и т.п.
Поэтому все мои знакомые DBA и senior developers, в том числе сотрудники Oracle, используют хинты не просто иногда, а как обыденный инструмент каждый день.
Ни один оптимизатор не может сравниться с человеком, имеющим десятки лет опыта, знающим всю подноготную этого алгоритма.
С другой стороны, я говорил в основном о хинтах в запросах, типа подсказок каким джоином джоинить, экспандить или нет и какие индексы брать. =)
Иногда, однако, в течение дня характер и распределение данных меняются кардиально.вот с этим можно и нужно бороться, очевидно.
Результат — оптимизатор выбирает неверный алгоритм соединения таблиц или неверный индекс и т.п.Для особо запущенных случаев, как было замечено, можно и план сохранить. Но всё же лучше решить проблему системно. Хотя бы потому что потом придёт новый разработчик, прогонит хинты на текущем состоянии базы и выбросить нафиг… )
Ни один оптимизатор не может сравниться с человеком, имеющим десятки лет опыта, знающим всю подноготную этого алгоритма.Знающий подноготную — построит систему где оптимизатор будет работать так как ему надо, а не тыкать костыли в виде хинтов.
ЗЫ:
сотрудники OracleВ оракле, возможно, всё иначе. Хотя, вроде, у него планировщик получше MSного.
Первый антипаттерн — называть поля русским текстом. Читаемость нулевая.
postgrespro.ru/docs/enterprise/9.6/pg-hint-plan.html
После беглого прочтения, кажется запрос очень странным. Ищем первый попавшийся план работ в нужном состоянии от заданного сотрудника. Почему первый попавшийся?
Допускаю, что большая часть документов создана этим сотрудником и поэтому индексы по «Сотрудник» и «Лицо3» не используются или их просто нет. Но в этом случае, искать «первый попавшийся» совсем странно.
Если же упомянутые индексы достаточно селективны, то PG будет использовать их. Буквально — пройтись по ветке индекса для заданного сотрудника и найти первый попавшийся документ подпадающий под условия, затем проверить его тип в другой таблице и завершить запрос если тип подошёл.
Типов документов может быть много, тогда индекс по (Сотрудник, Тип Документа) решит все проблемы.
В сухом остатке — я не вижу причин переписывать исходный запрос.
Почему первый попавшийся?Потому что хотим проверить просто наличие такой записи. Например, если сотрудник еще ни разу не оформлял себе или кому-то отпуска — давайте покажем ему в интерфейсе подсказку, как сделать это правильно.
Если же упомянутые индексы достаточно селективны, то PG будет использовать их.… В сухом остатке — я не вижу причин переписывать исходный запрос.Все планы сняты в одном и том же состоянии БД, то есть со всеми необходимыми индексами. И тем не менее — вот такое отличие в производительности.
Изначальный план запроса, отрабатывает 144 ms, хотя нужна всего одна запись. Если мои предположения о характере данных верны, то возможно во всей базе и есть только одна подходящая запись и поэтому запрос такой долгий. В вашем финальном варианте довольно тяжело разобраться. Нет гарантий, что он быстрее только потому, что запросу везет и он встречает эту запись, начиная сканировать таблицу «где нужно». Во всяком случае я не вижу, почему финальный вариант быстрее.
Если пристально посмотреть на исходный план, то видно, что по всем дополнительным условиям в Bitmap Heap Scan отфильтровалось всего 1.5K записей из 53K. А до проверки состояния по связанной таблице дело вовсе не дошло (never executed), потому что не нашлось ни одной записи после соединения с ТипДокумента.
То есть всех документов по сотруднику — много, а нужного типа — нет.
Но планировщик хоть и понимает, что идентификатор типа у нас уникален (rows=1), пробросить в индекс его через InitPlan не пытается. Вот и стоит ему помочь.
LIMIT без ORDER BY? Выглядит не очень, чтобы очень.
А вы посмотрите план с ORDER BY и без. Мне, например, очень интересно, что будет.
Вариант — любой, поле — индексированное.
PostgreSQL Antipatterns: вредные JOIN и OR