Sargable
 | This article has multiple issues. Please help improve it or discuss these issues on the talk page. (Learn how and when to remove these messages)
|
In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE. It was first used by IBM researchers as a contraction of Search ARGument, and has come to mean simply "can be looked up by an index."1[1][2]
For database query optimizers, sargable is an important property in OLTP workloads because it suggests a good query plan can be obtained by a simple heuristic2 matching query to indexes instead of a complex, time-consuming cost-based search,[1] thus it is often desired to write sargable queries. A query failing to be sargable is known as a non-sargable query and typically has a negative effect on query time, so one of the steps in query optimization is to convert them to be sargable. The effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index.
The typical situation that will make a SQL query non-sargable is to include in the WHERE clause a function operating on a column value. The WHERE clause is not the only clause where sargability can matter; it can also have an effect on ORDER BY, GROUP BY and HAVING clauses. The SELECT clause, on the other hand, can contain non-sargable expressions without adversely affecting the performance.
Some database management systems, for instance PostgreSQL, support functional indices. Conceptually, an index is simply a mapping between a value and one or more locations. With a functional index, the value stored in the index is the output of the function specified when the index is created. This capability expands what is sargable beyond base column expressions.
- Sargable operators:
=, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, IN - Sargable operators that rarely improve performance:
<>, NOT, NOT IN, NOT LIKE
Simple example
[edit]WHERE clauses that are sargable typically have field values on the left of the operator, and scalar values or expressions on the right side of the operator.
Not sargable:
SELECT *
FROM myTable
WHERE SQRT(myIntField) > 11.7
This is not sargable because myIntField is embedded in a function. If any indexes were available on myIntField, they could not be used. In addition, SQRT() would be called on every record in myTable.
Sargable version:
SELECT *
FROM myTable
WHERE myIntField > 11.7 * 11.7
This is sargable because myIntField is NOT contained in a function, making any available indexes on myIntField potentially usable. Furthermore, the expression is evaluated only once, rather than for each record in the table.
Text example
[edit]WHERE ... LIKE clauses that are sargable have field values on the left of the operator, and LIKE text strings that do not begin with the % on the right.
Not sargable:
SELECT *
FROM myTable
WHERE myNameField LIKE '%Wales%' -- Begins with %, not sargable
This is not sargable. It must examine every row to find the fields containing the substring 'Wales' in any position.
Sargable version:
SELECT *
FROM myTable
WHERE myNameField LIKE 'Jimmy%' -- Does not begin with %, sargable
This is sargable. It can use an index to find all the myNameField values that start with the substring 'Jimmy'.
See also
[edit]Notes
[edit]- ^1 Gulutzan and Pelzer, (Chapter 2, Simple "Searches")
- ^2 [3] gives an example of such simple heuristic.
External links
[edit]- SQL Shack - How to use sargable expressions in T-SQL queries; performance advantages and examples
- DBA.StackExchange.com - What does the word “SARGable” really mean?
References
[edit]- ^ a b Andy, Pavlo (Spring 2023). "CMU 15-721 :: Advanced Database Systems (Spring 2023) :: Lecture #16 Optimizer Implementation (Part 1) - Slide" (PDF). Archived (PDF) from the original on 2023-06-01. Retrieved 2024-01-25.
- ^ Selinger, P. Griffiths; Astrahan, M. M.; Chamberlin, D. D.; Lorie, R. A.; Price, T. G. (1979). "Access path selection in a relational database management system". Proceedings of the 1979 ACM SIGMOD international conference on Management of data - SIGMOD '79. ACM Press. p. 23. doi:10.1145/582095.582099. ISBN 978-0-89791-001-9.
- ^ Silberschatz, Abraham; Korth, Henry F.; Sudarshan, S. (2020). Database system concepts (7th ed.). New York, NY: McGraw-Hill Education. p. 773. ISBN 978-1-260-08450-4.
- SQL Performance Tuning by Peter Gulutzan, Trudy Pelzer (Addison Wesley, 2002) ISBN 0-201-79169-2 (Chapter 2, Simple "Searches")
- Microsoft SQL Server 2012 Internals by Kalen Delaney, Connor Cunningham, Jonathan Kehayias, Benjamin Nevarez, Paul S. Randal (O'Reily, 2013) ISBN 978-0-7356-5856-1 (Chapter 11, The Query Optimizer)