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類似的集合

以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。