В 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
Удаление «лимита» устраняет проблему, и индекс снова используется. Но лимит важен.
Есть ли способ заставить БД использовать индекс, чтобы обнаружить отсутствие соответствующих строк?
Да, вы можете иметь двумерный индекс вида:
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)
Какую возможную полезность может иметь эта рекомендация? То, что индекс выбран для использования в пустой, непроанализированной таблице с запросом, отличным от рассматриваемого, никому не приносит никакой пользы.
@jjanes: в вопросе говорилось, что добавление ограничения приводит к тому, что план не использует индекс.
@clamp Удаление ограничения не должно также удалять порядок.
@jjanes Да, и в этом ответе не используются ни ограничения, ни порядок.
@clamp Да, поэтому это не имеет отношения к вопросу. Ответ Лайоса выглядит как мусор, сгенерированный ИИ, хотя, возможно, это какой-то другой мусор.
@jjanes это было написано от руки.
PostgreSQL не может быть уверен, что ни одна строка не будет соответствовать данному идентификатору компании. На самом деле он думает, что 2703 будет. Вы можете попытаться улучшить эту оценку, убедившись, что для таблицы был запущен ANALYZE, или увеличив целевое значение статистики для этого столбца, а затем запустив ANALYZE.
Или вы можете просто создать более надежный индекс, например, на (companyId, id)
. Этот индекс по-прежнему будет выглядеть привлекательно и поэтому, вероятно, будет использоваться, даже если оценка строки неверна.
Вы пробовали запустить это с установленным лимитом?