在 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 字段上的索引来优化查询，速度也会更快。