每天5分钟PG聊通透第10期,为什么有的索引不支持like查询?
参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;
每天5分钟PG聊通透第10期,为什么有的索引不支持like查询?
背景
问题说明(现象、环境) 分析原因 结论和解决办法
链接、驱动、SQL
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)
本期彩蛋 - 数据库生态工具&信创开源数据库
用好周边工具, 数据库管理水平战胜90%老司机
1、管控软件
云猿生开源的kubeblocks, 如果你要管理很多套并且种类很多的数据库产品, 推荐选择.
https://github.com/apecloud/kubeblocks
乘数开源的clup, 专门用来管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 企业可以关注一下.
https://www.csudata.com/
若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套数据库, 并且对插件有特别多的需求, 推荐选择.
https://pigsty.cc/zh/
2、审计监控诊断优化
海信聚好看的 DBdoctor, 采用ebpf技术, 在对数据库几乎没有影响的情况下实时监控数据库和服务器的各项指标, 发现和诊断问题根因非常方便.
https://www.dbdoctor.cn/
Bytebase 的目标非常远大, 是位于您和数据库之间的中间件。它是数据库 DevOps 的 GitLab/GitHub，专为开发人员、DBA 和平台工程师打造。
https://bytebase.cc/docs/introduction/what-is-bytebase/
D-Smart, Oracle老前辈白老大他们搞的, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.
https://www.modb.pro/db/567140
3、数据同步&迁移&备份恢复
NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.
https://www.ninedata.cloud/home
DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.
https://www.dsgdata.com/
通过信创并且开源的数据库:
PolarDB for PostgreSQL
https://github.com/ApsaraDB/PolarDB-for-PostgreSQL
还有几个国产数据库也非常值得关注: HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、ProtonBase(云原生分布式数仓. https://protonbase.com/ ).
参考文档点击阅读原文获得
感谢关注我的github (https://github.com/digoal/blog) 及视频号: