Введение 0:04 Представление спикеров: Алебард Тонов и Саша Коротков. Тема видео: индексы в PostgreSQL. Обещание рассказать о том, что не вся правда об индексах.
Индексы как "серебряная пуля" 1:03 Индексы не всегда полезны и могут даже вредить. Индексы ускоряют запросы, но могут быть ненужными.
Что такое индекс 1:24 Индекс - это дополнительная структура для ускорения запросов. Индексы не являются частью SQL и не связаны с реляционной алгеброй. Индексы могут быть полезны для поиска, ограничений целостности, сортировки и группировки.
Проблемы с индексами 2:23 Индексы могут создавать дополнительные накладные расходы. Индексы могут быть менее эффективными, если они малоселективны. Индексы должны быть понятны разработчикам, а не администраторам баз данных.
Индексы в PostgreSQL 3:49 Индексы в PostgreSQL вторичны и отделены от таблиц. Информация об индексах хранится в системном каталоге. Индексы не содержат информацию о видимости, что требует проверки записей в таблице.
Обновление записей и индексы 4:48 Обновление записей в таблице приводит к созданию новых записей в индексе. Индексы могут блокировать обновления и требуют внимания. Индексы используются для сортировки и поиска ближайших соседей.
Условия использования индексов 6:46 Индексы должны соответствовать типам аргументов и порядку. Индексы должны быть валидными и соответствовать плану запроса. Порядок колонок в индексе важен для его использования.
Оптимизация запросов 7:53 Оптимизатор выбирает наиболее дешевый план выполнения запроса. Использует информацию из системного каталога для выбора оптимального индекса. Важно использовать явные касты для типов данных, чтобы избежать ошибок.
Пример выбора индекса 8:51 Создаем таблицу с случайными значениями и создаем индексы. Пример выбора индекса для поиска по колонке с условием. Показана упрощенная схема выбора индекса из системного каталога.
Алгоритм выбора индекса 9:51 Находим нужный оператор по его имени и типу. Узнаем идентификатор оператора и подходящие индексы. Проверяем, поддерживается ли нужный оператор в классе операторов. Выбираем индекс с учетом стоимости выполнения запроса.
Кэширование и оптимизация 11:46 Информация системного каталога кэшируется для быстрого выполнения запросов. Примеры использования индексов: bitmap, hash, и т.д. Простейший индекс ускоряет поиск по колонке, но требует чтения всей таблицы.
Ускорение поиска по индексу 12:46 В версии 92 появился индекс heriscan, ускоряющий поиск по индексу. Карта видимости позволяет не читать таблицу для проверки видимости. Простейший индекс можно улучшить, упорядочив колонку для быстрого поиска.
Деревья и bitmap индексы 14:42 Деревья позволяют ускорить чтение индекса, особенно для больших таблиц. Bitmap индексы используются для объединения нескольких индексов и сортировки записей. Bitmap карта содержит информацию о видимости записей, что позволяет читать таблицу в правильном порядке.
Управление индексами 16:39 В PostgreSQL нет стандартного способа создания индексов. Для создания индекса используется метод using, который позволяет выбрать один из пяти access методов. Методы access имеют свои параметры, влияющие на создание индекса, например, fill factor для BTree.
Параметры индексов 17:39 Fill factor определяет степень заполнения страницы данными. Fast Update включен по умолчанию, но его можно отключить для ускорения поиска. Create Index позволяет масштабировать производительность, создавая индексы на отдельном хранилище.
Сортировка и создание индексов 20:34 Можно сортировать данные по возрастанию или убыванию. Create Index не блокирует таблицу, но требует два прохода и ждет окончания всех транзакций. Функциональные индексы позволяют создавать индексы по функциям от колонок.
Мониторинг индексов 24:31 Важно мониторить неиспользуемые и дублирующие индексы. Неиспользуемые индексы замедляют обновления и занимают место. Администраторы баз данных должны следить за индексами для оптимизации производительности.
Параметр work_mem 28:03 Work_mem определяет, сколько памяти может использоваться для создания индекса. Если памяти недостаточно, создается набор временных файлов для сортировки данных. Слишком большой work_mem может привести к проблемам с производительностью и использованием свопа.
Использование индексов в MySQL 28:53 Пример создания тестовой таблицы с выключенным автовакуумом. Использование bitmap индекса для сканирования таблицы. Статистика помогает предсказывать число строк в таблице.
Влияние интервала значений на использование индексов 29:52 При большом интервале значений используется bitmap индекс. При малом интервале используется индекс can. Команда cluster упорядочивает таблицу, снижая input output.
Использование индексов при order by 30:50 Индекс по колонке позволяет избежать сортировки. Использование больших значений в set эффективно для частого доступа. Составной индекс требует совпадения колонок для быстрого выполнения order by.
Влияние vacuum на использование индексов 32:30 Вакуум улучшает видимость страниц для транзакций. Использование visibility map для ускорения выполнения запросов. Влияние page cost на использование индексов.
Влияние work_mem на использование индексов 35:18 Ограничение work_mem влияет на использование bitmap индекса. Увеличение work_mem улучшает производительность. Пример неправильного использования индекса для пересечения полигонов.
Дополнительные модули для управления индексами 37:42 Использование модуля plan_tuner для запрета использования индексов. Модуль explain_plan позволяет контролировать параметры запроса. Влияние порядка создания индекса и данных на производительность.
Частичные и функциональные индексы 40:48 Частичный индекс позволяет использовать условия в индексе. Функциональный индекс используется для запросов с условиями на сумму колонок. Механизм heddon позволяет обновлять индексы без изменения размера.
Ускорение поиска ближайших соседей 43:00 Механизм Canon Gist позволяет ускорять поиск ближайших соседей. Пример: создание таблицы с случайными точками и поиск 10 ближайших к заданной точке. Без индекса пришлось бы считать расстояние и сортировать, с индексом - сразу извлекать по индексу. На миллионе записей выигрыш составляет 10 раз, на больших объемах - еще больше.
Проблемы с индексами и их решение 43:57 Индексы не используются для больших таблиц из-за ограничений. Люди уже написали скрипты для автоматического создания индексов для протестированных таблиц. Вопрос о реализации индексов для больших таблиц остается открытым.
Оптимизация использования индексов 44:56 Индексы используются только при оптимальном количестве операций ввода-вывода. Вопрос о том, как узнать неиспользуемые индексы, остается актуальным. В SQL есть стандартные способы определения неиспользуемых индексов, но нужно убедить сообщество в их удобстве.
