Sargable

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.
[edit]

References

[edit]
  1. ^ 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.
  2. ^ 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.
  3. ^ 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.