優(yōu)化PG查詢：一問一答
正文
Q1：是否有普羅米修斯exporter，你知道普羅米修斯監(jiān)控PG的原生選項嗎衫画？
可以使用Postgres Exporter采集PG的各種指標(biāo)，并將其發(fā)送給普羅米修斯皮壁。更多詳細(xì)信息參考：
https://github.com/prometheus-community/postgres_exporter
Q2：能否監(jiān)控預(yù)定義日期范圍內(nèi)來自某個IP的所有查詢笼沥？我們需要找出哪個查詢正在加載服務(wù)器
可以使用pg_stat_activity視圖監(jiān)控來自某個IP的查詢：
SELECT query_start,now() AS CURRENT_TIME,query
FROM pg_stat_activity
WHERE client_addr=’your_ip_addr’?
ORDER BY now()-query_start DESC;
上面的查詢可以幫助我們找出來自某個IP的最長查詢。然而這些文本可能不夠完整窜骄。強(qiáng)烈推薦使用pg_stat_statements锦募、pg_stat_kcache、pg_profile插件獲取完整內(nèi)容邻遏。通過這些插件可以在業(yè)務(wù)應(yīng)用中找到長查詢的指定部分糠亩。
Q3：Grafana儀表板上推薦顯示哪些參數(shù)？是否可以提供一個准验？
postgres_exporter有很多有意義的指標(biāo)赎线，例如連接統(tǒng)計：
每秒的事務(wù)和查詢數(shù)：
每個事務(wù)的WAL大小：
后臺工作進(jìn)程糊饱，例如autovacuum worker
鎖統(tǒng)計：
shared_buffers使用率統(tǒng)計
Checkpoint統(tǒng)計：
查詢執(zhí)行的統(tǒng)計：
Q4：可以推薦一個開源的paid工具展示執(zhí)行計劃嗎垂寥？
可以使用以下開源模塊：
auto_explain將最長的查詢計劃寫入日志文件
pg_store_plan采集執(zhí)行計劃和參數(shù)
https://explain.depesz.com/ 用于可視化執(zhí)行計劃和發(fā)現(xiàn)查詢熱點(diǎn)
Postgrespro的客戶可以使用pgpro_stats模塊采集查詢計劃，但是計劃里面沒有參數(shù)值另锋。
Q5：在我們自己的數(shù)據(jù)庫上有現(xiàn)成的playgroud用于做學(xué)習(xí)訓(xùn)練嗎滞项？
可以使用我們的demo數(shù)據(jù)庫：https://edu.postgrespro.com/demo-big-en.zip
另外有本書可參考：https://edu.postgrespro.ru/introbook_v6_en.pdf
Q6：可以提供一些TCP測試鏈接嗎？
所有TPC測試都是在各種客戶審核期間進(jìn)行夭坪，每辦法發(fā)布文判。但可以使用JMeter工具構(gòu)建自己的測試，完成后室梅，可以獲得類似內(nèi)容：
Q7：哪些指標(biāo)可以告訴我們服務(wù)器配置錯誤戏仓？
[if !supportLists]1)?[endif]可以使用前面介紹的checkpoint統(tǒng)計來多個檢查點(diǎn)。這個案例中亡鼠，可以調(diào)整max_wal_size和min_wal_size參數(shù)赏殃。
[if !supportLists]2)?[endif]后臺worker進(jìn)程統(tǒng)計中，展示了autovacuum worker情況拆宛，可以通過autovacuum_max_workers調(diào)整：
autovacuum_max_workers=NCores/4..2嗓奢，其中NCores是CPU總核數(shù)
autovacuum_vacuum_cost_limit?= 200 * (autovacuum_max_workers / 3)
[if !supportLists]3)?[endif]shared_buffers使用率中，可以調(diào)整shared_buffers配置浑厚。
Q8：PG11中查詢執(zhí)行發(fā)現(xiàn)計劃時間占90%股耽，執(zhí)行時間僅占10%根盒。查詢使用的分區(qū)表，此問題是否有其他解決方案物蝙，或需要遷移到主版本炎滞？
PG12或者高版本扳埂，在patition_pruning機(jī)制上有很大提升趋急，簡化了查詢計劃的處理以及查詢時僅檢查很少的分區(qū)同欠。因此推薦升級PG版本茅逮。
Q9：EXISTS謂語和IN運(yùn)算符在性能方面有什么區(qū)別？
在編寫查詢時珍策，可以假設(shè)EXISTS將提供更好的結(jié)果劝萤，因?yàn)樗梢允褂盟羞壿嫼蛢?yōu)化來連接兩個表吵血，而IN運(yùn)算符將使用子計劃票堵。有趣的時扼睬，從PG10開始計劃者對于這兩個選項可能會產(chǎn)生相同結(jié)果。
然而悴势，在考慮NOT EXISTS和NOT IN場景中窗宇，NOT IN會產(chǎn)生SubPlans，當(dāng)處理大型數(shù)據(jù)集時造成瓶頸特纤。NOT EXISTS子句反而會導(dǎo)致anti join军俊，不會產(chǎn)生SubPlans。
EXISTS子句要求Planner在主連接前評估唯一行數(shù)捧存。如果數(shù)據(jù)集來自CTE物化粪躬，則無法使用統(tǒng)計數(shù)據(jù)進(jìn)行評估，因此可能導(dǎo)致不合適的執(zhí)行計劃矗蕊。因此在這種情況下建議謹(jǐn)慎使用短蜕。
表列和常量列進(jìn)行比較時，也可以使用IN運(yùn)算符傻咖。在PG14前，有一種線性搜索岖研，如果使用許多常量卿操，可能會導(dǎo)致性能不佳。從PG14開始孙援，將提供哈希查找害淤。
Q10：如何監(jiān)控vacuum進(jìn)程？如何調(diào)優(yōu)拓售？有什么推薦
沒有autovacuum的話數(shù)據(jù)庫中將有很多老版本記錄窥摄，造成表膨脹。例如础淤，pg_profile可以監(jiān)控某個時間段：
DML操作最多的表.
更新/刪除操作最多的表.
增長最快的表.
增長最快的索引.
Vacuum操作最多的表.
analyze 操作最多的表.
死元組率最多的表.
更新元組率最多的表.
vacuum I/O load最多的索引.
[if !supportLists]1）[endif]autovacuum_naptime應(yīng)該減小到20秒崭放，因?yàn)?分鐘太多了
[if !supportLists]2）[endif]autovacuum_max_workers通用公式：
???autovacuum_max_workers = NCores/4..2哨苛，其中NCores為CPU核數(shù)
需要確保autovacuum_vacuum_cost_limit 參數(shù)也需要調(diào)整：
???autovacuum_vacuum_cost_limit = 200 * (autovacuum_max_workers / 3)
[if !supportLists]3）[endif]推薦autovacuum_vacuum_scale_factor 為0.02.如果表中死元組率大于2%，那么
autovacuum會自動進(jìn)行處理币砂。
[if !supportLists]4）[endif]也推薦autovacuum_analyze_scale_factor 為0.05建峭，如果表中更改的元組率大于5%，autovacuum worker會采集統(tǒng)計信息以便planner使用决摧。
[if !supportLists]5）[endif]PG13可以調(diào)整autovacuum_vacuum_insert_scale_factor 亿蒸，處理append-only表，以阻止回卷問題掌桩。
[if !supportLists]6）[endif]如果autovacuum_work_mem是-1边锁，會使用maintenance_work_mem 值，作為起始值考慮將其增加到1GB
[if !supportLists]7）[endif]pg_stat_progress_vacuum 視圖實(shí)時顯示vacuum工作
[if !supportLists]8）[endif]使用ALTER TABLE tb_name SET（param_name=param_value）用于對指定表調(diào)整autovacuum_vacuum_scale_factor, autovacuum_analyze_scale_factor
[if !supportLists]9）[endif]避免長查詢和長事務(wù)（包括空閑事務(wù)）波岛，因?yàn)闀柚箘h除舊元組茅坛。這樣就會產(chǎn)生大量膨脹表，帶來沉重的IO負(fù)載
[if !supportLists]10）[endif]Autovacuum worker從索引和對應(yīng)表中清除死元組盆色。pg_profile報告中“Top indexes by estimated vacuum IO load”可以顯示索引如何影響autovacuum進(jìn)程灰蛙。在某些情況下，它可能會運(yùn)行很長時間隔躲，因?yàn)橛性S多龐大的索引需要清理摩梧。如果是這種情況，考慮將表劃分為較小的分區(qū)宣旱〗龈福可以參考：
Q11：是否有pg_stat_kcache的使用文檔？
https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_stat_kcache.html
https://github.com/powa-team/pg_stat_kcache
Q12：在列上創(chuàng)建索引后浑吟，仍使用順序掃描笙纤，怎么才能繞過？
很大程度上取決于查詢组力。也許省容，它從收集了75%的行，因此由于大量的隨機(jī)訪問開銷燎字，索引掃描沒有意義腥椒。如果查詢需要幾個列，考慮創(chuàng)建INCLUDE索引候衍，以index-only掃描使用笼蛛。
核心原因可能與索引不包括過濾字段這一事實(shí)有關(guān)。即使這樣蛉鹿，這些字段也不可能處于leading位置滨砍，因此這樣的索引掃描是低效的。
如果查詢使用LIKE操作符，確保使用合適的操作符類如text_pattern_ops惋戏、varchar_pattern_ops等领追。
Q13：在讀取性能測試期間，檢測到數(shù)據(jù)庫中某些寫入操作日川，原因是什么蔓腐？如何預(yù)防？
可能涉及臨時文件的生成龄句。當(dāng)內(nèi)部后端內(nèi)存不足回论，無法對大型數(shù)據(jù)集進(jìn)行排序或無法保存CTE的查詢結(jié)果時，PG開始將數(shù)據(jù)寫入到磁盤的臨時文件中分歇。此外傀蓉，由于不正確的終止語句，可能面臨無限遞歸查詢职抡。您可以使用pg_profile部分“Top SQL by temp usage”來監(jiān)視這些查詢葬燎，并對其進(jìn)行調(diào)優(yōu)。
Q14：PG中如何skip scan缚甩？是否和Oracle中的skip scan匹配
PG原生不支持index skip scan谱净，但這項工作正在進(jìn)行中：
https://commitfest.postgresql.org/19/1741/
可以使用遞歸CTE模擬index skip scan：
https://wiki.postgresql.org/wiki/Loose_indexscan
Q15：有關(guān)于如何啟用上述擴(kuò)展的文檔嗎？
pg_stat_statements和auto_explain模塊在標(biāo)準(zhǔn)PG分支中擅威，因此可在官方手冊中查看使用方法：
https://www.postgresql.org/docs/13/pgstatstatements.html
https://www.postgresql.org/docs/13/auto-explain.html
pg_stat_kcache模塊由PG repositories提供：
http://apt.postgresql.org/pub/repos/apt/pool/main/p/pg-stat-kcache/
pg_wait_sampling模塊也由PG repositories提供：
http://apt.postgresql.org/pub/repos/apt/pool/main/p/pg-wait-sampling/
pg_profile模塊可以在github中查看：
https://github.com/zubkov-andrei/pg_profile
原文
https://postgrespro.com/blog/company/5968040
