Postgresql не использует индексы, если я использую текст для поиска с 5 символами. С 6 работает. Почему?

Я использую Postgresql 13.

В этом запросе PostgreSQL использует индексы:

SELECT *
FROM
    "players"
WHERE team_id = 3
    AND (
    code ILIKE 'lushij'
    OR
    REPLACE(lastname||firstname,' ','') ILIKE '%lushij%'
    OR REPLACE(firstname||lastname,' ','') ILIKE '%lushij%'
    OR personal_info->>'houses' ILIKE '%lushij%'
    )
LIMIT 15

Limit  (cost=333.01..385.77 rows=15 width=360)
  ->  Bitmap Heap Scan on players  (cost=333.01..4061.29 rows=1060 width=360)
        Recheck Cond: ((code ~~* 'lushij'::text) OR (replace((lastname || firstname), ' '::text, ''::text) ~~* '%lushij%'::text) OR (replace((firstname || lastname), ' '::text, ''::text) ~~* '%lushij%'::text) OR ((personal_info ->> 'houses'::text) ~~* '%lushij%'::text))
        Filter: (team_id = 3)
        ->  BitmapOr  (cost=333.01..333.01 rows=1060 width=0)
              ->  Bitmap Index Scan on players_code_trgm  (cost=0.00..116.75 rows=100 width=0)
                    Index Cond: (code ~~* 'lushij'::text)
              ->  Bitmap Index Scan on players_replace_last_first_name_trgm  (cost=0.00..66.40 rows=320 width=0)
                    Index Cond: (replace((lastname || firstname), ' '::text, ''::text) ~~* '%lushij%'::text)
              ->  Bitmap Index Scan on players_replace_first_last_name_trgm  (cost=0.00..66.40 rows=320 width=0)
                    Index Cond: (replace((firstname || lastname), ' '::text, ''::text) ~~* '%lushij%'::text)
              ->  Bitmap Index Scan on players_personal_info_houses_trgm_idx  (cost=0.00..82.40 rows=320 width=0)
                    Index Cond: ((personal_info ->> 'houses'::text) ~~* '%lushij%'::text)

С тем же запросом, но с поисковым текстом на один символ меньше (от lushij к lushi) индексы НЕ используются:

SELECT *
FROM
    "players"
WHERE team_id = 3
    AND (
    code ILIKE 'lushi'
    OR
    REPLACE(lastname||firstname,' ','') ILIKE '%lushi%'
    OR REPLACE(firstname||lastname,' ','') ILIKE '%lushi%'
    OR personal_info->>'houses' ILIKE '%lushi%'
    )
LIMIT 15

Limit  (cost=0.00..235.65 rows=15 width=360)
  ->  Seq Scan on players  (cost=0.00..76853.53 rows=4892 width=360)
        Filter: ((team_id = 3) AND ((code ~~* 'lushi'::text) OR (replace((lastname || firstname), ' '::text, ''::text) ~~* '%lushi%'::text) OR (replace((firstname || lastname), ' '::text, ''::text) ~~* '%lushi%'::text) OR ((personal_info ->> 'houses'::text) ~~* '%lushi%'::text)))

Почему?

ОБНОВЛЕНИЕ:

Если я прокомментирую LIMIT 15 line используются индексы.

Вот конструкции:

Структура стола игроков
-- ----------------------------
-- Table structure for players
-- ----------------------------
DROP TABLE IF EXISTS "public"."players";
CREATE TABLE "public"."players" (
  "id" int8 NOT NULL DEFAULT nextval('players_id_seq'::regclass),
  "created_at" timestamptz(6) NOT NULL DEFAULT now(),
  "updated_at" timestamptz(6),
  "team_id" int8 NOT NULL,
  "firstname" text COLLATE "pg_catalog"."default",
  "lastname" text COLLATE "pg_catalog"."default",
  "code" text COLLATE "pg_catalog"."default",
  "personal_info" jsonb
)
;

-- ----------------------------
-- Indexes structure for table players
-- ----------------------------
CREATE INDEX "players_personal_info_houses_trgm_idx" ON "public"."players" USING gin (
  (personal_info ->> 'houses'::text) COLLATE "pg_catalog"."default" "public"."gin_trgm_ops"
);
CREATE INDEX "players_code_trgm" ON "public"."players" USING gin (
  "code" COLLATE "pg_catalog"."default" "public"."gin_trgm_ops"
);
CREATE INDEX "players_lower_code" ON "public"."players" USING btree (
  lower(code) COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
CREATE INDEX "players_replace_first_last_name_trgm" ON "public"."players" USING gin (
  replace(firstname || lastname, ' '::text, ''::text) COLLATE "pg_catalog"."default" "public"."gin_trgm_ops"
);
CREATE INDEX "players_replace_last_first_name_trgm" ON "public"."players" USING gin (
  replace(lastname || firstname, ' '::text, ''::text) COLLATE "pg_catalog"."default" "public"."gin_trgm_ops"
);

-- ----------------------------
-- Primary Key structure for table players
-- ----------------------------
ALTER TABLE "public"."players" ADD CONSTRAINT "players_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table players
-- ----------------------------
ALTER TABLE "public"."players" ADD CONSTRAINT "players_team_id_fkey" FOREIGN KEY ("team_id") REFERENCES "public"."teams" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
Источник

2 ответа

Чем короче строка, тем менее избирательным становится ваше условие. Основываясь на своих оценках, PostgreSQL считает, что с короткой строкой достаточно строк соответствует условию, что дешевле просто последовательно извлекать строки и отбрасывать те, которые не совпадают, пока не будет найдено 15 совпадающих строк.

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

Источник

Хорошо... это основано на моих знаниях SQL Server и SQL в целом, но, вероятно, применимо и здесь.

Для начала... потому что вы делаете SELECT *, в какой-то момент потребуется перейти к кластеризованному индексу.

Использование некластеризованного индекса (если он используется) заключается в идентификации соответствующих строк, а затем он переходит и выбирает эти строки 1 на 1 (соединение вложенного цикла, или иногда называемое поиском / сканированием индекса + поиск по ключу).

Если строк слишком много, это на самом деле неэффективно - в конечном итоге вы выполняете больше операций чтения и т. Д., Чем просто чтение всей таблицы.

Уменьшение длины фильтра LIKE увеличивает оценку количества элементов, например, увеличивает количество строк, которые, как ожидается, будет соответствовать фильтру в планировщике / оптимизаторе запросов.

Я предполагаю, что механизм SQL делает предположение (включая статистику по индексу / данным) и определяет, что, вероятно, более эффективно просто читать ВСЕ данные из кластерного индекса, а не определять строки и читать их 1 на 1.

Обновление после обновления OP повторного удаления лимита.

Ну... еще раз, это зависит от того, сколько строк существует, по его оценке, на основе фильтра.

Представьте, что вы выполняете ILIKE '%e%' в исходном запросе. Каждая вторая строка может соответствовать этому. Поскольку у вас нет сортировки, ему просто нужно прочитать (скажем) первые 30 строк кластерного индекса, и он получит ваш ответ. И снова планировщик / оптимизатор запросов может прийти к выводу, что это будет наиболее эффективный способ их получения.

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

  • Для%e% может быть более эффективным просто выполнить полное сканирование кластерного индекса, так как ожидается, что многие строки будут соответствовать
  • Для более сложной / выборочной фильтрации поиск в первую очередь по индексу (а затем прямой поиск данных в кластеризованном индексе) часто бывает более эффективным.
Источник
Другие вопросы по тегам