如果我使用带有5个字符的搜索文本,Postgresql不会使用索引。有了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 使用索引行。

以下是结构:

-- ----------------------------
-- 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;