Как сделать индекс в postgresql

Добавил пользователь Alex
Обновлено: 04.10.2024

Для полнотекстового поиска в PostgreSQL предусмотрены специальные типы данных: tsvector и tsquery.

Тип tsvector — представляет документ в виде оптимизированном для текстового поиска. По сути это нормализованная строка по которой будет производиться поиск.

Под нормализацией следует понимать такие процедуры как выкидывание стоп-слов, вырезание окончаний слов, приведение к нижнему регистру и т.д.

Для нормализации строки используется процедура to_tsvector.

Например так будет выглядеть нормализованная строка:

Тип tsquery – используется для представления запросов поиска. Для преобразования поисковых запросов используется процедура plainto_tsquery:

2. Поиск в таблице

Поиск должен найти документы tsvector соответствующие запросу tsquery.
Для сопоставления используется оператор @@.

Для таблицы news следующего формата:

поисковый запрос по колонкам title и content будет таким:

Оператор || используется для конкатенации tsvector.

3. Ранжирование результатов поиска

Оценка релевантности документа относительно запроса происходит с учетом весов элементов tsvector.

Для установки весов предусмотрена функция setweight, значения задаются буквами A B C D.

Например установим важность слов в заголовке больше чем в теле документа. Тогда наш запрос будет выглядеть так:

Функция ts_rank ранжирует результаты по частоте найденных лексем.

4. Создание индексов

Для полнотекстового поиска более предпочтительным является индекс GIN (Generalized Inverted Index).

Он содержит записи всех ключей (лексем) со списком мест их вхождений.

Индекс GIN для поиска ключей использует бинарное дерево, поэтому он слабо зависит от количества ключей и хорошо масштабируется.

Но будьте осторожны и не используйте индекс GIN для документов которые постоянно изменяются. Так как изменения приводят к большому количеству обновлений индекса.

Полнотекстовый поиск в PostgreSQL достаточно гибкий инструмент. Он имеет много конфигураций, что позволяет настроить поиск “под себя”.

Перед тем как использовать технологию убедитесь, что она вам подходит и вы используете ее по назначению.

Мониторинг индексов в PostgreSQL очень важен, потому как индексы могут разрастаться или не использоваться.

Поиск неиспользуемых индексов

Иногда случается, что в базе данных некоторые индексы не нужны. То есть при создании базы сделали лишние или пересекающиеся индексы, а они на самом деле не используются. Лишние индексы занимают место, а также добавляют накладные расходы. Как только вы меняете таблицу, вам нужно обновить все индексы в ней. Поэтому мониторинг индексов необходим в PostgreSQL.

В представлении pg_stat_all_indexes в поле idx.scan вы можете увидеть количество обращений к индексу. Если там ноль, значит индекс не используется и возможно его следует удалить. Но следует точно понимать что вы делаете, и перепроверить все перед удалением таких индексов.

Посмотреть представление pg_stat_all_indexes для какой-нибудь таблицы можно так:

Перестроение индексов

Существует команда REINDEX которая занимается перестроением индексов. Можно перестроить как отдельный индекс, так и все индексы в таблице, в базе данных или во всей системе:

  • REINDEX INDEX индекс;
  • REINDEX TABLE таблица;
  • REINDEX DATABASE база;
  • REINDEX SYSTEM;

Команда VACUUM FULL перестраивает и таблицы и индексы, а команда REINDEX только индексы. REINDEX также устанавливает эксклюзивную блокировку на таблицу.

Пересоздание индексов

Можно пересоздать индексы без эксклюзивной блокировки с помощью ключевого слова CONCURRENTLY:

С помощью такого перестроения индексов, новый индекс будет строится дольше, но это не приведёт к блокировке таблицы. Также такое создание нового индекса может завершиться с ошибкой, тогда этот новый индекс нужно удалить и попробовать сделать новый индекс ещё раз.

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

Подскажите, пожалуйста, как проверить, какие индексы создаются для какой-то таблицы в postgresql?

3 ответа

Представление pg_indexes предоставляет доступ к полезной информации о каждом индексе. в базе данных, например.

Вы можете использовать этот запрос:

select tablename,indexname,tablespace,indexdef from pg_indexes where tablename = 'your_table_name' ;

Где has tablename - это поле в pg_indexes , вы можете получить точные индексы, сопоставив определенную пользователем таблицу в ' your_table_name 'в предложении WHERE . Это даст вам желаемые детали.

1) есть 2 поля - varchar 'text' и int 'value'. По ним создан индекс:

CREATE INDEX idx_index_value_and_text ON index_test ( value, text )

select * from index_test r where r.text='some value'

Index Scan using idx_index_value_and_text on index_test

Вопрос: как он может искать по индексу, если первая колонка не указана? В терминах B-Tree - что представляет собой составной индекс: единое дерево, значения нод которого есть concatenation строк значений входящих в него полей?

2) Данный поиск в зависимости от распределения значений колонок имеет разные типы: Bitmap Heap Scan и Bitmap Index Scan, либо Index Scan если распределение достаточное ровное. Что означают Bitmap-типы, чем отличается Bitmap Index Scan от Index Scan? Кроме того, есть ли в Postgres Index Range Scan?

> Вопрос: как он может искать по индексу, если первая колонка не указана? Так он и не ищет - скан - это просмотр страниц подряд, а не поиск.

1 ответ 1

Составной индекс, в отличие от "одинарного" индекса в качестве индексируемого значения использует не одно значение, а несколько значений то есть если есть 2 поля A и B то составной индекс по ним будет выглядеть примерно так:

Грубо говоря это сортировка по двум полям, а не одному полю. То есть это не индексирование конкатенированных значений A+B.

На уровне BTree это выглядит как будто листочки BTree отвечающие за поле A, ссылаются на вложенное дерево BTree отвечающее за поле B.

Сканирование индекса в вашем случае фактически означает, что поскольку первое поле у вас не задано, то сервер сканирует все листочки дерева A, чтобы найти подходящее значение поля B.

Составной индекс полезен когда информация может быть кластеризована: скажем по этому принципу устроены словари/энциклопедии. Вам нужна статья на букву "Д" - вы находите по оглавлению раздел на букву "Д" и далее ищете нужную статью. А вот если нужна статья заканчивающаяся на "К" - то кластеризация уже не поможет - придется рыться во всей книге (сканировать индекс).

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

что несколько странно, так как индексы на field1 и field2 у нас в наличии, а id так вообще первичный ключ, но даже если без сортировки, что все равно не все проходит по индексу:

легче, но не намного, так как фильтр по field2 в данном примере накладывается всего на 28 тысяч записей, да сортировка таки нужна 🙁

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

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

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

Что интересно, при этом планировщик не говорит, что сортировка производится тоже по индексу, но при этом сортирует именно по нему.

Теперь можно поговорить уже и про теорию, что же это за составные индексы такие…

Как это работает

Составной индекс можно представить в виде такой схемы:


Как видно из схемы, что для каждого из элементов на следующем уровне формируется частичный индекс в его (элемента) пределах. Отсюда становится понятным, что порядок полей в составном индексе имеет очень большое значение.

Так, в предыдущем примере (SQL код (5)) мы указали последовательность полей как (field1, field2, id), можно сравнить, что будет происходить, если мы поменяем порядок полей как (id, field1, field2):

Как видно, скорость упала в разы. Рассмотрим как производится выборка по индексу в обоих случаях:



Теперь понятно откуда такие задержки. По сути получается все равно фильтрация, но по индексу, так как данные для этого присутствуют. Так же видно, что уникальное поле в композитном индексе имеет смысл ставить только в конце, так как на следующих уровнях индекса будет находится всего по одному элементу.

Немного усложним задачу и уберем LIMIT:

Как видно, при больших объемах, разница незначительная, так как все начинает упираться в чтение строк с диска.

Как это использовать

Итак, как работают составные индексы, понятно. Остается вопрос, как правильно их использовать.

Первое на что хотел бы обратить внимание, опять же порядок. Понятно, что в первую очередь в составном индексе нужно указывать поля из условия WHERE, а во второй — из сортировки ORDER BY. Причем, если порядок полей из сортировки понятен — ровно такой, какой указываем в сортировке, то порядок полей из условия WHERE не совсем однозначен.

По сути, для получения данных порядок полей в составном индексе не имеет значения, но для планировщика запросов очень даже имеет, так например:

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

Все очень просто. Вариантов значений field1 — 1269, а field2 всего 23, это называется селективность:

В итоге планировщик принимает относительно правильное решение, отфильтровывать сначала 1/1269 часть данных, чем 1/23.

Отсюда правило: порядок полей из условия WHERE зависит от того, сколько вариантов значений может принимать поле, чем больше — тем первее.

Как это можно оптимизировать

Понятно, что чем больше полей мы укажем в составном индексе, тем его размер будет больше, поэтому увлекаться ими сильно не стоит.

Что бы уменьшить объем можно сделать частичный составной индекс. Так например:

Соответственно можно создать составной индекс:

Что значительно облегчит индекс. Причем поле status вообще убирается из индекса, так как фильтрация по этому полю будет производится на уровне условия индекса, правда, если нам понадобится несколько статусов, то это поле все же придется добавить в индекс.

Итоги

В итоге получились следующие заключения для составных индексов: