Postgres indexes differ on matrix.org

This issue has been migrated from [#11893](https://github.com/matrix-org/synapse/issues/11893).

---

matrix.org seems to have different indexes for the `event_push_actions` than what is available via the Synapse distribution. I'm unsure how/why these appeared, but we should investigate and make them consistent with what is available in-repo.

Fresh database:

```sql
# SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'event_push_actions' ORDER BY indexname;
indexname | indexdef
-----------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
event_id_user_id_profile_tag_uniqueness | CREATE UNIQUE INDEX event_id_user_id_profile_tag_uniqueness ON event_push_actions USING btree (room_id, event_id, user_id, profile_tag)
event_push_actions_highlights_index | CREATE INDEX event_push_actions_highlights_index ON event_push_actions USING btree (user_id, room_id, topological_ordering, stream_ordering) WHERE (highlight = 1)
event_push_actions_rm_tokens | CREATE INDEX event_push_actions_rm_tokens ON event_push_actions USING btree (user_id, room_id, topological_ordering, stream_ordering)
event_push_actions_room_id_user_id | CREATE INDEX event_push_actions_room_id_user_id ON event_push_actions USING btree (room_id, user_id)
event_push_actions_stream_ordering | CREATE INDEX event_push_actions_stream_ordering ON event_push_actions USING btree (stream_ordering, user_id)
event_push_actions_u_highlight | CREATE INDEX event_push_actions_u_highlight ON event_push_actions USING btree (user_id, stream_ordering)
```

matrix.org:

```sql
# SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'event_push_actions' ORDER BY indexname;
indexname | indexdef
-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
event_push_actions_highlights_index | CREATE INDEX event_push_actions_highlights_index ON event_push_actions USING btree (user_id, room_id, topological_ordering, stream_ordering) WHERE (highlight = 1)
event_push_actions_rm_id_ev_id | CREATE INDEX event_push_actions_rm_id_ev_id ON event_push_actions USING btree (room_id, event_id)
event_push_actions_rm_tokens | CREATE INDEX event_push_actions_rm_tokens ON event_push_actions USING btree (user_id, room_id, topological_ordering, stream_ordering)
event_push_actions_stream_ordering | CREATE INDEX event_push_actions_stream_ordering ON event_push_actions USING btree (stream_ordering, user_id)
event_push_actions_u_highlight | CREATE INDEX event_push_actions_u_highlight ON event_push_actions USING btree (user_id, stream_ordering, highlight)
```

Looking closely... the differences are:

```diff
--- fresh 2022-02-02 09:38:02.000000000 -0500
+++ matrixdotorg 2022-02-02 09:40:09.000000000 -0500
@@ -1,10 +1,9 @@
# SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'event_push_actions' ORDER BY indexname;
indexname | indexdef
-----------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- event_id_user_id_profile_tag_uniqueness | CREATE UNIQUE INDEX event_id_user_id_profile_tag_uniqueness ON event_push_actions USING btree (room_id, event_id, user_id, profile_tag)
event_push_actions_highlights_index | CREATE INDEX event_push_actions_highlights_index ON event_push_actions USING btree (user_id, room_id, topological_ordering, stream_ordering) WHERE (highlight = 1)
+ event_push_actions_rm_id_ev_id | CREATE INDEX event_push_actions_rm_id_ev_id ON event_push_actions USING btree (room_id, event_id)
event_push_actions_rm_tokens | CREATE INDEX event_push_actions_rm_tokens ON event_push_actions USING btree (user_id, room_id, topological_ordering, stream_ordering)
- event_push_actions_room_id_user_id | CREATE INDEX event_push_actions_room_id_user_id ON event_push_actions USING btree (room_id, user_id)
event_push_actions_stream_ordering | CREATE INDEX event_push_actions_stream_ordering ON event_push_actions USING btree (stream_ordering, user_id)
- event_push_actions_u_highlight | CREATE INDEX event_push_actions_u_highlight ON event_push_actions USING btree (user_id, stream_ordering)
+ event_push_actions_u_highlight | CREATE INDEX event_push_actions_u_highlight ON event_push_actions USING btree (user_id, stream_ordering, highlight)
```

Start by locating the Synapse schema or migration that creates the event_push_actions table, then compare its PostgreSQL indexes with the fresh-database and matrix.org listings in the issue. Trace when each differing index was introduced or removed. Done means the intended index set is established and the in-repo schema and deployed database behavior are consistent.

Written by the indexing model from the issue text.