Re: Disallow redundant indexes

On Thu, 24 Apr 2025 at 21:27, Japin Li <japinli(at)hotmail(dot)com> wrote:
> 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.

> I’d love to hear your feedback or suggestions for improvement.

Sounds like pointless nannying to me. Also, we don't want GUCs that
change the way things work. It's too annoying for application
developers. I don't have a complete picture of the history of it, but
I believe we still have quite a mess as a result of some GUC-dependent
behaviour. Check the mess around backslash_quote,
standard_conforming_strings and escape_string_warning.

In any case, who are we to define what a duplicate index is? Would
creating a hash index on a column that's already part of a btree index
be disallowed? How about creating an index on (col1) when there's
already an index on (col1,col2)? One person might think that's a waste
of space, and someone else might think it's useful to have the (col1)
index to support faster index-only scans when only that column is
needed.

I get that we have REINDEX CONCURRENTLY now and there's less of a need
to recreate another duplicate index CONCURRENTLY before dropping the
old one, but aren't there still reasons to create a duplicate index
concurrently, e.g to move an index to another tablespace without
blocking queries.

If you want to do this, then maybe just write some query that looks at
pg_index to find duplicates and stick it on the wiki. Anyone who cares
enough about this can run that to check. Oh wait, someone did that
already, see [1].

David

[1] https://wiki.postgresql.org/wiki/Index_Maintenance#Duplicate_indexes