PostgreSQL limits the number of columns for each index with 32 columns.
Indexes can be created for all possible combination of 1, 2, ... N columns. Where N is not greater than 32, but is configurable by user.
It is well-defined for each of PostgreSQL type which indexes are better suited for that type.
So this stored procedure should have two optional arguments - the maximum number of columns for combined indexes and the restricted set of applied index types (B-tree, hash, etc.).
Once this stored procedure is done, the next step will be to implement a stored procedure that will delete all unused indexes. Used indexes can be checked using stats.
On the second thought, I think it should be a good idea to have a mandatory limitation on the maximum number of columns for combined indexes. If we do not limit it and use 32, we will end up with this number of possible combinations:
https://github.com/deepcase/materialized-path/blob/011c8b6b7464097357d2435b6822aa4003887837/migrations/1633562151439-multidirectional.ts#L158-L182
PostgreSQL limits the number of columns for each index with 32 columns. Indexes can be created for all possible combination of 1, 2, ... N columns. Where N is not greater than 32, but is configurable by user. It is well-defined for each of PostgreSQL type which indexes are better suited for that type. So this stored procedure should have two optional arguments - the maximum number of columns for combined indexes and the restricted set of applied index types (B-tree, hash, etc.).
Once this stored procedure is done, the next step will be to implement a stored procedure that will delete all unused indexes. Used indexes can be checked using stats.
On the second thought, I think it should be a good idea to have a mandatory limitation on the maximum number of columns for combined indexes. If we do not limit it and use 32, we will end up with this number of possible combinations:
https://www.wolframalpha.com/input/?i=Sum%5BCatalanNumber%5Bi%5D%2C+%7Bi%2C+1%2C+32%7D%5D
We can use smaller numbers: