Duplicate and overlapping DB indexes

[vhf]

Hi, I noticed a few duplicate DB indexes and indexes overlapping each other.

Removing them would make writes (inserts/updates) slightly faster (doesn't matter that much for TzKT in my experience) and reduce the DB size without impact on read performances.

The two cases are:

• More than one unique index on a same column (PK is always unique, having a two UNIQUE INDEX on the PK doesn't serve any purpose)
• Overlapping index: INDEX ix_foo ON the_table(foo) when INDEX ix_foo_bar ON the_table(foo, bar) also exists
  In this situation if we didn't have ix_foo, querying the_table with WHERE foo (and/or ORDER BY foo) would use ix_foo_bar. It's not exactly as simple as that because ix_foo_bar is bigger than ix_foo and ix_foo can have benefits when postgres is combining indexes. I'd totally understand if you want to keep (some of?) those. I know it's not easy to measure in production because in this situation we wouldn't see ix_foo as unused index. The performances shouldn't be much worse after removing it though, since pg would use ix_foo_bar instead of ix_foo.

On table: BakerCycles

--- overlap: when querying by `Cycle` only, `IX_BakerCycles_Cycle_BakerId` will be used
-CREATE INDEX "IX_BakerCycles_Cycle" ON "BakerCycles"("Cycle" int4_ops);
 CREATE UNIQUE INDEX "IX_BakerCycles_Cycle_BakerId" ON "BakerCycles"("Cycle" int4_ops,"BakerId" int4_ops);
--- duplicate
-CREATE UNIQUE INDEX "IX_BakerCycles_Id" ON "BakerCycles"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_BakerCycles" ON "BakerCycles"("Id" int4_ops);

On table: BakingRights

--- overlap: already covered by `IX_BakingRights_Cycle_BakerId`
-CREATE INDEX "IX_BakingRights_Cycle" ON "BakingRights"("Cycle" int4_ops);
 CREATE INDEX "IX_BakingRights_Cycle_BakerId" ON "BakingRights"("Cycle" int4_ops,"BakerId" int4_ops);

On table: BigMapKeys

--- overlap: already covered by `IX_BigMapKeys_BigMapPtr_KeyHash`
-CREATE INDEX "IX_BigMapKeys_BigMapPtr" ON "BigMapKeys"("BigMapPtr" int4_ops);
 CREATE INDEX "IX_BigMapKeys_BigMapPtr_KeyHash" ON "BigMapKeys"("BigMapPtr" int4_ops,"KeyHash" text_ops);

On table: BigMaps

--- duplicate
-CREATE UNIQUE INDEX "AK_BigMaps_Ptr" ON "BigMaps"("Ptr" int4_ops);
 CREATE UNIQUE INDEX "IX_BigMaps_Ptr" ON "BigMaps"("Ptr" int4_ops);
--- duplicate
-CREATE UNIQUE INDEX "IX_BigMaps_Id" ON "BigMaps"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_BigMaps" ON "BigMaps"("Id" int4_ops);

On table: BigMapUpdates

--- duplicate
-CREATE UNIQUE INDEX "IX_BigMapUpdates_Id" ON "BigMapUpdates"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_BigMapUpdates" ON "BigMapUpdates"("Id" int4_ops);

On table: Commitments

--- duplicate
-CREATE UNIQUE INDEX "IX_Commitments_Id" ON "Commitments"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_Commitments" ON "Commitments"("Id" int4_ops);

On table: Cycles

--- duplicate
-CREATE UNIQUE INDEX "AK_Cycles_Index" ON "Cycles"("Index" int4_ops);
 CREATE UNIQUE INDEX "IX_Cycles_Index" ON "Cycles"("Index" int4_ops);

On table: DelegatorCycles

--- overlap
-CREATE INDEX "IX_DelegatorCycles_Cycle" ON "DelegatorCycles"("Cycle" int4_ops);
 CREATE INDEX "IX_DelegatorCycles_Cycle_BakerId" ON "DelegatorCycles"("Cycle" int4_ops,"BakerId" int4_ops);

On table: Events

--- overlap
-CREATE INDEX "IX_Events_ContractCodeHash" ON "Events"("ContractCodeHash" int4_ops);
 CREATE INDEX "IX_Events_ContractCodeHash_Tag" ON "Events"("ContractCodeHash" int4_ops,"Tag" text_ops);
--- overlap
-CREATE INDEX "IX_Events_ContractId" ON "Events"("ContractId" int4_ops);
 CREATE INDEX "IX_Events_ContractId_Tag" ON "Events"("ContractId" int4_ops,"Tag" text_ops);
--- duplicate
-CREATE UNIQUE INDEX "IX_Events_Id" ON "Events"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_Events" ON "Events"("Id" int4_ops);

On table: Scripts

--- duplicate
-CREATE UNIQUE INDEX "IX_Scripts_Id" ON "Scripts"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_Scripts" ON "Scripts"("Id" int4_ops);

On table: SmartRollupCommitments

--- overlap
-CREATE INDEX "IX_SmartRollupCommitments_Hash" ON "SmartRollupCommitments"("Hash" text_ops);
 CREATE INDEX "IX_SmartRollupCommitments_Hash_SmartRollupId" ON "SmartRollupCommitments"("Hash" text_ops,"SmartRollupId" int4_ops);

On table: Storages

--- duplicate
-CREATE UNIQUE INDEX "IX_Storages_Id" ON "Storages"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_Storages" ON "Storages"("Id" int4_ops);

On table: TokenBalances

--- duplicate
-CREATE UNIQUE INDEX "IX_TokenBalances_Id" ON "TokenBalances"("Id" int8_ops);
 CREATE UNIQUE INDEX "PK_TokenBalances" ON "TokenBalances"("Id" int8_ops);

On table: Tokens

--- overlap
-CREATE INDEX "IX_Tokens_ContractId" ON "Tokens"("ContractId" int4_ops);
 CREATE UNIQUE INDEX "IX_Tokens_ContractId_TokenId" ON "Tokens"("ContractId" int4_ops,"TokenId" text_ops);
--- duplicate
-CREATE UNIQUE INDEX "IX_Tokens_Id" ON "Tokens"("Id" int8_ops);
 CREATE UNIQUE INDEX "PK_Tokens" ON "Tokens"("Id" int8_ops);

On table: TokenTransfers

--- duplicate
-CREATE UNIQUE INDEX "IX_TokenTransfers_Id" ON "TokenTransfers"("Id" int8_ops);
 CREATE UNIQUE INDEX "PK_TokenTransfers" ON "TokenTransfers"("Id" int8_ops);

On table: VotingPeriods

--- duplicate
-CREATE UNIQUE INDEX "AK_VotingPeriods_Index" ON "VotingPeriods"("Index" int4_ops);
 CREATE UNIQUE INDEX "IX_VotingPeriods_Index" ON "VotingPeriods"("Index" int4_ops);
--- duplicate
-CREATE UNIQUE INDEX "IX_VotingPeriods_Id" ON "VotingPeriods"("Id" int4_ops);
 CREATE UNIQUE INDEX "PK_VotingPeriods" ON "VotingPeriods"("Id" int4_ops);

On table: VotingSnapshots

--- overlap
-CREATE INDEX "IX_VotingSnapshots_Period" ON "VotingSnapshots"("Period" int4_ops);
 CREATE UNIQUE INDEX "IX_VotingSnapshots_Period_BakerId" ON "VotingSnapshots"("Period" int4_ops,"BakerId" int4_ops);

I guess my main suggestion is to first drop all duplicate indexes:

DROP INDEX public."IX_BakerCycles_Id";
DROP INDEX public."AK_BigMaps_Ptr";
DROP INDEX public."IX_BigMaps_Id";
DROP INDEX public."IX_BigMapUpdates_Id";
DROP INDEX public."IX_Commitments_Id";
DROP INDEX public."AK_Cycles_Index";
DROP INDEX public."IX_Events_Id";
DROP INDEX public."IX_Scripts_Id";
DROP INDEX public."IX_Storages_Id";
DROP INDEX public."IX_TokenBalances_Id";
DROP INDEX public."IX_Tokens_Id";
DROP INDEX public."IX_TokenTransfers_Id";
DROP INDEX public."AK_VotingPeriods_Index";
DROP INDEX public."IX_VotingPeriods_Id";

and carefully reconsider the overlapping ones.

[Groxan]

Thank you for spotting and for the detailed report! We will remove duplicates and will try to avoid overlapping where it is indeed unnecessary.