在一個(gè)（差）的PostgreSQL 查詢中只要一個(gè)小小到改動(dòng)(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查詢時(shí)間從20s縮減到0.2s。從最簡(jiǎn)單的學(xué)習(xí)使用 EXPLAIN ANALYZE開(kāi)始，到學(xué)習(xí)使用 Postgres community 大量學(xué)習(xí)時(shí)間的投入將有百倍時(shí)間到回報(bào)。
使用Postgres監(jiān)測(cè)慢的Postgres查詢
在這周早些時(shí)候，一個(gè)用于我們的圖形編輯器上的小表（10GB，1500萬(wàn)行）的主鍵查詢，在我們的一個(gè)（多個(gè)）數(shù)據(jù)庫(kù)上發(fā)生來(lái)大的查詢性能問(wèn)題。
99.9%到查詢都是非常迅速流暢的，但是在一些使用大量的枚舉值的地方，這些查詢會(huì)需要20秒。花費(fèi)如此多到時(shí)間在數(shù)據(jù)庫(kù)上，意味著使用者必須在瀏覽器面前等待圖形編輯器的響應(yīng)。很明顯只因?yàn)檫@0.01%就會(huì)造成很不好到影響。
查詢和查詢計(jì)劃
下面是這個(gè)出問(wèn)題的查詢
復(fù)制代碼 代碼如下:
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萬(wàn)條數(shù)據(jù)。兩張表的主鍵值“key”都有適當(dāng)?shù)乃饕＿@是一個(gè)非常簡(jiǎn)單清晰的主鍵查詢。但有趣的是，當(dāng)增加主鍵內(nèi)容的數(shù)量，如在主鍵有11,000個(gè)值的時(shí)候，通過(guò)在查詢語(yǔ)句上加上 EXPLAIN (ANALYZE, BUFFERS)我們得到如下的查詢計(jì)劃。
復(fù)制代碼 代碼如下:
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
在結(jié)果的最底部你可以看到，這個(gè)查詢總共花費(fèi)22秒。我們可以非常直觀的通過(guò)下面的CPU使用率圖觀察到這22秒的花費(fèi)。大部分的時(shí)間花費(fèi)在 Postgres和 OS 上, 只有很少部分用于I/O .
在最低的層面，這些查詢看起來(lái)就像是這些CPU利用率的峰值。CPU圖很少有用，但是在這種條件下它證實(shí)了關(guān)鍵的一點(diǎn)：數(shù)據(jù)庫(kù)并沒(méi)有等待磁盤(pán)去讀取數(shù)據(jù)。它在做一些排序，哈希以及行比較之類的事情。
第二個(gè)有趣的度量，就是距離這些峰值很近的軌跡，它們是由Postgres“取得”的行數(shù)（本例中沒(méi)有返回，就看看再忽略掉吧）。
顯然有些動(dòng)作在規(guī)則的有條不紊的瀏覽過(guò)許多行：我們的查詢。
Postgres 的問(wèn)題所在：位圖掃描
下面是行匹配的查詢計(jì)劃
復(fù)制代碼 代碼如下:
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. 當(dāng)主鍵的數(shù)據(jù)量小的時(shí)候，它能有效的使用索引在內(nèi)存里建立位圖。如果位圖太大，最優(yōu)查詢計(jì)劃就改變查詢方式了。在我們這個(gè)查詢中，因?yàn)橹麈I包含的數(shù)據(jù)量很大，所以查詢就使用最優(yōu)（系統(tǒng)自己判斷的）的方式去檢索查詢候選行，并且立即查詢所有和主鍵匹配的數(shù)據(jù)。就是這些¨放入內(nèi)存¨和¨立即查詢¨花費(fèi)太多的時(shí)間（查詢計(jì)劃中的Recheck Cond）。
幸好只有30%的數(shù)據(jù)被導(dǎo)入到內(nèi)存中，所以還不至于像從硬盤(pán)里讀取那么壞。但它仍然對(duì)性能有非常明顯的影響。記住，查詢是非常簡(jiǎn)單的。這是一個(gè)主鍵查詢所以沒(méi)有很多明了的方式來(lái)確定它有沒(méi)有戲劇性的重新架構(gòu)數(shù)據(jù)庫(kù)或應(yīng)用程序。PGSQL-Performance mailing list給予了我們很大的幫助.
解決方案
這是我們喜歡開(kāi)源和喜歡幫助用戶的另外一個(gè)原因。Tom Lane是開(kāi)源代碼作者中最盛產(chǎn)的程序員之一，他建議我們做如下嘗試：
復(fù)制代碼 代碼如下:
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，你能指出他們的不同點(diǎn)嗎？
我們使用ARRAY[...]列舉出所有的關(guān)鍵字以用來(lái)查詢，但是這卻欺騙了查詢優(yōu)化器。然而Values(...)卻能夠讓優(yōu)化器充分使用關(guān)鍵字索引。僅僅是一行代碼的改變，并且沒(méi)有產(chǎn)生任何語(yǔ)義的改變。
下面是新查詢語(yǔ)句的寫(xiě)法，差別就在于第三和第十四行。
復(fù)制代碼 代碼如下:
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
查詢時(shí)間從22000ms下降到200ms，僅僅一行代碼的改變效率就提高了100倍。
在生產(chǎn)中使用的新查詢
即將發(fā)布的一段代碼:
它使數(shù)據(jù)庫(kù)看起來(lái)更美觀輕松.
第三方工具
postgres慢查詢不存在了。但是有誰(shuí)樂(lè)意被0.1%不幸的少數(shù)折磨。要立即驗(yàn)證修改查詢的影響，就需要Datadog來(lái)幫助我們判斷修改是否是正確的。
如果你想要找出對(duì)Postgres查詢改變的影響，可能需要幾分鐘來(lái)注冊(cè)一個(gè)免費(fèi)的Datadog賬號(hào)。
英文原文：100x faster Postgres performance by changing 1 line
