一個提升PostgreSQL效能的小技巧_資料庫其它

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

使用Postgres監測慢的Postgres查詢

在這周早些時候，一個用於我們的圖形編輯器上的小表（10GB，1500萬行）的主鍵查詢，在我們的一個（多個）資料庫上發生來大的查詢效能問題。

99.9%到查詢都是非常迅速流暢的，但是在一些使用大量的枚舉值的地方，這些查詢會需要20秒。花費如此多到時間在資料庫上，意味著使用者必須在瀏覽器面前等待圖形編輯器的響應。很明顯只因為這0.01%就會造成很不好到影響。

查詢和查詢計劃

下面是這個出問題的查詢
 

SELECT c.key,    c.x_key,    c.tags,    x.name FROM context c JOIN x  ON c.x_key = x.keyWHERE c.key = ANY (ARRAY[15368196, -- 11,000 other keys --)]) AND c.x_key = 1 AND c.tags @> ARRAY[E'blah'];

表X有幾千行資料，表C有1500萬條資料。兩張表的主索引值“key”都有適當的索引。這是一個非常簡單清晰的主鍵查詢。但有趣的是，當增加主鍵內容的數量，如在主鍵有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=32575Total runtime: 22117.417 ms

在結果的最底部你可以看到，這個查詢總共花費22秒。我們可以非常直觀的通過下面的CPU使用率圖觀察到這22秒的花費。大部分的時間花費在 Postgres和 OS 上, 只有很少部分用於I/O . 

 在最低的層面，這些查詢看起來就像是這些CPU利用率的峰值。CPU圖很少有用，但是在這種條件下它證實了關鍵的一點：資料庫並沒有等待磁碟去讀取資料。它在做一些排序，雜湊以及行比較之類的事情。

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

 顯然有些動作在規則的有條不紊的瀏覽過許多行：我們的查詢。
 
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. 當主鍵的資料量小的時候，它能有效使用索引在記憶體裡建立位元影像。如果位元影像太大，最優查詢計劃就改變查詢方式了。在我們這個查詢中，因為主鍵包含的資料量很大，所以查詢就使用最優（系統自己判斷的）的方式去檢索查詢候選行，並且立即查詢所有和主鍵匹配的資料。就是這些¨放入記憶體¨和¨立即查詢¨花費太多的時間（查詢計劃中的Recheck Cond）。

幸好只有30%的資料被匯入到記憶體中，所以還不至於像從硬碟裡讀取那麼壞。但它仍然對效能有非常明顯的影響。記住，查詢是非常簡單的。這是一個主鍵查詢所以沒有很多明了的方式來確定它有沒有戲劇性的重新架構資料庫或應用程式。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.keyWHERE c.key = ANY (VALUES (15368196), -- 11,000 other keys --) AND c.x_key = 1 AND c.tags @> ARRAY[E'blah'];

把ARRAY改成VALUES，你能指出他們的不同點嗎？

我們使用ARRAY[...]列舉出所有的關鍵字以用來查詢，但是這卻欺騙了查詢最佳化工具。然而Values(...)卻能夠讓最佳化器充分使用關鍵字索引。僅僅是一行代碼的改變，並且沒有產生任何語義的改變。

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

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=44963Total runtime: 263.639 ms

查詢時間從22000ms下降到200ms，僅僅一行代碼的改變效率就提高了100倍。

在生產中使用的新查詢

即將發布的一段代碼:
它使資料庫看起來更美觀輕鬆. 

 第三方工具

postgres慢查詢不存在了。但是有誰樂意被0.1%不幸的少數折磨。要立即驗證修改查詢的影響，就需要Datadog來協助我們判斷修改是否是正確的。

如果你想要找出對Postgres查詢改變的影響，可能需要幾分鐘來註冊一個免費的Datadog帳號。