PostgreSQL Poor performance ORDER BY / LIMIT

2018-07-07

最近研究一個在 Postgres 奇怪的效能問題。

情況是這樣的，我有一個通知系統 (Notifications) 的 table，也有對常用的搜尋打 index

index_notifications_on_unread(read_at_unixtimestamp, user_id, id)

Notification 的 dataset 大約 500 萬筆。

情況 1, 我確定找的到資料

做「where」＋「order」

Notification.where(user: current_user).where(read_at_unixtimestamp: nil).order(id: :desc)

可以有效的吃到 index, 回應速度很快，約 10 ms。

Index Scan using index_notifications_on_unread on notifications
Execution time: 10.813 ms

做「where」＋「order」+ 「limit」

Notification.where(user: current_user).where(read_at_unixtimestamp: nil).order(id: :desc).limit(1)

一樣吃到 index, 搜尋時間也都差不多，但卻調用了 memory 去 sort (?)。

Sort Method: quicksort Memory: 25kB
Index Scan using index_notifications_on_unread on notifications
Execution time: 10.554 ms

情況 2, 我確定沒有這筆資料

做「where」＋「order」

Notification.where(user: current_user).where(read_at_unixtimestamp: nil).order(id: :desc)

可以有效的吃到 index ，約 12 ms。

Index Scan using index_notifications_on_unread on notifications
Execution time: 12.080 ms

做「where」＋「order」+ 「limit」

Notification.where(user: current_user).where(read_at_unixtimestamp: nil).order(id: :desc).limit(1)

這裡 Query plan 變了，竟然拿了 notification primary key 去搜尋，回應時間直接變 2800 ms (2.8 秒鐘)

Index Scan Backward using notifications_pkey on notifications 
Rows Removed by Filter: 5368808
Execution time: 2894.416 ms

整個跑去 table scan 了，這整個很神奇啊

探索原因 & 解決方案

爬了很多文，也看到不少人在 StackOverFlow 上詢問，解法大概都是

對 sort key 打排序的 index
用子查詢的方式解掉，主句先排序再把 Limit 放在子句做

我們長久以來都認為語句在分析的時候，是先執行 where 後再執行 order by，其實這個規則是對的，但是 order by 會影響 query plan。

主要原因是 order by id 後會使優化器將 id 的索引優先級調整到非常高，索引index_unread(read_at_unixtimestamp, user_id, id) 在查詢條件符合時確實能用上，但只要加上 order by id 之後，優化器會認為這隻 index 只有用到 id 太浪費了，轉而使用主鍵查詢而不用 index_unread

然後搜尋優化器拿了主鍵查詢會在還沒有篩選資料的情況下用倒序想盡快找到 LIMIT 要求的筆數，結果就是一個很糟糕的 query plan , 因為一直沒有找到資料，就把表給掃完了…

所以最好的解法是單獨再做一個給排序用的索引，然後直接給他倒序

CREATE INDEX notification_idx_null ON notifications (
  id DESC
)

雖然也有一些作法是強制使用索引，或是關閉 seq scan 之類的，但這作法可能會影響很多層面，並不推薦使用，優化索引才是正道啊。

不過我還是不知道為什麼在情況 1 的 order by limit 還是選擇了正常的 index, 或許是 postgers 自己學習的情況？如果我找到了更明確的答案再回來補上，你知道的話也請別吝嗇在下方留言告訴我，謝謝！

Nic Lin's Blog

PostgreSQL Poor performance ORDER BY / LIMIT

情況 1, 我確定找的到資料

情況 2, 我確定沒有這筆資料

探索原因 & 解決方案

參考來源：