SQL-Ex blog

Два способа использования индекса

Обычное индексное сканирование (Index Scan) проходит по индексу в порядке сортировки и для каждой подходящей записи немедленно переходит к куче, чтобы извлечь эту строку. Запись индекса, извлечение из кучи, запись индекса, извлечение из кучи — чередуясь. Это замечательно дёшево, когда вы извлекаете несколько строк, потому что вы выполняете несколько переходов к куче. Это становится дорогим по мере роста количества строк по конкретной причине: переходы к куче выполняются в порядке индекса, а не в физическом порядке, поэтому они случайным образом разбросаны по таблице, и одна и та же страница кучи может посещаться много раз, если несколько подходящих строк находятся на ней, но индекс обращается к ним непоследовательно. Много случайных, возможно, повторяющихся обращений к страницам.

Сканирование битовой карты (Bitmap Scan) — это ответ PostgreSQL на проблему масштабирования, и оно всегда появляется в EXPLAIN как пара узлов: Bitmap Index Scan, передающий данные в Bitmap Heap Scan. Первый узел сканирует индекс, но ничего не извлекает, — он строит битовую карту всех позиций строк, которые соответствуют условию. Второй узел затем проходит по этой битовой карте в порядке физических страниц, посещая каждую страницу кучи ровно один раз и извлекая все подходящие строки на ней, прежде чем перейти к следующей. Случайный, повторяющийся доступ к куче при обычном индексном сканировании превращается в один упорядоченный проход только по страницам, содержащим совпадения. Из этой конструкции вытекают два следствия, которые стоит знать: поскольку он собирает идентификаторы кортежей (TID) перед извлечением, сканирование битовой карты может комбинировать несколько индексов с помощью узлов BitmapAnd и BitmapOr, используя два индекса на одной таблице в одном запросе, — чего обычное индексное сканирование не может сделать; и поскольку оно знает свои целевые страницы заранее, Bitmap Heap Scan является единственным узлом плана, который выполняет собственное упреждающее чтение (prefetching), используя effective_io_concurrency (параметр, описанный в отдельной статье).

Таким образом, планировщик выбирает между ними в основном на основе количества строк. Мало строк — обычное индексное сканирование с его дешёвым небольшим количеством переходов к куче. Много строк — сканирование битовой карты, с затратами на построение битовой карты в обмен на упорядоченный однократный доступ к куче. Очень много строк (большая часть таблицы) — и оба проигрывают последовательному сканированию, за которое отвечает enable_seqscan. Эти два параметра позволяют вам «оттолкнуть» планировщик от его выбора и увидеть стоимость альтернативы.

Одна тонкость относительно enable_indexscan: его отключение также отключает index-only scans — вариант, когда покрывающий индекс полностью удовлетворяет запрос из самого индекса, вообще не обращаясь к куче. Они управляются тем же переключателем, поэтому enable_indexscan = off одним движением убирает из меню планировщика как обычное индексное сканирование, так и индексное сканирование без обращения к таблице.

Симптомы, при которых стоит их использовать

Диагностические ситуации довольно чётко различаются.

Обращайтесь к enable_indexscan = off, когда планировщик выбрал обычное индексное сканирование, а запрос выполняется медленнее, чем вы считаете, и вы подозреваете, что количество строк достаточно велико, чтобы сканирование битовой карты или даже последовательное сканирование было лучшим выбором. Признаком является Index Scan в EXPLAIN ANALYZE с большим фактическим количеством строк, часто с тем, что запрос тратит время на доступ к куче. Отключите индексные сканирования, запустите повторно и посмотрите, будет ли запасной вариант планировщика (обычно сканирование битовой карты) на самом деле быстрее; если да, возникает вопрос, почему планировщик неверно оценил индексное сканирование, и ответ часто заключается в том, что random_page_cost установлена слишком низко для вашего хранилища или устаревшая статистика заставляет планировщик занижать оценку количества строк. Эксперимент говорит вам, что план был неверен; настройки стоимости говорят вам о причинах.

Обращайтесь к enable_bitmapscan = off в зеркальной ситуации: когда Bitmap Heap Scan работает хуже ожидаемого, и вы хотите узнать, будет ли лучше обычное индексное сканирование или последовательное. Два конкретных признака находятся в выводе EXPLAIN ANALYZE самого плана битовой карты. Rows Removed by Index Recheck с большим числом означает, что битовая карта стала слишком большой для work_mem и стала неточной («lossy») — перейдя от «точных позиций строк» к «на этих страницах могут быть совпадения», что требует повторной проверки каждого кортежа на этих страницах, — что является признаком того, что вы либо выбираете слишком много для сканирования битовой карты, либо имеет место «голодание» по work_mem. А Heap Blocks: lossy= с большим числом — та же история с другой стороны. Отключение сканирований битовой карты позволяет вам измерить альтернативы; видя количество неточных блоков, вы понимаете, что сканирование битовой карты изначально работало с перенапряжением.

В обоих случаях дисциплина идентична, и её стоит повторить один раз для этой пары: SET ... = off — это исследовательский зонд, запускаемый в сеансе, который вы закроете, цель которого — раскрыть логику планировщика, чтобы вы могли исправить реальную причину: статистику, random_page_cost, work_mem, effective_cache_size. Оставить любой из этих параметров выключенным в вашей конфигурации для принудительного выбора плана — значит лечить симптом и лгать планировщику о том, какие планы существуют; следующий запрос, которому действительно понадобится отключённый метод доступа, заплатит за это. Диагностируйте, узнайте причину, верните параметры обратно и исправьте то, на что указал зонд.