PostgreSQL Index Advisor is a helpful extension that recommends indexes to enhance query performance. It supports generic parameters like $1, $2, and materialized views. The API identifies tables and columns obscured by views and generates SQL DDL create index statements to optimize query execution. For example, running the index_advisor function on a simple query with an unindexed column can significantly reduce startup and total costs. More complex queries may require additional suggested indexes for further performance improvement. A notable aspect is that it requires Postgres with hypopg installed for installation and can be easily installed and tested using the provided git commands.
https://github.com/supabase/index_advisor