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