PostgreSQL pg_qualstats 解決索引缺失的方法

這篇文章主要講解了“PostgreSQL  pg_qualstats 解決索引缺失的方法”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“PostgreSQL  pg_qualstats 解決索引缺失的方法”吧！

商業數據庫中，很多新版本都可以自動創建索引，給出索引創建的建議，并且以此作為賣點，ORACLE ，SQL SERVER 均有類似的功能，實際上通過查詢語句，與全表掃描的語句，與謂詞的比對，做出這樣的系統其實不不是一件很難的事情。

我們下面采用PG11 的版本來進行相關的工作,(安裝中遇到很多問題，最終還算解決了)

1 需要安裝 PGDG的repo 環境，這樣能節省你很多的安裝中的麻煩以及依賴包問題。具體請移步PGDG中查看對應你版本的安裝信息，并且安裝 pg-devel環境

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

也可以下載源碼包，進行編譯安裝，這里就省略了安裝的過程。

最終的效果應該在配置文件中，添加pg_stat_statements 和 pg_qualstats 以及創建 extension   pg_qualstats  pg_stat_statements 這2個插件。

首先我們要知道 pg_qualstats 到底能做什么，pg_qualstats是一個PostgreSQL擴展，用于保存“WHERE”語句和“JOIN”子句中謂詞的統計信息。

看上圖可以看到相關 pg_qualstats 的變量

另外需要將pg_qualstats.track_constants 關掉，否則會收集很多類似的查詢占用相關的存儲位置。

我們可以寫一個存儲過程，批量運行一些查詢語句，在執行完畢后，我們可以通過 pg_qualstats_indexes來查看當前查詢中的謂詞，并且這些謂詞是沒有索引的，以及查詢的次數。

同時根據 pg_qualstats_indexes_ddl 表可以看到 pg_qualstats 推薦你需要建立的索引，（因為PG支持的索引多，所以提供了一種索引需求的多種建立方案）

故事到這里看似完了，其實不然。我們先的說說這個插件是從何而來，去往何處，在哪里打間，在哪里住店.....

實際上是有一個程序的組建，powa ，通過這個組建本身是可以動態，WEB化查詢系統中缺失的索引，并給出相關信息的。這里我們僅僅是借用了這個軟件的一部分，也可以說叫 client。我們使用的這個插件僅僅是負責收集信息使用的。但其實對我們來說，已經足夠了，如果你有幾十臺的PG 到是可以進一步的安裝這個軟件，來講所有的PG 囊獲其中。軟件的名字叫 PostgreSQL Workload Analyzer。

使用了這個插件后能回答你對系統的幾個問題

平時系統是怎么進行查詢的，經常查詢的語句是什么，查詢中同一個查詢不同的值的分布式怎樣的，那些列會經常在一起查詢。

實際上我們可以問自己幾個問題，我為什么要用這個軟件，會使用這個插件，并且通過他來建立一些索引，自然是好的，但這也透露出一個問題，開發在開發系統的時候，并不知道自己的SQL 語句，或無法提供，并且DBA 也在系統上線前對此一無所知，這其實就是一個BUG，而通過這個工具來彌補，那只能是虎狼の藥。另外的就是不要迷信這個軟件，認為頭痛醫頭，腳痛醫腳，其實病根在心。（以上啰嗦幾句，不感興趣的客官可以移步 NEXT STATION）

我們回來看看這個插件里面的一些表

1  pg_qualstats 這個表本身包含了執行語句的用戶，表所處的數據庫是那個，以及表名（可以通過和其他表連接后獲得相關信息），另外關鍵的execution_count 和 nbfiltered 這里面的意思是這個語句執行了多少次，并且多少次是重復的，另外也包含的queryid, 可以追溯你的查詢語句。

2 

select * from pg_qualstats_by_query ;

這個表重要的地方在于queryid 通過這個表才能關聯你要的查詢語句，最后通過關聯其他表將其顯示出來

3 

 select * from pg_qualstats_indexes  這個表也是關鍵，他給出了你查詢中需要建立索引的建議

另外還有一些其他的表，這里就不在介紹了，直接給一些自己寫的列子來闡述一下這個工具到底可以解決實際什么問題

1  如何確認語句運行的時間，下面這個SQL 可以定期的運行，來獲取系統中運行的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 可以查看到底那個表上需要建立什么樣的索引，配合上面的表可以通過查詢語句來確認添加索引的正確性。

最后說說他的想法是什么

第一步是獲取查詢中所有的謂詞，并分析這個查詢中提取的謂詞是否有益于查詢，這個信息存儲在pg_qualstats中，在這其中會去重一些同樣的語句，但會記錄相關的次數，當然這其中也和查詢的方式有關，如果你是多個條件加and的操作，則這些條件會進行記錄。根據查詢的次數，和頻繁度，查詢數據的分布，等推薦需要建立的索引的方式。最終生成相關的DDL 語句。

感謝各位的閱讀，以上就是“PostgreSQL  pg_qualstats 解決索引缺失的方法”的內容了，經過本文的學習后，相信大家對PostgreSQL  pg_qualstats 解決索引缺失的方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！