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).
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.
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).
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?
> 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.
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...
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.
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.)
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..)
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.
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.
40 comments
[ 3.8 ms ] story [ 81.1 ms ] threadDatabase 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).
http://www.postgresql.org/docs/9.4/static/using-explain.html
(Blog post I wrote) http://jimkeener.com/posts/explain-pg
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.
The value this provides is making development very agile (in the dictionary sense of the word).
Why not just use a function 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.
It’s things like this that keep us paying $millions for SQL Server Enterpise vs. Postgres.
https://docs.microsoft.com/en-us/sql/relational-databases/vi...
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
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.
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.
It means that the database take your Search ARGuments (SARG) and do an index seek to deliver the exact results you want.
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.