Remove duplicate indexes

[tamirms]

I noticed today that there is a duplicate index on the id column of the history transactions table:

https://github.com/stellar/go/blob/master/services/horizon/internal/db2/schema/migrations/1_initial_schema.sql#L247

CREATE UNIQUE INDEX hs_transaction_by_id ON history_transactions USING btree (id);

https://github.com/stellar/go/blob/master/services/horizon/internal/db2/schema/migrations/1_initial_schema.sql#L344

CREATE UNIQUE INDEX index_history_transactions_on_id ON history_transactions USING btree (id);

I thought that there might be more duplicate indexes so I ran the sql queries here to detect if there were more:

psql (9.6.14, server 9.6.17)
Type "help" for help.
horizon=# SELECT indrelid::regclass
horizon-#      , array_agg(indexrelid::regclass)
horizon-#   FROM pg_index
horizon-#  GROUP BY indrelid
horizon-#      , indkey
horizon-# HAVING COUNT(*) > 1;
       indrelid       |                        array_agg                        
----------------------+---------------------------------------------------------
 history_ledgers      | {hs_ledger_by_id,index_history_ledgers_on_id}
 history_transactions | {hs_transaction_by_id,index_history_transactions_on_id}
(2 rows)
horizon=# SELECT indrelid::regclass
horizon-#      , array_agg(indexrelid::regclass)
horizon-#   FROM pg_index
horizon-#  GROUP BY indrelid
horizon-#      , indkey[0]
horizon-# HAVING COUNT(*) > 1;
       indrelid       |                               array_agg                               
----------------------+-----------------------------------------------------------------------
 exp_asset_stats      | {exp_asset_stats_pkey,exp_asset_stats_by_code}
 history_transactions | {hs_transaction_by_id,index_history_transactions_on_id}
 history_assets       | {history_assets_asset_code_asset_type_asset_issuer_key,asset_by_code}
 history_ledgers      | {hs_ledger_by_id,index_history_ledgers_on_id}
(6 rows)

I've confirmed that hs_ledger_by_id,index, history_ledgers_on_id are also duplicate indexes.

Also, it seems that we forgot to drop the exp_asset_stats table in https://github.com/stellar/go/blob/master/services/horizon/internal/db2/schema/migrations/32_drop_exp_history_tables.sql

We should remove the duplicate indexes and the exp_asset_stats table. Removing duplicate indexes will improve write performance and reduce storage space for Horizon's db.

[ire-and-curses]

Sounds great, nice catches!

[tamirms]

fixed in #2419