Re: Disallow redundant indexes

On Tue, 29 Apr 2025 at 12:14, Sami Imseih <samimseih(at)gmail(dot)com> wrote:
>> On Mon, Apr 28, 2025 at 01:12:18PM -0500, Sami Imseih wrote:
>> > If I had a dollar for every time I asked a user to check for duplicate
>> > indexes :) So
>> > I think it's a good idea to provide some way to detect these ( besides
>> > a query in the
>> > Wiki ), but I don't think we should prevent it.
>> >
>> > While a WARNING will be a good to have, it could easily go unnoticed,
>> > but it's still good to have.
>>
>> I don't doubt that this is a problem in the field, but I'm not sure I'm
>> convinced it's worthwhile to emit a warning that only appears for exact
>> duplicates and is very easily missed, not to mention that the user could be
>> intentionally creating the index.
>>
>>
>> I don't like to idea of a new GUC to control an SQL command. The WARNING seems
>> fragile because it doesn't solve the problem. I also don't want to disallow
>> duplicate indexes; it can break old routines. If we want to do something about
>> it, I would say let's add a psql meta command or even a new view to show
>> duplicate indexes. I do think that official maintenance queries are a good to
>> step forward to make life easier for DBAs.
>
> I do think adding an annotation to the index in the \d[+] psql
> meta-command will be
> very useful when there are indexes that are the same except for the
> name (and do
> not enforce constraints?). This will at least help DBAs quickly spot
> such indexes in
> their tables.
>

I agree that psql annotations are helpful, but to accommodate users who don't
use it, should we also consider adding a view?

--
Regrads,
Japin Li