3 ms·

> Adding an index has performance costs, storage costs, etc. Just indexing every permutation of columns is prohibitively expensive. Then, columns with low varia

> 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...
no-s 6y ago
>> 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. You'd be surprised. The thing that really drives index explosion is the number of distinct queries. IME, inspecting and reducing distinct queries is one of the easiest ways to optimize and it can typically be automated. I've migrated systems from millions of distinct queries down to hundreds or dozens. However the objective of the optimization is typically driven by requirements external to the database and may vary over time. Thus difficult to align with an optimizer.
- Akronymus 6y ago
  Distinct as in doing different things or distinct as in prefer batching? My English seems to be failing me here.

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

no-s 6y ago

>> 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. You'd be surprised. The thing that really drives index explosion is the number of distinct queries. IME, inspecting and reducing distinct queries is one of the easiest ways to optimize and it can typically be automated. I've migrated systems from millions of distinct queries down to hundreds or dozens. However the objective of the optimization is typically driven by requirements external to the database and may vary over time. Thus difficult to align with an optimizer.

Akronymus 6y ago
Distinct as in doing different things or distinct as in prefer batching? My English seems to be failing me here.