3 ms·

I totally agree with this. I'm relatively new to SQL but have recently had to start messing around with it. In all other CS domains I know exactly what will hap

I totally agree with this. I'm relatively new to SQL but have recently had to start messing around with it. In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance.

DaiPlusPlus 6y ago
> I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. Azure SQL and Amazon RDS do this - but they’re Cloud-hosted IaaS rather than on-prem. On-prem SQL Server does have similar, but limited capabilities with its Query-Store feature - but I don’t recall any of it being fully automatic - at least not by default: which may or may not be a good thing.
wenc 6y ago
> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. In mathematical modeling, it's the same. You write the model, but what happens under the hood numerically is not that transparent -- in complex cases, you normally don't get to set breakpoints and step through numerical iterations, especially for iterative solvers -- so you have to treat it as a grey box (not black box). You can analyze the problem by analyzing its inputs and outputs, and through the intermediates it exposes. SQL is the same. As are many systems in the physical world. I'd argue the situation in CS is actually more the exception than the norm. > I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. You can do this by inspecting the query plan. In MS SQL, you can even inspect the plan live (while the query is running) and figure out where the bottlenecks are. For obvious cases, MS SQL's client profiler returns (imperfect) recommendations on which fields to index. The trouble is because the behavior of algorithms depends so much on the characteristics of the data (which in a transactional database is changing all the time) and the underlying intention behind the query (which is not always explicitly declared in SQL -- otherwise the language would very extremely verbose), automated optimization is not always possible. Optimizing query plans is a bit of an art -- it's like trying to give hints to an optimizing compiler via pragmas. Azure SQL does some simple analyses and adds/removes indexes as needed and performance is generally somewhat improved, but if you understand your query patterns, you can actually do far better. For instance, if you know you're going to be joining or filtering on certain fields, indexing those fields will speed up the join. Or when you're doing a nonunique JOIN where just one to join to any one record (doesn't matter which one), you can rewrite that as an OUTER APPLY -- a query optimizer wouldn't be able to deduce your intention of "it doesn't matter which to join to" from your declarative query.
- DaiPlusPlus 6y ago
  > 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.
nine_k 6y ago
> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. With experience, you'll learn to appreciate the lack of need to say how to do something, and the ability to just specify what needs to be done. Read more about declarative vs imperative. Knowing exactly what happens under the hood costs a lot of mental resources (this is why assembly is considered low-level), and is often an illusion. A proven tool which knows a particular area instead of you is immensely valuable (think about compilers, for instance). > it would automatically add an index Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema. Software that suggests which indexes would benefit a particular query does exist. Consideration is still needed.
- DaiPlusPlus 6y ago
  > Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema. To be clear, I’m not suggesting a DBMS should always add a new index every time it encounters a query that’s slow due to a missing index. I’m saying that DB servers should look at trends of queries - and see where real wall-clock time is being wasted, and act accordingly. The performance cost of an extra index for insert/update/delete queries is only a concern when you get to the point where you do need to manually manage indexes - but for most database tables with less a few million rows there’s no reason for the server not to automatically add indexes on columns used in predicates. It won’t be perfect, but it’s better than allowing slow queries to always be slow.
  - nine_k 6y ago
    Unfortunately every index takes time to access; it's like dereferencing a pointer. You also usually want the hottest indexes to reside in RAM. Making more indexes compete for RAM pages may and will have implications for other queries, possibly more important. I agree that often machine resources are plentiful, and human resources are scarce, so managing indexes automatically makes sense. It's even implemented, e.g. in Oracle as a complete management tool [1], or in Postgres as a discovery and recommendation tool [2]. [1]: https://oracle-base.com/articles/19c/automatic-indexing-19c https://oracle-base.com/articles/19c/automatic-indexing-19c [2]: https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/ https://www.percona.com/blog/2019/07/22/automatic-index-reco...

DaiPlusPlus 6y ago

> I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. Azure SQL and Amazon RDS do this - but they’re Cloud-hosted IaaS rather than on-prem. On-prem SQL Server does have similar, but limited capabilities with its Query-Store feature - but I don’t recall any of it being fully automatic - at least not by default: which may or may not be a good thing.

wenc 6y ago

> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. In mathematical modeling, it's the same. You write the model, but what happens under the hood numerically is not that transparent -- in complex cases, you normally don't get to set breakpoints and step through numerical iterations, especially for iterative solvers -- so you have to treat it as a grey box (not black box). You can analyze the problem by analyzing its inputs and outputs, and through the intermediates it exposes. SQL is the same. As are many systems in the physical world. I'd argue the situation in CS is actually more the exception than the norm. > I would love it if I could just write queries and it would automatically add an index or do something else for performance if needed, and possibly report back to me if a query I wrote has particularly bad performance. You can do this by inspecting the query plan. In MS SQL, you can even inspect the plan live (while the query is running) and figure out where the bottlenecks are. For obvious cases, MS SQL's client profiler returns (imperfect) recommendations on which fields to index. The trouble is because the behavior of algorithms depends so much on the characteristics of the data (which in a transactional database is changing all the time) and the underlying intention behind the query (which is not always explicitly declared in SQL -- otherwise the language would very extremely verbose), automated optimization is not always possible. Optimizing query plans is a bit of an art -- it's like trying to give hints to an optimizing compiler via pragmas. Azure SQL does some simple analyses and adds/removes indexes as needed and performance is generally somewhat improved, but if you understand your query patterns, you can actually do far better. For instance, if you know you're going to be joining or filtering on certain fields, indexing those fields will speed up the join. Or when you're doing a nonunique JOIN where just one to join to any one record (doesn't matter which one), you can rewrite that as an OUTER APPLY -- a query optimizer wouldn't be able to deduce your intention of "it doesn't matter which to join to" from your declarative query.

DaiPlusPlus 6y ago
> 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.

nine_k 6y ago

> In all other CS domains I know exactly what will happen under the hood for any given action, but for SQL I have zero clue. With experience, you'll learn to appreciate the lack of need to say how to do something, and the ability to just specify what needs to be done. Read more about declarative vs imperative. Knowing exactly what happens under the hood costs a lot of mental resources (this is why assembly is considered low-level), and is often an illusion. A proven tool which knows a particular area instead of you is immensely valuable (think about compilers, for instance). > it would automatically add an index Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema. Software that suggests which indexes would benefit a particular query does exist. Consideration is still needed.

DaiPlusPlus 6y ago
> Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low variation need different indexing approaches, or sometimes even refactoring of the schema. To be clear, I’m not suggesting a DBMS should always add a new index every time it encounters a query that’s slow due to a missing index. I’m saying that DB servers should look at trends of queries - and see where real wall-clock time is being wasted, and act accordingly. The performance cost of an extra index for insert/update/delete queries is only a concern when you get to the point where you do need to manually manage indexes - but for most database tables with less a few million rows there’s no reason for the server not to automatically add indexes on columns used in predicates. It won’t be perfect, but it’s better than allowing slow queries to always be slow.
- nine_k 6y ago
  Unfortunately every index takes time to access; it's like dereferencing a pointer. You also usually want the hottest indexes to reside in RAM. Making more indexes compete for RAM pages may and will have implications for other queries, possibly more important. I agree that often machine resources are plentiful, and human resources are scarce, so managing indexes automatically makes sense. It's even implemented, e.g. in Oracle as a complete management tool [1], or in Postgres as a discovery and recommendation tool [2]. [1]: https://oracle-base.com/articles/19c/automatic-indexing-19c https://oracle-base.com/articles/19c/automatic-indexing-19c [2]: https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/ https://www.percona.com/blog/2019/07/22/automatic-index-reco...