Hi, hackers
Currently, PostgreSQL permits creating multiple indexes on the same columns
in the same order for a table, potentially leading to redundant indexes.
For example:
CREATE INDEX ON t(id);
CREATE INDEX ON t(id);
While permitted, this leads to:
- Increased storage consumption
- Performance degradation (for data modification)
- Maintenance overhead
- Potential query optimizer confusion
Oracle prevents this with an error like ORA-01408: such column list already
indexed [1].
I propose that PostgreSQL prevent redundant index creation by:
- Checking for identical existing indexes during CREATE INDEX.
- Failing with an error (like Oracle's ORA-01408) if a duplicate is found.
- Providing a GUC parameter (allow_redundant_indexes) to control this.
This change would:
- Prevent accidental redundancy
- Optimize storage
- Improve performance
- Simplify maintenance
- Enhance efficiency and user flexibility
I’d love to hear your feedback or suggestions for improvement.
[1] https://docs.oracle.com/en/error-help/db/ora-01408/?r=19c
--
Regrads,
Japin Li
