
Can adding an index make a non SARGable query SARGable? - furtively
http://sqlservercode.blogspot.com/2019/06/Can-adding-an-index-make-a-non-SARGable-query-SARGable-instead-of-rewriting-sql-query.html
======
Topgamer7
Databases can be a black box from the developer perspective. This solution
might perform differently across a number of variables:

Database management solution (sql server, postgres, mysql, etc) Database
version (mysql 5.6, 5.7, 5.8, mariadb, etc)

This method of finding whether your hunch works or not is the easiest
alternative other than trying to read the code (or documentation, which many
db engines have excellent documentation for this sort of thing).

~~~
Pxtl
Hah, if versions were enough for consistent performance I'd be in heaven.

I'm plagued by cases where the same schema with the same indices on same
version of servers running on the same architecture gets different plans and
performance.

~~~
DougBTX
For SQL Server there’s a very nice article on making query plans more
predictable here: [http://www.sommarskog.se/query-plan-
mysteries.html](http://www.sommarskog.se/query-plan-mysteries.html)

------
Shish2k
Could you not just add an index on `Right(SomeColumn,3)`? Creating indexes for
common queries seems less weird than adding columns...

~~~
hotsauceror
The issue with nonsargable expressions is not that you can’t create an index
that contains what would be the output of that expression. It’s that you are
forced to do a row-by-row evaluation of each record in order to even utilize
the corresponding index to do a seek/scan against. You basically force a scan
of the smallest available index. And there are tons of commonly used ,
nonsargable expressions. Implicit conversions, string parsing, date parsing,
isnull/coalesce.

~~~
jimktrains2
Can you explain what sargable is? I've never used SQL server, but I'm sure a
similar expression could be indexed in postgresql. (Not at a computer to
check.)

~~~
BrentOzar
> Can you explain what sargable is?

It means that the database take your Search ARGuments (SARG) and do an index
seek to deliver the exact results you want.

------
jimktrains2
I had no idea what sargable was. I guess it means using an index? Is this a
sql server specific term? [https://www.sqlshack.com/how-to-use-sargable-
expressions-in-...](https://www.sqlshack.com/how-to-use-sargable-expressions-
in-t-sql-queries-performance-advantages-and-examples/)

Why not just use a function index?

~~~
BrentOzar
> I had no idea what sargable was. I guess it means using an index?

Close - it refers to SQL Server being able to take your Search ARGuments
(SARG) and do an index seek to jump to the rows you're looking for.

> Is this a sql server specific term?

No: [https://dba.stackexchange.com/questions/162263/what-does-
the...](https://dba.stackexchange.com/questions/162263/what-does-the-word-
sargable-really-mean)

> Why not just use a function index?

Microsoft SQL Server doesn't have those.

------
taspeotis
SQL Server Enterprise will also allow you to create a view with somewhat
complex joins and conditions, index it, and then other queries can benefit
from the indexed joins.

It’s things like this that keep us paying $millions for SQL Server Enterpise
vs. Postgres.

~~~
jwcacces
In postgres you could just make an index on the RIGHT(SomeColumn,3)
expression.

See [https://www.postgresql.org/docs/current/sql-
createindex.html](https://www.postgresql.org/docs/current/sql-
createindex.html)

> An index field can be an expression computed from the values of one or more
> columns of the table row. This feature can be used to obtain fast access to
> data based on some transformation of the basic data. For example, an index
> computed on upper(col) would allow the clause WHERE upper(col) = 'JIM' to
> use an index.

No need to maintain a separate view or its index

~~~
hotsauceror
That’s actually pretty slick. I’d pay a fair bit of money for SQL Server to
have computed indexes like that.

~~~
marzell
I mean, you could add a new computed column to a table and index that... It's
really effectively the same thing, just more visible. Still requires a table
scan and storage/maintenance of the index/column. I guess technically it may
require additional storage but it's not radically different. Either way, still
requires some foresight to implement it before your queries require it so
neither is a magic bullet.

~~~
hotsauceror
Right. What you’d really need would be for the query optimizer to recognize
your non-sargable expression and be able to look up a corresponding function
index, and then have a mechanism for seeking on that. Indexing a computed
column, like you said, wouldn’t actually solve the problem of having to
evaluate each row to begin with.

~~~
marzell
Does a function index somehow eliminate the need for RBAR evaluation? I don't
see how it's functionally any different in this context from a computed column

~~~
hotsauceror
I don’t know how postgresql does it, but I’m interpreting it as yes, their
engine has a way to actually replace nonsargable expressions in the query plan
itself, with some other operation that performs a seek against the index on
the filter. Otherwise you’re right, you’d still need an RBAR evaluation, which
is really the crux of the issue. I wish they’d reframe this discussion as RBAR
instead of sargability, because it covers so many more sins...

