index_advisor

pghero and/or https://github.com/supabase/index_advisor If you want to pay for a saas then pganalyze is like $400 a month for 4 dbs is a pretty good pricing model

hypopg is now supported in RDS so the steps would be: 1. Enable hypopg create extension if not exists hypopg; 2. Copy/paste the plpgsql file: https://github.com/supabase/index_advisor/blob/main/index_ad... We are also developing the Trusted Language Extension with the RDS team, so at some point it should be easier to do this through database.dev: https://database.dev/olirice/index_advisor

This is a Postgres extension that can determine if a query should have an index. For example, for this table: create table book( id int primary key, title text not null ); You can run `index_advisor()` to see if there should be an index on a select statement: select * from index_advisor('select book.id from book where title = $1'); And it will return (summarized): {"CREATE INDEX ON public.book USING btree (title)"} It works particularly well with pg_stat_statements[0] which tracks execution statistics of all SQL statements executed on your Postgres database. It leans heavily on HypoPG[1], an excellent extension to determine if PostgreSQL will use a given index without spending resources to create them. [0] pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.htm... [1] https://git