Hacker News Reader

Comment by Retra | Hacker News Reader

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.

View on HN

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.

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.

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.

Reply on news.ycombinator.com