一個(gè)提升PostgreSQL性能的小技巧

2020-10-29 21:50:01

字體：大中小

供稿：網(wǎng)友

在一個(gè)（差）的PostgreSQL 查詢(xún)中只要一個(gè)小小到改動(dòng)(ANY(ARRAY[...])to ANY(VALUES(...)))就能把查詢(xún)時(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查詢(xún)

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

99.9%到查詢(xún)都是非常迅速流暢的，但是在一些使用大量的枚舉值的地方，這些查詢(xún)會(huì)需要20秒?；ㄙM(fèi)如此多到時(shí)間在數(shù)據(jù)庫(kù)上，意味著使用者必須在瀏覽器面前等待圖形編輯器的響應(yīng)。很明顯只因?yàn)檫@0.01%就會(huì)造成很不好到影響。

查詢(xún)和查詢(xún)計(jì)劃

下面是這個(gè)出問(wèn)題的查詢(xún)

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有幾千行數(shù)據(jù)，表C有1500萬(wàn)條數(shù)據(jù)。兩張表的主鍵值“key”都有適當(dāng)?shù)乃饕?。這是一個(gè)非常簡(jiǎn)單清晰的主鍵查詢(xún)。但有趣的是，當(dāng)增加主鍵內(nèi)容的數(shù)量，如在主鍵有11,000個(gè)值的時(shí)候，通過(guò)在查詢(xún)語(yǔ)句上加上 EXPLAIN (ANALYZE, BUFFERS)我們得到如下的查詢(xún)計(jì)劃。

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

在結(jié)果的最底部你可以看到，這個(gè)查詢(xún)總共花費(fèi)22秒。我們可以非常直觀(guān)的通過(guò)下面的CPU使用率圖觀(guān)察到這22秒的花費(fèi)。大部分的時(shí)間花費(fèi)在 Postgres和 OS 上, 只有很少部分用于I/O .

2015421115721448.png (562×337)

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

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

2015421115811688.png (563×338)

顯然有些動(dòng)作在規(guī)則的有條不紊的瀏覽過(guò)許多行：我們的查詢(xún)。

Postgres 的問(wèn)題所在：位圖掃描

下面是行匹配的查詢(xún)計(jì)劃

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)查詢(xún)計(jì)劃就改變查詢(xún)方式了。在我們這個(gè)查詢(xún)中，因?yàn)橹麈I包含的數(shù)據(jù)量很大，所以查詢(xún)就使用最優(yōu)（系統(tǒng)自己判斷的）的方式去檢索查詢(xún)候選行，并且立即查詢(xún)所有和主鍵匹配的數(shù)據(jù)。就是這些¨放入內(nèi)存¨和¨立即查詢(xún)¨花費(fèi)太多的時(shí)間（查詢(xún)計(jì)劃中的Recheck Cond）。

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

解決方案

這是我們喜歡開(kāi)源和喜歡幫助用戶(hù)的另外一個(gè)原因。Tom Lane是開(kāi)源代碼作者中最盛產(chǎn)的程序員之一，他建議我們做如下嘗試：

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，你能指出他們的不同點(diǎn)嗎？

我們使用ARRAY[...]列舉出所有的關(guān)鍵字以用來(lái)查詢(xún)，但是這卻欺騙了查詢(xún)優(yōu)化器。然而Values(...)卻能夠讓優(yōu)化器充分使用關(guān)鍵字索引。僅僅是一行代碼的改變，并且沒(méi)有產(chǎn)生任何語(yǔ)義的改變。

下面是新查詢(xún)語(yǔ)句的寫(xiě)法，差別就在于第三和第十四行。

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

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

在生產(chǎn)中使用的新查詢(xún)

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