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

11–20 of 41 posts

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

#11

Post by BrentOzar »

jimktrains2 wrote: post #9
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…

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.

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

#12

Post by BrentOzar »

jimktrains2 wrote: post #3

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.

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

#13

Post by [deleted] »

BrentOzar wrote: post #11
jimktrains2 wrote: post #9

Earlier quoted context omitted.

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.

[deleted]

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

#14

Post by jwcacces »

taspeotis wrote: post #4

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.

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

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

#15

Post by hotsauceror »

jimktrains2 wrote: post #9
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…

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

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

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

#16

Post by hotsauceror »

BrentOzar wrote: post #10
hotsauceror wrote: post #7

Earlier quoted context omitted.

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

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

#17

Post by hotsauceror »

jwcacces wrote: post #14
taspeotis wrote: post #4

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.

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…

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

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

#18

Post by mlyle »

hotsauceror wrote: post #6
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 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…

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

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

#19

Post by shawnz »

taspeotis wrote: post #4

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.

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

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

#20

Post by ComputerGuru »

mlyle wrote: post #18
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…

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.
Post reply on HN