PostgreSQL 資料通路 offset 的質變 case

offset limit是一個多麼常見的需求啊，但是你知道offset的資料可能隐藏着質變嗎？

如圖

node有30w條資料，其中前100條是滿足條件的，然後100條到20w條都是不滿足條件的。

是以offset 10 limit 10非常的快。

但是offset 100 limit 10，就要掃描從100到20w條記錄，然後再往後才是滿足條件的記錄。

這就是質變的原因。

生成1000萬測試記錄。

更新info字段的資料，分布在前1000條和第500萬後的100條。

order by id offset 100 limit 100查詢的是前面的記錄，非常快。

如果掃描的是1000條以後的，因為滿足條件的記錄是500w往後的，是以至少要掃描500萬條記錄才能拿到結果。

關閉seqscan則會使用索引掃描，一樣的需要掃描一些不滿足條件的記錄。

removed by filter就是很好的說明

如果把limit加大到超過實際的滿足條件的結果，則需要掃完所有的記錄。

offset僅僅是偏移量，不是從此位置開始掃描，是以偏移量前的tuple都是需要被掃描到的。

limit的使用也需要注意，如果有斷層産生，會額外的掃描更多的塊。

offset一種好的優化方法是根據pk來位移。

例子見我以前寫的一批文章。

一位開發的同僚給我一個sql, 問我為什麼隻改了一個條件, 查詢速度居然從毫秒就慢到幾十秒了,

如下 :

運作結果100毫秒左右.

執行計劃 :

改成如下 :

運作幾十秒.

執行計劃如下 :

我們看到兩個sql執行計劃是一樣的, 但是走索引掃描的記錄卻千差萬别. 第二個sql掃描了多少行呢?

我們來看看第二個查詢得到的create_time值是多少:

結果 :

那麼它掃描了多少行(或者說多少個資料塊)呢? 通過explain verbose可以輸出.

當然使用以下查詢也可以估算出來 :

也就是說本例的sql中的where條件的資料在create_time這個字段順序上的分布比較零散, 并且資料量比較龐大.

是以offset 10後, 走create_time這個索引自然就慢了.

仔細的了解了一下開發人員的需求, 是要做類似翻頁的需求.

優化方法1,

在不新增任何索引的前提下, 還是走create_time這個索引, 減少重複掃描的資料.

需要得到每次取到的最大的create_time值, 以及可以标示這條記錄的唯一id.

下次取的時候, 不要使用offset 下一頁, 而是加上這兩個條件.

例如 :

通過這種方法, 可以減少limit x offset y這種方法取後面的分頁資料帶來的大量資料塊離散掃描.

以前寫的一些關于分頁優化的例子 :

<a href="http://blog.163.com/digoal@126/blog/static/163877040201111694355822/">http://blog.163.com/digoal@126/blog/static/163877040201111694355822/</a>

<a href="http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/">http://blog.163.com/digoal@126/blog/static/1638770402012520105855757/</a>