Что такое частичные индексы
Что такое частичные индексы
Частичный индекс — это индекс, который строится по подмножеству строк таблицы, определяемому условным выражением (оно называется предикатом частичного индекса). Такой индекс содержит записи только для строк, удовлетворяющих предикату. Частичные индексы довольно специфичны, но в ряде ситуаций они могут быть очень полезны.
Частичные индексы могут быть полезны, во-первых, тем, что позволяют избежать индексирования распространённых значений. Так как при поиске распространённого значения (такого, которое содержится в значительном проценте всех строк) индекс всё равно не будет использоваться, хранить эти строки в индексе нет смысла. Исключив их из индекса, можно уменьшить его размер, а значит и ускорить запросы, использующие этот индекс. Это также может ускорить операции изменения данных в таблице, так как индекс будет обновляться не всегда. Возможное применение этой идеи проиллюстрировано в Примере 11.1.
Пример 11.1. Настройка частичного индекса, исключающего распространённые значения
Предположим, что вы храните в базе данных журнал обращений к корпоративному сайту. Большая часть обращений будет происходить из диапазона IP-адресов вашей компании, а остальные могут быть откуда угодно (например, к нему могут подключаться внешние сотрудники с динамическими IP). Если при поиске по IP вас обычно интересуют внешние подключения, IP-диапазон внутренней сети компании можно не включать в индекс.
Пусть у вас есть такая таблица:
Создать частичный индекс для нашего примера можно так:
Так будет выглядеть типичный запрос, использующий этот индекс:
А следующий запрос не будет использовать этот индекс:
Заметьте, что при таком определении частичного индекса необходимо, чтобы распространённые значения были известны заранее, так что такие индексы лучше использовать, когда распределение данных не меняется. Хотя такие индексы можно пересоздавать время от времени, подстраиваясь под новое распределение, это значительно усложняет поддержку.
Во-вторых, частичные индексы могут быть полезны тем, что позволяют исключить из индекса значения, которые обычно не представляют интереса; это проиллюстрировано в Примере 11.2. При этом вы получаете те же преимущества, что и в предыдущем случае, но система не сможет извлечь « неинтересные » значения по этому индексу, даже если сканирование индекса может быть эффективным. Очевидно, настройка частичных индексов в таких случаях требует тщательного анализа и тестирования.
Пример 11.2. Настройка частичного индекса, исключающего неинтересные значения
Если у вас есть таблица, в которой хранятся и оплаченные, и неоплаченные счета, и при этом неоплаченные счета составляют только небольшую часть всей таблицы, но представляют наибольший интерес, производительность запросов можно увеличить, создав индекс только по неоплаченным счетам. Сделать это можно следующей командой:
Этот индекс будет применяться, например в таком запросе:
Однако он также может применяться в запросах, где order_nr вообще не используется, например:
Заметьте, что в таком запросе этот индекс не будет использоваться:
Счёт с номером 3501 может оказаться, как в числе неоплаченных, так и оплаченных.
Третье возможное применение частичных индексов вообще не связано с использованием индекса в запросах. Идея заключается в том, чтобы создать уникальный индекс по подмножеству строк таблицы, как в Примере 11.3. Это обеспечит уникальность среди строк, удовлетворяющих условию предиката, но никак не будет ограничивать остальные.
Пример 11.3. Настройка частичного уникального индекса
Предположим, что у нас есть таблица с результатами теста. Мы хотим, чтобы для каждого сочетания предмета и целевой темы была только одна запись об успешном результате, а неудачных попыток могло быть много. Вот как можно этого добиться:
Это подход будет особенно эффективным, когда неудачных попыток будет намного больше, чем удачных.
Наконец, с помощью частичных индексов можно также переопределять выбираемый системой план запроса. Возможно, что для данных с неудачным распределением система решит использовать индекс, тогда как на самом деле это неэффективно. В этом случае индекс можно настроить так, чтобы в подобных запросах он не работал. Обычно Postgres Pro принимает разумные решения относительно применения индексов (т. е. старается не использовать их для получения распространённых значений, так что частичный индекс в вышеприведённом примере помог только уменьшить размер индекса, для отказа от использования индекса он не требовался), поэтому крайне неэффективный план может быть поводом для сообщения об ошибке.
Узнать о частичных индексах больше можно в следующих источниках: [ston89b], [olson93] и [seshadri95].
Что такое частичные индексы
Частичный индекс — это индекс, который строится по подмножеству строк таблицы, определяемому условным выражением (оно называется предикатом частичного индекса). Такой индекс содержит записи только для строк, удовлетворяющих предикату. Частичные индексы довольно специфичны, но в ряде ситуаций они могут быть очень полезны.
Частичные индексы могут быть полезны, во-первых, тем, что позволяют избежать индексирования распространённых значений. Так как при поиске распространённого значения (такого, которое содержится в значительном проценте всех строк) индекс всё равно не будет использоваться, хранить эти строки в индексе нет смысла. Исключив их из индекса, можно уменьшить его размер, а значит и ускорить запросы, использующие этот индекс. Это также может ускорить операции изменения данных в таблице, так как индекс будет обновляться не всегда. Возможное применение этой идеи проиллюстрировано в Примере 11.1.
Пример 11.1. Настройка частичного индекса, исключающего распространённые значения
Предположим, что вы храните в базе данных журнал обращений к корпоративному сайту. Большая часть обращений будет происходить из диапазона IP-адресов вашей компании, а остальные могут быть откуда угодно (например, к нему могут подключаться внешние сотрудники с динамическими IP). Если при поиске по IP вас обычно интересуют внешние подключения, IP-диапазон внутренней сети компании можно не включать в индекс.
Пусть у вас есть такая таблица:
Создать частичный индекс для нашего примера можно так:
Так будет выглядеть типичный запрос, использующий этот индекс:
А следующий запрос не будет использовать этот индекс:
Заметьте, что при таком определении частичного индекса необходимо, чтобы распространённые значения были известны заранее, так что такие индексы лучше использовать, когда распределение данных не меняется. Хотя такие индексы можно пересоздавать время от времени, подстраиваясь под новое распределение, это значительно усложняет поддержку.
Во-вторых, частичные индексы могут быть полезны тем, что позволяют исключить из индекса значения, которые обычно не представляют интереса; это проиллюстрировано в Примере 11.2. При этом вы получаете те же преимущества, что и в предыдущем случае, но система не сможет извлечь « неинтересные » значения по этому индексу, даже если сканирование индекса может быть эффективным. Очевидно, настройка частичных индексов в таких случаях требует тщательного анализа и тестирования.
Пример 11.2. Настройка частичного индекса, исключающего неинтересные значения
Если у вас есть таблица, в которой хранятся и оплаченные, и неоплаченные счета, и при этом неоплаченные счета составляют только небольшую часть всей таблицы, но представляют наибольший интерес, производительность запросов можно увеличить, создав индекс только по неоплаченным счетам. Сделать это можно следующей командой:
Этот индекс будет применяться, например в таком запросе:
Однако он также может применяться в запросах, где order_nr вообще не используется, например:
Заметьте, что в таком запросе этот индекс не будет использоваться:
Счёт с номером 3501 может оказаться, как в числе неоплаченных, так и оплаченных.
Третье возможное применение частичных индексов вообще не связано с использованием индекса в запросах. Идея заключается в том, чтобы создать уникальный индекс по подмножеству строк таблицы, как в Примере 11.3. Это обеспечит уникальность среди строк, удовлетворяющих условию предиката, но никак не будет ограничивать остальные.
Пример 11.3. Настройка частичного уникального индекса
Предположим, что у нас есть таблица с результатами теста. Мы хотим, чтобы для каждого сочетания предмета и целевой темы была только одна запись об успешном результате, а неудачных попыток могло быть много. Вот как можно этого добиться:
Наконец, с помощью частичных индексов можно также переопределять выбираемый системой план запроса. Возможно, что для данных с неудачным распределением система решит использовать индекс, тогда как на самом деле это неэффективно. В этом случае индекс можно настроить так, чтобы в подобных запросах он не работал. Обычно Postgres Pro принимает разумные решения относительно применения индексов (т. е. старается не использовать их для получения распространённых значений, так что частичный индекс в вышеприведённом примере помог только уменьшить размер индекса, для отказа от использования индекса он не требовался), поэтому крайне неэффективный план может быть поводом для сообщения об ошибке.
Что такое частичные индексы
Частичный индекс — это индекс, который строится по подмножеству строк таблицы, определяемому условным выражением (оно называется предикатом частичного индекса). Такой индекс содержит записи только для строк, удовлетворяющих предикату. Частичные индексы довольно специфичны, но в ряде ситуаций они могут быть очень полезны.
Частичные индексы могут быть полезны, во-первых, тем, что позволяют избежать индексирования распространённых значений. Так как при поиске распространённого значения (такого, которое содержится в значительном проценте всех строк) индекс всё равно не будет использоваться, хранить эти строки в индексе нет смысла. Исключив их из индекса, можно уменьшить его размер, а значит и ускорить запросы, использующие этот индекс. Это также может ускорить операции изменения данных в таблице, так как индекс будет обновляться не всегда. Возможное применение этой идеи проиллюстрировано в Примере 11.1.
Пример 11.1. Настройка частичного индекса, исключающего распространённые значения
Предположим, что вы храните в базе данных журнал обращений к корпоративному сайту. Большая часть обращений будет происходить из диапазона IP-адресов вашей компании, а остальные могут быть откуда угодно (например, к нему могут подключаться внешние сотрудники с динамическими IP). Если при поиске по IP вас обычно интересуют внешние подключения, IP-диапазон внутренней сети компании можно не включать в индекс.
Пусть у вас есть такая таблица:
Создать частичный индекс для нашего примера можно так:
Так будет выглядеть типичный запрос, использующий этот индекс:
В нём фигурирует IP-адрес, попадающий в частичный индекс. Следующий запрос не может использовать частичный индекс, так как в нём IP-адрес не попадает в диапазон индекса:
Заметьте, что при таком определении частичного индекса необходимо, чтобы распространённые значения были известны заранее, так что такие индексы лучше использовать, когда распределение данных не меняется. Хотя такие индексы можно пересоздавать время от времени, подстраиваясь под новое распределение, это значительно усложняет поддержку.
Во-вторых, частичные индексы могут быть полезны тем, что позволяют исключить из индекса значения, которые обычно не представляют интереса; это проиллюстрировано в Примере 11.2. При этом вы получаете те же преимущества, что и в предыдущем случае, но система не сможет извлечь « неинтересные » значения по этому индексу, даже если сканирование индекса может быть эффективным. Очевидно, настройка частичных индексов в таких случаях требует тщательного анализа и тестирования.
Пример 11.2. Настройка частичного индекса, исключающего неинтересные значения
Если у вас есть таблица, в которой хранятся и оплаченные, и неоплаченные счета, и при этом неоплаченные счета составляют только небольшую часть всей таблицы, но представляют наибольший интерес, производительность запросов можно увеличить, создав индекс только по неоплаченным счетам. Сделать это можно следующей командой:
Этот индекс будет применяться, например в таком запросе:
Однако он также может применяться в запросах, где order_nr вообще не используется, например:
Заметьте, что в таком запросе этот индекс не будет использоваться:
Счёт с номером 3501 может оказаться, как в числе неоплаченных, так и оплаченных.
Третье возможное применение частичных индексов вообще не связано с использованием индекса в запросах. Идея заключается в том, чтобы создать уникальный индекс по подмножеству строк таблицы, как в Примере 11.3. Это обеспечит уникальность среди строк, удовлетворяющих условию предиката, но никак не будет ограничивать остальные.
Пример 11.3. Настройка частичного уникального индекса
Предположим, что у нас есть таблица с результатами теста. Мы хотим, чтобы для каждого сочетания предмета и целевой темы была только одна запись об успешном результате, а неудачных попыток могло быть много. Вот как можно этого добиться:
Наконец, с помощью частичных индексов можно также переопределять выбираемый системой план запроса. Возможно, что для данных с неудачным распределением система решит использовать индекс, тогда как на самом деле это неэффективно. В этом случае индекс можно настроить так, чтобы в подобных запросах он не работал. Обычно PostgreSQL принимает разумные решения относительно применения индексов (т. е. старается не использовать их для получения распространённых значений, так что частичный индекс в вышеприведённом примере помог только уменьшить размер индекса, для отказа от использования индекса он не требовался), поэтому крайне неэффективный план может быть поводом для сообщения об ошибке.
Пример 11.4. Не применяйте частичные индексы в качестве замены секционированию
У вас может возникнуть желание создать множество неперекрывающихся частичных индексов, например:
Но так делать не следует! Почти наверняка вам лучше использовать один не частичный индекс, объявленный так:
(Поставьте первым столбец категорий, по причинам описанным в Разделе 11.3.) При поиске в большем индексе может потребоваться опуститься на несколько уровней ниже, чем при поиске в меньшем частичном, но это почти гарантированно будет дешевле, чем выбрать при планировании из всех частичных индексов подходящий. Сложность с выбором индекса объясняется тем, что система не знает, как взаимосвязаны частичные индексы, и ей придётся проверять каждый из них, чтобы понять, соответствует ли он текущему запросу.
Если ваша таблица настолько велика, что создавать один индекс кажется действительно плохой идей, рассмотрите возможность использования секционирования (см. Раздел 5.11). Когда применяется этот механизм, система понимает, что таблицы и индексы не перекрываются, и может выполнять запросы гораздо эффективнее.
Узнать о частичных индексах больше можно в следующих источниках: [ston89b], [olson93] и [seshadri95].
Что такое частичные индексы
Частичный индекс — это индекс, который строится по подмножеству строк таблицы, определяемому условным выражением (оно называется предикатом частичного индекса). Такой индекс содержит записи только для строк, удовлетворяющих предикату. Частичные индексы довольно специфичны, но в ряде ситуаций они могут быть очень полезны.
Частичные индексы могут быть полезны, во-первых, тем, что позволяют избежать индексирования распространённых значений. Так как при поиске распространённого значения (такого, которое содержится в значительном проценте всех строк) индекс всё равно не будет использоваться, хранить эти строки в индексе нет смысла. Исключив их из индекса, можно уменьшить его размер, а значит и ускорить запросы, использующие этот индекс. Это также может ускорить операции изменения данных в таблице, так как индекс будет обновляться не всегда. Возможное применение этой идеи проиллюстрировано в Примере 11.1.
Пример 11.1. Настройка частичного индекса, исключающего распространённые значения
Предположим, что вы храните в базе данных журнал обращений к корпоративному сайту. Большая часть обращений будет происходить из диапазона IP-адресов вашей компании, а остальные могут быть откуда угодно (например, к нему могут подключаться внешние сотрудники с динамическими IP). Если при поиске по IP вас обычно интересуют внешние подключения, IP-диапазон внутренней сети компании можно не включать в индекс.
Пусть у вас есть такая таблица:
Создать частичный индекс для нашего примера можно так:
Так будет выглядеть типичный запрос, использующий этот индекс:
В нём фигурирует IP-адрес, попадающий в частичный индекс. Следующий запрос не может использовать частичный индекс, так как в нём IP-адрес не попадает в диапазон индекса:
Заметьте, что при таком определении частичного индекса необходимо, чтобы распространённые значения были известны заранее, так что такие индексы лучше использовать, когда распределение данных не меняется. Хотя такие индексы можно пересоздавать время от времени, подстраиваясь под новое распределение, это значительно усложняет поддержку.
Во-вторых, частичные индексы могут быть полезны тем, что позволяют исключить из индекса значения, которые обычно не представляют интереса; это проиллюстрировано в Примере 11.2. При этом вы получаете те же преимущества, что и в предыдущем случае, но система не сможет извлечь « неинтересные » значения по этому индексу, даже если сканирование индекса может быть эффективным. Очевидно, настройка частичных индексов в таких случаях требует тщательного анализа и тестирования.
Пример 11.2. Настройка частичного индекса, исключающего неинтересные значения
Если у вас есть таблица, в которой хранятся и оплаченные, и неоплаченные счета, и при этом неоплаченные счета составляют только небольшую часть всей таблицы, но представляют наибольший интерес, производительность запросов можно увеличить, создав индекс только по неоплаченным счетам. Сделать это можно следующей командой:
Этот индекс будет применяться, например в таком запросе:
Однако он также может применяться в запросах, где order_nr вообще не используется, например:
Заметьте, что в таком запросе этот индекс не будет использоваться:
Счёт с номером 3501 может оказаться, как в числе неоплаченных, так и оплаченных.
Третье возможное применение частичных индексов вообще не связано с использованием индекса в запросах. Идея заключается в том, чтобы создать уникальный индекс по подмножеству строк таблицы, как в Примере 11.3. Это обеспечит уникальность среди строк, удовлетворяющих условию предиката, но никак не будет ограничивать остальные.
Пример 11.3. Настройка частичного уникального индекса
Предположим, что у нас есть таблица с результатами теста. Мы хотим, чтобы для каждого сочетания предмета и целевой темы была только одна запись об успешном результате, а неудачных попыток могло быть много. Вот как можно этого добиться:
Наконец, с помощью частичных индексов можно также переопределять выбираемый системой план запроса. Возможно, что для данных с неудачным распределением система решит использовать индекс, тогда как на самом деле это неэффективно. В этом случае индекс можно настроить так, чтобы в подобных запросах он не работал. Обычно PostgreSQL принимает разумные решения относительно применения индексов (т. е. старается не использовать их для получения распространённых значений, так что частичный индекс в вышеприведённом примере помог только уменьшить размер индекса, для отказа от использования индекса он не требовался), поэтому крайне неэффективный план может быть поводом для сообщения об ошибке.
Пример 11.4. Не применяйте частичные индексы в качестве замены секционированию
У вас может возникнуть желание создать множество неперекрывающихся частичных индексов, например:
Но так делать не следует! Почти наверняка вам лучше использовать один не частичный индекс, объявленный так:
(Поставьте первым столбец категорий, по причинам описанным в Разделе 11.3.) При поиске в большем индексе может потребоваться опуститься на несколько уровней ниже, чем при поиске в меньшем частичном, но это почти гарантированно будет дешевле, чем выбрать при планировании из всех частичных индексов подходящий. Сложность с выбором индекса объясняется тем, что система не знает, как взаимосвязаны частичные индексы, и ей придётся проверять каждый из них, чтобы понять, соответствует ли он текущему запросу.
Если ваша таблица настолько велика, что создавать один индекс кажется действительно плохой идей, рассмотрите возможность использования секционирования (см. Раздел 5.11). Когда применяется этот механизм, система понимает, что таблицы и индексы не перекрываются, и может выполнять запросы гораздо эффективнее.
Узнать о частичных индексах больше можно в следующих источниках: [ston89b], [olson93] и [seshadri95].
Используем все возможности индексов в PostgreSQL
В мире Postgres индексы крайне важны для эффективной навигации по хранилищу базы данных (его называют «куча», heap). Postgres не поддерживает для него кластеризацию, и архитектура MVCC приводит к тому, что у вас накапливается много версий одного и того же кортежа. Поэтому очень важно уметь создавать и сопровождать эффективные индексы для поддержки приложений.
Предлагаю вашему вниманию несколько советов по оптимизации и улучшению использования индексов.
Примечание: показанные ниже запросы работают на не модифицированном образце базы данных pagila.
Использование покрывающих индексов (Covering Indexes)
Помогло, последующее сканирование превратилось в » index scan «. Это означает, что Postgres просканирует индекс » idx_cust1 «, а затем дальше продолжит поиск по куче таблицы, чтобы прочесть значения других колонок (в данном случае, колонку email ), которые нужны запросу.
В PostgreSQL 11 появились покрывающие индексы. Они позволяют включать в сам индекс одну или несколько дополнительных колонок — их значения хранятся в хранилище данных индекса.
» Index Only Scan » говорит нам, что запросу теперь достаточно одного лишь индекса, что помогает избегать всех дисковых операций ввода/вывода для чтения кучи таблицы.
Сегодня покрывающие индексы доступны только для B-деревьев. Однако в этом случае усилия по сопровождению будут выше.
Использование частичных индексов
Частичные индексы индексируют лишь подмножество строк таблицы. Это позволяет экономить размер индексов и быстрее выполнять сканирование.
Допустим, нам нужно получить список адресов электронной почты наших клиентов из Калифорнии. Запрос будет таким:
Что нам дадут обычные индексы:
Поскольку это частый запрос и его нужно оптимизировать, мы можем использовать частичный индекс, который индексирует лишь те строки с адресами, в которых район ‘California’ :
Использование многозначных индексов (Multi-Value Indexes)
Оператор вложенности (containment operator) @> проверяет, является ли правая часть подмножеством левой части.
Который запрашивает полное сканирование кучи со стоимостью 67.
Посмотрим, поможет ли нам обычный индекс B-дерева:
Индекс даже не рассматривался. Индекс B-дерева не догадывается о существовании отдельных элементов в индексируемых значениях.
Нам нужен GIN-индекс.
GIN-индекс поддерживает сопоставление отдельных значений с проиндексированными составными значениями, в результате стоимость плана запроса уменьшится более чем вдвое.
Избавляемся от дублирования индексов
Индексы надмножеств (Superset Indexes)
Может случиться так, что у вас накопится много индексов, один из которых индексирует надмножество колонок, которые индексируют другие индексы. Это может быть как желательно, так и нет — надмножество может привести к сканированию только по индексам, что хорошо, но при этом оно может занимать слишком много места, или запрос, для оптимизации которого предназначалось это надмножество, уже не используется.
Неиспользуемые индексы
Вот запрос на получение текущих счётчиков сканирования всех индексов в схеме ‘public’ :
Пересоздание индексов с меньшим количеством блокировок
Частенько индексы приходится пересоздавать, например, когда они раздуваются в размерах, и пересоздание может ускорить сканирование. Также индексы могут повреждаться. Изменение параметров индекса тоже может потребовать его пересоздания.
Включаем параллельное создание индексов
В PostgreSQL 11 создание индекса B-Tree является конкурентным. Для ускорения процесса создания может использоваться несколько параллельно работающих воркеров. Однако убедитесь, что эти параметры конфигурации заданы правильно:
Значения по умолчанию слишком малы. В идеале, эти числа нужно увеличивать вместе с количеством ядер процессора. Подробнее читайте в документации.
Фоновое создание индексов
Вы можете создать индекс в фоновом режиме, воспользовавшись параметром CONCURRENTLY команды CREATE INDEX :
Эта процедура создания индекса отличается от обычной тем, что она не требует блокирования таблицы, а значит и не блокирует операции записи. С другой стороны, она занимает больше времени и потребляет больше ресурсов.
Postgres предоставляет множество гибких возможностей для создания индексов и путей решения любых частных случаев, а так же предоставляет способы управления базой данных на случай взрывного роста вашего приложения. Надеемся, что эти советы помогут вам сделать запросы быстрыми, а базу готовой масштабироваться.