把這個不同的 SQL 指令抓出來, 拿到 pgAdmin 上頭跑看看, 果然, 如果其中的日期是在上個月的, 就會利用 index 去找, 很快就有結果, 但是... 如果日期是落在這幾天內, 居然就是使用 Full Table Scan 去找, 而這個 table 目前有一千萬筆左右的資料, 這一找, 當然要花上數分鐘才有結果.
找了一下 PostgreSQL 的文件, 並沒有發現有支援任何類似 Oracle 的 hint 功能, 可以在執行 SQL 指令時, 避免內建的最佳化引擎誤判, 可以告訴 Oracle 應該使用那些方法來執行這個 SQL 指令. 在 PostgreSQL 中, 我們並不能控制 SQL Engine 要怎麼運作, 完全只能交由 PostgreSQL 來決定. (看來, 除了 online backup 方面不如人外, 在這方面, 還是輸人家不少... 有賣錢的還真的在某些地方是有優點的)
後來試著做一次 re-index 的指令, 結果仍然一樣. 接著再試著做一次 vacuum analyze, 這次, 果然有用了, 之前會用到 full table scan 的指令, 現在都會改用 index 了. 可是... 問題是, 我們每天都對整個資料庫跑一次 vacuum analyze 怎麼會沒有作用呢? 查了一下每天執行的記錄, 並沒有任何錯誤, 都很正常的執行. 問題到底在那兒呢?
隔天, 同樣的事情又發生了, 這次只有有關當天資料的 SQL 指令會造成 full table scan (該 table 一天會有 1-2 萬筆新增的記錄, 發現有問題時, 當天新增的筆數約 5000 筆左右). 這... 再做一次 vacuum analyze 之後, 又正常了. 不過.... 總不能一天對就這個 table 做好幾次 vacuum analyze 吧. 我們每天做一次, 應該就已經是很頻繁了. 一天要數次才能正常使用, 似乎不是一個可以接受的做法.
找了一下 PostgreSQL 的文件, 也沒發現什麼好的解決方法. 後來在檢查我們的 postgresql.conf 時, 發現我們裡頭有關 Planner Method Configuration 的設定, 那些 enable_xxx 的參數, 全部都設為 true:
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
enable_hashagg = true
enable_hashjoin = true
enable_indexscan = true
enable_mergejoin = true
enable_nestloop = true
enable_seqscan = true
enable_sort = true
enable_tidscan = true
裡頭有個 enable_seqscan 也是被設定為 true.
我試著把這個參數改為 false:
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
enable_hashagg = true
enable_hashjoin = true
enable_indexscan = true
enable_mergejoin = true
enable_nestloop = true
enable_seqscan = false
enable_sort = true
enable_tidscan = true
然後重新 reload 一次 postgresql, 之後, 就正常了. 在後續的幾天, 該報表並沒有再發生類似的情形了. 看來把 enable_seqscan 設為 false 之後, PostgreSQL 在有其他 index 的情形下, 就不會去使用 full table scan 了.
雖然事情解決了, 不過我們還是覺得奇怪, 同樣的程式, 也用了三年多了. 同樣裡頭的資料也累積三年多了, 雖然有換過機器, 但是新的機器比舊的要快多了, 且也換了一年多了, 為什麼之前都沒有問題呢? 難道是累積到了快一千萬筆資料才會有問題?
