postgres の関数を where 句の中で使うと、クエリが 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 エンジンがインデックスシークを行えるため、テーブル全体をスキャンするよりはるかに高速です。non-sargable なクエリはインデックスを使えないため遅くなります。「sargable」という言葉は「Search ARGument ABLE」に由来し、DBMS エンジンがインデックスを使ってクエリを最適化できることを意味します。sargable と non-sargable の違いを理解することが、データベース最適化の鍵になります。

Non-Sargable なクエリ

non-sargable なクエリは、特に大規模なデータセットにおいて、クエリパフォーマンスに大きな影響を与えることがあります。クエリが non-sargable だと、DBMS エンジンはフルテーブルスキャンかインデックススキャンを行う必要があり、どちらも重い処理です。その結果、実行時間の増加、CPU 使用率の上昇、システム全体のパフォーマンス低下につながります。non-sargable なクエリを見つけて最適化することが、データベースの効率と応答性を改善する鍵です。

WHERE 句の中の関数

WHERE 句の中で関数を使うと、クエリが non-sargable になることがあります。WHERE 句でカラムに関数が適用されていると、DBMS エンジンはインデックスを使ってクエリを最適化できないためです。たとえば SELECT FROM table WHERE UPPER(column) = 'VALUE' というクエリを考えてみましょう。UPPER 関数がカラムに適用されていてインデックスが使えないため、これは non-sargable です。このクエリを 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 というクエリは、YEAR 関数が date カラムに適用されているため non-sargable です。このクエリを sargable にするには SELECT FROM table WHERE date >= '2022-01-01' AND date < '2023-01-01' と書き換えます。書き換えたクエリは sargable で、DBMS エンジンが date カラムのインデックスを使ってクエリを最適化できるため、高速になります。