postgresql 中position函数的性能详解

起因：

postgresql中position函数提供从头查找返回第一个匹配到字符串的下标。

而我需要返回从后向前查找第一个匹配到的坐标，但是postgressql并未提供相关函数，所以自己写了如下代码提供相关功能：

CREATE OR REPLACE FUNCTION lastindexof(text, character)
 RETURNS integer AS
$BODY$
begin
 if $1 is null then return NULL;
 end if;
 for i in reverse length($1) .. 1
 loop
  if substr($1,i,1) = $2
  then
   return i;
  end if;
 end loop;
 return NULL;
end
$BODY$
LANGUAGE plpgsql IMMUTABLE STRICT

本来以为事情完美解决，但是性能的差距却让人感觉很失望，如下图

数据库原生的“position”和自己提供“lastindexof”，居然产生了30倍以上的性能差距，那么探究缘由就变成一个有意思的事情。也算第一次尝试翻阅数据库源码，中间总是有点小麻烦，不过当我找到如下代码的时候，那种恍然大悟的欣喜也算满足了自己求知欲。

注意看1054行，运用了指针~ 可见数据库底层运算，用了引用传递，而我自己写的函数是一个拷贝传递。

原因找到了，解决问题也就手到擒来，拿C写一个扩展？或者？

本可以拿C写一些底层代码练练手，不过那又需要重新编译等等，时间有限，留给以后去做吧，先想个简单的办法去解决它。

解决方法如下

select length(dir) -position('/' in reverse(dir)) +1 from log_hup_ftp_30

测试性能截图

好吧，虽然由于函数的复杂性增加，性能还是慢了一倍多，但是比起之前5s之久还是快了不少。

tips:

最近得到德哥的回答，引用传递可以使用游标类型！再次谢谢德哥~

补充： SQL之查询函数LOCATE、POSITION、INSTR、FIND_IN_SET、IN、LIKE

LOCATE()

返回要查询的字符串在被查询字符串里第一次出现的位置

注：当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串，它才是字母大小写敏感的

LOCATE(substr,str)

返回substr字符串在str里第一次出现的位置，没有返回0

SELECT LOCATE('.',t.str)FROM `table` t;
>5

LOCATE(substr,str,pos)

返回substr字符串在str里pos（起始位置）出现的位置，没有返回0

注：pos必须大于第一次出现的位置，才能显示第二次出现的位置

SELECT LOCATE('.',t.str,6)FROM `table` t;
>9//当小于等于第一次出现的位置（5）时，返回的还是第一次出现的位置

POSITION()

返回要查询的字符串在被查询字符串里第一次出现的位置（和locate用法一样,查了很多资料position是locate的别名）

POSITION(substr IN str)

返回substr字符串在str出现的位置，没有返回0

SELECT POSITION('cn' IN t.str)FROM `table` t;
>10

INSTR()

返回要查询的字符串在被查询字符串里第一次出现的位置。这和LOCATE()的双参数形式相同，只是参数的顺序被颠倒。

INSTR(str,substr)

返回substr字符串在str出现的位置，没有返回0

SELECT INSTR(t.str,'com')FROM `table` t;
>6

FIND_IN_SET()

返回在集合中的索引位置（竖向发展）

FIND_IN_SET(str,strlist)

返回str1在strlist集合中的索引位置

SELECT FIND_IN_SET('demo.com.cn',t.str) FROM `table` t;
>1//返回索引

IN()

返回在集合中的索引位置（同FIND_IN_SET）

str IN (strlist)

返回str1在strlist集合中的索引位置

SELECT 'demo.com.cn' IN(t.str) FROM `table` t;
>1//返回索引

LIKE

返回类似(模糊)字符的集合

LIKE %str%

返回以str类似的集合

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • postgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    postgresql中的to_char()用法和Oracle相比,多了一个参数. to_char(待转换值,转换格式); 常用转换格式有2种: 一个是写若干个0,如果待转换的值位数少于于你定义的转换格式位数,输出值会自动在左边补0,位数补齐到转换格式的长度:如果待转换的值位数多于你定义的转换格式位数,输出值为:##(长度跟你定义的转换格式一样): 另一个是写若干个9,如果待转换的值位数少于你定义的转换格式位数,正常输出: 如果待转换的值位数多于于你定义的转换格式位数,输出值为:##(长度跟你定义

  • PostgreSQL 字符串处理与日期处理操作

    字符串长度.大小写 SELECT CHAR_LENGTH('test') -- 字符串长度 SELECT LENGTH('test') LENGTH(string,encoding name) SELECT LENGTH('测试','UTF-8'); LOWER(string) 或者 UPPER(string) -- 大小写 ASCII(string) SELECT ASCII('abc') -- 结果是'a'的ascii码 字符串格式化 FORMAT(formatstr text [,form

  • PostgreSQL字符切割:substring函数的用法说明

    PostgreSQL字符切割:substring函数的用法说明

    作为当前最强大的开源数据库,Postgresql(以下简称pg)对字符的处理也是最为强大的,首先他也有substr,trim等其他数据库都有的普通函数,这里我们介绍他更强大的一个函数substring,可以像python,java等编程语言一样使用正则表达式,强大到极点 在此之前我们先了解一下正则表达式最基础的四个 %代表一个或多个字符 _代表一个字符 ^代表字符前 $代表字符后 pg的官网上对其用法是下面这样,但不够清楚了然,下面我一一解释 第一个 用法和substr差不多,是指定序列,fro

  • postgresql 实现字符串分割字段转列表查询

    postgresql 实现字符串分割字段转列表查询

    在数据查询中,有一张a表存有另一张b表的id并以','隔开 如: 假设现在要关联查询关于 b表的一些信息,怎么办. 分割查询:字符串转列表函数 :regexp_split_to_table() select * from regexp_split_to_table ((select product_ids from fee_project_meal where id = 116199376233182210 ), ',') 查询后,字符串就变成了列表,然后你就可以根据这个列表去找b表的相关信息了

  • postgresql 查询集合结果用逗号分隔返回字符串处理的操作

    postgresql 查询集合结果用逗号分隔返回字符串处理的操作

    关键字: string_agg('' , '') 例如: select string_agg(name||'' , ',') from sys_user 补充:PostgreSQL 字段用逗号 ","隔开 判断是否含有某个值 Array Functions and Operators https://www.postgresql.org/docs/9.2/functions-array.html -- ---------------------------- -- Table stru

  • Postgresql 截取字符串的案例

    截取字符串一般用 substring 就够用了.对于有些长度不定的就没法用这个函数了,但还是有规律的,可以某个字符分割. 如:(这是一个url,截取最后一部分.现在要取 - 后面部分内容) 8a59e88177ad5e70-20170317120301AB9E.plist 12b8d5c26d83a17f-20170308173008D5DD.plist 这时需要 split_part 函数,如: -- url 一共3个 - ,字符串被分成4部分,取最后一部分,那最后一个参数就是4 select

  • postgresql 中position函数的性能详解

    postgresql 中position函数的性能详解

    起因: postgresql中position函数提供从头查找返回第一个匹配到字符串的下标. 而我需要返回从后向前查找第一个匹配到的坐标,但是postgressql并未提供相关函数,所以自己写了如下代码提供相关功能: CREATE OR REPLACE FUNCTION lastindexof(text, character) RETURNS integer AS $BODY$ begin if $1 is null then return NULL; end if; for i in reve

  • Java 中synchronize函数的实例详解

    Java 中synchronize函数的实例详解 java中的一个类的成员函数若用synchronized来修饰,则对应同一个对象,多个线程像调用这个对象的这个同步函数时必须等到上一个线程调用完才能由下一个线程调用. 那么如果一个类同时有两个成员函数是由synchronized修饰如代码所示,对与同一个对象,是否可以在两个线程运行时,一个调用funcA,同时另一个调用funcB? Mysyn是这样一个类,如果我有两个线程,一个在run方法中先运行funcA再运行funcB,另一个线程在run方法

  • 对pandas中apply函数的用法详解

    最近在使用apply函数,总结一下用法. apply函数可以对DataFrame对象进行操作,既可以作用于一行或者一列的元素,也可以作用于单个元素. 例:列元素 行元素 列 行 以上这篇对pandas中apply函数的用法详解就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们. 您可能感兴趣的文章: 浅谈Pandas中map, applymap and apply的区别

  • mysql中find_in_set()函数的使用详解

    首先举个例子来说: 有个文章表里面有个type字段,它存储的是文章类型,有 1头条.2推荐.3热点.4图文等等 . 现在有篇文章他既是头条,又是热点,还是图文,type中以 1,3,4 的格式存储.那我们如何用sql查找所有type中有4的图文类型的文章呢?? 这就要我们的 find_in_set 出马的时候到了.以下为引用的内容: select * from article where FIND_IN_SET('4',type) --------------------------------

  • PyTorch中topk函数的用法详解

    听名字就知道这个函数是用来求tensor中某个dim的前k大或者前k小的值以及对应的index. 用法 torch.topk(input, k, dim=None, largest=True, sorted=True, out=None) -> (Tensor, LongTensor) input:一个tensor数据 k:指明是得到前k个数据以及其index dim: 指定在哪个维度上排序, 默认是最后一个维度 largest:如果为True,按照大到小排序: 如果为False,按照小到大排序

  • 对tensorflow 中tile函数的使用详解

    tensorflow中tile是用来复制tensor的指定维度,具体看下面的代码: import tensorflow as tf a = tf.constant([[1, 2], [3, 4], [5, 6]], dtype=tf.float32) a1 = tf.tile(a, [2, 2]) with tf.Session() as sess: print(sess.run(a1)) 结果就是: [[ 1. 2. 1. 2.] [ 3. 4. 3. 4.] [ 5. 6. 5. 6.] [

  • Python-re中search()函数的用法详解(查找ip)

    Python-re中search()函数的用法详解(查找ip)

    1.首先来看一下search()和find()的区别 import re s1 = "2221155" #search 字符串第一次出现的位置 print(re.search("1",s1)) print(s1.find("1")) 它们的输出分别是: search(): 函数返回的是查找的字符在字符串中的位置 find(): 函数返回的也是在字符串中的位置 目前来看两者并没有什么区别,但是**search()**函数的高级操作还在后边哦! 2.

  • PHP中断言函数的使用详解

    原来一直以为断言相关的函数是 PHPUnit 这些单元测试组件提供的,在阅读手册后才发现,这个 assert() 断言函数是 PHP 本身就自带的一个函数.也就是说,我们在代码中进行简单的测试的时候是不需要完全引入整个单元测试组件的. assert() 断言函数 assert(1==1); assert(1==2); // assert.exception = 0 时,Warning: assert(): assert(1 == 2) // assert.exception = 1 时,Fata

  • MATLAB中print函数使用示例详解

    目录 语法 说明 示例 打印图窗纸张副本 将图窗复制到剪贴板 将图窗另存为图像文件 将图窗另存为向量图形文件 向 EPS 文件添加 TIFF 预览 指定要保存的图窗 以屏幕大小和分辨率保存图窗 保存填满页面的图窗 保存图窗而不保存 UIControl 返回图窗的 RGB 图像数据 创建高分辨率影片帧 print函数的功能是打印图窗或保存为特定文件格式. 语法 print(filename,formattype) print(filename,formattype,formatoptions) p

  • Python3.2中Print函数用法实例详解

    本文实例讲述了Python3.2中Print函数用法.分享给大家供大家参考.具体分析如下: 1. 输出字符串 >>> strHello = 'Hello World' >>> print (strHello) Hello World 2. 格式化输出整数 支持参数格式化,与C语言的printf类似 >>> strHello = "the length of (%s) is %d" %('Hello World',len('Hello

随机推荐