Partial индексы

Использование #partial индексов - один из способов оптимизации работы баз данных. Представьте, что у вас есть таблица с заказами с 10млн строк и часто выполняющийся типовой запрос SELECT * FROM orders WHERE state IN ('new', 'processing') ORDER BY order_time, возвращающий не более пары сотен заказов. Индекс по state в данном случае будет достаточно эффективным ввиду хорошей селективности по указанным значениям, но будет весить пару сотен мегабайт. И, кажется, поднимать с диска в память индекс, величиной в пару сотен мегабайт, для выгрузки нескольких килобайт информации - очень непродуктивно. Что можно сделать? Ну, например, CREATE INDEX order_time_processing_idx ON order_time(state) WHERE state in ('new', 'processing'). Индекс теперь будет весить несколько мегабайт и, более того, он будет в отсортированном варианте. БД будет его использовать для нашего запроса. Даже если изменить запрос и не сортировать и не запрашивать поле order_time - все равно этот индекс будет использоваться, т.к. в нем есть информация о расположении нужных строк.

Вот так простейшей правкой можно отрезать большие бесполезные куски индексов. И это очень положительно влияет на общую производительность БД, т.к. уменьшает размер и количество IO операций на диске для пересчета индекса и высвобождает много памяти для других кэшей и других индексов что еще больше снижает общее количество дискового IO.

Пример был для PostgreSQL, но подобные штуки можно проворачивать и на БД, не поддерживающих partial индексы. Например, с помощью горизонтального деления таблиц по состояниям. Правда это уже ближе к application-level оптимизациям.