PostgreSQL 函数式索引使用注意 - 暨非immutable函数不适合索引的原因

最新推荐文章于 2024-07-18 07:30:00 发布
最新推荐文章于 2024-07-18 07:30:00 发布
阅读量1.4k 收藏
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/647447
版权
本文探讨了PostgreSQL中函数式索引的使用，强调了使用immutable函数的重要性。由于表达式索引依赖于输入参数不变时结果恒定的特性，如果涉及的变量如商品原价变化，可能导致索引与实际数据不一致。文中通过商品和折扣表的例子，说明了原价变化时索引不更新的问题，提醒读者在创建函数索引时要注意函数的稳定性。
摘要由CSDN通过智能技术生成

标签

PostgreSQL , 表达式 , 函数稳定性 , immutable

背景

PostgreSQL支持表达式索引，但是表达式必须是immutable的，也即是当输入参数不变时，结果是永恒不变的。

因为当表达式涉及的变量不变时，索引本身不会变化。

给个例子，如果我们有一张表存储了商品价格，另一张表存储了商品折扣 ，如果我们想通过折扣后的价格范围搜索符合价格区间的商品ID，可以使用索引吗？

表达式索引，可以。但是前提是：输入一个商品ID时，商品原价永恒不变。

否则原价发生变化就可能出现索引内容与实际不一致的问题。

例子




                

        

        




    

  


        

            

                    
                      最低0.47元/天 解锁文章
                        
                    
            

        

    

      

        

            

              
                
                  weixin_34408717
                
              
            

            

                关注
              
            

        

        

          
      

      









                



	

		

			

				
					
PostgreSQL教程(八)：索引详解

				
			

			

				

					12-16
				

			

		

		

			
				
一、索引的类型：
    PostgreSQL提供了多　　种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。
    
    1. B-Tree:
  代码如下:
    CREATE TABLE test1 (
        id integer,
        content varchar
    );
    CREATE INDEX test1_id_index ON test1 (id); 
 
    B-Tree索引主要用于等于和范围查询，特别是

			
		

	



                

              
                



	

		

			

				
					
错误“索引表达式中的函数必须标记为IMMUTABLE”

				
			

			

				

					Yusili1111的博客
				

				

					10-14
					
					1044
					
				

			

		

		

			
				
PostgreSQL系列——创建索引错误（1）

			
		

	



                


  
              

                


	

		

			

				
					
从零开始学PostgreSQL (三)： 索引篇

				
			

			

				

					qq_34692500的博客
				

				

					07-18
					
					1566
					
				

			

		

		

			
				
目录1、索引类型2、多列索引3、索引和ORDER BY4、组合多个索引5、唯一索引6、表达式的索引7、部分索引8、索引扫描和覆盖索引9、检查和优化索引的使用情况10、索引的选择和使用索引类型B-tree 索引：适用场景：范围查询、等值查询、排序操作。特点：适用于大部分查询场景，是 PostgreSQL 默认的索引类型。哈希索引：适用场景：等值查询，对于频繁的等值查询有性能优势。特点：不支持范围查询...

			
		

	





	

		

			

				
					
Postgresql中创建函数索引

				
			

			

				

					夏季版
				

				

					05-30
					
					6139
					
				

			

		

		

			
				
1、创建函数索引的语法，如下表示为表的指定字段的upper函数创建了一个函数索引 
CREATE INDEX 索引名 ON 表名 (upper(字段名));

2、使用场景： 
如果某张表的数据量很大，而其中某个字段在当前数据库中保存大小写共存，当需要查询的时候忽略大小写，那么此时一般使用到的方法就是使用sql中的upper()函数，但是使用upper()函数后，查询语句是不会走索引的，所以建议为...

			
		

	



		

			
		



	

		

			

				
					
PostgreSQL 函数稳定级别：IMMUTABLE   STABLE  VOLATILE

				
			

			

				

					思念十年后
				

				

					07-02
					
					3313
					
				

			

		

		

			
				
PostgreSQL 函数的过程


CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | WI...

			
		

	





	

		

			

				
					
postgresql immutable_PostgreSQL 之函数索引

				
			

			

				

					weixin_39567870的博客
				

				

					11-27
					
					1066
					
				

			

		

		

			
				
PostgreSQL支持函数索引，但函数必须是immutable的，变量不变时，索引本身不会变化的，所以函数当输入参数不变时，结果必须是永恒不变的。做个实验，一次其中考试的成绩，有一张表存储学生信息，另一张表存储考试成绩，我们通过平均成绩搜索符合成绩的学生，可以使用索引吗？使用函数索引，可以的。但是前提是，输入学号，得到平均成绩不能变化(当然成绩是不会变化的)，不然数据变化，但索引不会刷...

			
		

	





	

		

			

				
					
使用函数波动性优化 PostgreSQL 查询:Volatile, Stable, and Immutable

				
			

			

				

					weixin_43230594的博客
				

				

					05-12
					
					662
					
				

			

		

		

			
				
Optimizing PostgreSQL Queries with Function Volatility: Volatile, Stable, and Immutable

			
		

	





	

		

			

				
					
PostgreSQL数据库中窗口函数的语法与使用

				
			

			

				

					09-09
				

			

		

		

			
				
在PostgreSQL数据库中，窗口函数（Window Functions）是一种强大的功能，允许你在一组相关行（即“窗口”）上执行计算，而不仅仅限于当前行。这些函数极大地扩展了SQL查询的能力，尤其在数据分析和报告中非常有用。...

			
		

	





	

		

			

				
					
PostgreSQL数据库列式存储cstore_fdw插件CStoreProcessUtility函数图.eddx

				
			

			

				

					10-20
				

			

		

		

			
				
PostgreSQL数据库的列式存储cstore_fdw 钩子函数CStoreProces的函数架构思维导图，详细分析请见我的博客：https://rng-songbaobao.blog.csdn.net/

			
		

	





	

		

			

				
					
postgresql-16.2-1-windows-x64.exe

				
			

			

				

					04-08
				

			

		

		

			
				
PostgreSQL 是一个强大的开源关系型数据库管理系统，其最新版本为16.2，在Windows操作系统上提供了64位的安装程序，文件名为"postgresql-16.2-1-windows-x64.exe"。这个安装程序是专为在Windows环境中运行PostgreSQL...

			
		

	





	

		

			

				
					
postgresql查看表和索引大小函数

				
			

			

				

					08-18
				

			

		

		

			
				
使用postgresql 查下数据库中表大小和索引大小的函数。

			
		

	





	

		

			

				
					
PostgreSQL中函数属性的性能优化

				
			

			

				

					weixin_68383707的博客
				

				

					02-22
					
					1029
					
				

			

		

		

			
				
PostgreSQL 的函数提供了非常丰富的特性，很多特性并不为大众所熟悉，当你发掘出一个函数属性的效用后，总能给你带来一些不可思议的体验！在 PostgreSQL 中，几乎可以用任何通用语言（如 Perl、Python 或 C）编写函数。一般来说，这提供了很大的灵活性和可接受的性能。但是，在某些情况下，用户可能会说：“我们感觉程序很慢”。事实是：PostgreSQL 可能不是这种现象的问题根源 - 在许多情况下，它只是“表面错误”。本文尝试来详细说明下由函数调用引起性能差的关键问题。

			
		

	





	

		

			

				
					
Postgresql函数稳定性学习

				
			

			

				

					wyjzwj的博客
				

				

					08-15
					
					548
					
				

			

		

		

			
				
PostgreSQL 函数在定义的时候有三个稳定性状态可选:

IMMUTABLE | STABLE | VOLATILE
分别代表，非常稳定，稳定，不稳定。


不稳定，函数可以修改数据库的数据，输入同样的参数可以返回不同的结果，同一个QUERY中，如果需要返回该函数的结果，那么每一行都会运算一遍这个函数(后面会有例子)。
稳定，函数不可以修改数据库的数据，同一个QUERY中，如果

			
		

	





	

		

			

				
					
retalk PostgreSQL function's [ volatile|stable|immutable ]

				
			

			

				

					heqiyu34的专栏
				

				

					03-19
					
					1292
					
				

			

		

		

			
				
PostgreSQL 函数的稳定性, 以前写过几篇BLOG讲述, 见参考部分.
本文再细化并举例说明一下他们的差别.
首先函数稳定性分三种 : 

volatilestableimmutable



首先创建1个测试表 : 

digoal=> create table test (id int, info text);CREATE TABLEdigoal=> insert 

			
		

	





	

		

			

				
					
postgreSql创建自定义函数

				
			

			

				

					jinxiumeihappy的专栏
				

				

					03-27
					
					2061
					
				

			

		

		

			
				
1.求两点之间的距离（米）


create or replace function st_distance3857(geom1 geometry,geom2 geometry) returns float as $$
  select 
      st_distance(
	st_transform(geom1,3857),
	st_transform(geom2,3857)
      ) ...

			
		

	





	

		

			

				
					
函数索引引用的函数必须是immutable类型

				
			

			

				

					lyu1026的博客
				

				

					09-10
					
					1068
					
				

			

		

		

			
				
用户在使用中，可能会用到基于函数的索引，但是函数是非 immutable 类型的，导致函数索引无法创建。如：

test=# create index ind_t1 on t1(to_char(create_date,'yyyy-mm'));
ERROR:  functions in index expression must be marked IMMUTABLE


这里我们先看下函数的类型...

			
		

	





	

		

			

				
					
pg 创建函数选项介绍

				
			

			

				

					紫无之紫的博客
				

				

					08-21
					
					282
					
				

			

		

		

			
				
如果它们访问临时表、客户端连接状态、游标、预备语句或者系统无法在并行模式中同步的本地后端状态（例如setseed只能在组领导者中执行，因为另一个进程所作的更改不会在领导者中被反映出来，再比如pg_backend_pid, random），它们应该被标为PARALLEL RESTRICTED。比如：currval。指定函数的估计执行代价，若返回一个集合，这就是每个被返回行的代价，对 C 语言和内部函数会指定为 1 ，对其他语言的函数则会指定为 100。给planner提供了解函数的支持函数，需要为c函数，

			
		

	





	

		

			

				
					
PostGIS空间数据中基础常用函数介绍

					
热门推荐

				
			

			

				

					夜郎King的博客
				

				

					09-01
					
					1万+
					
				

			

		

		

			
				
本文将围绕PostGIS空间数据库，重点讲解在空间数据库中几个常用的空间函数，通过实例的讲解带读者了解函数的基础使用。

			
		

	





	

		

			

				
					
PostgreSQL 9.4文档 第4章 SQL语法

				
			

			

				

					Tony.Dong的专栏
				

				

					12-27
					
					4144
					
				

			

		

		

			
				
本章介绍SQL的语法。它是理解后面章节的基础，它们详细描述了SQL如何用于定义和修改数据。
对于已经熟悉SQL的用户，同样应该仔细阅读本章，因为它包含了各个SQL数据库中实现方式不同的一些规则和概念，这些是PostgreSQL特有的属性。
4.1. 词法结构
SQL输入由一系列命令组成。命令由一系列符号组成，以分号（“;”）结束。输入流的结束也会结束一个命令。每个命令能够使用的符号不尽相同。

			
		

	





	

		

			

				
					
postgresql 函数式索引

					
最新发布

				
			

			

				

					07-31
				

			

		

		

			
				
PostgreSQL是一种支持函数式查询的语言，它提供了一种特殊的索引类型叫做“函数式索引”或“表达式索引”。这类索引不同于传统的B树或哈希索引，它们不是基于列值直接存储，而是对数据经过特定函数计算后的结果进行...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值