Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Can adding an index make a non SARGable query SARGable?

sqlservercode.blogspot.com

Post reply on HN
Print view View original

31–40 of 41 posts

Re: Can adding an index make a non SARGable query SARGable?

#31

Post by hotsauceror »

marzell wrote: post #27
hotsauceror wrote: post #17

Earlier quoted context omitted.

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.

Re: Can adding an index make a non SARGable query SARGable?

#32

Post by hotsauceror »

Pxtl wrote: post #22
hotsauceror wrote: post #6

Earlier quoted context omitted.

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 conver…

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

Re: Can adding an index make a non SARGable query SARGable?

#33

Post by marzell »

hotsauceror wrote: post #31
marzell wrote: post #27

Earlier quoted context omitted.

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

Re: Can adding an index make a non SARGable query SARGable?

#34

Post by bfung »

Shish2k wrote: post #5

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

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.

Re: Can adding an index make a non SARGable query SARGable?

#35

Post by DougBTX »

Pxtl wrote: post #21
Topgamer7 wrote: post #2

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…

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.

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

Re: Can adding an index make a non SARGable query SARGable?

#36

Post by andrewf »

bfung wrote: post #34
Shish2k wrote: post #5

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

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 corre…

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)

Re: Can adding an index make a non SARGable query SARGable?

#37

Post by heavenlyblue »

hotsauceror wrote: post #32
Pxtl wrote: post #22

Earlier quoted context omitted.

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

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.

Re: Can adding an index make a non SARGable query SARGable?

#38

Post by hotsauceror »

heavenlyblue wrote: post #37
hotsauceror wrote: post #32

Earlier quoted context omitted.

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 tab…

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.

Re: Can adding an index make a non SARGable query SARGable?

#39

Post by hotsauceror »

marzell wrote: post #33
hotsauceror wrote: post #31

Earlier quoted context omitted.

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

Re: Can adding an index make a non SARGable query SARGable?

#40

Post by bfung »

andrewf wrote: post #36
bfung wrote: post #34

Earlier quoted context omitted.

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 corre…

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 ------------------ aaaaa…

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