Postgres Functions & Non-sargable Queries

Using postgres functions inside a where clause can make a query non-sargable.

Database Structure

tracks has_many artists

Non-Sargable Query

Using a LOWER function prevents DBMS engine from using indexes.

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 Query

Removing LOWER function allows DBMS engine to use indexes, resulting in faster execution.

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 and Non-Sargable

A sargable query is one that can use an index to speed up the query. A sargable query allows the DBMS engine to do an index seek, which is much faster than scanning the whole table. A non-sargable query can’t use an index, so it’s slower. The term “sargable” comes from “Search ARGument ABLE,” meaning the DBMS engine can use an index to optimize the query. Understanding the difference between sargable and non-sargable is key to optimizing your database.

Non-Sargable Queries

Non-sargable queries can have a big impact on query performance especially on large datasets. When a query is non-sargable, the DBMS engine has to do a full table scan or an index scan, both of which are heavy operations. This can result to slower execution time, higher CPU usage and overall poor system performance. Finding and optimizing non-sargable queries is key to improve your database’s efficiency and responsiveness.

Functions in the WHERE Clause

Using functions in the WHERE clause can make a query non-sargable. This happens because the DBMS engine can’t use an index to optimize the query when a function is applied to a column in the WHERE clause. For example, consider the query SELECT FROM table WHERE UPPER(column) = 'VALUE'. This is non-sargable because the UPPER function is applied to the column, so the index can’t be used. To make this query sargable, you can remove the function and rewrite the query as SELECT FROM table WHERE column = 'VALUE'. Then the DBMS engine can use an index and the query will be faster.

Indexes and Query Optimization

Indexes are key to query optimization because they allow the DBMS engine to find the data quickly, hence speeding up the query. But an index can only be used if the query is sargable. If the query is non-sargable, the index can’t be used and the query will be slower. So create indexes on columns used in the WHERE clause and make sure the queries are sargable. This will improve your database’s performance big time.

Converting Non-Sargable Predicates

Converting non-sargable predicates to sargable is a query optimization step. A non-sargable predicate can be converted to sargable by rewriting the query to remove any functions or operations that prevents the use of an index. For example the query SELECT FROM table WHERE YEAR(date) = 2022 is non-sargable because the YEAR function is applied to the date column. To make this query sargable you can rewrite it as SELECT FROM table WHERE date >= '2022-01-01' AND date < '2023-01-01'. This rewritten query is sargable because the DBMS engine can use an index on the date column to optimize the query and will be faster.