5 ms·

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 force

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 7y ago
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 7y ago
  > 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.
  - deleted 7y ago
    [deleted]
- hotsauceror 7y ago
  What Brent said. I basically interpret it to mean “this expression can not be satisfied (covered) by the use of an index even if an appropriate one existed.”
mlyle 7y ago
A lot of people are used to Postgres where you can create an index on the expression itself, and then it's cheap to seek or order on that expression. (Common ones are things like this-- substrings, date expressions, uppercasing of things..)
- ComputerGuru 7y ago
  SQLite does this too out of the box, though many GUI tools struggle to cope with tables created in that fashion.
Pxtl 7y ago
... if isnull/coalesce is really non-sargable, it's time for me to give up and switch to Mongo.
- hotsauceror 7y ago
  It is. You’ve applied a function to a value, and that function needs to be evaluated for every row in the set. One way to deal with cases where you need to replace Nulls, or to do any other kind of polishing, is to return the raw data into a temp table and then do an update on that temp table, replacing all nulls with your chosen value using WHERE x IS NULL. This minimizes the time you hold any locks on your live tables. And now you can index the hell out of your “staging” table and use that as your driver for additional joins etc.
  - heavenlyblue 7y ago
    Let's be honest - this is an SQL-server only issue. Nobody says you can't index NULL values, it's just that SQL server can't do that.
    - hotsauceror 7y ago
      That’s not quite true. You can certainly index null values, and you can filtered indexes to remove nulls. What sql server cannot do is index functions themselves, and use those indexes to satisfy queries with nonsargable expressions.

jimktrains2 7y ago

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 7y ago
> 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.
- deleted 7y ago
  [deleted]
hotsauceror 7y ago
What Brent said. I basically interpret it to mean “this expression can not be satisfied (covered) by the use of an index even if an appropriate one existed.”

mlyle 7y ago

A lot of people are used to Postgres where you can create an index on the expression itself, and then it's cheap to seek or order on that expression. (Common ones are things like this-- substrings, date expressions, uppercasing of things..)

ComputerGuru 7y ago
SQLite does this too out of the box, though many GUI tools struggle to cope with tables created in that fashion.

Pxtl 7y ago

... if isnull/coalesce is really non-sargable, it's time for me to give up and switch to Mongo.

hotsauceror 7y ago
It is. You’ve applied a function to a value, and that function needs to be evaluated for every row in the set. One way to deal with cases where you need to replace Nulls, or to do any other kind of polishing, is to return the raw data into a temp table and then do an update on that temp table, replacing all nulls with your chosen value using WHERE x IS NULL. This minimizes the time you hold any locks on your live tables. And now you can index the hell out of your “staging” table and use that as your driver for additional joins etc.
- heavenlyblue 7y ago
  Let's be honest - this is an SQL-server only issue. Nobody says you can't index NULL values, it's just that SQL server can't do that.
  - hotsauceror 7y ago
    That’s not quite true. You can certainly index null values, and you can filtered indexes to remove nulls. What sql server cannot do is index functions themselves, and use those indexes to satisfy queries with nonsargable expressions.