模糊查询or导致索引失效

# 表结构

```pgsql
-- ----------------------------
-- Table structure for t_test
-- ----------------------------
DROP TABLE IF EXISTS "public"."t_test";
CREATE TABLE "public"."t_test" (
  "id" int4 NOT NULL,
  "name" varchar(50) COLLATE "pg_catalog"."default",
  "desc" varchar(50) COLLATE "pg_catalog"."default",
  "age" int4,
  "sort" int4
)
;
-- ----------------------------
-- Indexes structure for table t_test
-- ----------------------------
CREATE INDEX "idx_test_age" ON "public"."t_test" USING btree (
  "age" "pg_catalog"."int4_ops" ASC NULLS LAST
);
CREATE INDEX "idx_test_name" ON "public"."t_test" USING btree (
  "name" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);
-- ----------------------------
-- Primary Key structure for table t_test
-- ----------------------------
ALTER TABLE "public"."t_test" ADD CONSTRAINT "t_test_pkey" PRIMARY KEY ("id");

```

## 插入测试数据

插入100000条测试数据

# 验证

<br/>

```pgsql
explain SELECT * FROM "t_test" where age= 25;

explain SELECT * FROM "t_test" where name like '%张三1%' or age= 25;

explain SELECT * FROM "t_test" where age= 25 or name like '%张三1%';

explain analyse SELECT * FROM "t_test" where age= 25
union
SELECT * FROM "t_test" where name like '%张三1%';

```

 

 

 

 

# 结论

pgsql中or条件中只要有一个条件存在前模糊都会破坏索引的使用

# 优化

用**union**去组合非索引条件或索引破坏条件字段替换**or**