Sargable
{{Short description|Condition in databases}}
{{Multiple issues|
{{original research|date=August 2015}}
{{onesource|date=April 2013}}
}}
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."{{ref|Gulutzan|1}}{{Cite web |last=Andy |first=Pavlo |date=Spring 2023 |title=CMU 15-721 :: Advanced Database Systems (Spring 2023) :: Lecture #16 Optimizer Implementation (Part 1) - Slide |url=https://15721.courses.cs.cmu.edu/spring2023/slides/16-optimizer1.pdf |url-status=live |archive-url=https://web.archive.org/web/20230601074825/https://15721.courses.cs.cmu.edu/spring2023/slides/16-optimizer1.pdf |archive-date=2023-06-01 |access-date=2024-01-25}}{{Cite book |last1=Selinger |first1=P. Griffiths |last2=Astrahan |first2=M. M. |last3=Chamberlin |first3=D. D. |last4=Lorie |first4=R. A. |last5=Price |first5=T. G. |chapter=Access path selection in a relational database management system |date=1979 |title=Proceedings of the 1979 ACM SIGMOD international conference on Management of data - SIGMOD '79 |chapter-url=http://portal.acm.org/citation.cfm?doid=582095.582099 |language=en |publisher=ACM Press |pages=23 |doi=10.1145/582095.582099 |isbn=978-0-89791-001-9}}
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 heuristic{{ref|heuristic|2}} matching query to indexes instead of a complex, time-consuming cost-based search, 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 list, on the other hand, can contain non-sargable expressions without adversely affecting the performance.
Some database management systems, for instance [https://www.postgresql.org/docs/current/indexes-expressional.html 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: {{code|lang=sql|1==, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, IN}}
- Sargable operators that rarely improve performance: {{code|lang=sql|1=<>, NOT, NOT IN, NOT LIKE}}
Simple example
{{code|lang=sql|WHERE}} clauses that are sargable typically have column 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(myIntColumn) > 11.7
This is not sargable because myIntColumn is embedded in a function. If any indexes were available on myIntColumn, they could not be used. In addition, {{code|lang=sql|SQRT()}} would be called on every row in myTable.
Sargable version:
SELECT *
FROM myTable
WHERE myIntColumn > 11.7 * 11.7
This is sargable because myIntColumn is NOT contained in a function, making any available indexes on myIntColumn potentially usable. Furthermore, the expression is evaluated only once, rather than for each row in the table.
Text example
{{code|lang=sql|WHERE}} ... {{code|lang=sql|LIKE}} clauses that are sargable have column values on the left of the operator, and {{code|lang=sql|LIKE}} text strings that do not begin with the {{code|lang=sql|%}} on the right.
Not sargable:
SELECT *
FROM myTable
WHERE myNameColumn LIKE '%Wales%' -- Begins with %, not sargable
This is not sargable. It must examine every row to find the column containing the substring {{code|lang=sql|'Wales'}} in any position.
Sargable version:
SELECT *
FROM myTable
WHERE myNameColumn LIKE 'Jimmy%' -- Does not begin with %, sargable
This is sargable. It can use an index to find all the myNameColumn values that start with the substring {{code|lang=sql|'Jimmy'}}.
See also
Notes
:{{note|Gulutzan|1}} Gulutzan and Pelzer, ([http://www.informit.com/articles/article.aspx?p=30247 Chapter 2, Simple "Searches"])
:{{note|heuristic|2}} {{Cite book |last1=Silberschatz |first1=Abraham |title=Database system concepts |last2=Korth |first2=Henry F. |last3=Sudarshan |first3=S. |date=2020 |publisher=McGraw-Hill Education |isbn=978-1-260-08450-4 |edition=7th |location=New York, NY |pages=773}} gives an example of such simple heuristic.
External links
- [https://www.sqlshack.com/how-to-use-sargable-expressions-in-t-sql-queries-performance-advantages-and-examples/ SQL Shack - How to use sargable expressions in T-SQL queries; performance advantages and examples]
- [https://dba.stackexchange.com/questions/162263/what-does-the-word-sargable-really-mean DBA.StackExchange.com - What does the word “SARGable” really mean?]
References
{{Reflist}}
- SQL Performance Tuning by Peter Gulutzan, Trudy Pelzer (Addison Wesley, 2002) {{ISBN|0-201-79169-2}} ([http://www.informit.com/articles/article.aspx?p=30247 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)