baking-bad/tzkt

Duplicate and overlapping DB indexes

Closed this issue · 1 comments

vhf commented

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 commented

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

  • ❤️1