ReaderMX.
PostgreSQL码农集散地

每天5分钟PG聊通透第23期,为什么有的函数不能创建索引?

Pdigoal约 5 分钟

参考文档点击文末阅读原文打开; 推荐《最好的PostgreSQL学习镜像》;

每天5分钟PG聊通透第23期,为什么有的函数不能被用来创建表达式索引?

背景

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

链接、驱动、SQL

23、为什么有的函数不能被用来创建表达式索引?

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

1、什么是表达式索引?

create index idx on a ((express));  

2、为什么需要表达式索引?

当SQL的输入条件是表达式时, 如果选择性较好, 使用索引加速是比较常见的优化手段. 因此就有了表达式索引:

create table a (id int, col text);  
insert into a select generate_series(1,1000000), md5(random()::text);  
  select * from a where substring(col,1,5) = 'abcde';  
  create index idx_a on a (substring(col,1,5));  
  explain select * from a where substring(col,1,5) = 'abcde';  
  postgres=# explain select * from a where substring(col,1,5) = 'abcde';  
                           QUERY PLAN                             
----------------------------------------------------------------  
 Index Scan using idx_a on a  (cost=0.42..3.76 rows=2 width=37)  
   Index Cond: ("substring"(col, 1, 5) = 'abcde'::text)  
(2 rows)  

3、思考一个问题:

有这样一个函数, 多次调用这个函数, 在没有参数或者参数都一样的情况下, 返回值会出现2种情况:

  • 1、不管是谁、在什么时间、在什么空间调用这个函数, 只要函数的输入参数不变, 结果就不变.
  • 2、不管是谁、在什么时间、在什么空间调用这个函数, 虽然函数的输入参数不变, 但是结果依旧有可能变化.

因此有了函数稳定性的概念(详见create function语法), PostgreSQL的3中函数稳定性状态:

  • immutable, 任何人任何时候调用它, 输入参数不变, 结果就不变. 如果输入参数是常量, 优化器会在生成执行计划前就把这个函数的结果算出来. (即使重启实例,即使修改数据库的GUC参数都不例外, 极其稳定)
    • 可以用来创建表达式索引
    • 如果作为where条件(例如 where id > func_immu(常数)), 允许使用索引扫描.
  • stable, 在同一个事务中, 多次调用, 输入参数不变, 结果就不变.
    • 不能用来创建表达式索引
    • 如果作为where条件(例如 where id > func_immu(常数)), 允许使用索引扫描.
  • volatile, 任何地方多次调用, 输入参数不变, 结果都有可能不一样.
    • 不能用来创建表达式索引
    • 如果作为where条件(例如 where id > func_immu(常数)), 不允许使用索引扫描.

注意: 函数稳定性只是个软性定义, 优化器用它来做出某些判断, 但是, 如果你自定义了一个函数, 真正的性质和稳定性定义允许不一样(例如明明是多次调用返回结果不一样的函数, 你确把它定义为immutable的), 可能出现一些不可预估的结果.

4、为什么只有immutable函数能用来创建表达式索引呢?

在索引中的存储的是表达式运算后的结果. 表达式是在创建索引、或者新增、更新数据时计算的.

create index idx_a on a (func(col,1,5));    

如果将来查询, 表达式的再次计算结果 和 存储在索引中的内容有可能不一致, 那会怎么样?
走索引扫描返回的结果集 和 使用非索引扫描得到的结果集 就可能不一样. 那不是个问题么?

explain select * from a where func(col,1,5) = 'abcde';  

今日荐书

彩蛋:国产数据库周边生态

当然一款数据库要流行起来, 除了自己要强大, 还离不开生态. 用好周边生态工具, 管理水平战胜90%老司机!!! 下面简单介绍一下国产数据库周边生态.

1、管控软件

鸣嵩(前阿里云数据库总经理 / 研究员)等大佬们创业创办的云猿生, 核心产品是KubeBlocks. 他们的理念是让管理数据库和搭积木一样简单, 如果你要管理很多套并且种类(OLTP\OLAP\NoSQL\KV\TS\MQ等)很多的数据库产品, 推荐首选.

  • https://github.com/apecloud/kubeblocks

PG中文社区核心委员唐成老师的公司乘数开源的Clup, 专用管理PostgreSQL和PolarDB的集群管理软件, 如果你要管理很多套数据库, 推荐选择. 并且Clup还提供了企业版、自研的连接池、分布式存储、一体机、备份平台等, 是企业用户推荐之选.

  • https://www.csudata.com/

若航开源的pigsty, 集成了300多个PG插件的PG集群和PolarDB集群管理软件, 如果你要管理很多套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/

PawSQL, SQL优化和诊断产品.  

D-Smart, Oracle老前辈白老大出品, 专注企业级市场, 将业界顶级DBA经验的产品化作品, 产品功能包括数据库监控、诊断、优化等.

  • https://www.modb.pro/db/567140

3、国产数据库IDE

IDE是开发者的必备工具，例如社区有pgAdmin, 国产IDE则可以看看老程序猿达刚老师的DeskUI:

  • https://www.deskui.com

4、数据同步&迁移&备份恢复

NineData, 老领导出去创业做的产品, 产品涵盖了数据同步、迁移、备份、比对、devops、chatDBA等.

  • https://www.ninedata.cloud/home

DSG, 非常老牌的数据库同步迁移企业级产品, 支持各种数据库的异构和同构迁移, 用他们的话说, 没有dsg搞不定的迁移, 比goldengate还牛.

  • https://www.dsgdata.com/

公开课

如果你对PolarDB学习感兴趣可以阅读这个公开课系列:

除了PolarDB还非常值得关注的几款PG栈国产数据库:

  • HaloDB(基于PG兼容PostgreSQL、Oracle、MySQL. http://www.halodbtech.com/ )、
  • IvorySQL(基于开源PG兼容PG、Oracle. https://www.ivorysql.org/zh-cn/ )、
  • ProtonBase(云原生分布式数仓. https://protonbase.com/ )、
  • 成都文武数据库（https://ww-it.cn）

参考文档点击阅读原文获得

感谢关注我的github (https://github.com/digoal/blog) 及视频号:
Image

KEEP THE THREAD GOING

更多来源文章