index_advisor is a project that almost made the cut for LW7. We decided to launch it as a pgtle instead to get some feedback before we (spoiler) probably release it in LW8

It uses the hypopg package to very quickly generate an index for each column referenced in the query and then re-run's an explain plan to measure the estimated "total cost".

If the expected "total_cost" reduces, we add the indexes to the list of recommendations and continue testing the remaining columns.

Its currently limited to single column indexes only but in a later update we'll enhance that to do more comprehensive searches with multiple columns in a single index

Cool idea for an extension!

I had a whole set of comments about concerns about doing this to a large table in prod, and statistics and such - then I read about hypopg.

What a neat package, learned something new today.