The function `Right(...)` is not indexable because... it's a function - we could apply infinite many types of function on column `SomeColumn` and it'd be unreasonable to create index for them all, with different arguments.

It depends heavily how smart the optimizer of the underlying database is in order to pick up on the fact that there's another column or index made that's precomputed for `Right(...)` with the correct arguments applied to it.

Postgres can do indexes on expressions. (A bitmap scan over 2 rows may not be the most impressive example!) https://www.postgresql.org/docs/12/indexes-expressional.html

  foo=# create table foo(x TEXT);
  foo=# CREATE INDEX foo_r3 on foo (right(x,3));
  foo=# INSERT INTO foo VALUES ('aaaaaaaaaaaaazzz');
  foo=# INSERT INTO foo VALUES ('xxxxxxxaaa');
  foo=# SELECT * FROM foo WHERE right(x,3) > 'fff';
          x         
  ------------------
   aaaaaaaaaaaaazzz
  (1 row)
  
  foo=# EXPLAIN ANALYZE SELECT * FROM foo WHERE right(x,3) > 'fff';
                                                     QUERY PLAN                                                    
  -----------------------------------------------------------------------------------------------------------------
   Bitmap Heap Scan on foo  (cost=7.66..24.46 rows=453 width=32) (actual time=0.018..0.018 rows=1 loops=1)
     Recheck Cond: ("right"(x, 3) > 'fff'::text)
     Heap Blocks: exact=1
     ->  Bitmap Index Scan on foo_r3  (cost=0.00..7.55 rows=453 width=0) (actual time=0.010..0.010 rows=1 loops=1)
           Index Cond: ("right"(x, 3) > 'fff'::text)
   Planning Time: 0.072 ms
   Execution Time: 0.063 ms
  (7 rows)

If one thinks about how that index is created underneath the covers, it's basically precomputing that column and using it as an index! =)

Yes exactly. But it works very well in practice.

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.

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..)

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

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.)

> 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.

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.”

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

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.

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.

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.