在PostgreSQL中修改了一行不明顯的代碼，把（ANY(ARRAY[...]) 改成 ANY(VALUES(...))），結(jié)果查詢時(shí)間從20s變?yōu)?.2s。最初我們學(xué)習(xí)使用EXPLAN ANALYZE來(lái)優(yōu)化代碼，到后來(lái)，Postgres社區(qū)也成為我們學(xué)習(xí)提升的一個(gè)好幫手，付出總會(huì)有回報(bào)，我們產(chǎn)品的性能也因此得到了極大的提升。
?
?事出有因
?
?我們所開(kāi)發(fā)的產(chǎn)品是Datadog，它是專門為那些編寫和運(yùn)營(yíng)大規(guī)模應(yīng)用的團(tuán)隊(duì)、IT運(yùn)營(yíng)商提供監(jiān)控服務(wù)的一個(gè)平臺(tái)，幫助他們把海量的數(shù)據(jù)轉(zhuǎn)化為切實(shí)可行的計(jì)劃、操作方案。而在這周早些時(shí)候，我們的許多數(shù)據(jù)庫(kù)所面臨的一個(gè)性能問(wèn)題是在一個(gè)較小的表上進(jìn)行大量的key查詢。這些查詢中的99.9%都是高效靈活的。在極少數(shù)實(shí)例中，有些數(shù)量的性能指標(biāo)tag查詢是費(fèi)時(shí)的，這些查詢需要花費(fèi)20s時(shí)間。這也就意味著用戶需要在瀏覽器面前花費(fèi)這么長(zhǎng)的時(shí)間來(lái)等待圖形編輯器做出響應(yīng)。即使是0.1%，這樣的用戶體驗(yàn)也顯然糟透了，對(duì)此，我們進(jìn)行了監(jiān)測(cè)，探究為何速度會(huì)這么慢。
?
?查詢與計(jì)劃
?
?結(jié)果令人震驚，罪魁禍?zhǔn)拙谷皇窍旅孢@個(gè)簡(jiǎ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 (ARRAY[15368196, -- 11,000 other keys --)])
?AND c.x_key = 1
?AND c.tags @> ARRAY[E'blah'];
?
?X表?yè)碛猩锨袛?shù)據(jù)，C表?yè)碛?500萬(wàn)行數(shù)據(jù)，這兩個(gè)表的“key”列都帶有適當(dāng)?shù)乃饕麈I。簡(jiǎn)單地說(shuō)，它就是一個(gè)簡(jiǎn)單的主鍵查詢。但有趣地是，隨著key列中記錄的增加，例如在11000行時(shí)，我們通過(guò)添加EXPLAIN (ANALYZE, BUFFERS)前綴來(lái)查看key列的值是否與數(shù)組中的值匹配 托福改分
?
?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
?
?Postgres的性能問(wèn)題：位圖堆掃描
?
?rows_fetched度量與下面的部分計(jì)劃是一致的：
?
?12345 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使用位圖堆掃描（ Bitmap Heap Scan）來(lái)讀取C表數(shù)據(jù)。當(dāng)關(guān)鍵字的數(shù)量較少時(shí)，它可以在內(nèi)存中非常高效地使用索引構(gòu)建位圖。如果位圖太大，查詢優(yōu)化器會(huì)改變其查找數(shù)據(jù)的方式。在我們這個(gè)案例中，需要檢查大量的關(guān)鍵字，所以它使用了非常相似的方法來(lái)檢查候選行并且單獨(dú)檢查與x_key和tag相匹配的每一行。而所有的這些“在內(nèi)存中加載”和“檢查每一行”都需要花費(fèi)大量的時(shí)間。
?
?幸運(yùn)的是，我們的表有30%都是裝載在RAM中，所以在從磁盤上檢查行的時(shí)候，它不會(huì)表現(xiàn)的太糟糕。但在性能上，它仍然存在非常明顯的影響。查詢過(guò)于簡(jiǎn)單，這是一個(gè)非常簡(jiǎn)單的key查找，所以沒(méi)有顯而易見(jiàn)的數(shù)據(jù)庫(kù)或應(yīng)用重構(gòu)，它很難找到一些簡(jiǎn)單的方式來(lái)解決這個(gè)問(wèn)題。最后，我們使用 PGSQL-Performance郵件向社區(qū)求助 托福答案
?
?解決方案
?
?開(kāi)源幫了我們，經(jīng)驗(yàn)豐富的且代碼貢獻(xiàn)量非常多的Tom Lane讓我們?cè)囋囘@個(gè)：
?
?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'];
?
?你能發(fā)現(xiàn)有啥不同之處嗎？把ARRAY換成了VALUES。
?
?我們使用ARRAY[...]列舉出所有的關(guān)鍵字來(lái)進(jìn)行查詢，但卻欺騙了查詢優(yōu)化器。Values(...)讓優(yōu)化器充分使用關(guān)鍵字索引。僅僅是一行代碼的改變，并且沒(méi)有產(chǎn)生任何語(yǔ)義的改變。
?
?
?下面是新查詢語(yǔ)句的寫法，差別就在于第三和第十四行。
?
?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)品里新的查詢
?
?部署后的代碼：
?
?Postgres慢查詢將一去不復(fù)返了。但有誰(shuí)愿意因?yàn)檫@個(gè)0.1%的倒霉蛋再去折磨呢？我們使用Datadog來(lái)驗(yàn)證修改是否正確，它能夠做出即時(shí)驗(yàn)證。如果你想查看Postgres查詢速度的各種影響， 不妨試試Datadog吧。
?
?
?

修改一行SQL代碼 性能提升了N倍