PostgreSQLpg_qualstats解決索引缺失的方法
這篇文章主要講解了“PostgreSQL pg_qualstats 解決索引缺失的方法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“PostgreSQL pg_qualstats 解決索引缺失的方法”吧！
公司主營業(yè)務(wù)：成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)公司是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)公司推出崇川免費做網(wǎng)站回饋大家。
商業(yè)數(shù)據(jù)庫中，很多新版本都可以自動創(chuàng)建索引，給出索引創(chuàng)建的建議，并且以此作為賣點，ORACLE ，SQL SERVER 均有類似的功能，實際上通過查詢語句，與全表掃描的語句，與謂詞的比對，做出這樣的系統(tǒng)其實不不是一件很難的事情。
我們下面采用PG11 的版本來進行相關(guān)的工作,(安裝中遇到很多問題，最終還算解決了)
1 需要安裝 PGDG的repo 環(huán)境，這樣能節(jié)省你很多的安裝中的麻煩以及依賴包問題。具體請移步PGDG中查看對應(yīng)你版本的安裝信息，并且安裝 pg-devel環(huán)境
2 可以通過下載rpm包的方式安裝
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pg_qualstats11-1.0.9-1.rhel7.x86_64.rpm
也可以下載源碼包，進行編譯安裝，這里就省略了安裝的過程。
最終的效果應(yīng)該在配置文件中，添加pg_stat_statements 和 pg_qualstats 以及創(chuàng)建 extension pg_qualstats pg_stat_statements 這2個插件。
首先我們要知道 pg_qualstats 到底能做什么，pg_qualstats是一個PostgreSQL擴展，用于保存“WHERE”語句和“JOIN”子句中謂詞的統(tǒng)計信息。
看上圖可以看到相關(guān) pg_qualstats 的變量
另外需要將pg_qualstats.track_constants 關(guān)掉，否則會收集很多類似的查詢占用相關(guān)的存儲位置。
我們可以寫一個存儲過程，批量運行一些查詢語句，在執(zhí)行完畢后，我們可以通過 pg_qualstats_indexes來查看當(dāng)前查詢中的謂詞，并且這些謂詞是沒有索引的，以及查詢的次數(shù)。
同時根據(jù) pg_qualstats_indexes_ddl 表可以看到 pg_qualstats 推薦你需要建立的索引，（因為PG支持的索引多，所以提供了一種索引需求的多種建立方案）
故事到這里看似完了，其實不然。我們先的說說這個插件是從何而來，去往何處，在哪里打間，在哪里住店.....
實際上是有一個程序的組建，powa ，通過這個組建本身是可以動態(tài)，WEB化查詢系統(tǒng)中缺失的索引，并給出相關(guān)信息的。這里我們僅僅是借用了這個軟件的一部分，也可以說叫 client。我們使用的這個插件僅僅是負(fù)責(zé)收集信息使用的。但其實對我們來說，已經(jīng)足夠了，如果你有幾十臺的PG 到是可以進一步的安裝這個軟件，來講所有的PG 囊獲其中。軟件的名字叫 PostgreSQL Workload Analyzer。
使用了這個插件后能回答你對系統(tǒng)的幾個問題
平時系統(tǒng)是怎么進行查詢的，經(jīng)常查詢的語句是什么，查詢中同一個查詢不同的值的分布式怎樣的，那些列會經(jīng)常在一起查詢。
實際上我們可以問自己幾個問題，我為什么要用這個軟件，會使用這個插件，并且通過他來建立一些索引，自然是好的，但這也透露出一個問題，開發(fā)在開發(fā)系統(tǒng)的時候，并不知道自己的SQL 語句，或無法提供，并且DBA 也在系統(tǒng)上線前對此一無所知，這其實就是一個BUG，而通過這個工具來彌補，那只能是虎狼の藥。另外的就是不要迷信這個軟件，認(rèn)為頭痛醫(yī)頭，腳痛醫(yī)腳，其實病根在心。（以上啰嗦幾句，不感興趣的客官可以移步 NEXT STATION）
我們回來看看這個插件里面的一些表
1 pg_qualstats 這個表本身包含了執(zhí)行語句的用戶，表所處的數(shù)據(jù)庫是那個，以及表名（可以通過和其他表連接后獲得相關(guān)信息），另外關(guān)鍵的execution_count 和 nbfiltered 這里面的意思是這個語句執(zhí)行了多少次，并且多少次是重復(fù)的，另外也包含的queryid, 可以追溯你的查詢語句。
2
select * from pg_qualstats_by_query ;
這個表重要的地方在于queryid 通過這個表才能關(guān)聯(lián)你要的查詢語句，最后通過關(guān)聯(lián)其他表將其顯示出來
3
select * from pg_qualstats_indexes 這個表也是關(guān)鍵，他給出了你查詢中需要建立索引的建議
另外還有一些其他的表，這里就不在介紹了，直接給一些自己寫的列子來闡述一下這個工具到底可以解決實際什么問題
1 如何確認(rèn)語句運行的時間，下面這個SQL 可以定期的運行，來獲取系統(tǒng)中運行的SQL 以及每條SQL的平均運行時間。
with table_info as (select pc.relname,pgq.execution_count,pgq.uniquequalnodeid
from pg_qualstats as pgq
left join pg_class as pc on pgq.lrelid = pc.relfilenode),
query_info as (
select pss.total_time/calls as average_time,pss.query,pgb.uniquequalnodeid
from pg_qualstats_by_query as pgb
left join pg_stat_statements as pss on pgb.queryid = pss.queryid)
select *
from table_info as t
right join query_info as q on t.uniquequalnodeid = q.uniquequalnodeid
2
select pqi.relid,pqi.attnames,pqi.execution_count,pqd.idxtype,pqd.ddl
from pg_qualstats_indexes_ddl as pqd
left join pg_qualstats_indexes as pqi on pqd.relid = pqi.relid and pqd.attnames = pqi.attnames
上面這個SQL 可以查看到底那個表上需要建立什么樣的索引，配合上面的表可以通過查詢語句來確認(rèn)添加索引的正確性。
最后說說他的想法是什么
第一步是獲取查詢中所有的謂詞，并分析這個查詢中提取的謂詞是否有益于查詢，這個信息存儲在pg_qualstats中，在這其中會去重一些同樣的語句，但會記錄相關(guān)的次數(shù)，當(dāng)然這其中也和查詢的方式有關(guān)，如果你是多個條件加and的操作，則這些條件會進行記錄。根據(jù)查詢的次數(shù)，和頻繁度，查詢數(shù)據(jù)的分布，等推薦需要建立的索引的方式。最終生成相關(guān)的DDL 語句。
感謝各位的閱讀，以上就是“PostgreSQL pg_qualstats 解決索引缺失的方法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對PostgreSQL pg_qualstats 解決索引缺失的方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！
新聞標(biāo)題：PostgreSQLpg_qualstats解決索引缺失的方法
本文鏈接：http://www.jiaotiyi.com/article/jioohp.html