Комментарии 40
хорошая статья ) автору респект за возврат к традициям Хабра.
Однако, замечу, что порой ясность кода более важна для его поддержки, чем скорость выполнения. Не всегда, но тем не менее.
Статья полезная. Молодец, что описал exists. Многие о нем не знают
В ваших примерах не всегда очевидна польза той или иной оптимизации.
К примеру, вы пишите, что
Оператор EXISTS будет эффективнее, чем JOIN, потому что сервер не считывает лишние строки из таблицы, если необходимо убедиться, что запись существует, в какой-либо таблице.
Если вы взгляните на планы для обоих запросов с JOIN и EXISTS в статье, то увидите, что в обоих случаях делается полный скан таблиц, и считывается 830 записей из salesorder и 2155 из orderdetail. Разница в стоимости запросов в данном случае из-за ошибки оптимизатора PostgreSQL, который решил, что в первом случае после join останется 2155 записей, а во втором 830, что явная ошибка так как их должно остаться одинаково. В реальности я бы сказал,что второй запрос будет дороже из-за дополнительного HashAggregate stage.
Вообще, если взглянуть на проблему JOIN и EXISTS шире, то хороший оптимизатор старается переписать запрос, который использует подзапросы, как в случае с JOIN, в запрос с JOIN, что, кстати, и произошло в данном случае, если вы взляните на планы.
В друих случаях, я бы доработал примеры, например, в случае с BETWEEN не продемонстрировано использовование индекса в одном случае и полного скана в другом. И я бы переименовал этот совет - речь ведь больше не про BETWEEN, а про то, что в ряде случаев из-за использование функций, индексы становится невозможно использовать и по\тому лучше избегать использования функций на проиндексированных колонках.
Ни в одном из примеров не продемонстрировано, что перезаписывание действительно делает запрос быстрее, только показана оценка стоимости запроса. Но доверять ей не стоит так как оптимизаторы баз данных частенько ошибаются в них. Хорошо было бы увидеть выигрыш во времени исполнения на реалистичных данных (а не на таблицах с парой тысяч записей) и с использованием индексов.
Примерах? Да тут примеры вообще ничего не показывают...
Обнаружилось, что при большом количестве значений скорость запроса сильно падает, если используется IN.
Во-первых, вот это утверждение само следовало бы подтвердить измерениями. Потому что строго говоря, на число значений в IN есть практический лимит (пусть даже не в СУБД, так в JDBC драйвере, например), т.е. померять следовало бы хотя бы 1, 10, 100, 1000 и 100000 значений в IN. Кроме того, "обнаружилось" что c IN (1, 2, 3, ...) всегда работает быстро, если в колонке c всегда 1 (и я тоже не буду это доказывать - если это не так, это баг). Т.е. вот это вот "скорость сильно падает" зависит еще и от распределения значений в колонке слева от IN.
А дальше следует пример с двумя значениями в IN, и двумя значениями в VALUES, и при этом разницы в быстродействии мы практически не видим, т.е. она на грани погрешности измерения (и как вы уже отметили, кост в плане - не показатель вообще).
в ряде случаев из-за использование функций, индексы становится невозможно использовать и по\тому лучше избегать использования функций на проиндексированных колонках
В постгрес есть функциональные индексы. Очень выручали при рефакторинге текущих решений, когда, например, столбец с номерами автомобилей превращался в мешанину русских и английских букв в различных регистрах (не продумали заранее). Индекс по функции fn_normalize(gosnumber) сильно исправляет ситуацию.
Можно еще использовать CTE(Common Table Expressions) для оптимизации запросов. Жаль, что в статье ничего не сказано
Добрый день, в блоке пред оптимизационные шаги описана CTE
CTE далеко не всегда помогает в оптимизации. У меня есть кейс, когда CTE, переписанный на временные таблицы, ускорялся с 10 минут до 7 секунд.
Иногда помогает materialized.
К сожалению, то, как временные таблицы реализованы в PostgreSQL, приводит к необходимости их избегать, когда это возможно. Или средствами CTE, или, если результат временной выборки используется неоднократно и требует индексации, то средствами нежурналируемых таблиц.
В рамках одного оптимизируемого запроса, использование временных таблиц может дать явный прирост производительности. Но в рамках конкурентной среды с относительно длительными транзакциями, общая производительность может ухудшиться.
Стоило бы убрать Big Data из тэгов, к бигдате статья не имеет отношения. И добавить PostgreSQL в заголовок и в тэги. Как раньше пользователи MS SQL считали, что он и есть SQL, так теперь пользователи PostgreSQL считают, что он и есть SQL. Хотя, разумеется, это не так в обоих случаях.
Статья банальная, такие регулярно выходят на Хабре. Ничего нового не увидел.
В ряде мест подача какая-то вывернутая наизнанку. Например, не BETWEEN выполняется быстрее, а использование конструкции функция(поле) часто делает невозможным использование индекса по этому полю. Но это не значит, что BETWEEN обладает каким-то особенным волшебством.
Почему-то под картинками подпись "Скорость ...", хотя на них никакой скорости не отображено.
Возможно проглядел, но во всей статье не увидел ничего про скорость.
Некоторые советы сомнительные и нуждаются в тщательной аргументации, например, "Использование ранжирования вместо DISTINCT".
"Использование CTE может помочь оптимизировать вычисления".
А может и не помочь. В других SQL-движках встречал вред от CTE, когда CTE выполняется несколько раз (по числу ссылок на него в запросе) и, как следствие, несколько раз читает огромную таблицу.
"Обнаружилось, что при большом количестве значений скорость запроса сильно падает, если используется IN. Это происходит потому, что значение колонки каждой строки поочерёдно сравнивается с каждым из возможных вариантов, тем самым нагружая процессор."
Даже MySQL древних версий умеет из значений в IN строить бинарное дерево и находить соответствие за O(log(n))-время. Неужели PostgreSQL не умеет?
И странное дело - вроде бы картинки в статье в png-формате, однако шрифты дико замылены, как будто их сохраняли в jpg формат с хорошим сжатием.
"низкий технический уровень материала"
Оптимизация сравнения IN
Считаю, что эта часть статьи абсолютно бездарна и просто провалена.
Во-первых, нужно было рассмотреть все три альтернативы - WHERE IN, WHERE EXISTS и INNER JOIN, и соответственно три анти-альтернативы WHERE NOT IN, WHERE NOT EXISTS и LEFT JOIN WHERE IS NULL. Уже на этой стадии возникли бы ба-а-альшие проблемы, потому как в зависимости от структуры хранения и статистики данных оптимальна то она, то другая альтернатива... более того, иногда одна из альтернатив (это прекрасно видно при анализе плана) сервером приводится к другой, иногда нет... На самом деле очень объёмная и интересная тема.
Во-вторых, а где собственно исходные данные для оптимизации? Где структуры, индексы, статистика данных? На непонятно каком исходном материале взята совершенно с потолка пара запросов...
В третьих, где адекватные результаты сделанного (вот никак не могу применить к этому термина "оптимизация")? почему сравнение сделано не для самих запросов, а для предсказаний к этим запросам? Что, большая проблема написать не EXPLAIN, а EXPLAIN ANALYZE? Запрос не изменяет данных, выполняется на крошечном объёме данных, потому никаких осложнений от реального выполнения запроса не ожидается. А ведь порой результат предсказания и результат реального выполнения - это две ну очень большие разницы.
------------------
Дальше - ничуть не лучше. Особенно убивает спрятанное под спойлер с текстом "Код". Это ж насколько надо не уважать читателя, чтобы кинуть ему какой-то непонятный запрос, потом его же, но абы оптимизированный, и полагать, что читателю этого достаточно. А читатель хочет повторить! Нет, запросы из-под спойлера он скопирует, а дальше что?
-------------------
Избегание CASE при проверке булевых полей
А тут просто-таки ерунда написана. Выражения - неэквивалентны.
Просто посмотрите, что будет, еслиt1.is_deleted_row = NULL, а t2.is_deleted_row = FALSE.
Добрый вечер.
По поводу скрытого кода. Для удобства чтения сделано. Если читателю неинтересен код, то он дополнительно не скролит вниз.
По поводу case. Проверка на null и bool значения принято через оператор is. К тому же в PostgreSQL подефолту проверка на true, если указаны операторы and/or.
По поводу не представленных тестовых данных. Если прикреплю ссылку - найдутся пользователи, которые задизлайкают и напишут не для забора. Уже проходили.
По поводу индексов. Индексы не всегда ускоряют. Сводить в статье, что используйте правильно индексы - везде написано. Смысл статьи был как рефакторинг может ускорить запрос. В начале описано, о чем будет статья. Про индексы не было сказано.
Практически со всеми критикующими я соглашусь, но почему когда люди рассказывают об оптимизации запросов мимоходом упоминают индексы и практически ни разу в статьях такого уровня я не видел разъяснений по этому поводу, не говоря уже о том, что при необходимости, возможности и целесообразности индексы можно …. создать самому! Или для уровня тех, на кого рассчитан материал, это совсем магия по мнению авторов?
Да потому что подавляющее большинство этих статей пишут не для людей, а "для галочки". И в значительном проценте случаев такие статьи пишут люди, которые сами "плавают" в теме.
Добрый вечер. Прошу не что стоит принижать статью до "галочки", а также, что автор "плавает" в теме. Вам не понравилось, что было не рассмотрено exists/in/join на достойном уровне. Я сделаю выводы и в дальнейшем будут тут более качественные статьи.
То, что вы ниже стали писать. Это необъективщина. Вам не понравился блок скрытый, кому-то нравится. Насчёт case, вы также не правы. Прошу использовать при проверке оператор is, а не равенство. Если мы за достойные примеры, то прошу писать принятые практики.
Насчёт case, вы также не правы. Прошу использовать при проверке оператор is, а не равенство.
Да вы что, не отличаете оператор сравнения от оператора присвоения, что ли?
Кстати, вы проверили то, что я написал? убедились, что выражения неэквивалентны? Если всё ещё нет - то вот вам ссылочка: https://dbfiddle.uk/nezOP17J
Вам не понравился блок скрытый, кому-то нравится.
То есть, вы решили, что претензии - к форме, а не к содержанию? иными словами, вы вообще не поняли, что я пишу и чем именно остался не удовлетворён? Или это был единственный способ и вроде бы ответить, и в то же время не признать?
Впрочем, вы абсолютно так же "не так поняли" и соответственно непонятно о чём ответили на мой отзыв чуть выше - причём аж четыре раза подряд.
@Akina вам не понравилась статья, так зачем вы, вместо предложений делаете хейт спич? Вы могли предложить переписать и примеры статей, чтобы читающие комментарии пользователи посмотрели более подробно. А вместо этого демотивируете потенциальных писателей хабра на интересные статьи.
Считаю, что эта часть статьи абсолютно бездарна и просто провалена.
Специально зашел к вам, могу также сказать, что ваши посты можно считать бездарными, но зачем.
Зачем это может понадобиться? не знаю...
У каждого есть своя аудитория. Если вам не нравится, ну пройдите, а не называйте статьями бездарными и тд. Статья хорошая, не идеальная.
Добрый вечер. В статье сказано про рефакторинг запроса. Про индексы не сказано. Сейчас вы предъявляете, что можно создать индексы для ускорения. По мнению автора - создание индекса описано в других статьях по оптимизации запроса. В этой про рефакторинг. Например антипаттерн кореллирующий подзапрос и обычный join.
Если мы возьмём Greenplum, то индексация там лучше не делать.
В очередной раз убедился, что у нас очень токсичная культура критики. Если вы, критики, знаете и можете дать советы, как улучшить - научитесь это делать в форме, которая не отбивает желания у авторов продолжать публикации. Хочу сказать автору спасибо - я узнал что-то новое с точки зрения идейных стратегий или подходов оптимизаций в некоторых случаях. Замеры производительности именно на моих объемах данных и на моих структурах покажут, что из этих идей сработает. А если не сработает, то это не потому что автор написал что-то не то.
И спасибо автору за выбор именно Postgres, т.к. в свете импортозамещения именно эта СУБД актуальна сейчас.
Спасибо за статью) Как раз искал как оптимизировать IN
Мда.
Соглашусь с мнением @Q3_Results. Здешним критикам надо поработать над манерой доносить свою позицию. Критика - это хорошо, но не тогда, когда она доносится языком, за который критика хочется послать... хм, самому писать статью, раз такой умный)
А автору - спасибо за материал и идеи. Filter, кстати, я давно уже полюбил за удобство и читабельность. Про exists хорошо, что написал. Я замечал, что даже довольно опытные коллеги иногда попросту забывают, что он есть.
Добрый вечер, а мне не хватило информации в статье про анализ плана запроса, про описание хеш джойна, нести лупа чем отличаются, в каких случаях что лучше. А то план есть, кост есть а что в данном плане происходит и почему ни слова.
>Использование LIKE позволяет использовать индексы
Только LIKE 'abc%' может использовать индексы, если быть точным.
LIKE '%abc%' и LIKE '%abc' НЕ используют индексы.
"LIKE '%abc%' и LIKE '%abc' НЕ используют индексы."
Не знаю как в PostgreSQL, а вообще индекс тут может быть использован для полного сканирования индекса вместо полного сканирования таблицы.
Оно, конечно, может - формально. Но придумать сочетание условий, когда сервер пойдёт на сканирование индекса (кроме случаев покрывающего индекса, само собой), будет крайне сложно. А с учётом достаточно жёстких ограничений на размер строковых данных, хранящихся в блоках тела таблицы, которые имеются практически в любой СУБД - это случаи, когда LIKE делается по полю буквально из нескольких, три-пять, не больше, символов. А это - повод задуматься об исправлении ошибки проектирования.
когда LIKE делается по полю буквально из нескольких, три-пять, не больше, символов. А это - повод задуматься об исправлении ошибки проектирования.
Или использовать расширение pg_trgm c GIN или GIST индексом.
LIKE '%abc%' и LIKE '%abc' НЕ используют индексы.
Смотря какой индекс. Даже не считая частичный индекс с WHERE Value LIKE '%abc%', можно использовать функциональный индекс:
DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp (
Id serial,
Value varchar
);
INSERT INTO tmp_tmp (Value)
SELECT to_hex(O.n)
FROM generate_series(1,1000000) O(n);
CREATE INDEX tmp_tmp_like_val ON tmp_tmp ((Value ~~ '%abc%'));
EXPLAIN ANALYZE
SELECT Id, Value
FROM tmp_tmp
WHERE Value LIKE '%abc%';Результат:
Index Scan using tmp_tmp_like_val on tmp_tmp (cost=0.42..15160.12 rows=40000 width=36) (actual time=0.019..0.188 rows=740 loops=1)
Index Cond: (((value)::text ~~ '%abc%'::text) = true)
Planning Time: 0.062 ms
Execution Time: 0.217 msНу а если совсем честно:
CREATE EXTENSION pg_trgm;
DROP INDEX IF EXISTS tmp_tmp_like_val;
CREATE INDEX tmp_tmp_like_val ON tmp_tmp USING gin (Value gin_trgm_ops);
EXPLAIN ANALYZE
SELECT Id, Value
FROM tmp_tmp
WHERE Value LIKE '%abc%';Результат:
Bitmap Heap Scan on tmp_tmp (cost=329.80..6231.80 rows=40000 width=36) (actual time=0.094..0.370 rows=740 loops=1)
Recheck Cond: ((value)::text ~~ '%abc%'::text)
Heap Blocks: exact=261
-> Bitmap Index Scan on tmp_tmp_like_val (cost=0.00..319.80 rows=40000 width=0) (actual time=0.062..0.062 rows=740 loops=1)
Index Cond: ((value)::text ~~ '%abc%'::text)
Planning Time: 0.074 ms
Execution Time: 0.407 ms
>Только LIKE 'abc%' может использовать индексы, если быть точным.
Tree-index, если быть еще точнее
Странно, а почему никто не увидел очевидную ошибку в:
WHERE orderdate BETWEEN '2006-07-01 00:00:00'::TIMESTAMP
AND '2006-07-31 00:00:00'::TIMESTAMP
?
Тут же должно быть '2006-07-31 23:59:59'::TIMESTAMP, разве не так?
Если реальное содержимое поля orderdate имеет значения с долями секунд, то ваш вариант потеряет данные из последней секунды.
Если в orderdate долей секунд не бывает, то исходный вариант зацепит лишнюю секунду.
Чтобы не сомневаться, я бы написал так:WHERE orderdate >= '2006-07-01 00:00:00'::TIMESTAMP
AND orderdate < '2006-07-31 00:00:00'::TIMESTAMP
Вы потеряли всё 31 июля
Я ленивый и пишу такое короче:
WHERE orderdate >= '2006-07-01'::date
AND orderdate < '2006-08-01'::dateЕсли в orderdate долей секунд не бывает, то исходный вариант зацепит лишнюю секунду.
Исходный вариант потеряет всё 31 июля, за исключением 00:00:00.
А с долями секунд да, вы правы, мой вариант не сработает.
а почему никто не увидел очевидную ошибку
Да потому что в неё никто и не смотрел. Речь-то шла о производительности, а не об алгоритмической или логической корректности.
Поле названо orderdate, структуры нет, и по названию априорно предполагаешь, что тип поля - date, а не timestamp.
Поле названо orderdate, структуры нет, и по названию априорно предполагаешь, что тип поля - date, а не timestamp.
В таком случае, наверное, вообще должно быть
BETWEEN '2006-07-01'::DATE AND '2006-07-31'::DATE
чтобы уж точно никаких неоднозначностей не было.
И когда речь о производительности, это не значит то, что можно забивать на логическую корректность. В противном случае можно дооптимизироваться до
int rand() {
return 42;
}
Не смотря на всю полезность статьи (особенно учитывая то, что вообще в последнее время публикуют) тут мы имеем абстрактные оптимизации в вакууме. У нас нет ни схемы таблиц, ни датасета с которым ведутся работы, а без этих вводных нельзя корректно оценить корректность и целесообразность таких оптимизаций. И в целом можно сократить до делайте хорошо, не делайте плохо. :)
Оптимизация SQL запросов