---
title: "Postgres 函数与 Non-sargable 查询"
description: "在 where 子句中使用 postgres 函数可能会让查询变成 non-sargable。"
canonical: https://oozou.com/til/postgres-functions-non-sargable-queries-48
locale: zh-Hans
date: 2020-10-13
topic: ruby-on-rails
---

# Postgres 函数与 Non-sargable 查询

> 在 where 子句中使用 postgres 函数可能会让查询变成 non-sargable。

Published: 2020-10-13 · Topic: [ruby on rails](https://oozou.com/zh-Hans/til-topics/ruby-on-rails) ([markdown](https://oozou.com/zh-Hans/til-topics/ruby-on-rails.md))

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

---

Contact OOZOU: hello@oozou.com · +66 2544 0000 · https://oozou.com/zh-Hans/contact

More for AI agents: https://oozou.com/llms.txt · every page has a markdown twin at its URL plus ".md"