每天5分钟,PG聊通透 - 系列1 - 热门问题 - 链接、驱动、SQL - 第10期 - 为什么有的索引不支持字符串前置`like` `~`查询?

作者

digoal

日期

2021-12-20

标签

PostgreSQL , 热门问题

• 问题说明(现象、环境)
• 分析原因
• 结论和解决办法

10、为什么有的索引不支持字符串前置查询? (patten, lc_collate)

https://www.bilibili.com/video/BV1G3411v7Ts/

collate <> C 时, 默认index ops不支持like 前缀、~ 模糊前缀索引扫描.

解决办法:
1、才有非默认ops
2、创建索引时指定collate=C

例子:

create unlogged table a (id int, info text);  
insert into a select generate_series(1,1000000), md5(random()::text);  
select * from a where info like 'xx%';   
select * from a where info ~ '^xx';   
复制

数据库collate <> C.

\l   
collate <> C   
create index idx_a_1 on a (info);   
db1=# set enable_seqscan=off;   
SET   
db1=# explain select * from a where info like 'xx%';   
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Seq Scan on a  (cost=10000000000.00..10000020834.00 rows=5000 width=36)  
   Filter: (info ~~ 'xx%'::text)  
(2 rows)  
db1=# create index idx_a_2 on a (info text_pattern_ops);   
CREATE INDEX  
db1=# explain select * from a where info like 'xx%';   
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Index Scan using idx_a_2 on a  (cost=0.42..2.65 rows=100 width=37)  
   Index Cond: ((info ~>=~ 'xx'::text) AND (info ~<~ 'xy'::text))  
   Filter: (info ~~ 'xx%'::text)  
(3 rows)  
drop index idx_a_2;  
create index idx_a_3 on a (info collate "C");   
db1=# explain select * from a where info like 'xx%';   
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Index Scan using idx_a_3 on a  (cost=0.42..2.65 rows=100 width=37)  
   Index Cond: ((info >= 'xx'::text) AND (info < 'xy'::text))  
   Filter: (info ~~ 'xx%'::text)  
(3 rows)  
db1=# explain select * from a where info ~ '^xx';   
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Index Scan using idx_a_3 on a  (cost=0.42..2.65 rows=100 width=37)  
   Index Cond: ((info >= 'xx'::text) AND (info < 'xy'::text))  
   Filter: (info ~ '^xx'::text)  
(3 rows)  
db1=# explain select * from a where info like 'xx%' collate "zh_CN";  
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Index Scan using idx_a_3 on a  (cost=0.42..2.65 rows=100 width=37)  
   Index Cond: ((info >= 'xx'::text) AND (info < 'xy'::text))  
   Filter: (info ~~ 'xx%'::text COLLATE "zh_CN")  
(3 rows)  
db1=# explain select * from a where info like 'xx%' collate "C";  
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Index Scan using idx_a_3 on a  (cost=0.42..2.65 rows=100 width=37)  
   Index Cond: ((info >= 'xx'::text) AND (info < 'xy'::text))  
   Filter: (info ~~ 'xx%'::text COLLATE "C")  
(3 rows)  
复制

数据库collate = C.

\l  
collate=C  
create index idx_a_1 on a (info);   
postgres=# explain select * from a where info like 'xx%';  
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Index Scan using idx_a_1 on a  (cost=0.42..2.65 rows=100 width=37)  
   Index Cond: ((info >= 'xx'::text) AND (info < 'xy'::text))  
   Filter: (info ~~ 'xx%'::text)  
(3 rows)  
复制

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.