Без индексов postgresql на каждый поиск читает таблицу целиком - строку за строкой. На миллионе строк это секунды вместо миллисекунд. Индекс - это отдельная структура на диске, которая хранит значения столбца уже упорядоченными и указатели на физическое расположение строк. В этом уроке разберём самый главный тип индекса - B-tree, поймём, как он устроен внутри, что такое селективность, как работает индекс по нескольким столбцам и почему порядок столбцов критичен. И главное - почему планировщик иногда демонстративно игнорирует ваш индекс и делает seq scan, хотя индекс вот он, готовый.
Как это работает
B-tree (балансированное дерево) - это структура из страниц-узлов. Сверху корень, под ним промежуточные узлы, в самом низу - листья. В листьях лежат отсортированные значения ключа и ссылки (TID - tuple id) на конкретные строки в таблице. Чтобы найти значение, СУБД спускается от корня к листу: на каждом уровне выбирает нужную ветку. Дерево неглубокое - даже для миллиардов строк это три-четыре спуска по страницам, а не миллион чтений.
Именно поэтому B-tree хорош для операций сравнения: равенство, больше, меньше, диапазон, BETWEEN, и для ORDER BY - данные в листьях уже отсортированы, можно идти по ним подряд. Hash так не умеет, GIN решает другие задачи. B-tree - дефолтный индекс, его ставит CREATE INDEX без указания типа.
Ключевое понятие - селективность. Это доля строк, которые остаются после условия. Поиск конкретного номера билета среди миллионов - селективность близка к нулю, индекс блестящий. Условие status = 'Arrived', когда таких рейсов половина таблицы - селективность высокая, индекс почти бесполезен. Планировщик это знает из статистики, которую собирает ANALYZE и хранит в pg_statistic.
И тут главная мысль урока. Индекс - это НЕ всегда быстрее. Чтение по индексу - это случайные прыжки по диску: нашли TID в листе, прыгнули в таблицу за самой строкой (в PostgreSQL данные строк лежат отдельно от индекса, в куче - heap). Если строк нужно много, тысячи случайных прыжков оказываются дороже, чем один последовательный проход seq scan, который читает страницы пачками по порядку. Планировщик считает стоимость обоих вариантов и выбирает дешёвый. Если он выбрал seq scan - часто он прав.
SQL и примеры
Возьмём демобазу Авиаперевозки (схема bookings). Создадим индекс по номеру рейса и посмотрим план:
Код: Выделить всё
CREATE INDEX idx_flights_flight_no ON flights (flight_no);
EXPLAIN ANALYZE
SELECT * FROM flights WHERE flight_no = 'PG0001';
Код: Выделить всё
EXPLAIN ANALYZE
SELECT * FROM flights WHERE status = 'Scheduled';
Составной индекс - по нескольким столбцам сразу. Порядок имеет огромное значение:
Код: Выделить всё
CREATE INDEX idx_tf_flight_fare
ON ticket_flights (flight_id, fare_conditions);
Классическая ловушка - функция или приведение типа над столбцом. Это убивает обычный индекс:
Код: Выделить всё
-- индекс по book_ref НЕ сработает, lower() меняет значение
EXPLAIN ANALYZE
SELECT * FROM bookings WHERE lower(book_ref) = '00000f';
-- лечится индексом по выражению
CREATE INDEX idx_bookings_lower_ref ON bookings (lower(book_ref));
Частые грабли
- Несовпадение типов: столбец bigint, а в условии WHERE id = '123' строкой или number = 5.0 - неявное приведение может отключить индекс. Передавайте литерал того же типа.
- Функция над столбцом: WHERE date(scheduled_departure) = '2017-08-15' игнорирует индекс по самому столбцу. Перепишите через диапазон BETWEEN или сделайте индекс по выражению.
- LIKE '%текст%' с ведущим процентом B-tree не использует - дерево отсортировано слева направо. Префиксный LIKE 'текст%' работать может.
- Свежесозданный индекс без ANALYZE: статистика устарела, планировщик считает по старым данным и выбирает не то. После массовой загрузки запускайте ANALYZE.
- Низкая селективность: индекс по полю с двумя-тремя значениями (пол, флаг, статус) обычно мёртвый груз - планировщик его не возьмёт, а на запись он замедляет.
- Слишком много индексов: каждый INSERT/UPDATE обновляет все индексы таблицы. Десяток индексов на горячей таблице бьёт по скорости записи.
- OR между разными столбцами часто мешает использовать индексы - иногда быстрее переписать через UNION.
- Разверните демобазу Авиаперевозки и подключитесь: psql -d demo
- Выполните EXPLAIN ANALYZE SELECT * FROM ticket_flights WHERE flight_id = 1234; запомните тип скана и время.
- Создайте индекс: CREATE INDEX ON ticket_flights (flight_id); затем ANALYZE ticket_flights;
- Повторите тот же EXPLAIN ANALYZE - сравните тип скана (стал Index Scan?) и время.
- Сделайте запрос с низкой селективностью: EXPLAIN SELECT * FROM ticket_flights WHERE fare_conditions = 'Economy'; убедитесь, что планировщик выбрал Seq Scan, и объясните себе почему.
- Спровоцируйте отказ от индекса функцией: EXPLAIN SELECT * FROM bookings WHERE lower(book_ref) = '00000f'; потом создайте индекс по выражению lower(book_ref), сделайте ANALYZE и проверьте план снова.
- Удалите свои тестовые индексы через DROP INDEX, чтобы не мусорить в базе.
- Почему B-tree подходит для диапазонных запросов и ORDER BY, а Hash - нет?
- Что такое селективность и как она влияет на выбор между index scan и seq scan?
- В составном индексе (a, b, c) для каких условий WHERE он сработает, а для каких нет, и почему?
- Почему WHERE date(scheduled_departure) = ... не использует обычный индекс по столбцу и как это починить?
- Когда seq scan действительно быстрее index scan, хотя индекс существует?
- Зачем запускать ANALYZE после создания индекса или массовой загрузки данных?
