共計 4128 個字符，預計需要花費 11 分鐘才能閱讀完成。
本篇內(nèi)容介紹了“怎么提升 PostgreSQL 性能”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
使用 Postgres 監(jiān)測慢的 Postgres 查詢
在這周早些時候，一個用于我們的圖形編輯器上的小表（10GB，1500 萬行）的主鍵查詢，在我們的一個（多個）數(shù)據(jù)庫上發(fā)生來大的查詢性能問題。
99.9% 到查詢都是非常迅速流暢的，但是在一些使用大量的枚舉值的地方，這些查詢會需要 20 秒。花費如此多到時間在數(shù)據(jù)庫上，意味著使用者必須在瀏覽器面前等待圖形編輯器的響應。很明顯只因為這 0.01% 就會造成很不好到影響。
查詢和查詢計劃
下面是這個出問題的查詢
SELECT c.key,
c.x_key,
c.tags,
x.name
FROM context c
JOIN x
ON c.x_key = x.key
WHERE c.key = ANY (ARRAY[15368196, -- 11,000 other keys --)])
AND c.x_key = 1
AND c.tags @ ARRAY[E blah 表 X 有幾千行數(shù)據(jù)，表 C 有 1500 萬條數(shù)據(jù)。兩張表的主鍵值“key”都有適當?shù)乃饕＿@是一個非常簡單清晰的主鍵查詢。但有趣的是，當增加主鍵內(nèi)容的數(shù)量，如在主鍵有 11,000 個值的時候，通過在查詢語句上加上 EXPLAIN (ANALYZE, BUFFERS) 我們得到如下的查詢計劃。
Nested Loop (cost=6923.33..11770.59 rows=1 width=362) (actual time=17128.188..22109.283 rows=10858 loops=1)
Buffers: shared hit=83494
- Bitmap Heap Scan on context c (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
Recheck Cond: ((tags @ {blah} ::text[]) AND (x_key = 1))
Filter: (key = ANY ( {15368196,(a lot more keys here)} ::integer[]))
Buffers: shared hit=50919
- BitmapAnd (cost=6923.33..6923.33 rows=269 width=0) (actual time=132.910..132.910 rows=0 loops=1)
Buffers: shared hit=1342
- Bitmap Index Scan on context_tags_idx (cost=0.00..1149.61 rows=15891 width=0) (actual time=64.614..64.614 rows=264777 loops=1)
Index Cond: (tags @ {blah} ::text[])
Buffers: shared hit=401
- Bitmap Index Scan on context_x_id_source_type_id_idx (cost=0.00..5773.47 rows=268667 width=0) (actual time=54.648..54.648 rows=267659 loops=1)
Index Cond: (x_id = 1)
Buffers: shared hit=941
- Index Scan using x_pkey on x (cost=0.00..8.27 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=10858)
Index Cond: (x.key = 1)
Buffers: shared hit=32575
Total runtime: 22117.417 ms在結果的最底部你可以看到，這個查詢總共花費 22 秒。我們可以非常直觀的通過下面的 CPU 使用率圖觀察到這 22 秒的花費。大部分的時間花費在 Postgres 和 OS 上, 只有很少部分用于 I /O .
在最低的層面，這些查詢看起來就像是這些 CPU 利用率的峰值。CPU 圖很少有用，但是在這種條件下它證實了關鍵的一點：數(shù)據(jù)庫并沒有等待磁盤去讀取數(shù)據(jù)。它在做一些排序，哈希以及行比較之類的事情。
第二個有趣的度量，就是距離這些峰值很近的軌跡，它們是由 Postgres“取得”的行數(shù)（本例中沒有返回，就看看再忽略掉吧）。
顯然有些動作在規(guī)則的有條不紊的瀏覽過許多行：我們的查詢。
Postgres 的問題所在：位圖掃描
下面是行匹配的查詢計劃
Buffers: shared hit=83494
- Bitmap Heap Scan on context c (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
Recheck Cond: ((tags @ {blah} ::text[]) AND (x_key = 1))
Filter: (key = ANY ( {15368196,(a lot more keys here)} ::integer[]))
Buffers: shared hit=50919Postgres 使用位圖掃描表 C. 當主鍵的數(shù)據(jù)量小的時候，它能有效的使用索引在內(nèi)存里建立位圖。如果位圖太大，最優(yōu)查詢計劃就改變查詢方式了。在我們這個查詢中，因為主鍵包含的數(shù)據(jù)量很大，所以查詢就使用最優(yōu)（系統(tǒng)自己判斷的）的方式去檢索查詢候選行，并且立即查詢所有和主鍵匹配的數(shù)據(jù)。就是這些¨放入內(nèi)存¨和¨立即查詢¨花費太多的時間（查詢計劃中的 Recheck Cond）。
幸好只有 30% 的數(shù)據(jù)被導入到內(nèi)存中，所以還不至于像從硬盤里讀取那么壞。但它仍然對性能有非常明顯的影響。記住，查詢是非常簡單的。這是一個主鍵查詢所以沒有很多明了的方式來確定它有沒有戲劇性的重新架構數(shù)據(jù)庫或應用程序。PGSQL-Performance mailing list 給予了我們很大的幫助.
解決方案
這是我們喜歡開源和喜歡幫助用戶的另外一個原因。Tom Lane 是開源代碼作者中最盛產(chǎn)的程序員之一，他建議我們做如下嘗試：
SELECT c.key,
c.x_key,
c.tags,
x.name
FROM context c
JOIN x
ON c.x_key = x.key
WHERE c.key = ANY (VALUES (15368196), -- 11,000 other keys --)
AND c.x_key = 1
AND c.tags @ ARRAY[E blah把 ARRAY 改成 VALUES，你能指出他們的不同點嗎？
我們使用 ARRAY[…] 列舉出所有的關鍵字以用來查詢，但是這卻欺騙了查詢優(yōu)化器。然而 Values(…) 卻能夠讓優(yōu)化器充分使用關鍵字索引。僅僅是一行代碼的改變，并且沒有產(chǎn)生任何語義的改變。
下面是新查詢語句的寫法，差別就在于第三和第十四行。
Nested Loop (cost=168.22..2116.29 rows=148 width=362) (actual time=22.134..256.531 rows=10858 loops=1)
Buffers: shared hit=44967
- Index Scan using x_pkey on x (cost=0.00..8.27 rows=1 width=37) (actual time=0.071..0.073 rows=1 loops=1)
Index Cond: (id = 1)
Buffers: shared hit=4
- Nested Loop (cost=168.22..2106.54 rows=148 width=329) (actual time=22.060..242.406 rows=10858 loops=1)
Buffers: shared hit=44963
- HashAggregate (cost=168.22..170.22 rows=200 width=4) (actual time=21.529..32.820 rows=11215 loops=1)
- Values Scan on *VALUES* (cost=0.00..140.19 rows=11215 width=4) (actual time=0.005..9.527 rows=11215 loops=1)
- Index Scan using context_pkey on context c (cost=0.00..9.67 rows=1 width=329) (actual time=0.015..0.016 rows=1 loops=11215)
Index Cond: (c.key = *VALUES* .column1)
Filter: ((c.tags @ {blah} ::text[]) AND (c.x_id = 1))
Buffers: shared hit=44963
Total runtime: 263.639 ms查詢時間從 22000ms 下降到 200ms，僅僅一行代碼的改變效率就提高了 100 倍。
在生產(chǎn)中使用的新查詢
即將發(fā)布的一段代碼:
它使數(shù)據(jù)庫看起來更美觀輕松.
“怎么提升 PostgreSQL 性能”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實用文章！
