Can adding an index make a non SARGable query SARGable? | Hacker News Reader

Can adding an index make a non SARGable query SARGable?

sqlservercode.blogspot.com

35 pointsfurtively38 comments

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

Explain and explain analyze are very powerful tools for understanding query performance.

http://www.postgresql.org/docs/9.4/static/using-explain.html

(Blog post I wrote) http://jimkeener.com/posts/explain-pg

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.

Regather the table statistics. If it was the same data I'd be shocked, but this isn't that surprising.

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

It's the price you pay for having a declarative language that lets the language implementation pick from a large universe of equivalent ways to actually compile and run the code. You now have to know how to tune the thing, and that requires having either specialist knowledge of the implementation in question, or the skills to find and apply that knowledge quickly.

The value this provides is making development very agile (in the dictionary sense of the word).

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

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.

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

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.

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

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.

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

Why not just use a function index?

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

> Why not just use a function index?

Microsoft SQL Server doesn't have those.

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.

Actually the last time I checked, materialized views had a ton of restrictions on them, including things like not being able to use joins. Has that changed in recent versions?

You've been able to do joins in them for a decade. You just can't do self-joins. (But there are indeed a ton of restrictions.)

According to this here web site, it also forbids outer joins, which is probably what I was remembering.

https://docs.microsoft.com/en-us/sql/relational-databases/vi...

They're basically only usable on a super blue moon lunar eclipse.

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

See 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

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

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.

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.

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

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

Isn't an mssql indexed view essentially a materialized view? Postgres also supports indexes on materialized views.

An MSSQL indexed view is like a materialized view, except it's not actually materialized beyond the index, as I understand it, and there are restrictions on the view definition that mat views don't have, and it updates automatically.

I think in all the dozens of times I've tried it I've never had a view pass their draconian requirements for indexing.

There's always a self-join or a left join or a sqlclr function or a format or an unsupported aggregate or something else that was a critical thing that I really needed in the index.

If the Microsoft SQL Server team really thinks this feature is a critical selling point, maybe they should work more on it since they were introduced back in like SQL Server 2005 and still have the same miserable whips-and-chains-bondage level masochism experience.

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

Using these millions to better design your applications is better in the long run. You can totally live without those with a good schema and better queries.

I think features like column store and always on are worth more that those band-aid features aimed at pleasing ugly ERPs.

Reply on news.ycombinator.com