Postgresql почему не использует индекс
Хотя индексы в PostgreSQL не требуют какого-либо обслуживания или настройки, это не избавляет от необходимости проверять, как и какие индексы используются на самом деле в реальных условиях. Узнать, как отдельный запрос использует индексы, можно с помощью команды EXPLAIN ; её применение для этих целей описывается в Разделе 14.1. Также возможно собрать общую статистику об использовании индексов на работающем сервере, как описано в Разделе 28.2.
Вывести универсальную формулу, определяющую, какие индексы нужно создавать, довольно сложно, если вообще возможно. В предыдущих разделах рассматривались некоторые типовые ситуации, иллюстрирующие подходы к этому вопросу. Часто найти ответ на него помогают эксперименты. Ниже приведены ещё несколько советов:
Всегда начинайте исследование с ANALYZE . Эта команда собирает статистические данные о распределении значений в таблице, которые необходимы для оценивания числа строк, возвращаемых запросов. А это число, в свою очередь, нужно планировщику, чтобы оценить реальные затраты для всевозможных планов выполнения запроса. Не имея реальной статистики, планировщик будет вынужден принять некоторые значения по умолчанию, которые почти наверняка не будут соответствовать действительности. Поэтому понять, как индекс используется приложением без предварительного запуска ANALYZE , практически невозможно. Подробнее это рассматривается в Подразделе 24.1.3 и Подразделе 24.1.6.
Используйте в экспериментах реальные данные. Анализируя работу системы с тестовыми данными, вы поймёте, какие индексы нужны для тестовых данных, но не более того.
Особенно сильно искажают картину очень маленькие наборы тестовых данных. Тогда как для извлечения 1000 строк из 100000 может быть применён индекс, для выбора 1 из 100 он вряд ли потребуется, так как 100 строк скорее всего уместятся в одну страницу данных на диске и никакой другой план не будет лучше обычного сканирования 1 страницы.
Тем не менее, пока приложение не эксплуатируется, создавать какие-то тестовые данные всё равно нужно, и это нужно делать обдуманно. Если вы наполняете базу данных очень близкими, или наоборот, случайными значениями, либо добавляете строки в отсортированном порядке, вы получите совсем не ту статистику распределения, что дадут реальные данные.
Когда индексы не используются, ради тестирования может быть полезно подключить их принудительно. Для этого можно воспользоваться параметрами выполнения, позволяющими выключать различные типы планов (см. Подраздел 19.7.1). Например, выключив наиболее простые планы: последовательное сканирование ( enable_seqscan ) и соединения с вложенными циклами ( enable_nestloop ), вы сможете заставить систему выбрать другой план. Если же система продолжает выполнять сканирование или соединение с вложенными циклами, вероятно, у неё есть более серьёзная причина не использовать индекс; например, индекс может не соответствовать условию запроса. (Какие индексы работают в запросах разных типов, обсуждалось в предыдущих разделах.)
Если система начинает использовать индекс только под принуждением, тому может быть две причины: либо система права и применять индекс в самом деле неэффективно, либо оценка стоимости применения индекса не соответствует действительности. В этом случае вам следует замерить время выполнения запроса с индексами и без них. В анализе этой ситуации может быть полезна команда EXPLAIN ANALYZE .
Если выясняется, что оценка стоимости неверна, это может иметь тоже два объяснения. Общая стоимость вычисляется как произведение цены каждого узла плана для одной строки и оценки избирательности узла плана. Цены узлов при необходимости можно изменить параметрами выполнения (описанными в Подразделе 19.7.2). С другой стороны, оценка избирательности может быть неточной из-за некачественной статистики. Улучшить её можно, настроив параметры сбора статистики (см. ALTER TABLE ).
| Пред. | Наверх | След. |
| 11.11. Сканирование только индекса | Начало | Глава 12. Полнотекстовый поиск |
gray-hemp: PostgreSQL, Emacs, Linux, etc.
«Почему мой индекс не используется?» — очень давний и, возможно, наиболее часто задаваемый даже опытными пользователями вопрос.
В этой статье мы кратко опишем основные причины и попробуем упорядочить их по статистической значимости.
Если кто не в курсе, то узнать о том, что ваш индекс используется или не используется можно с помощью инструкций EXPLAIN, EXPLAIN ANALYZE, или, например, продвинутого инструмента графического объяснения планов в PgAdmin.
Ок, допустим запрос не использует индекс. Почему и что можно с этим сделать?
Проблема: Устаревшая статистика.
Сейчас это менее вероятно в связи с тем, что по умолчанию auto-vacuum включён. Но если в таблице только что было обновлено/добавлено/удалено много записей, или был добавлен новый индекс, то есть вероятность, что во время выполнения запроса новая статистика ещё не соберётся.
Решение: vacuum analyze verbose ;
Я добавил verbose для того, что бы видеть что делает сборка мусора/статистики, и, если вы настолько же нетерпеливы как и я и ваша таблица довольно большая, то здорово видеть что что-то в самом деле происходит. Так же можно выполнить vacuum analyze verbose без указания таблицы, если необходимо провести профилактику всей БД.
Проблема: Планировщик решил, что последовательное сканирование быстрее чем индексное.
Это может случиться если а) ваша таблица относительно мала, или поле, по которому вы индексируете, содержит много дубликатов.
Решение: В случае использования, например boolean, когда 50% данных содержат одно значение, а вторая половина другое, индексирование будет не очень полезным. Однако, это неплохая причина для использования частичных индексов, например для индексирования только активных данных.
Проблема: Вы создали индекс, который не совместим с тем, как вы фильтруете данные. Есть ряд таких случаев.
1. LIKE ‘%me’ никогда не будет использовать индексы, но можно использовать LIKE ‘me%’.
2. Ловушка с upper/lower — если определить индекс как:
CREATE INDEX idx_faults_name ON faults USING btree(fault_name);
и выполнить такой запрос
SELECT * FROM faults where UPPER(fault_name) LIKE 'CAR%'
то индекс не будет использоваться. Тут необходимо создать индекс следующим образом:
CREATE INDEX idx_faults_name ON faults USING btree(upper(fault_name));
3. Этого не избежал даже я, всегда полезно почитать о проблемах других людей в новостных группах, т.к. вы узнаёте о вещах, о которых даже не подозревали. Если ваш сервер инициализирован с C-локалью, выше сказанное опять же не будет работать. Подобный вопрос недавно было в группе pgsql-novice, где Tom Lane дал подробный ответ, и это, по моему, затрагивает многих. Ситуацию возможно исправит:
CREATE INDEX idx_faults_uname_varchar_pattern ON faults USING btree(upper(fault_name) varchar_pattern_ops);
Однако, даже в этом случае может потребоваться учитывать описанное ниже:
Не совсем понятно является ли это проблемой кодировки БД, самих данных или разницы версий 8.3 b 8.2. Похоже, что в 8.3 в конкретных случаях с данными в кодировке UTF-8 требуется точное сравнение, однако в подобных ситуациях с 8.2 в SQL-ASCII достаточно varchar_pattern_ops как для точного сравнения так и для LIKE.
CREATE INDEX idx_faults_uname ON faults USING btree(upper(fault_name));
SELECT fault_name from faults
WHERE upper(fault_name) IN('CASCADIA ABDUCTION', 'CABIN FEVER');
4. Ошибки новичков, когда делается что-нибудь типа создания индекса по дате и сравнение текста с этой датой приведённой к тексту.
Проблема: Не все индексы могу быть использованы.
Не смотря на то, что с версии 8.1+ поддерживаются Bitmap Index Scan, позволяющие множеству индексов быть использованными в запросе путём создания в памяти bitmap индексов, если индексов много, не ждите, что будут использоваться все ожидаемые. Иногда сканирование таблицы бывает эффективнее.
Проблема: Планировщик не идеален.
Решение: Рыдать и молиться о более светлых временах. На самом деле я был очень потрясён возможностями планировщика Postgresql в сравнении с другими СУБД. Некоторые говорят, если бы только тут были «хинты», я бы сделал это работающим быстрее. Я же думаю, что хинты это плохая идея и лучшим решением было бы сделать планировщик лучше. Проблема хинтов в том, что они отодвигают на второй план одну хорошую вещь, идею того, что СУБД знает состояние данных лучше чем человек и постоянно обновляет это знание. Хинты могут быстро стать не актуальными, в то время когда хороший планировщик будет постоянно менять стратегию по мере изменений в БД, и это то что делает программирование баз данных уникальным.
SQL-Ex blog
Типы индексов в PostgreSQL: изучаем PostgreSQL вместе с Grant Fritchey
Добавил Sergey Moiseenko on Среда, 24 мая. 2023
Как и любая другая реляционная система управления базами данных (РСУБД), PostgreSQL использует индексы как механизм улучшения доступа к данным. PostgreSQL имеет большое число различных типов индексов, поддерживающих различное поведение и различные типы данных. Помимо этого, подобно другим РСУБД, эти индексы характеризуются различными свойствами и поведением.
В этой статье я собираюсь пройтись по индексам различных типов и поведению некоторых индексов. Мы узнаем, что такое индексы, как они работают и как наилучшим образом применять их в ваших базах данных. Я надеюсь, что вы разовьете понимание того, какие индексы будут работать лучше в той или иной ситуации.
Однако я не буду обсуждать индексы подробно. Более того, я не собираюсь глубоко вникать в то, как индексы влияют на производительность. Я еще не настолько хорошо продвинулся в настройке производительности с PostgreSQL. Эта статья довольно далека от этого. Когда вы узнаете, какой индекс и где использовать, вы сможете углубиться, чтобы лучше понять этот индекс и как лучше всего оценить его влияние на производительность.
Важно помнить, что как типы ваших данных, так и фактические запросы, определяют те индексы, которые лучше всего подходят для ваших баз данных. Это особенно справедливо для PostgreSQL, т.к. некоторые типы индексов сильно ориентированы на конкретные шаблоны запросов.
- B-Tree
- Hash
- GIST
- SP-GIST
- GIN
- RIN
Хотя, прежде чем приступить, я сохраню все скрипты, которые я создал для этой статьи в публичном доступе на GitHub. Начать следует с CreateDatabase.sql. Вы можете использовать скрипт в SampleData.sql для загрузки некоторых данных.
B-Tree
Название индекса B-Tree в PostgreSQL является сокращением для многостороннего сбалансированного древовидного индекса. В основном он подобен индексу в SQL Server, когда значения индекса сохраняются на страницах в виде двусвязного списка. Страницы составляют дерево, у которого страницы на листовом уровне хранят данные ключевого столбца. Вы определяете ключевой столбец, или столбцы, в определении индекса:
CREATE INDEX radios_radioname ON radio.radios(radio_name);
Поведение по умолчанию позволяет игнорировать некоторые элементы синтаксиса, который вам стоит начать использовать сейчас, так как все остальные индексы, которые вы создаете, не будут работать таким образом:
CREATE INDEX radios_radioname ON radio.radios USING BTREE(radio_name);
Предложение USING BTREE сообщает PostgreSQL какой вид индекса вы хотите построить. Вы увидите другие команды для других типов индексов далее в статье.
Это, в частности, связано с тем, что имена в пределах заданной базы данных PostgreSQL являются уникальными. Это несколько необычно для старого пользователя SQL Server, который обычно использует дубликаты имен индексов, поскольку они привязаны к индексируемой таблице.
Вы можете сделать составной ключ, просто добавив еще столбцы:
CREATE INDEX radios_radioname_radioid ON radios.radios
USING BTREE(radio_name, radio_id);
Индексы B-Tree являются единственным типом индексов в PostgreSQL, который поддерживает определение UNIQUE (уникальность):
CREATE UNIQUE INDEX radios_radioid_unique ON radio.radios
USING BTREE(radio_id);
Этот индекс гарантирует, что дублирующиеся значения не смогут вводиться в ключевой столбец или столбцы индекса.
Другим уникальным поведением индекса B-Tree является его способность хранить данные в заданном порядке. Это может помочь с получением данных в правильном порядке. Конечно, вообще говоря, это прерогатива предложений ORDER BY, однако есть и другие условия и обстоятельства, в которых это может помочь. Подобно SQL Server, вы можете сделать выбор, в каком порядке упорядочивать значения ключа, по возрастанию или по убыванию. Вы также можете решить, где хранить NULL-значения — в начале или в конце индекса. Например, индекс будет сортировать значения столбца radioname по убыванию, но размещать NULL-значения в начале:
CREATE INDEX radios_radioname ON radio.radios
USING BTREE(radio_name DESC NULLS FIRST);
Одно отличие от SQL Server состоит в том, что существует только один тип индекса B-Tree. Все таблицы в PostgreSQL хранятся в виде кучи. Не существует кластерзованных индексов, определяющих способ хранения данных. Все индексы B-Tree являются вторичным хранилищем, означающим, что столбцы, определенные в индексе, хранятся в самом индексе. Без глубокого погружения в настройку производительности (которую, напомню, я еще не готов преподавать) это может привести к большим проблемам с производительностью, поскольку доступ в куче может быть случайным. Итак, вы можете использовать команду INCLUDE для создания покрывающего индекса — индекса, который удовлетворяет потребностям в столбцах для запроса, избегая при этом лишних чтений в куче:
CREATE INDEX radios_radioid ON radio.radios
USING BTREE(radio_id) INCLUDE(radio_name);
PostgreSQL также поддерживает то, что называется Частичными Индексами, которые в SQL Server называют фильтрованными индексами:
CREATE INDEX radios_radioname ON radio.radios USING BTREE(radio_name)
WHERE radio_name IS NOT NULL;
В данном случае только те значения в столбце radioname, которые не являются NULL, будут добавлены в индекс. Вы можете использовать частичный индекс даже с определением UNIQUE, что открывает некоторые интересные возможности для индексирования.
Наконец, вы можете создавать такие индексы:
CREATE INDEX ON radios (adioed);
Я потому не демонстрировал это в начале, поскольку считаю это плохой практикой. PostgreSQL будет именовать индекс вместо вас, что замечательно. Но вы могли выполнить этот скрипт более одного раза, получив дубликаты индексов. Тут PostgreSQL присвоит вашему индексу имя, и в итоге вы получите что-то вроде этого:
Это один и тот же индекс в пяти экземплярах. Это означает пятикратное увеличение хранилища, пятикратное обслуживание и пятикратные накладные расходы. Не говоря уже о совершенно запутанной базе данных. Я бы не рекомендовал строить ваши индексы в такой манере.
Еще пример, когда бы вы делали что-то подобное на одном сервере:
CREATE INDEX ON radios (radioid);
CREATE INDEX ON radios (radio_name);
А затем так на другом:
CREATE INDEX ON radios (radio_name);
CREATE INDEX ON radios (radioid);
В итоге вы получите разные индексы с совпадающими именами, что вызовет головную боль при тестировании и разработке. Поэтому я начал именовать свои индексы сам. Избегайте головной боли, следуя простой и понятной практике написания кода.
Имеется еще много деталей в поведении индексов B-Tree в PostgreSQL, но здесь обсуждаются основы.
Хеш-индексы
PostgreSQL поддерживает хэш-индексы на данных любых типов. Хэш является просто результатом вычислений, выполняемых над данными в столбце и которые всегда дают в результате одинаковое значение для одинакового входа. Хэш хранится как 4-байтовое значение вне зависимости от размера индексируемого столбца. Это делает хэш хорошим выбором для больших объемов данных. Подумайте о длинных строках, например, веб-адресах. Создание хэш-индекса почти такое же, как и создание B-Tree:
CREATE INDEX bands_band_name ON radio.bands USING HASH(band_name);
Однако существует значительно больше ограничений в использовании хэш-индексов по сравнению с B-Tree. Во-первых, хэш-индекс может быть создан только на единственном столбце. Конечно, на любых данных вообще, но никаких составных ключей или INCLUDE для покрытия хэш-индекса.
Самая большая проблема, связанная с хэш-индексами, это коллизии, когда два не совсем одинаковых значения получают одно и то же хэш-значение. Это происходит довольно редко, но может случиться. Именно поэтому настоятельно рекомендуется, чтобы хэш-индексы строились на уникальных, или почти уникальных, данных. Сам по себе хэш-индекс не поддерживает оператор UNIQUE, но в значительной степени он функционирует так, как если бы имел дело с уникальными значениями. В зависимости от размера данных и самих данных, вы можете никогда не столкнуться с коллизии при хэшировании. Но когда это происходит, то может привести к серьезным проблемам с производительностью.
Значения хэш-индекса сохраняются в так называемых хэш-ведрах. Ведро имеет метаданные, которые связаны с наибольшим и наименьшим значениями, хранящимся в ведре. Это помогает сделать быстрым поиск хэш-значения. Когда имеется несколько копий хэш-значения, выполняется дополнительный поиск для идентификации соответствующего значения, что замедляет поиск. Это основная причина, по которой предлагается выполнять хэш-индексацию уникальных или почти уникальных данных. Указатель на таблицу кучи хранится вместе с самим хэш-значением.
Хэш-индексы могут быть частичными, поэтому для приведения их к уникальности вы можете добавить предложение WHERE при создании индекса:
CREATE INDEX logs_log_date ON logging.logs USING HASH(log_date)
WHERE log_date > '1/1/2020';
Здесь предполагается, что значения, превосходящие ‘1/1/2020′, будут более уникальными, что делает индекс более эффективным.
Другое отличие от B-Tree состоит в том, что хэш-индекс не поддерживает сканирование только по индексу. Однако это хорошо, поскольку вы не захотели бы видеть сканирование хэша, особенно при возможном наличии коллизий, из-за возникающих при этом накладных расходах. Кроме того, вывод из хэш-индекса не сортирован, это означает, что данные возвращаются в том порядке, в котором они извлекаются, и предложения ORDER BY отсутствуют. Невозможность сканирования индекса является главной причиной того, что поддерживается только оператор эквивалентности (=).
Если вы выполните поиск в интернет по хэш-индексам в PostgreSQL, то можете найти информацию о том, что их не следует использовать, поскольку они не записываются в журнал WAL (Write Ahead Log). Однако эта информация уже устарела после выхода версии PostgreSQL 9. Это больше не актуально и, если этот тип индекса может помочь, смело используйте его.
GiST
Индекс обобщенного дерева поиска (GiST) фактически не является индексом как таковым. Он является методом доступа, который позволяет вам определить различные виды индексов для поддержки различных типов данных и операций поиска для этих типов данных. Индексы GiST используются для типов данных, которые не имеют дела с диапазонами или равенством, подобных индексам B-Tree или хэш-индексам, а связаны с геометрическими данными, документами или даже изображениями. Кроме того, индексы GiST являются расширяемыми. Фактически документация по самому индексу GiST гораздо больше ориентирована на расширяемость индексов в рамках методов доступа GiST. Документация о поведении GiST фактически крутится вокруг различных типов данных, которые им поддерживаются, что несколько затрудняет понимание, как фактически работают индексы GiST. Советую тем, кто только начал процесс изучения GiST, не беспокоиться об этом, пока вы не уйдете от базовых типов данных как-то VARCHAR, DATE и т.д.
Данные в пределах индекса GiST хранятся в сбалансированном дереве, но интересно что там хранится. Вместо того, чтобы начать со значений ключа или хэш-значений, как в ранее обсуждаемых индексах, хранилище в индексах GiST начинается с предиката. Затем последовательности строк дочерних поддеревьев, которые удовлетворяют этому предикату, сохраняются в ветке предиката.
Уже существует большое число расширений индексов GiST в PostgreSQL. Так, например, если у вас географическое местоположение хранится в виде точек, вы могли бы добавить индекс, который поможет найти информацию на геометрической плоскости:
create index logs_location on logging.logs using gist(log_location);
Синтаксис в целом тот же. Вопрос в том, имеется ли уже функциональность в рамках индексов PostgreSQL для используемого типа данных. Если нет, вам придется обеспечить поведение, а также определить индекс. Это выходит далеко за рамки данной статьи, поэтому мы отложим это на потом.
SP-GIST
Пространственно секционированный GIST (Space Partitioned GiST или SP-GIST) во многом подобен индексу GIST в том, что он строится для поддержки полностью настраиваемого набора данных. В этом случае функция полностью подобна индексу GIST, но хранилище секционируется с тем, чтобы данные, которые поддаются группировке посредством секционирования, больше выиграли от индекса SP-GIST. Типы данных, поддерживаемые из коробки довольно похожи на GIST, включая геометрические данные типа моего примера выше. Так что в этом случае я, вероятно, снова выбрал бы этот пример синтаксиса:
create index logs_location on logging.logs using spgist(log_location);
Наиболее интересным моментом как для GIST, так и SP-GIST, является расширяемость. Я не буду это обсуждать здесь. Я все еще изучаю, как в запросах использовать эти вещи. Тем не менее, еще один интересный аспект заключается в том, что GiST в первую очередь ориентирован на операции, в то время как SP-GIST, безусловно, тоже ориентирован на операции, но именно секционирование данных будет определяющим фактором для того, чтобы это был лучший вариант при выборе индексов.
GIN
Обобщенный инвертированный индекс (GIN) эффективно индексирует массивы. PostgreSQL имеет много различных типов массивов данных. Мы можем долго дискутировать на тему, является ли массив подходящим способом хранения данных, но по факту массивы в PostgreSQL работают достаточно хорошо и весьма функциональны. Плюс все хранят данные в JSON или YAML. Поэтому вы собираетесь увидеть типы массивов данных в действии. Почему бы не найти способ сделать ваш массив более быстрым?
Индекс GIN почти так же эффективен, как индекс B-Tree, за несколькими исключениями. Самое интересное находится на уровне страниц. Вместо хранения значений ключа и идентификатора кортежа (TID), расположение в куче, где хранятся данные, вы получаете с помощью так называемого списка публикации. Это список всех значений, которые удовлетворяют ключу, и указатель на TID. Хотя это может оказаться немного сложнее, т.к. по мере того, как вы получаете больше и больше дублирующихся значений, вы можете получить то, что называется деревом публикации. По сути, это способ расширить страницу до набора страниц, каждая из которых имеет значение TID, своего рода B-Tree внутри B-Tree.
В то время как массивы, особенно JSON, вероятно, являются основным назначением индекса GIN, вы можете также использовать его для полнотекстового поиска. Те же концепции применимы, если вы выполняли поиск в массиве. Помимо прочего, большой текстовый документ можно мыслить просто как большой массив текста.
В моей тестовой базе сейчас нет полнотекстового столбца, поэтому мы просто проиндексируем один из массивов:
create index parksontheair_contacts on logging.parksontheair
using GIN(contacts);
Вы не можете использовать столбцы INCLUDE, поскольку ничего не сохраняется на уровне страниц за исключением PID или списка публикации. Хотя вы можете построить многостолбцовый индекс GIN, что сделает индекс значительно более селективным на уровне ключа, несколько увеличивая его на листовом уровне. На самом деле, одна большая слабость индексов GIN, которая признается всеми, заключается в том, что они работают медленно, когда дело доходит до операторов UPDATE. Имеется несколько обходных путей, которые я еще не готов понять, не говоря уже о том, чтобы объяснить, которые могут помочь смягчить эту проблему. Больше информации по этому вопросу можно найти здесь.
BRIN
Индекс диапазона блоков (Block Range Index) предназначен для хранения упорядоченных значений диапазона данных (представьте себе даты). Поэтому, когда у вас есть естественным путем сгруппированные данные, которые, скорее всего, будут возвращены в виде набора (на ум приходят почтовые индексы), у вас есть индекс, который может тут помочь. Более того, когда вы пытаетесь исключить наборы, скажем, выдать даты, которые меньше определенного значения, опять таки тут может помочь индекс BRIN. Просто помните, что речь идет о диапазонах данных, а не о поиске значений отдельных точек, где лучше подходит индекс B-Tree.
Итак, если вы имеете запросы с поиском по диапазону, BRIN — ваш друг. Я знаю, что их может поддерживать также B-Tree, но для ограниченных типов запросов (думайте опять таки об упорядоченных наборах), BRIN не только превосходит B-Tree, но обычно занимает около 1% места. Вы прочли это правильно, экономия 99% пространства. Приблизительно. Все тесты, которые я видел с большими наборам данных, говорят об этом.
Синтаксис вас не удивит:
create index logs_log_date on logging.logs using BRIN(logs_log_date);
Поскольку журналы, по большей части, следуют по порядку и упорядочены по возрастанию даты, это оказывается полезным индексом.
Мой редактор, Louis Davidson, справедливо заметил, что до некоторой степени индекс BRIN сортируется как кластеризованный индекс B-Tree в SQL Server. Сходства очевидны, порядок данных и, следовательно, где вы начинаете поиск или сканирование, определяется индексом. Ключевым отличием все же служит тот факт, что этот индекс не определяет хранилище данных. Данные все так же находятся в куче.
Заключение
Чтобы увидеть реальные преимущества индексов, вам бы хотелось посмотреть примеры. Однако я пока все еще поверхностно изучил PostgreSQL. Я пока еще не готов сказать вам, что происходит в плане EXPLAIN. Однако простое обсуждение типов данных и поддерживаемых запросов даст вам некоторые указания, как индекс может улучшить поведение в PostgreSQL. Как и в других системах баз данных, вы можете добавить более одного индекса, чтобы повысить производительность таблицы. Просто знайте, что, как и везде, это может привести к дополнительным накладным расходам относительно хранилища и обработки, т.к. данные меняются со временем.
- Об использовании индексов
- Поиск в индексе (Index Seek)
- Введение в B-Tree и хэш-индексы в PostgreSQL
- Понимание CRUD-операций на таблицах с индексами B-Tree, разбиение страниц и фрагментация
Проблема с PostgreSQL. При использовании LIMIT не используется индекс.
Как видите запрос в данном случае использует index facets_value. Но стоит добавить вконец LIMIT 10:
feeds=# explain SELECT DISTINCT facets.product_id FROM facets WHERE facets.value ILIKE '%0731304123538%' ORDER BY facets.product_id offset 0 limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------ Limit (cost=0.56..8557.31 rows=10 width=8) -> Unique (cost=0.56..1913289.72 rows=2236 width=8) -> Index Scan using unique_facet on facets (cost=0.56..1913284.12 rows=2242 width=8) Filter: (value ~~* '%0731304123538%'::text) (4 rows) Time: 1.523 ms feeds=# SELECT DISTINCT facets.product_id FROM facets WHERE facets.value ILIKE '%0731304123538%' ORDER BY facets.product_id offset 0 limit 10; product_id ------------ 1767628 1787052 1787365 2271634 (4 rows) Time: 27863.692 ms Как вы видите index facets_value не используется. В итоге запрос занимает 27 секунд вместо нескольких миллисекунд как в первом случае.
В чём может быть причина.
Проблема возникает только на удалённом сервере debian PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
На моём компьютере gentoo PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 5.4.0-r3 p1.3, pie-0.6.5) 5.4.0, 64-bit индекс используется в любом случае..
Может быть следует изменить какие то настройки??