在 where 子句中使用 postgres 函式可能會讓查詢變成 non-sargable。

資料庫結構

tracks has_many artists

Non-Sargable 查詢

使用 LOWER 函式會讓 DBMS 引擎無法使用索引。

Track.joins(:artists).where('LOWER(tracks.display_name) LIKE ?', "eric clapton%").explain

Gather  (cost=1000.85..56714.32 width=4061)
   Workers Planned: 2
   ->  Nested Loop  (cost=0.85..55713.62 rows=3 width=4061)
         ->  Nested Loop  (cost=0.42..55708.37 rows=3 width=4069)
               ->  Parallel Seq Scan on tracks  (cost=0.00..55365.17 rows=41 width=4061)
                     Filter: (lower((display_name)::text) ~~* 'eric clapton%'::text)
               ->  Index Scan using index_artist_relations_on_artist_item_type_and_artist_item_id on artist_relations  (cost=0.42..8.36 rows=1 width=16)
                     Index Cond: (((artist_item_type)::text = 'Track'::text) AND (artist_item_id = tracks.id))
         ->  Index Only Scan using idx_35952_primary on artists  (cost=0.43..1.75 rows=1 width=8)
               Index Cond: (id = artist_relations.artist_id)

Sargable 查詢

移除 LOWER 函式後，DBMS 引擎就能使用索引，執行速度也更快。

Track.joins(:artists).where('tracks.display_name ILIKE ?', "eric clapton%").explain

Nested Loop  (cost=1497.60..2695.43 width=4061)
   ->  Nested Loop  (cost=1497.17..2684.94 rows=6 width=4069)
         ->  Bitmap Heap Scan on tracks  (cost=1496.75..1873.05 rows=97 width=4061)
               Recheck Cond: ((display_name)::text ~~* 'eric clapton%'::text)
               ->  Bitmap Index Scan on index_tracks_on_display_name  (cost=0.00..1496.73 rows=97 width=0)
                     Index Cond: ((display_name)::text ~~* 'eric clapton%'::text)
         ->  Index Scan using index_artist_relations_on_artist_item_type_and_artist_item_id on artist_relations  (cost=0.42..8.36 rows=1 width=16)
               Index Cond: (((artist_item_type)::text = 'Track'::text) AND (artist_item_id = tracks.id))
   ->  Index Only Scan using idx_35952_primary on artists  (cost=0.43..1.75 rows=1 width=8)
         Index Cond: (id = artist_relations.artist_id)

Sargable 與 Non-Sargable

Sargable 查詢是指可以利用索引來加速的查詢。Sargable 查詢讓 DBMS 引擎能執行 index seek，比掃描整張資料表快得多。Non-sargable 查詢無法使用索引，所以比較慢。「sargable」一詞來自「Search ARGument ABLE」，意思是 DBMS 引擎可以利用索引來最佳化查詢。理解 sargable 與 non-sargable 的差異，是最佳化資料庫的關鍵。

Non-Sargable 查詢

Non-sargable 查詢可能對查詢效能造成很大影響，在大型資料集上尤其明顯。當查詢是 non-sargable 時，DBMS 引擎必須進行 full table scan 或 index scan，兩者都是很重的操作。這可能導致執行時間變慢、CPU 使用率升高，以及整體系統效能不佳。找出並最佳化 non-sargable 查詢，是改善資料庫效率與回應速度的關鍵。

WHERE 子句中的函式

在 WHERE 子句中使用函式可能會讓查詢變成 non-sargable。這是因為當函式套用在 WHERE 子句中的欄位上時，DBMS 引擎無法利用索引來最佳化查詢。舉例來說，考慮查詢 SELECT FROM table WHERE UPPER(column) = 'VALUE'。這是 non-sargable 的，因為 UPPER 函式套用在欄位上，導致索引無法使用。要讓這個查詢變成 sargable，可以移除函式並把查詢改寫為 SELECT FROM table WHERE column = 'VALUE'。這樣 DBMS 引擎就能使用索引，查詢也會更快。

索引與查詢最佳化

索引是查詢最佳化的關鍵，因為它讓 DBMS 引擎能快速找到資料，進而加速查詢。但只有在查詢是 sargable 時才能使用索引。如果查詢是 non-sargable，索引就派不上用場，查詢也會變慢。所以請在 WHERE 子句用到的欄位上建立索引，並確保查詢是 sargable。這會大幅提升資料庫的效能。

轉換 Non-Sargable 述詞

把 non-sargable 述詞轉換為 sargable 是查詢最佳化的一個步驟。透過改寫查詢，移除任何妨礙索引使用的函式或運算，就能把 non-sargable 述詞轉換為 sargable。例如查詢 SELECT FROM table WHERE YEAR(date) = 2022 是 non-sargable 的，因為 YEAR 函式套用在 date 欄位上。要讓這個查詢變成 sargable，你可以把它改寫為 SELECT FROM table WHERE date >= '2022-01-01' AND date < '2023-01-01'。改寫後的查詢是 sargable 的，因為 DBMS 引擎可以利用 date 欄位上的索引來最佳化查詢，速度也會更快。