For partner chains active sync a couple of additional indexes are handy

[gilescope]

Partner chains noticed that they needed to add in the following two indexes:

CREATE INDEX IF NOT EXISTS idx_ma_tx_out_ident ON ma_tx_out(ident);
CREATE INDEX IF NOT EXISTS idx_redeemer_tx_id ON redeemer(tx_id);

These help with performance using as an active bridge with Cardano to partnerchains. We've been manually applying these indexes but maybe these would be helpful for others if they were included by default?

[rdlrt]

Quoting from #1349 (you'd find many other indexes too that may be relevant to some based on their usage patterns, but has adverse effect for those who dont neeed them)

In general, since db-sync is used by different clients with different query needs, it should be the responsibility of clients to add the indexes that they need. More information can be found https://github.com/input-output-hk/cardano-db-sync/blob/1040fa9ec85fd75ce9f02dae2006170136793d02/doc/migrations.md#upgrading-to-13100

(Ofcourse - the master branch of that doc here for updated reference)

[Cmdv]

Another option is to have "common" indexes be added as a config option in the config file. Technically adding the index sits behind a flag, but what we couldn't provide is thorough support of new features affecting these indexes.