Comments 26
Не знаю как сейчас, а вот в ms sql 2012 любой in() превращался в кучу OR. Лучше и in() и or избегать везде, где это возможно.
Если востребовано, я могу потом разобрать и случай с IN(). Там не все однозначно. И опять по разному в MS SQL и PostgreSQL. Причем в последнем, до выхода 14-ой версии, IN() был медленней ANY(), так как не хешировался.
Спасибо за разбор, думаю будет интересно почитать про IN().
Пользуясь случаем попрошу сравнить запрос, использующий IN() с большим количеством аргументов (сотни), и выполнение отдельного запроса для каждого из аргументов, передаваемых IN(), в цикле.
Никогда не делай так. Если у тебя огромное количество однотипных параметров, как, например, портянка в IN(), то лучше перепиши на временную таблицу и фильтруй по ней. Если лень с CREATE TEMPORARY TABLE возиться, по крайней мере в PostgreSQL, можно подсунуть JSON в запрос, который СУБД сама развернет во временную таблицу.
Я как-то раз попробовал огромный запрос с OR и IN(). Даже EXPLAIN (без ANALYZE) не дождался, он тупо парсился часами.
В PostgreSQL точно после 14-ой версии включительно пробовали? Потому что с 14-ой версии список IN при парсинге сразу преобразуется в массив, а IN - в ANY.
DROP TABLE IF EXISTS tmp_tmp;
CREATE TABLE tmp_tmp (
ID int PRIMARY KEY,
Val int NOT NULL);
INSERT INTO tmp_tmp(ID, Val)
SELECT G.n AS ID, G.n%1000 AS Val
FROM generate_series(1,1000000) G(n);
-- используем для получения списка из 200 элементов
SELECT string_agg(G.n::text,',')
FROM generate_series(5000,1000000,5000) G(n);
-- сюда вставили в IN список, полученный из предыдущего запроса
EXPLAIN ANALYZE
SELECT *
FROM tmp_tmp
WHERE ID IN (5000,10000,15000,20000,25000,30000,35000,40000,45000,50000,55000,60000,65000,70000,75000,80000,85000,90000,95000,100000,105000,110000,115000,120000,125000,130000,135000,140000,145000,150000,155000,160000,165000,170000,175000,180000,185000,190000,195000,200000,205000,210000,215000,220000,225000,230000,235000,240000,245000,250000,255000,260000,265000,270000,275000,280000,285000,290000,295000,300000,305000,310000,315000,320000,325000,330000,335000,340000,345000,350000,355000,360000,365000,370000,375000,380000,385000,390000,395000,400000,405000,410000,415000,420000,425000,430000,435000,440000,445000,450000,455000,460000,465000,470000,475000,480000,485000,490000,495000,500000,505000,510000,515000,520000,525000,530000,535000,540000,545000,550000,555000,560000,565000,570000,575000,580000,585000,590000,595000,600000,605000,610000,615000,620000,625000,630000,635000,640000,645000,650000,655000,660000,665000,670000,675000,680000,685000,690000,695000,700000,705000,710000,715000,720000,725000,730000,735000,740000,745000,750000,755000,760000,765000,770000,775000,780000,785000,790000,795000,800000,805000,810000,815000,820000,825000,830000,835000,840000,845000,850000,855000,860000,865000,870000,875000,880000,885000,890000,895000,900000,905000,910000,915000,920000,925000,930000,935000,940000,945000,950000,955000,960000,965000,970000,975000,980000,985000,990000,995000,1000000);
Index Scan using tmp_tmp_pkey on tmp_tmp (cost=0.42..301.90 rows=200 width=8) (actual time=0.025..0.990 rows=200 loops=1)
Index Cond: (id = ANY ('{5000,10000,15000,20000,25000,30000,35000,40000,45000,50000,55000,60000,65000,70000,75000,80000,85000,90000,95000,100000,105000,110000,115000,120000,125000,130000,135000,140000,145000,150000,155000,160000,165000,170000,175000,180000,185000,190000,195000,200000,205000,210000,215000,220000,225000,230000,235000,240000,245000,250000,255000,260000,265000,270000,275000,280000,285000,290000,295000,300000,305000,310000,315000,320000,325000,330000,335000,340000,345000,350000,355000,360000,365000,370000,375000,380000,385000,390000,395000,400000,405000,410000,415000,420000,425000,430000,435000,440000,445000,450000,455000,460000,465000,470000,475000,480000,485000,490000,495000,500000,505000,510000,515000,520000,525000,530000,535000,540000,545000,550000,555000,560000,565000,570000,575000,580000,585000,590000,595000,600000,605000,610000,615000,620000,625000,630000,635000,640000,645000,650000,655000,660000,665000,670000,675000,680000,685000,690000,695000,700000,705000,710000,715000,720000,725000,730000,735000,740000,745000,750000,755000,760000,765000,770000,775000,780000,785000,790000,795000,800000,805000,810000,815000,820000,825000,830000,835000,840000,845000,850000,855000,860000,865000,870000,875000,880000,885000,890000,895000,900000,905000,910000,915000,920000,925000,930000,935000,940000,945000,950000,955000,960000,965000,970000,975000,980000,985000,990000,995000,1000000}'::integer[]))
Planning Time: 0.325 ms
Execution Time: 1.009 msТак что с "никогда" Вы явно погорячились.
Здесь ничтожно малое количество значений. Попробуй с десятком тысяч.
Проблема не в механике работы IN(), а в парсинге самого запроса.
Странный Вы. Могли и сами проверить. Рецепт я Вам дал.
Ну ладно, по той же таблице:
DO $$
DECLARE
sql_cmd varchar;
start_time timestamp;
execution_time integer;
records_processed integer;
BEGIN
DROP TABLE IF EXISTS tmp_result;
SELECT 'CREATE TEMP TABLE tmp_result AS
SELECT ID, Val
FROM tmp_tmp
WHERE ID IN ('
||string_agg(G.n::text,',')||');'
FROM generate_series(50,1000000,50) G(n)
INTO sql_cmd;
start_time=clock_timestamp();
EXECUTE sql_cmd;
execution_time=ROUND(EXTRACT(EPOCH FROM clock_timestamp()-start_time)*1000);
SELECT COUNT(1)
FROM tmp_result
INTO records_processed;
RAISE NOTICE 'Start time %, execution time % ms, processed % records',
start_time, execution_time, records_processed;
END; $$ LANGUAGE plpgsql;
Start time 2023-12-31 15:41:01.773914, execution time 50 ms, processed 20000 records
Это точно был 14й. К сожалению, сам запрос уже сейчас не могу найти, но его размер после генерации упирался, если не изменяет память, в несколько мегабайт. Я тогда ещё специально искал лимиты на размер запроса в постгресе, с общей итоговой рекомендацией: don't, believe me, just don't, ever. Объяснялось (и совпадало с моими наблюдениями) тем, что такую прорву значений пансеру всё равно парсить, поштучно, потом всасывать в память, затем делать частичный мэппинг относительно выводимых типов полей и выражений, в которых эти литералы используются, делать повторный мэтчинг на предмет приводимости типов, и только потом можно приступать к планированию. В тот раз у меня Analyze не смог минут за десять завершиться. Впрочем, спишу на выброс конкретного неудачного запроса и конкретных неудачных условий его исполнения. Но с тех пор предпочитаю ANY в котором аргумент всего один — array, что упрощает подготовку плана, например, в prepare, а уж грузить во время исполнения массив целиком всяко менее накладно (и память не жрёт).
с тех пор предпочитаю ANY
Я вообще то изначально писал "с 14-ой версии список IN при парсинге сразу преобразуется в массив, а IN - в ANY". И даже привел пример, когда в плане запроса это явно отображается. Так что без разницы ему, IN или ANY. В любом случае, если нужен поиск по массиву в ANY, то планировщик запросов строит хеш индекс по нему и использует уже его.
И давайте, пожалуйста, все же без ОБС. Приводите реальный пример, который можно обсуждать, а не воспоминания юности о том, что повторить никто не может, включая Вас.
Ого, вот это крайне удобная фича!
Недавно как раз с такой проблемой столкнулся в MSSQL. Случаем не в курсе, как у них дела обстоят?
смысл не в том, что любой OR тормозит, а в том, что только автору понятно как будет оптимальней, если накрутить замороченные индексы. посмотрите квери план своих кверей и успокойтесь, а то наделаете дел :)
Хм, я не понял базовой вещи. Привык к аналитическим / колоночным базам, где всё по другому.
IsValidated - булевский, не ноль. Следовательно (T.IsValidated OR NOT T.IsValidated) - покрывает всю таблицу. Каким образом использование совершенно не избирательного индекса ускоряет запрос? Неужели замержить два битмапа и потом проверять в битмапе настолько быстрее, чем просто проверить булевское значение напрямую?
Вы не поняли другого. В статье рассматривается не вопрос покрытия или не покрытия индексами таблицы, а вопрос SQL запроса, требующего двух разных выборок по разным индексам одной и той же таблицы.
А причины упрощения, на которое Вы обратили внимание, в статье я указал дважды:
Для лучшего понимания, пример будет сильно упрощен. Ровно до того предела, пока проявляется суть проблемы.
Еще раз извиняюсь за столь сильное упрощение, когда индексы различаются лишь фильтром в WHERE. В реальных условиях индексы могут различаться не только этим и вовсе не быть частичными (фильтрованными в терминах MS). Для рассмотрения проблемы нам важно только чтобы это были разные индексы, необходимые в рамках одного запроса.
Мой вопрос это не придирка к статье, пример вполне нормальный, и разумное упрощение, хорошо показавшее разные планы.
Мой вопрос ортогональный: почему использование столь неселективного индекса ускорило выполнение запроса? Индекс хорошо когда он помогает найти "иголку в итоге сена". Когда индекс покрывает всю таблицу (и чтением только индекса не обойтись), толку от него вроде бы быть не должно, но цифры показывают что есть. Вот про это вопрос.
почему использование столь неселективного индекса ускорило выполнение запроса?
Потому что два индекса покрыли все записи, необходимые этому запросу. При наличии полного индекса потребовалась бы лишь одна выборка и это было бы явно быстрее, чем две выборки по двум индексам. Например:
CREATE INDEX tmp_tmp_All ON tmp_tmp (SessionId, Val, IsValidated) INCLUDE (ID);
SELECT T.ID
FROM tmp_sessions S
JOIN tmp_tmp T ON T.SessionId=S.SessionId
AND T.Val=530
AND (T.IsValidated OR NOT T.IsValidated);
Nested Loop (cost=0.42..38.91 rows=157 width=4) (actual time=0.479..1.258 rows=1000 loops=1)
-> Seq Scan on tmp_sessions s (cost=0.00..1.21 rows=21 width=4) (actual time=0.380..0.382 rows=21 loops=1)
-> Index Only Scan using tmp_tmp_all on tmp_tmp t (cost=0.42..1.73 rows=7 width=8) (actual time=0.030..0.039 rows=48 loops=21)
Index Cond: ((sessionid = s.sessionid) AND (val = 530))
Filter: (isvalidated OR (NOT isvalidated))
Heap Fetches: 0
Planning Time: 2.322 ms
Execution Time: 1.304 msНа 1 ms быстрее, чем запрос по двум частичным индексам.
А вот причины использования частичных индексов могут быть разными. Например, 95% запросов выполняются только по записям с IsValidated = TRUE, а в таблице записей с IsValidated = FALSE столько же или даже больше, чем записей с IsValidated = TRUE. Тогда эти 95% запросов смогут работать с индексом, в два или больше раз меньшим, чем полный индекс. Что сокращает и потребляемую память, и время выборки по этому индексу.
Запрос по полному индексу только IsValidated:
SELECT T.ID
FROM dev.tmp_tmp T
WHERE T.SessionId BETWEEN 30 AND 49
AND T.Val BETWEEN 300 AND 699
AND T.IsValidated;
Index Only Scan using tmp_tmp_all on tmp_tmp t (cost=0.42..4802.01 rows=40516 width=4) (actual time=0.050..9.381 rows=40000 loops=1)
Index Cond: ((sessionid >= 30) AND (sessionid <= 49) AND (val >= 300) AND (val <= 699) AND (isvalidated = true))
Heap Fetches: 0
Buffers: shared hit=759
Planning Time: 0.100 ms
Execution Time: 10.310 msИ по частичному:
Index Only Scan using tmp_tmp_isvalidated on tmp_tmp t (cost=0.42..2348.52 rows=40516 width=4) (actual time=0.034..5.445 rows=40000 loops=1)
Index Cond: ((sessionid >= 30) AND (sessionid <= 49) AND (val >= 300) AND (val <= 699))
Heap Fetches: 0
Buffers: shared hit=381
Planning Time: 0.116 ms
Execution Time: 6.399 msКак видим, время выполнения запроса в полтора раза больше по полному индексу, чем по частичному. А буферов (памяти) в запросе по частичному индексу использовалось в два раза меньше.
Естественно, никто не запрещает иметь и полный индекс, и частичный. Но следует понимать, что раз подавляющее большинство запросов работают с частичным индексом, то он с большой вероятностью уже закеширован в памяти. И для тех редких запросов, для которых оптимален полный индекс, часто эффективней будет считать в память только второй частичный индекс, чем полный.
Есть предположение, что данный запрос будет быстрее работать и без индекса.
Тут вообще-то технический профессиональный сайт. И здесь не гадают и предполагают, а доказывают свои утверждения экспериментально.
Особенно если утверждается, что прочитать 4% записей по индексу медленней, чем эти же 4%, но отсканировав всю таблицу. К тому же результат выполнения планов запросов без использования индексов для MS SQL и PostgreSQL в статье приведен.
Статья интересная и полезная. Раньше я не прогонял свой код sql процедур через план запроса, а руководствовалмя известными практикам создания индексов для полей в фильтах и join. Но насколько помню, оператор OR в них не использовал, так как он требуется редко и интуитивно понимал, что он неоптимален с точки зрения быстродействия. Мои специфические задачи и приложения требуют мониторинга в онлайне и поэтому быстродействия важно, так как логи приложений находятся в бд и они активно используются sql процедурами мониторинга и минимально загружают CPU сервера. В мониторинге загрузка CPU и объем используемой памяти отображается каждые 10 секунд специальным запросом.
SELECT T.ID
FROM tmp_tmp2 T
WHERE T.SessionId BETWEEN 30 AND 49
AND T.Val=530
AND T.IsValidated in (0,1)
Касаемо MS SQL, потестил на своем маке (докер + Azure Data Studio)
Пересоздал индексы правильно (без включения ID в некластеризованный индекс), сделал замеры... И создал 1 индекс по колонкам Sessionid, Val, IsValidated без фильтров.
Clustered Index Scan заменился на Index Seek.
Было Est. CPU Cost 0.220031 + 0.220031, Act. CPU Cost 28, Est. IO Cost 3.30979
Стало Est. CPU Cost 0.220157, Act. CPU Cost 22, Est. IO Cost 0.351273.
Также, количество выполнений снизилось с 11 до 1. IO Cost снизилось более чем в 9 раз!
Выводы:
Индексы надо создавать правильно
В некластеризованный индекс не нужно пихать колонку кластеризованного индекса, она там уже есть
Не натягивать сову на глобус (покрывающие индексы для общих примеров)
Кластеризованный индекс - B+ дерево, он будет выбираться по-умолчанию, если нет более точных индексов для конкретных запросов. Можете поиграться с Plan Explorer, чтоб проверить это.
создал 1 индекс по колонкам Sessionid, Val, IsValidated без фильтров
А теперь перечитайте проблему, рассматриваемую в статье и не занимайтесь упрощениями, которые уже не позволят эту проблему рассмотреть:
Для лучшего понимания, пример будет сильно упрощен. Ровно до того предела, пока проявляется суть проблемы.
Еще раз извиняюсь за столь сильное упрощение, когда индексы различаются лишь фильтром в WHERE. В реальных условиях индексы могут различаться не только этим и вовсе не быть частичными (фильтрованными в терминах MS). Для рассмотрения проблемы нам важно только чтобы это были разные индексы, необходимые в рамках одного запроса.
Обиделся чтоли?) Давай еще один минус, может легче станет.
Кластеризованный ключ не надо пихать в некластеризованный индекс.
Создал 1 индекс вместо 2 (1 лучше чем 2 в этом случае) по 1 колонке IsValidated. И предложенные индексы без фильтрации. Итог - тот же вложенный цикл с Key Lookup. И IO Cost сравнимый с "улучшенной версией", примерно 0.34.
А после создания рекомендованного индекса (добавляем в индекс 1 бит, прикол), IO Cost становится 0.003. От исходного варианта, Вашего, разница примерно в 1100 раз. Занавес. С наступающим)
Кластеризованный ключ не надо пихать в некластеризованный индекс.
Так как в статье рассматривается поведение сервера как с кластерным первичным индексом, так и без него, то включение ID в остальные индексы вполне оправдано, чтобы не загромождать примеры кода.
Кроме того, хоть при поиске по не кластерному индексу обращение к кластерному индексу и не отображается в плане запроса, но тоже стоит чего-то.
По утверждению MS: Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.
Доказательство. Выполняем запрос:
SELECT V.ID
FROM tmp_tmp V
WHERE V.SessionId BETWEEN 10 AND 29
AND V.Val BETWEEN 100 AND 899
AND V.IsValidated=1Без INCLUDE(ID):
И с INCLUDE(ID):
Наглядно видно, что INCLUDE(ID) привел как к ускорению запроса, так и к снижению стоимости операции и стоимости ввода-вывода.
Создал 1 индекс вместо 2
И именно за это и получили заслуженный минус. Так как проанализировать проблему оптимизации выполнения запроса, использующего одновременно два разных индекса одной таблицы, с одним индексом уже невозможно физически.
Вы сделали ровно то, о чем я писал открытым текстом. Упростили пример до полной непригодности в целях исследования рассматриваемой проблемы.
Причем второй раз проигнорировали то, что я Вам пишу:
В реальных условиях индексы могут различаться не только этим и вовсе не быть частичными (фильтрованными в терминах MS). Для рассмотрения проблемы нам важно только чтобы это были разные индексы, необходимые в рамках одного запроса.
P.S. Просьба не переходить снова на личности. Это не профессионально.
Немного про OR в SQL запросах