[btown]

Very true. That said, there are almost always situations where reading the data + creating the index + using it N times would be less intensive than just reading the data N times, for very small N.

I'd love to see Postgres or MongoDB with an index-suggesting logger - I'd pay the cost of the logging to have it run on every query, then a background thread would figure out what the "hot spot" types of queries are, figure out what indices to create concurrently, and show it to me in a web interface where I can click a button and create the indices I need concurrently during a low-traffic period of time.

Does anything out there like this exist?

[MSM]

SQL Server has all this information saved (I'm not paid by Microsoft, I swear). I would suspect something is available for other engines as well.

For example, I can easily grab (what it thinks are) the top 10 most impactful missing indexes and create them. The big issue is that, well, they can be really dumb when looking at a macro level. Do I often query a 10 column table for 8 of the columns? It will likely recommend I index those 8 columns. In a vacuum that might be a great index for this specific query, but now I'm basically maintaining two copies of the full table.

To be able to weigh these pros and cons would be a really nice feat, then DBAs will have to abandon the "It Depends" mantra!

[pizza234]

I think indexing is ultimately a trade-off design; the end effect of it is a decision which belongs to a human - what do we make faster at the expense of what?

Probably the best thing a "machine" can do is to give tools to easily perform a reasonably accurate and extensive analysis, but I think any major RDBMS has "good enough" tools to do so.

Interestingly, I think it's important to think who is the target of such tools - the casual developer? the novice dba? the expert dba who works on large datasets? I think even theoretically advanced tools would help only the first case; the other two need to know very well what they're working with.

[rjuju]

The PoWA project (https://dalibo.github.io/powa/) + pg_qualstats extension is doing most of this work.

The pg_qualstats extension will gather statistics on WHERE clauses (number of execution, selectivity...) on the fly, and the powa UI can show you which are the most expensive queries, and suggest index creation based on your real workload. It's then your choice to create the indexes.

hypopg should be added soon to compare query EXPLAIN with and without the hypothetical indexes.

[wc-]

There's a python package called dex (https://github.com/mongolab/dex) that helps assist with index recommendations by looking at mongo's oplog and query response times. It's not perfect but better than nothing for alerting people to a missing index. Unfortunately I think it is not under active development anymore and beginning to fall behind with changes in mongodb.