Внутреннее устройство баз данных — Примеры

Пример 1 — Почему один лишний столбец увеличил скан в разы

Ночной отчёт выбирает из таблицы на два миллиона строк два узких столбца и укладывается в 400 мс, но стоит добавить в список выборки длинный столбец с описанием — и тот же запрос занимает уже 1,6 с. Расчёт ниже опирается на страницу размером 8192 байта.

размер страницы 8192 байта

узкая строка:   id (8) + status (4)                    = 12 байт  -> ~680 строк/страницу
широкая строка: id (8) + status (4) + description 480  = 492 байта -> ~16 строк/страницу

2 000 000 строк при 680/страницу = ~2 940 страниц для чтения
2 000 000 строк при  16/страницу = ~125 000 страниц для чтения

Решение

лишний столбец расширил строку, поэтому страница вмещает намного
меньше строк и скан задевает примерно в 42 раза больше страниц.

исправить: выбирайте только нужные столбцы, чтобы строки оставались узкими
исправить: пусть индекс покрывает запрос, тогда страницы таблицы не читаются

База данных читает страницы фиксированного размера, и как только страница загружена, каждая строка на ней достаётся бесплатно. Значит, важно не число строк, а то, сколько страниц задевает скан. Широкая строка умещает меньше строк на страницу, и число страниц растёт гораздо быстрее, чем подсказывает число строк. Ловушка в том, чтобы прочитать оценку строк и обвинить строки: изменилось именно число страниц.

Пример 2 — План, который поверил неверному числу

Отчёт по клиентам выполняется за 30 мс для небольшого клиента и за 9 секунд для крупного. Ниже — план, который выбрала база. Скажите, какое число неверно, и назовите две первые вещи, которые стоит попробовать.

Nested Loop  (cost=0.29..412.55 rows=1 width=64) (actual rows=84000)
  ->  Index Scan on customers  (actual rows=1)
        Index Cond: (id = 42)
  ->  Seq Scan on orders  (rows=20) (actual rows=84000)
        Filter: (customer_id = 42)

Решение

оценка — 20 строк, реальность — 84 000. Вложенный цикл подходит
для 20 строк и губителен для 84 000.

попробовать: обновить статистику по orders и перечитать план
попробовать: проиндексировать orders.customer_id, чтобы внутренний шаг искал по индексу для каждой внешней строки

Вложенный цикл перезапускает внутренний шаг для каждой внешней строки. Поэтому его стоимость равна числу внешних строк, умноженному на стоимость внутреннего шага. Для двадцати строк это нормально, для восьмидесяти четырёх тысяч — катастрофа. Выбор показался дешёвым из-за неверной оценки, а обычная причина такой оценки после большой загрузки — устаревшая статистика. Индекс по столбцу соединения делает внутренний шаг дешёвым в любом случае. Ловушка — переписывать текст запроса: один и тот же оператор выполняется быстро или медленно в зависимости от данных под ним.