一個提升PostgreSQL性能的小技巧

更新時間：2015年04月21日 12:03:20 投稿：goldensun

這篇文章主要介紹了一個提升Postgres性能的小技巧,通過修改很少的代碼來優(yōu)化查詢,需要的朋友可以參考下

在一個（差）的PostgreSQL 查詢中只要一個小小到改動(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查詢時間從20s縮減到0.2s。從最簡單的學習使用 EXPLAIN ANALYZE開始，到學習使用 Postgres community 大量學習時間的投入將有百倍時間到回報。

使用Postgres監(jiān)測慢的Postgres查詢

在這周早些時候，一個用于我們的圖形編輯器上的小表（10GB，1500萬行）的主鍵查詢，在我們的一個（多個）數(shù)據(jù)庫上發(fā)生來大的查詢性能問題。

99.9%到查詢都是非常迅速流暢的，但是在一些使用大量的枚舉值的地方，這些查詢會需要20秒?；ㄙM如此多到時間在數(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ù)乃饕＿@是一個非常簡單清晰的主鍵查詢。但有趣的是，當增加主鍵內容的數(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 .

2015421115721448.png (562×337)

在最低的層面，這些查詢看起來就像是這些CPU利用率的峰值。CPU圖很少有用，但是在這種條件下它證實了關鍵的一點：數(shù)據(jù)庫并沒有等待磁盤去讀取數(shù)據(jù)。它在做一些排序，哈希以及行比較之類的事情。

第二個有趣的度量，就是距離這些峰值很近的軌跡，它們是由Postgres“取得”的行數(shù)（本例中沒有返回，就看看再忽略掉吧）。

2015421115811688.png (563×338)

顯然有些動作在規(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=50919

Postgres 使用位圖掃描表C. 當主鍵的數(shù)據(jù)量小的時候，它能有效的使用索引在內存里建立位圖。如果位圖太大，最優(yōu)查詢計劃就改變查詢方式了。在我們這個查詢中，因為主鍵包含的數(shù)據(jù)量很大，所以查詢就使用最優(yōu)（系統(tǒng)自己判斷的）的方式去檢索查詢候選行，并且立即查詢所有和主鍵匹配的數(shù)據(jù)。就是這些¨放入內存¨和¨立即查詢¨花費太多的時間（查詢計劃中的Recheck Cond）。

幸好只有30%的數(shù)據(jù)被導入到內存中，所以還不至于像從硬盤里讀取那么壞。但它仍然對性能有非常明顯的影響。記住，查詢是非常簡單的。這是一個主鍵查詢所以沒有很多明了的方式來確定它有沒有戲劇性的重新架構數(shù)據(jù)庫或應用程序。PGSQL-Performance mailing list給予了我們很大的幫助.

解決方案

這是我們喜歡開源和喜歡幫助用戶的另外一個原因。Tom Lane是開源代碼作者中最盛產的程序員之一，他建議我們做如下嘗試：

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)化器充分使用關鍵字索引。僅僅是一行代碼的改變，并且沒有產生任何語義的改變。

下面是新查詢語句的寫法，差別就在于第三和第十四行。

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倍。

在生產中使用的新查詢

即將發(fā)布的一段代碼:
它使數(shù)據(jù)庫看起來更美觀輕松. 2015421115846694.png (565×341)