【PostgreSQL】PG左模糊 右模糊匹配查询，如何走btree索引

【PostgreSQL】PG左模糊 右模糊匹配查询，如何走btree索引

锥栗

2022年11月06日 11:09 ·  阅读 41

前言

PG如果直接使用左右模糊查询，可能会不走btree索引，这里记录一下模糊匹配走索引的方法。

这里使用DBeaver来操作本地数据库。

后模糊 建表

首先打开SQL编辑页：

我们建表为 test.user_content ，维护了主键id，username，user_id和content字段，并且插入一点数据，语句如下：

CREATE TABLE test.user_content (
   id serial PRIMARY key NOT null,
   username varchar(128)    NOT NULL,
   user_id varchar(128)    NOT null,
   content varchar(128)
);
-- CREATE INDEX test_user_content_username ON user_content (username);
--CREATE INDEX test_user_username_idx on user_content using btree(username collate "C"); 
CREATE INDEX test_user_username_idx on user_content using btree(username varchar_pattern_ops); 
CREATE INDEX test_user_content_user_id ON user_content (user_id);
CREATE INDEX test_user_content_content ON user_content (content);

INSERT INTO test.user_content (id,username,user_id,"content")
	VALUES (1,'admin123','123456','asdiignipasdpj123i-185-91j-sa=<>-2=oe=1,2''11.24,1');
INSERT INTO test.user_content (id,username,user_id,"content")
	VALUES (2,'admin124','424211','onfffaaqq');
复制代码

如果使用以下

CREATE INDEX test_user_content_username ON user_content (username);
复制代码

是无法走索引的，因此需要改写为：

CREATE INDEX test_user_username_idx on user_content using btree(username collate "C"); 
复制代码

或者是：

CREATE INDEX test_user_username_idx on user_content using btree(username varchar_pattern_ops); 
复制代码

执行SQL编辑页：

得到我们的test.user_content： 

可以看到索引和依赖关系：

 

查询分析

之后我们对username字段进行模糊查询：

SELECT id, username, user_id, "content"
FROM test.user_content
WHERE username LIKE 'adm%'
;

EXPLAIN SELECT id, username, user_id, "content"
FROM test.user_content
WHERE username LIKE 'adm%'
;
复制代码

可以看到成功走了索引。

前模糊 建表

前模糊，其实就是后模糊做字符串翻转，因此我们有建表语句：

CREATE TABLE test.user_content (
   id serial PRIMARY key NOT null,
   username varchar(128)    NOT NULL,
   user_id varchar(128)    NOT null,
   content varchar(128)
);
-- CREATE INDEX test_user_content_username ON user_content (username);
--CREATE INDEX test_user_username_idx on user_content using btree(REVERSE(username) collate "C"); 
CREATE INDEX test_user_username_idx on user_content using btree(REVERSE(username) varchar_pattern_ops); 
CREATE INDEX test_user_content_user_id ON user_content (user_id);
CREATE INDEX test_user_content_content ON user_content (content);

INSERT INTO test.user_content (id,username,user_id,"content")
	VALUES (1,'admin123','123456','asdiignipasdpj123i-185-91j-sa=<>-2=oe=1,2''11.24,1');
INSERT INTO test.user_content (id,username,user_id,"content")
	VALUES (2,'admin124','424211','onfffaaqq');
复制代码

执行之后：

查询分析

我们对username字段做模糊查询有：

SELECT id, username, user_id, "content"
FROM test.user_content
WHERE REVERSE(username) LIKE '321%'
;

EXPLAIN SELECT id, username, user_id, "content"
FROM test.user_content
WHERE REVERSE(username) LIKE '321%'
;
复制代码

执行有：

可以看到我们走了索引

并且通过321%实现了123%的前模糊查询

参考资料

blog.csdn.net/weixin_3954…