Is there a tool that automatically recommends indexes to speed up PostgreSQL?

[henryblythwork]

I know there are custom extensions out there that offer this, like hypopg, but Cloud SQL for Postgres doesn't allow custom extensions to be installed.

Is there a gcloud tool that offers a similar feature, that continuously recommends indexes that would improve performance based on database usage? Or a way I could do this myself using, say, `pg_stat_all_tables` or similar?

Thanks in advance!

Currently running PostgreSQL 14, but we can upgrade to 15-17 if that brings a solution.

[kshenoy]

Will this help?

https://cloud.google.com/sql/docs/postgres/troubleshoot-slow-queries

https://cloud.google.com/sql/docs/postgres/troubleshoot-high-database-load

[henryblythwork]

Thanks @kshenoy , those are helpful, but I'm looking for something that'll alert us ongoing into the future, with a formulated index that we could apply.

Looking at the query insights does help to find slow queries as it shows their plan, but it's often not clear to inexperienced devs how exactly to solve them. If there could be a tool that really understands PostgreSQL and uses its internals to recommend indexes for oft-queried columns in large tables, that'd be really helpful, y'know what I mean?

Like since I saw `hypopg`, I'd essentially really love that in Cloud SQL for Postgres. Is it possible with the tools available in Cloud SQL? Or would we have to gain much more database experience and knowledge (specifically PostgreSQL) in order to improve performance?

Thanks!