Postgres не использует индекс, когда отфильтрованное/индексированное значение не существует

В Postgres 16.2 у меня есть таблица Product с дополнительным индексом:

CREATE TABLE Product (id   SERIAL , name: text, companyId: number, PRIMARY KEY (id));
CREATE INDEX product_company_id ON Product (companyId);

В таблице около 12 миллионов строк.

Когда я ищу строку с идентификатором компании, которая существует в таблице/индексе, используется правильный индекс «product_company_id», а время выполнения составляет около 100 мс.

select id, name, companyId from Product where companyId=12 order by id limit 1;

Но когда я ищу строку с несуществующим идентификатором компании, выполнение происходит очень медленно, около 7 секунд. В соответствии с объяснением/анализом индекс компанииId не используется:

Limit  (cost=0.09..208.24 rows=1 width=43) (actual time=6404.660..6404.661 rows=0 loops=1)
  ->  Index Scan Backward using "Product_pkey" on "Product"  (cost=0.09..562625.56 rows=2703 width=43) (actual time=6404.659..6404.659 rows=0 loops=1)
        Filter: (companyid = 667)
        Rows Removed by Filter: 11797182
Planning Time: 0.100 ms
Execution Time: 6404.674 ms

Удаление «лимита» устраняет проблему, и индекс снова используется. Но лимит важен.

Есть ли способ заставить БД использовать индекс, чтобы обнаружить отсутствие соответствующих строк?

Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
1
0
50
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Да, вы можете иметь двумерный индекс вида:

CREATE INDEX product_company_id ON Product ((companyId / 1000000),companyId);

то есть первое измерение индекса генерирует кластеры слотов для 1000000 разделов, и если поэтому вы фильтруете по идентификатору компании, равному 12, он будет иметь 0 в первом индексе и будет искать миллион компаний в этом диапазоне, а не полнота таблицы. Вы, конечно, можете изменить 1000000 на какое-то другое число.

Рабочий пример: https://www.db-fiddle.com/f/ueAq14fNuYbq7Wy9TJXxFL/0

Бег

explain select * from Product where CompanyId = 12

дает:

QUERY PLAN
Bitmap Heap Scan on product (cost=13.15..22.62 rows=6 width=40)
Recheck Cond: (companyid = 12)
-> Bitmap Index Scan on product_company_id (cost=0.00..13.15 rows=6 width=0)
Index Cond: (companyid = 12)

Вы пробовали запустить это с установленным лимитом?

clamp 15.06.2024 18:07

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

jjanes 15.06.2024 21:49

@jjanes: в вопросе говорилось, что добавление ограничения приводит к тому, что план не использует индекс.

clamp 15.06.2024 23:34

@clamp Удаление ограничения не должно также удалять порядок.

jjanes 16.06.2024 19:55

@jjanes Да, и в этом ответе не используются ни ограничения, ни порядок.

clamp 17.06.2024 11:01

@clamp Да, поэтому это не имеет отношения к вопросу. Ответ Лайоса выглядит как мусор, сгенерированный ИИ, хотя, возможно, это какой-то другой мусор.

jjanes 17.06.2024 19:40

@jjanes это было написано от руки.

Lajos Arpad 17.06.2024 20:09

PostgreSQL не может быть уверен, что ни одна строка не будет соответствовать данному идентификатору компании. На самом деле он думает, что 2703 будет. Вы можете попытаться улучшить эту оценку, убедившись, что для таблицы был запущен ANALYZE, или увеличив целевое значение статистики для этого столбца, а затем запустив ANALYZE.

Или вы можете просто создать более надежный индекс, например, на (companyId, id). Этот индекс по-прежнему будет выглядеть привлекательно и поэтому, вероятно, будет использоваться, даже если оценка строки неверна.

Другие вопросы по теме