Comments 9
Отличная статья о том какая "магия" творится внутри современной СУБД.
Вы очень хороша написали, как рассчитывается стоимость каждого метода доступа, и в общих чертах далее пояснения в каких случаях используется тот или иной метод доступа. Вопрос - есть ли какие-то критерии на основе которых выбирается метод доступа? Может быть есть какие-то ориентировочные значения данных критериев? Данные знания могут помочь при оптимизации запросов.
Спасибо, рад, что понравилось.
Критерий простой - итоговая стоимость всего запроса.
Одна из идей, которые я пытался донести - что при расчете стоимости планировщик учитывает кучу разных факторов и в итоге выбирает наименее затратный с его точки зрения план. Поэтому в каком-то смысле лучший вариант оптимизации - дать планировщику всю необходимую информацию и не мешать. (Что, конечно, не всегда работает.)
Если же пытаться оптимизировать запрос самому (лишая планировщик свободы выбора и подсказывая ему ходы), то обычно держат в уме набор правил. Типа того, что первой должна идти таблица с наиболее ограничивающим условием, или что одни и те же данные не надо читать дважды. Для коротких OLTP-подобных запросов характерен точечный доступ по индексу и соединение вложенными циклами, для длинных OLAP-подобных запросов - полные сканирования (и часто секционирование) и хеш-соединения. В этой книге все довольно хорошо и подробно написано.
"Ситуация с доступом к таблице меняется, когда корреляция оказывается низкой. Создадим индекс по столбцу book_date, имеющему практически нулевую корреляцию с индексом, и посмотрим на запрос, выбирающий приблизительно ту же долю строк, что и в предыдущем примере. Индексный доступ оказывается настолько дорогим (56957 против 4639 в хорошем случае), что планировщик выбирает его, только если запретить ему все альтернативы"
Не совсем ясно, почему такая высокая стоимость. Правильно ли я понимаю, что такая ситуация может возникнуть, если для одной строки имеется достаточно большое количество версий строк, разбросанных по разным страницам и процедура очистки не выполнялась. Если выполнить очистку, то стоимость снизится до "хорошего случая".
Процитирую сам себя (абзацем ниже):
Дело в том, что чем ниже корреляция, тем выше вероятность того, что следующая версия табличной строки, идентификатор которой выдает метод доступа, окажется на другой странице. Поэтому вместо последовательного чтения узел Index Scan «скачет» со страницы на страницу, а количество обращений к страницам в предельном случае может достигать количества выбираемых версий строк.
Если в странице помещается M строк, а запросу надо прочитать N строк, то при идеальной корреляции нужно прочитать всего N/M страниц, а при нулевой корреляции - N страниц. Вот и разница.
Про текущее положение дел со старыми версиями строк оптимизатору ничего не известно, он ведь ориентируется на статистику. Конечно, если вы много раз обновите строку и очистка не успеет прибрать старые версии, то при выполнении запроса все эти версии придется просмотреть. Но это одинаково верно при любой корреляции и к разнице в стоимости, которую я показывал, не имеет отношения.
А что понимается под термином "версия табличной строки"? Это связано с MVCC?
Правильно, ли я понимаю, что количество версий строк зависит, от интенсивности обновления строки и настроек автовакуума? И если это так, то при одной версии строки (вставили строку и после не было обновлений и удаления этой строки) речь о корреляции вообще не должна идти, потому что не с чем коррелировать, т.е. нет версий строк?
Правильно, ли я понимаю, что количество версий строк зависит, от интенсивности обновления строки и настроек автовакуума?
Да.
И если это так, то при одной версии строки (вставили строку и после не было обновлений и удаления этой строки) речь о корреляции вообще не должна идти, потому что не с чем коррелировать, т.е. нет версий строк?
Нет. Видимо я запутал вас тем, что написал корреляция версий строк (с порядком в индексе), а не просто корреляция строк. Даже если у каждой строки ровно одна версия, то самих-то строк обычно много.
Вот пусть в таблице хранятся числа, для каждой строки есть только одна (актуальная) версия, и в страницах эти версии расположены так:
0 1 2 3 4 5 6 7 8 9.
Тогда корреляция равна единице, поскольку в индексе эти числа упорядочены точно так же.
А если в страницах что-то такое вперемешку:
3 8 2 6 9 4 1 0 7 5,
то корреляция около нуля.
Все это не важно, если мы читаем по индексу ровно одну строку, но очень важно, если читаем много.
Запросы в PostgreSQL: 4. Индексное сканирование