Can adding an index make a non SARGable query SARGable?

http://sqlservercode.blogspot.com/2019/06/Can-adding-an-index-make-a-non-SARGable-query-SARGable-instead-of-rewriting-sql-query.html

by furtively • 7 years ago

35 38 7 years ago

sqlservercode.blogspot.com

Topgamer7• 7 years ago

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

jimktrains23• 7 years ago

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

Pxtl4• 7 years ago

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.

wbl• 7 years ago

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

DougBTX• 7 years ago

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

cryptonector• 7 years ago

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

jimktrains23• 7 years ago

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?

BrentOzar3• 7 years ago

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.

taspeotis• 7 years ago

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.

hotsauceror9• 7 years ago

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?

BrentOzar3• 7 years ago

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

jwcacces• 7 years ago

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

hotsauceror9• 7 years ago

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

shawnz• 7 years ago

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

dragonwriter• 7 years ago

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.

Pxtl4• 7 years ago

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.

grumpydba• 7 years ago

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.

Shish2k• 7 years ago

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

hotsauceror9• 7 years ago

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.

jimktrains23• 7 years 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.)

mlyle• 7 years 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..)

Pxtl4• 7 years ago

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

bfung2• 7 years ago

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.

andrewf• 7 years ago

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)

Can adding an index make a non SARGable query SARGable?

Related Stories