Postgresql 查詢中的特異功能是什么

這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)Postgresql 查詢中的特異功能是什么，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

本來上次是寫過這個PostgreSQL 的功能的，但上次在一個論壇里面發(fā)現(xiàn)其實大家對這個功能認(rèn)識上是有誤區(qū)的，所示這次是的詳細(xì)的一次文字。

功能很簡單的就是模糊查詢，類似 select * from table where column1 like ‘%PG牛逼%’；然后走一個靠譜的索引的查詢，ORACLE 打死他都不行，當(dāng)然可以走全文索引，那MYSQL, SQL SERVER 人家也不干，人家也有這功能。

pg_trgm本身是不包含在PostgreSQL 的源碼安裝中的，當(dāng)然是插件的方式安裝，安裝上是很簡單的，具體請百度（弄濕了我可不管）

進(jìn)入到你的數(shù)據(jù)庫，create extension pg_trgm；就OK 了

我在論壇中發(fā)現(xiàn)的第一個問題，是說建立這樣的模糊查詢，僅僅建立btree 索引就可以了，但pg_trgm 只支持兩種索引Gist and Gin, 這兩種索引。（這可不是我說的，官方的白紙黑字）

所以說正確的針對一個列的索引，是要建立兩個索引的，一個是BTREE 索引，一個是 GIN 或 Gist 索引，兩種索引面對的“客戶”是不同的。

那我們來看看到底他是怎么工作的

首先我們先生成一個表用來測試

創(chuàng)建一個存儲過程用來插入測試數(shù)據(jù)  

create or replace function data_produce(int) returns text as $$    

declare    

  res text;    

begin    

  if $1 >=1 then    

    select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);    

    return res;    

  end if;    

  return null;    

end;    

$$ language plpgsql strict; 

insert into test_pg_trgm (search) select data_produce(20) from generate_series(1,1110000);

執(zhí)行后生成我們本次要測試的數(shù)據(jù)10萬條

下面我們創(chuàng)建索引了，創(chuàng)建GIN 索引 

創(chuàng)建索引中系統(tǒng)報錯，這是由于還沒有創(chuàng)建相關(guān)的擴(kuò)展

添加了這些擴(kuò)展后我們就可以建立相關(guān)的索引

我們可以看到查詢已經(jīng)走了索引，并且查詢時間1ms

那如果我們沒有這個索引會怎么樣，這條語句慢了 48倍并且只能和ORACLE  SQL SERVER , MYSQL一樣走了全表掃描。

OK 如果已經(jīng)體會到了PG 在模糊查詢中的厲害之處，群里有人問的第二個問題是 GIN VS GIST  那種索引更好

這也是一個熱門的問題？

下面也做一個測試，（但不證明GIN 比 GIST 性能強），我們建立一個gist的索引，也提通過查詢來進(jìn)行模糊方式的查詢

圖中的時間 12ms ，比全表掃描快了4倍，比GIN 慢了12倍

當(dāng)然這里并不是說 GIST 不如GIN ，具體的索引有不同的使用場景。（做人辦事都的客觀）

最后，我們來證明一下，普通的運算方式對于GIST GIN 索引是無效的，所以我們對某個字段必須建立兩個索引 BTREE AND  GIST OR GIN。

下圖整體的證明了上面的立論。這里就不解釋了

上述就是小編為大家分享的Postgresql 查詢中的特異功能是什么了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。