Duplicate database indexes

[acscampos]

I started using the feature 'Index Tuning' of the Azure PostgreSQL flexible server for the Netbox database. Netbox version is 3.7.1 and PostgreSQL version is 14.13. After 24 hours, it found 6 rcommendations, all of them to drop duplicate indexes:

• Duplicate of "vpn_tunneltermination_termination". The equivalent index "vpn_tunneltermination_termination" is a unique index, while "vpn_tunnelt_termina_c1f04b_idx" is not.
• Duplicate of "core_managedfile_unique_root_path". The equivalent index "core_managedfile_unique_root_path" is a unique index, while "core_managedfile_root_path" is not.
• Duplicate of "core_autosyncrecord_object". The equivalent index "core_autosyncrecord_object" is a unique index, while "core_autosy_object__c17bac_idx" is not.
• Duplicate of "core_datafile_unique_source_path". The equivalent index "core_datafile_unique_source_path" is a unique index, while "core_datafile_source_path" is not.
• Duplicate of "vpn_l2vpntermination_assigned_object". The equivalent index "vpn_l2vpntermination_assigned_object" is a unique index, while "vpn_l2vpnte_assigne_9c55f8_idx" is not.
• Duplicate of "dcim_cabletermination_unique_termination". The equivalent index "dcim_cabletermination_unique_termination" is a unique index, while "dcim_cablet_termina_884752_idx" is not.

Does anybody know if I can follow the Azure recommendations? Is it possible the Netbox database has duplicate indexes? If so, why?

Thank you.

[jeremystretch]

Looking at the first instance, there are indeed two similar indexes:

"vpn_tunnelt_termina_c1f04b_idx" btree (termination_type_id, termination_id)
"vpn_tunneltermination_termination" UNIQUE CONSTRAINT, btree (termination_type_id, termination_id)

I believe this is because we explicitly define an index for the generic foreign key (GFK) relation termination, which Django does not do automatically. Separately, we also declare a uniqueness constraint for this pair of fields, to ensure an object cannot have more than one termination. Hence the duplicate indexes. It should be safe to remove the former index.

I haven't dug into the others but I suspect they all result from similar situations, and can probably be cleaned up. @acscampos would you mind submitting a housekeeping issue with this information? We can dig into it further when we have time.

Thanks for the report!