3 ms·

> You can do this by inspecting the query plan That’s what I’m railing against! Most of the time the RDBMS should be self-aware enough to implement the require

> You can do this by inspecting the query plan That’s what I’m railing against! Most of the time the RDBMS should be self-aware enough to implement the required changes to improve query performance by itself, without needing us to do that. SQL Server’s query-plans are nice - I won’t deny - but it’s impossible for a relative database newbie to even understand what a query-plan even is - less so to understand what the plan is saying - and considerably less to understand what changes should be made to the system to fix any performance issues. Probably 90% of the time it’s a missing index or out-of-date STATISTICS objects. When I first learned about RDBMS indexes (this was probably about 2-3 years after I was already writing database-driven websites in Dreamweaver in secondary school) I was flummoxed that I had to manage indexes manually - I long assumed that a DBMS would just index everything because Google search engine and Windows XP’s file indexer did, I got the impression that indexing was a-given on any modern platform, that’s why I’m disappointed that it’s 2020 and a local MySQL instance will still gladly let a regularly-run query perform a full table-scan. If Apple designed an RDBMS as a consumer product, there wouldn’t be any exposed indexes.

wenc 6y ago
I point you to this discussion: https://dba.stackexchange.com/questions/43772/why-dont-databases-create-their-own-indexes-automatically https://dba.stackexchange.com/questions/43772/why-dont-datab... Auto update statistics is something you can turn on, but you can't (well shouldn't) arbitrarily index every field because there's a write load/performance cost. Also sometimes a table scan has the same performance characteristics as reading from an index -- it all depends on your query. The query planner already does a ton of optimizations, but indexing is one of those things that may or may not help -- there's a stochastic element at play -- you almost have to have a model of current and future data + query mix, and which you then have to profile for any gains -- which is why most databases leave the decision to the admins. Index creation after all is based on a priori expectations of what future data will look like based on historical patterns -- it is predictive in nature. It is akin to front-loading computation to create a derived data structure that you hope will match future query patterns. You could do continuous profiling but it's expensive. Also finding the right combination of indexes is a combinatorial problem coupled with the fact that the data is constantly changing -- it's a very difficult optimization problem. The situation has changed somewhat, and I've read about research on using modern machine learning methods to help databases tune/index themselves (which probably what Azure SQL is doing, as well as a few other cloud db's). Azure SQL's database tuning again is passable and better than nothing, but it's not super good -- I suspect it's very conservative in order to avoid risky indexes that may slow down corner cases. I have it turned on but I still find myself hand-tuning indexes to achieve the performance I need. (that said, I also mostly run analytic queries so converting all my fact tables to the Clustered Columnstore Index data structure pretty much speeds all of my queries up without any tuning.) There's also an alternate approach based on linting: https://www.eversql.com/sql-query-optimizer/ https://www.eversql.com/sql-query-optimizer/ At any rate, for most of the history of RDBMSes this type of ML technology was not available or was very expensive to operate on large-scale databases. It's a heavily researched area, and the fact you don't see it implemented at scale reliably suggests that there practical difficulties. It's not just an ease-of-use issue.
ralph84 6y ago
> If Apple designed an RDBMS as a consumer product, there wouldn’t be any exposed indexes. Apple has an RDBMS product: FileMaker. It does support automatically creating indexes, but indexes are exposed and users can manually configure indexes if they choose.

wenc 6y ago

I point you to this discussion: https://dba.stackexchange.com/questions/43772/why-dont-databases-create-their-own-indexes-automatically https://dba.stackexchange.com/questions/43772/why-dont-datab... Auto update statistics is something you can turn on, but you can't (well shouldn't) arbitrarily index every field because there's a write load/performance cost. Also sometimes a table scan has the same performance characteristics as reading from an index -- it all depends on your query. The query planner already does a ton of optimizations, but indexing is one of those things that may or may not help -- there's a stochastic element at play -- you almost have to have a model of current and future data + query mix, and which you then have to profile for any gains -- which is why most databases leave the decision to the admins. Index creation after all is based on a priori expectations of what future data will look like based on historical patterns -- it is predictive in nature. It is akin to front-loading computation to create a derived data structure that you hope will match future query patterns. You could do continuous profiling but it's expensive. Also finding the right combination of indexes is a combinatorial problem coupled with the fact that the data is constantly changing -- it's a very difficult optimization problem. The situation has changed somewhat, and I've read about research on using modern machine learning methods to help databases tune/index themselves (which probably what Azure SQL is doing, as well as a few other cloud db's). Azure SQL's database tuning again is passable and better than nothing, but it's not super good -- I suspect it's very conservative in order to avoid risky indexes that may slow down corner cases. I have it turned on but I still find myself hand-tuning indexes to achieve the performance I need. (that said, I also mostly run analytic queries so converting all my fact tables to the Clustered Columnstore Index data structure pretty much speeds all of my queries up without any tuning.) There's also an alternate approach based on linting: https://www.eversql.com/sql-query-optimizer/ https://www.eversql.com/sql-query-optimizer/ At any rate, for most of the history of RDBMSes this type of ML technology was not available or was very expensive to operate on large-scale databases. It's a heavily researched area, and the fact you don't see it implemented at scale reliably suggests that there practical difficulties. It's not just an ease-of-use issue.

ralph84 6y ago

> If Apple designed an RDBMS as a consumer product, there wouldn’t be any exposed indexes. Apple has an RDBMS product: FileMaker. It does support automatically creating indexes, but indexes are exposed and users can manually configure indexes if they choose.