Почему PostgreSQL не использует * просто * индекс покрытия в этом запросе в зависимости от содержимого его предложения IN()?

Question

У меня есть таблица с индексом покрытия, который должен отвечать на запрос, используя только индекс, без проверки таблицы вообще. Postgres действительно делает это, если в предложении IN() содержится 1 или несколько элементов. Однако, если предложение IN имеет много элементов, кажется, что он выполняет поиск по индексу, а затем идет к таблице и повторно проверяет условия...

Я не могу понять, почему Postgres сделал бы это. Он может либо обслуживать запрос прямо из индекса, либо не может, зачем ему идти к таблице, если ему (в теории) больше нечего добавить?

Стол:

CREATE TABLE phone_numbers
(
  id serial NOT NULL,
  phone_number character varying,
  hashed_phone_number character varying,
  user_id integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  ghost boolean DEFAULT false,
  CONSTRAINT phone_numbers_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX index_phone_numbers_covering_hashed_ghost_and_user
  ON phone_numbers
  USING btree
  (hashed_phone_number COLLATE pg_catalog."default", ghost, user_id);

Запрос, который я запускаю:

SELECT "phone_numbers"."user_id" 
FROM "phone_numbers" 
WHERE "phone_numbers"."hashed_phone_number" IN (*several numbers*) 
  AND "phone_numbers"."ghost" = 'f'

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

И если у меня есть только одно или несколько чисел в предложении IN, это делает:

1 номер:

Сканирование индекса с использованием index_phone_numbers_on_hashed_phone_number для phone_numbers (стоимость =0.41..8.43 строк =1 ширина =4)
Указатель Cond: ((hashed_phone_number)::text = 'bebd43a6eb29b2fda3bcb63dcc7ffaf5433e78660ccd1a495c1180a3eaaf6b6a'::text)
Фильтр: (НЕ призрак)"

3 номера:

Сканирование только по индексу, используя index_phone_numbers_covering_hashed_ghost_and_user для phone_numbers (стоимость =0.42..17.29 строк =1 ширина =4)
Индекс Cond: ((hashed_phone_number = ANY ('{8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,43ddeebdca2ea829d468d5debc84d475c8322cf4bf6edca286c918b04216387e,1578bf773eb6eb8a9b57a130922a28c9c91f1bda67202ef5936b39630ca4cfe4}':: Текст [])) И (...)
Фильтр: (НЕ призрак)"

Однако, когда в предложении IN много цифр, Postgres использует индекс, но затем попадает в таблицу, и я не знаю почему:

Сканирование кучи растровых изображений по номерам телефонов (стоимость =926.59..1255.81 строк = ширина 106 =4)
Перепроверьте Cond: ((hashed_phone_number):: текст = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e,8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,ab3554acc1f287bb2e22ff20bb855e19a4177ef552676689d217dbb2a1a6177b,7ec9f58 (...)
Фильтр: (НЕ призрак)
-> Сканирование индекса растрового изображения на index_phone_numbers_covering_hashed_ghost_and_user (стоимость =0.00..926.56 строк =106 ширина =0)
Индекс Cond: (((hashed_phone_number):: текст = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e, 8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8, ab3554acc1f287bb2e22ff20bb855e19a4177ef552676689d217dbb2a1a6177b, 7е (...)

В настоящее время выполняется этот запрос, который ищет 250 записей в таблице с общим количеством строк 50 тыс., Что примерно в два раза меньше аналогичного запроса в другой таблице, который ищет 250 записей в таблице с 5 миллионами строк, что не имеет много смысла.

Есть идеи, что может произойти, и могу ли я сделать что-нибудь, чтобы улучшить это?

---

ОБНОВЛЕНИЕ: Изменение порядка столбцов в индексе покрытия, чтобы иметь сначала призрак, а затем hashed_phone_number, также не решает это:

Сканирование кучи растровых изображений по номерам телефонов (стоимость =926.59..1255.81 строк = ширина 106 =4)
Перепроверьте Cond: ((hashed_phone_number):: текст = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e,8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,ab3554acc1f287bb2e22ff20bb855e19a4177ef552676689d217dbb2a1a6177b,7ec9f58 (...)
Фильтр: (НЕ призрак)
-> Сканирование индекса растрового изображения на index_phone_numbers_covering_ghost_hashed_and_user (стоимость =0.00..926.56 строк =106 ширина =0)
Индекс Cond: ((призрак = ложь) AND ((hashed_phone_number):: текст = ANY ('{b6459ce58f21d99c462b132cce7adc9ea947fa522a3849321e9fb65893006a5e,8228a8116f1fdb12e243102cb85ecd859ebf7873d9332dce5f1343a481ec72e8,ab3554acc1f287bb2e22ff20bb855e19a4177ef55267668 (...)

Answer 1

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

Лучший индекс имеет ghost первый:

CREATE INDEX index_phone_numbers_covering_hashed_ghost_and_user
  ON phone_numbers
  USING btree
  (ghost, hashed_phone_number COLLATE pg_catalog."default", user_id);

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

По сути, происходит то, что Postgres должен выполнить поиск по индексу для каждого элемента in список. Это может быть осложнено использованием строк - потому что сопоставления / кодировки влияют на сравнения. В конце концов, Postgres решает, что другие подходы более эффективны. Если вы положите ghost сначала он просто перейдет к правой части индекса и найдет нужные ему строки.