3 ms·

Because it doesn't really matter. A tree works just fine as an index, be it 100k or 1 billion records. The other part you mention - self tuning - is also quite

by dom0 10y ago

Because it doesn't really matter. A tree works just fine as an index, be it 100k or 1 billion records. The other part you mention - self tuning - is also quite clear imho: either the DB is so small that indexes genuinely do not matter (and this can be surprisingly large today) or it's large enough that you don't want the DB to reorganize itself on a whim. We all know "smart" systems, and we all experienced how flaky their outputs are. You don't want a DB that quite literally scratches the scratch storage because of that. And if we are talking about serious need for indexes, then we almost certainly are talking about subtle tradeoffs that are unlikely to be made like we hope by algorithms that optimize aggregate metrics (eg. average/median query time, cache efficiency etc.) -- consider complicated reporting queries that don't run often, but take a while (but since they are seldom run they are not influential on the databases' performance metrics). A "smart" DB could decide to drop an index, because it accelerates a common write operation, and no important queries needed that index anyway. But now your complex reporting queries are all turned into nested full-table scans. Oops...

Retra 10y ago
There could be a lot of room for optimization if instead of saying "create index blah" you said "I want these queries to be fast: ..." It's easy to lock an optimization plan in place. It's absolutely trivial, so there's no reason to be afraid of your DB reorganizing itself. Just tell it not to if you are satisfied with how it runs. The problem is that people tend to need a ton of expertise and awareness when they restructure their data manually. Did they build the right indexes? Well... the DB doesn't know. It just builds what you tell it to. Even if it could know how to optimize access, if only you gave it an example query rather than a directive over its internal structure. This is the advantage of using declarative language in lieu of imperative language. The DB is supposed to be a data manager. You should say "I want to run these queries, and I have this much memory for you" and it should know how to get your results as fast as possible.
- quizotic 10y ago
  Exactly! We're so stuck in the implementation language of the 1980s: index, tablespace, partition, hash-vs-merge-vs-nestloop join .... that we lose sight of what matters. "I want these queries to be fast" ... genius.
- blitzd 10y ago
  The SQL Server Database Tuning Adviser will happily create all the indexes, stats, and what not, for a given workload. And yeah, you can speed up your queries (the workload) as a result. The problem is that there's always a trade off - those queries may now be fast, but inserts and updates end up being slow due to the burden of index maintenance. There's also maintenance views that can be queried to see if SQL Server is detecting any missing indexes, or if there's any indexes that are not being used - but again these have to be taken with a grain of salt, as the stats get reset each time those indexes are rebuilt. > Did they build the right indexes? Well... the DB doesn't know. The DB does in fact know - looking at the query plan for a given query will outline if there are any obvious missing indexes - but having the DB go ahead and build indexes by default for every given query is dangerous. When you go through 10 iterations of an ad-hoc query refining it down to what you actually want, how's the DB to know that the first 9 don't count? Etc.. > You should say "I want to run these queries, and I have this much memory for you" and it should know how to get your results as fast as possible. That is in essence what the query optimizer attempts to do, though it has breaking points where it will settle for 'good enough' because optimization of the query itself becomes too expensive. Sometimes people just write bad queries.
  - Retra 10y ago
    My point is that you don't build indexes for every query. You build it for the important queries, and you let the user tell you which queries are important, rather than which indexes to build. Because honestly, the only reason any user cares that an index exists is if they need it for performance. It should be an implementation detail. Something better than an index might come along, and we've all got to run victorian-age schemas because we've written "create index" all over the place.
quizotic 10y ago
No. Not true at all. At around a billion records, you start getting into distributed/scale-out systems, and distributed indexes are performance death whether for OLTP or analytics. Even before you cross into distributed query processing, the random access nature of indexes levies a heavy tax. Indexes are a tiny piece of this issue. They address the problem of finding a single needle in a haystack, but if you need to aggregate lots of data, indexes are largely worthless. What about different compression schemes for different sets of "column" values - with the idea that you execute against the compressed values, rarely ever decompressing. Even column store systems that allow you to specify a compression scheme aren't adaptive. Why the hell not? This is almost 2020.
- needusername 10y ago
  > At around a billion records, you start getting into distributed/scale-out systems, and distributed indexes are performance death whether for OLTP or analytics. Even before you cross into distributed query processing, the random access nature of indexes levies a heavy tax. I assume you mean once process/aggregate a billion records. Having a billion records in a single table on a single, non-distributed system is not an issue at all.
- gaius 10y ago
  At around a billion records, you start getting into distributed/scale-out systems Uhh, a billion-row table you can handle on a laptop easily. Probably in SQLite.
- orf 10y ago
  > At around a billion records, you start getting into distributed/scale-out systems Yeahhh.... no. I remember running a 250-300,000,000 row postgres table of Wikipedia links on my 2010 Dell XPS and it functioning fine, if not a bit slow (it had a huge index on an array column). Good machine, but ancient now.

by dom0 10y ago

Retra 10y ago

There could be a lot of room for optimization if instead of saying "create index blah" you said "I want these queries to be fast: ..." It's easy to lock an optimization plan in place. It's absolutely trivial, so there's no reason to be afraid of your DB reorganizing itself. Just tell it not to if you are satisfied with how it runs. The problem is that people tend to need a ton of expertise and awareness when they restructure their data manually. Did they build the right indexes? Well... the DB doesn't know. It just builds what you tell it to. Even if it could know how to optimize access, if only you gave it an example query rather than a directive over its internal structure. This is the advantage of using declarative language in lieu of imperative language. The DB is supposed to be a data manager. You should say "I want to run these queries, and I have this much memory for you" and it should know how to get your results as fast as possible.

quizotic 10y ago
Exactly! We're so stuck in the implementation language of the 1980s: index, tablespace, partition, hash-vs-merge-vs-nestloop join .... that we lose sight of what matters. "I want these queries to be fast" ... genius.
blitzd 10y ago
The SQL Server Database Tuning Adviser will happily create all the indexes, stats, and what not, for a given workload. And yeah, you can speed up your queries (the workload) as a result. The problem is that there's always a trade off - those queries may now be fast, but inserts and updates end up being slow due to the burden of index maintenance. There's also maintenance views that can be queried to see if SQL Server is detecting any missing indexes, or if there's any indexes that are not being used - but again these have to be taken with a grain of salt, as the stats get reset each time those indexes are rebuilt. > Did they build the right indexes? Well... the DB doesn't know. The DB does in fact know - looking at the query plan for a given query will outline if there are any obvious missing indexes - but having the DB go ahead and build indexes by default for every given query is dangerous. When you go through 10 iterations of an ad-hoc query refining it down to what you actually want, how's the DB to know that the first 9 don't count? Etc.. > You should say "I want to run these queries, and I have this much memory for you" and it should know how to get your results as fast as possible. That is in essence what the query optimizer attempts to do, though it has breaking points where it will settle for 'good enough' because optimization of the query itself becomes too expensive. Sometimes people just write bad queries.
- Retra 10y ago
  My point is that you don't build indexes for every query. You build it for the important queries, and you let the user tell you which queries are important, rather than which indexes to build. Because honestly, the only reason any user cares that an index exists is if they need it for performance. It should be an implementation detail. Something better than an index might come along, and we've all got to run victorian-age schemas because we've written "create index" all over the place.

quizotic 10y ago

No. Not true at all. At around a billion records, you start getting into distributed/scale-out systems, and distributed indexes are performance death whether for OLTP or analytics. Even before you cross into distributed query processing, the random access nature of indexes levies a heavy tax. Indexes are a tiny piece of this issue. They address the problem of finding a single needle in a haystack, but if you need to aggregate lots of data, indexes are largely worthless. What about different compression schemes for different sets of "column" values - with the idea that you execute against the compressed values, rarely ever decompressing. Even column store systems that allow you to specify a compression scheme aren't adaptive. Why the hell not? This is almost 2020.

needusername 10y ago
> At around a billion records, you start getting into distributed/scale-out systems, and distributed indexes are performance death whether for OLTP or analytics. Even before you cross into distributed query processing, the random access nature of indexes levies a heavy tax. I assume you mean once process/aggregate a billion records. Having a billion records in a single table on a single, non-distributed system is not an issue at all.
gaius 10y ago
At around a billion records, you start getting into distributed/scale-out systems Uhh, a billion-row table you can handle on a laptop easily. Probably in SQLite.
orf 10y ago
> At around a billion records, you start getting into distributed/scale-out systems Yeahhh.... no. I remember running a 250-300,000,000 row postgres table of Wikipedia links on my 2010 Dell XPS and it functioning fine, if not a bit slow (it had a huge index on an array column). Good machine, but ancient now.