sam0x17

This this this a thousand times this I've had buliding a system such as this for postgres on my bucket list for almost a decade now. Why is this not a thing

code_biologist
There's a lot of good ecosystem stuff around this, though I personally can't use most of it as the HypoPG extension isn't on AWS RDS. :(

https://github.com/HypoPG/hypopg

HypoPG is a PostgreSQL extension adding support for hypothetical indexes.

An hypothetical -- or virtual -- index is an index that doesn't really exists, and thus doesn't cost CPU, disk or any resource to create. They're useful to know if specific indexes can increase performance for problematic queries, since you can know if PostgreSQL will use these indexes or not without having to spend resources to create them.

With one approach to actually implementing it here: https://www.percona.com/blog/2019/07/22/automatic-index-reco...

https://github.com/ankane/dexter is an autoindexer project that builds on hypopg, and I believe it can be configured to autocreate indices — it will only suggest them by default.