So presumably you could do the following on your entire database:<p><pre><code> for each query in my codebase Q:
for each possible combination of indexes I for Q:
create hypothetical index I
run EXPLAIN query Q
if Q uses I, create I
</code></pre>
If EXPLAIN doesn't actually run a query, and creating hypothetical indexes doesn't actually write anything, the above could presumably done in milliseconds, no?<p>Why not take this one step further and just enable an autoindexing mode in postgres using the query history as a heuristic?
Combining this with just a bit of programming loops could get you pretty far at automatically creating indexes. Basically take a query plan and then create all possibly indexes hypothetically until you get the most optimal query.
Cool one! Thankfully postgres allows creating indexes concurrently (without blocking writes) but 'hypothetical indexes' seem more convenient.<p>What would be even cooler though is the ability to see multiple plans considered (and rejected) by the planner with all associated costs. It often takes a lot of painful guesswork to understand why particular superior plan is not used.
I'd always wondered why query engines don't create such temporary indices, for example in the case of large subquery queries, where creating and index AND doing the query with an index runs way faster in total than waiting for it to run without the index. Any insights on this? I guess predicting the gain could be difficult.
This is quite excellent for those of us doing research in physical schema design. As far as I know, this is the first "what-if" analysis tool integrated into an open source database. Although if there are others, I'd love to know about them :)
One of my major hang-ups about transitioning away from SQL Server to Postgres is the productivity that comes from tools like Profiler and Database Engine Tuning Advisor (which uses hypothetical indexes to find performance improvements). Can anyone suggest analogous tools for Postgres?