> For instance, the fact that you need to manually specify so many details about indices sounds like a failure to have a good theory around indexing
Real life isn't about theory, it's about what works and doesn't work in production. These are not flaws, they're features. I don't need a bitmap index on my primary key, nor does it need to be selective because it will include every row. But having these features and the ability to customize based on the needs of my product is paramount.
If you were not using an RDBMS to store your data, you'd still have these same problems and you'd still end up with optimized secondary indexes based on the lookup criteria.
Real life isn't about theory, it's about what works and doesn't work in production. These are not flaws, they're features. I don't need a bitmap index on my primary key, nor does it need to be selective because it will include every row. But having these features and the ability to customize based on the needs of my product is paramount.
If you were not using an RDBMS to store your data, you'd still have these same problems and you'd still end up with optimized secondary indexes based on the lookup criteria.