AnolisOS 8.8 PostgreSQL FULL TEXT SEARCH 全文检索

PostgreSQL 全文检索

项目上需要用到对汉字的检索和匹配查询，并且由于这种需求量并不大，如果搭建一套完整的 ES 集群那么需要额外的增加服务器的数量和成本。那么 PostgreSQL 数据库本身能不能在一定的范围内既能满足对汉字的模糊检索效率又不会特别的差呢？下面是一个测试的案例。

有一个表 log_sys_shared 是按照日期做的一个分区表。表结构如下，现在想对字段 remark 做检索。

CREATE TABLE "public"."log_sys_shared" (
  "id" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "create_user" varchar(32) COLLATE "pg_catalog"."default",
  "create_time" timestamp(3) NOT NULL,
  "modify_user" varchar(32) COLLATE "pg_catalog"."default",
  "modify_time" timestamp(3),
  "sys_code" varchar(32) COLLATE "pg_catalog"."default",
  "mod_code" varchar(32) COLLATE "pg_catalog"."default",
  "log_classify" varchar(255) COLLATE "pg_catalog"."default",
  "log_type" varchar(255) COLLATE "pg_catalog"."default",
  "remark" text COLLATE "pg_catalog"."default",
  "rst_flag" varchar(2) COLLATE "pg_catalog"."default",
  "ext_attr" jsonb,
  "req_ip" varchar(32) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
  "req_param" text COLLATE "pg_catalog"."default" DEFAULT ''::text,
  "res_param" text COLLATE "pg_catalog"."default" DEFAULT ''::text,
  "trace" text COLLATE "pg_catalog"."default" DEFAULT ''::text,
  "start_time" timestamp(3),
  "end_time" timestamp(3),
  "use_time" int8,
  "exec_ip" varchar(32) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
  "exec_log_id" varchar(32) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
  "err_code" varchar(16) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
  "err_msg" text COLLATE "pg_catalog"."default" DEFAULT ''::text,
  "db_style" varchar(32) COLLATE "pg_catalog"."default" DEFAULT ''::character varying
)
PARTITION BY RANGE ("create_time" "pg_catalog"."timestamp_ops");

ALTER TABLE "public"."log_sys_shared" OWNER TO "postgres";
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240626" FOR VALUES FROM ('2024-06-26 00:00:00') TO ('2024-06-27 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240627" FOR VALUES FROM ('2024-06-27 00:00:00') TO ('2024-06-28 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240628" FOR VALUES FROM ('2024-06-28 00:00:00') TO ('2024-06-29 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240629" FOR VALUES FROM ('2024-06-29 00:00:00') TO ('2024-06-30 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240630" FOR VALUES FROM ('2024-06-30 00:00:00') TO ('2024-07-01 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240701" FOR VALUES FROM ('2024-07-01 00:00:00') TO ('2024-07-02 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240702" FOR VALUES FROM ('2024-07-02 00:00:00') TO ('2024-07-03 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240703" FOR VALUES FROM ('2024-07-03 00:00:00') TO ('2024-07-04 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240704" FOR VALUES FROM ('2024-07-04 00:00:00') TO ('2024-07-05 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240705" FOR VALUES FROM ('2024-07-05 00:00:00') TO ('2024-07-06 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240706" FOR VALUES FROM ('2024-07-06 00:00:00') TO ('2024-07-07 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240707" FOR VALUES FROM ('2024-07-07 00:00:00') TO ('2024-07-08 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240708" FOR VALUES FROM ('2024-07-08 00:00:00') TO ('2024-07-09 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240709" FOR VALUES FROM ('2024-07-09 00:00:00') TO ('2024-07-10 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240710" FOR VALUES FROM ('2024-07-10 00:00:00') TO ('2024-07-11 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240711" FOR VALUES FROM ('2024-07-11 00:00:00') TO ('2024-07-12 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240712" FOR VALUES FROM ('2024-07-12 00:00:00') TO ('2024-07-13 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240713" FOR VALUES FROM ('2024-07-13 00:00:00') TO ('2024-07-14 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240714" FOR VALUES FROM ('2024-07-14 00:00:00') TO ('2024-07-15 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240715" FOR VALUES FROM ('2024-07-15 00:00:00') TO ('2024-07-16 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240716" FOR VALUES FROM ('2024-07-16 00:00:00') TO ('2024-07-17 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240717" FOR VALUES FROM ('2024-07-17 00:00:00') TO ('2024-07-18 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240718" FOR VALUES FROM ('2024-07-18 00:00:00') TO ('2024-07-19 00:00:00');
ALTER TABLE "public"."log_sys_shared" ATTACH PARTITION "public"."log_sys_shared_20240719" FOR VALUES FROM ('2024-07-19 00:00:00') TO ('2024-07-20 00:00:00');
COMMENT ON COLUMN "public"."log_sys_shared"."id" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."create_user" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."create_time" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."modify_user" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."modify_time" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."sys_code" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."mod_code" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."log_classify" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."log_type" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."remark" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."rst_flag" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."ext_attr" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."req_ip" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."req_param" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."res_param" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."trace" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."start_time" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."end_time" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."use_time" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."exec_ip" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."exec_log_id" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."err_code" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."err_msg" IS '';
COMMENT ON COLUMN "public"."log_sys_shared"."db_style" IS '';
COMMENT ON TABLE "public"."log_sys_shared" IS '';

首先确保已经安装并启用了 PostgreSQL 的全文搜索插件。可以通过以下命令检查是否开启了 full text search。
SELECT name FROM pg_available_extensions WHERE name LIKE 'pg_trgm';
SELECT * FROM pg_extension;
CREATE extension pg_trgm;
如果结果中包含 pg_trgm，则说明已经开启了 full text search。

接下来创建一个全文检索索引。我们可以创建一个使用 trigram 索引的全文检索索引，可以通过以下 SQL 语句实现
CREATE INDEX idx_log_sys_shared_remark_full_text_search ON log_sys_shared USING gin (remark gin_trgm_ops);
这条 SQL 语句将在 log_sys_shared 表的 remark 字段上创建一个全文检索索引，使用了 trigram 索引类型(gin_trgm_ops)。

EXPLAIN ANALYZE 
SELECT * 
FROM log_sys_shared a
WHERE 1=1
AND a.create_time >= '2024-07-01 00:00:00'::timestamp
AND a.create_time <= '2024-07-01 23:59:59'::timestamp
AND a.sys_code = 'queue'
AND a.mod_code = '300'
AND a.remark ILIKE '张利';

Bitmap Heap Scan on log_sys_shared_20240701 a  (cost=28.00..32.02 rows=1 width=1193) (actual time=12.466..12.467 rows=0 loops=1)
  Recheck Cond: (remark ~~* '张利'::text)
  Filter: ((create_time >= '2024-07-01 00:00:00'::timestamp without time zone) AND (create_time <= '2024-07-01 23:59:59'::timestamp without time zone) AND ((sys_code)::text = 'queue'::text) AND ((mod_code)::text = '300'::text))
  ->  Bitmap Index Scan on log_sys_shared_20240701_remark_idx  (cost=0.00..28.00 rows=1 width=0) (actual time=12.463..12.463 rows=0 loops=1)
        Index Cond: (remark ~~* '张利'::text)
Planning Time: 0.210 ms
Execution Time: 12.486 ms

可以对比一下没有 FULL INDEX 和有 FULL INDEX 在查询效率上的区别。