幾個月前 - 民眾 到 PostgreSQL。
從那時起，您已經使用它超過 6000 次，但其中一項可能未被注意到的便利功能是 結構線索，看起來像這樣：
傾聽他們的聲音，您的要求將“變得如絲般順滑”。 🙂
但嚴重的是，許多情況下請求在資源方面緩慢且“貪婪”， 具有典型性，可以通過計劃的結構和數據識別.
在這種情況下，每個開發人員都不必自己尋找優化選項，完全依靠自己的經驗——我們可以告訴他這裡發生了什麼，可能是什麼原因，以及 如何提出解決方案. 這就是我們所做的。
讓我們仔細看看這些案例——它們是如何定義的以及它們帶來了哪些建議。
為了更好地沉浸在主題中，您可以先從中收聽相應的塊 ，然後才去詳細分析每個例子：
#1：索引“欠排序”
什麼時候發生
顯示客戶“LLC Kolokolchik”的最後一張發票。
如何鑑別
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
建議
使用的索引 擴展排序字段.
示例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
你可以立即註意到索引減去了 100 多條記錄，然後將這些記錄全部排序，然後只剩下一條。
我們修復：
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
即使在這樣一個原始樣本上—— 讀取速度提高 8.5 倍，讀取次數減少 33 倍. 效果會更清晰，每個值的“事實”越多。 fk.
我注意到這樣的索引將作為“前綴”索引使用，不會比其他查詢的前一個索引差 fk, 排序依據 pk 過去不是現在也不是（你可以閱讀更多關於這個 ). 特別是，它將提供正常的 顯式外鍵支持 通過這個領域。
#2：索引交集（BitmapAnd）
什麼時候發生
顯示代表“NJSC Lyutik”為客戶“LLC Kolokolchik”簽訂的所有合同。
如何鑑別
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scan建議
創建 綜合指數 按來自兩個源的字段或從第二個擴展現有字段之一。
示例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
我們修復：
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
此處增益較小，因為位圖堆掃描本身非常有效。 但不管怎麼說 讀取速度提高 7 倍，讀取次數減少 2.5 倍.
#3：合併索引（BitmapOr）
什麼時候發生
顯示前 20 個最舊的“自己的”或未分配的處理請求，優先處理自己的請求。
如何鑑別
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scan建議
使用 聯盟[全部] 為每個條件 OR 塊組合子查詢。
示例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
我們修復：
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
我們利用了第一個塊中立即獲得所有 20 條必要記錄的事實，所以第二個具有更“昂貴”的位圖堆掃描的記錄甚至沒有被執行——結果 速度提高 22 倍，讀取次數減少 44 倍!
關於這種優化方法的更詳細的故事 具體例子 可以在文章中閱讀 и .
通用版 通過幾個鍵有序選擇 （而不僅僅是一對 const / NULL）在文章中討論 .
#4：我們讀得太多了
什麼時候發生
通常，當您想要“附加另一個過濾器”到現有請求時，它就會發生。
“而你沒有相同的，但是 帶珍珠鈕扣“？ 電影《鑽石之手》
例如，修改上面的任務，顯示前 20 個最舊的“關鍵”處理請求，無論它們的目的如何。
如何鑑別
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
建議
創造 [更多] 專業 帶 WHERE 子句的索引 或在索引中包含其他字段。
如果過濾條件對於您的任務是“靜態的”——即 不包括擴展 未來的值列表 - 最好使用 WHERE 索引。 各種布爾/枚舉狀態很適合這一類。
如果過濾條件 可以取不同的值，最好用這些字段擴展索引——就像 Bitmap 和上面的情況一樣。
示例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
我們修復：
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
如您所見，計劃中的過濾完全沒有了，請求變成了 快 5 倍.
#5：稀疏表
什麼時候發生
各種嘗試讓自己的任務處理隊列，當大量更新/刪除表上的記錄導致出現大量“死”記錄的情況。
如何鑑別
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
建議
定期手動執行 真空[滿] 或達到足夠頻繁的處理 通過微調其參數，包括 .
在大多數情況下，此類問題是由業務邏輯調用時查詢佈局不佳引起的，例如在 .
但是我們必須明白，即使是 VACUUM FULL 也不能總是提供幫助。 對於這種情況，您應該熟悉本文中的算法。 .
#6：從索引的“中間”讀取
什麼時候發生
看起來他們讀了一點，所有的東西都被索引了，他們沒有額外過濾任何人——但是，閱讀的頁面仍然比我們想要的多得多。
如何鑑別
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
建議
仔細查看所用索引的結構和查詢中指定的關鍵字段 - 很可能， 未設置索引部分. 您很可能需要創建一個類似的索引，但沒有前綴字段，或者 .
示例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
一切似乎都很好，即使就索引而言，但有些可疑 - 對於讀取的 20 條記錄中的每一條，必須減去 4 頁數據，每條記錄 32KB - 這不是大膽的嗎？ 是和索引名稱 tbl_fk_org_fk_cli_idx 導致思想。
我們修復：
CREATE INDEX ON tbl(fk_cli);
突然 - 閱讀速度提高 10 倍，閱讀時間減少 4 倍!
有關索引使用效率低下的更多示例，請參閱文章 .
#7：CTE×CTE
什麼時候發生
應要求 得分“胖”CTE 來自不同的表，然後決定在它們之間做 JOIN.
該案例與低於 v12 的版本或帶有 WITH MATERIALIZED.
如何鑑別
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
建議
仔細分析請求 ？ 如果是，那麼 在 hstore/json 中應用“字典” 根據描述的模型 .
#8：交換到磁盤（臨時寫入）
什麼時候發生
大量記錄的一次性處理（排序或唯一化）不適合為此分配的內存。
如何鑑別
-> *
&& temp written > 0建議
如果操作使用的內存量沒有大大超過參數的設置值 , 應該糾正。 你可以立即在每個人的配置中，或者你可以通過 SET [LOCAL] 對於特定的請求/交易。
示例：
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
我們修復：
SET work_mem = '128MB'; -- перед выполнением запроса
出於顯而易見的原因，如果只使用內存而不使用磁盤，那麼查詢的執行速度會快得多。 同時，部分負載也從硬盤上卸下。
但是你需要明白，分配大量內存也總是行不通的——這對每個人來說都是不夠的。
#9：不相關的統計數據
什麼時候發生
一下子往基地裡倒了很多，但他們沒有時間把它趕走 ANALYZE.
如何鑑別
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10建議
花同樣的錢 ANALYZE.
這種情況在中有更詳細的描述 .
＃10：“出了點問題”
什麼時候發生
有鎖在等待競爭請求，或者沒有足夠的 CPU/管理程序硬件資源。
如何鑑別
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
建議
使用外部 監視系統 服務器阻塞或異常資源消耗。 我們已經討論過我們為數百台服務器組織此過程的版本。 и .
來源： www.habr.com