訂閱
糾錯
加入自媒體

PG:查詢計(jì)劃器與random_page_cost

2021-03-05 10:09
yzsDBA
關(guān)注

查詢計(jì)劃器與random_page_cost

本周小貼士比較奇怪,基于我們今天遇到的一個問題。將簡訊的鏈接存儲到一個簡單數(shù)據(jù)庫中:

CREATE TABLE links (

 uid CHAR(60) PRIMARY KEY,

 data TEXT,

 timestamp INT

CREATE INDEX idx_trgm ON links USING GIN (data gin_trgm_ops)

當(dāng)然,這個設(shè)計(jì)比較爛。但僅供內(nèi)部使用,我只是一個粗略的想法原型。數(shù)據(jù)是一個包含json(I know, I know...)的文本,以同樣可怕的方式檢查鏈接的存在:

SELECT * FROM links WHERE data ILIKE '%whatever we want%' LIMIT 1;

在低容量下運(yùn)行很好,但查詢時(shí)間偶爾會超過300ms,很好奇這是為啥?

執(zhí)行EXPLAIN ANALYZE后,發(fā)現(xiàn)PG根本沒使用GIN索引,而是使用了全表掃描。但是如果去掉LMIT 1,查詢將使用索引,執(zhí)行只需要5ms。為什么PG會忽略索引?

PG的查詢規(guī)劃器并不是真正基于人們做一些荒唐的事情。比如使用ILIKE進(jìn)行全表掃描,關(guān)心的是走索引快還是全表掃描快。變量random_page_cost用于決定使用索引的代價(jià)是否值得,或者和seq_page_cost合作使用。

這種情況下,索引掃描是值得的,但是查詢規(guī)劃器不同意。因?yàn)榭雌饋砗芎唵蔚腖IMIT 1,只找到一個結(jié)果就可以停止。并繼續(xù)進(jìn)行全表掃描。

SET random_page_cost = 1;

EXPLAIN ANALYZE SELECT * ... LIMIT 1;

   [see the index being used]

SET random_page_cost = DEFAULT;    

EXPLAIN ANALYZE SELECT * ... LIMIT 1;

[see the index NOT being used]

除了明顯的“修復(fù)那個糟糕的模式”,看看PG會做什么,如果覺得索引掃描和順序掃描比代價(jià)低。因此如果最終得到的查詢使用索引,那么有必要嘗試一下,通過EXPLAIN ANALYZE分析。

聲明: 本文由入駐維科號的作者撰寫,觀點(diǎn)僅代表作者本人,不代表OFweek立場。如有侵權(quán)或其他問題,請聯(lián)系舉報(bào)。

發(fā)表評論

0條評論,0人參與

請輸入評論內(nèi)容...

請輸入評論/評論長度6~500個字

您提交的評論過于頻繁,請輸入驗(yàn)證碼繼續(xù)

  • 看不清,點(diǎn)擊換一張  刷新

暫無評論

暫無評論

人工智能 獵頭職位 更多
掃碼關(guān)注公眾號
OFweek人工智能網(wǎng)
獲取更多精彩內(nèi)容
文章糾錯
x
*文字標(biāo)題:
*糾錯內(nèi)容:
聯(lián)系郵箱:
*驗(yàn) 證 碼:

粵公網(wǎng)安備 44030502002758號