I too would like to know concretely what sort of industry or scenario you are involved in, because in general “it’s simpler to roll your own than use mature, industry standard tooling” is bad advice. “Just build your own index with another table” is either hyper-specific to a niche scenario or flat out a terrible idea.

As another commenter pointed out, this means if nothing else you pay an extra round trip to the DB. Allowing the DB to own the index moves that computation to the data and eliminate a round trip network cost. In general fetching some data from the DB to tell you what other data to fetch from the same DB is a poor design. SQL exists to solve this exact problem.

There are absolutely scenarios where scale is so large that you need a partition lookup service. But this is different from not using indices.