Postgres indexes differ on matrix.org

[clokep]

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:

# 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:

# 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:

--- 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)

[callahad]

...if we've seen this divergence, we should also check the rest of the schema.

[richvdh]

specific questions:

• are the indexes that exist only on matrix.org actually useful/important?
• are the indexes that don't exist on matrix.org redundant?
• event_push_actions_u_highlight sounds like it should cover the highlight column... that sounds important?
• what does profile_tag even do?

[callahad]

@clokep Can you take a quick look at this? (At least pulling the full diff without the tablename constraint)

[clokep]

I pulled the indexes for the entire schema and saved them to files with a query like:

\copy (SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public'  ORDER BY tablename, indexname) TO 'clean.csv' WITH CSV HEADER;

I then removed the schema name from the file (replaced all "public\." with "") and removed a few on tables that only exist on matrix.org (I assume they're some past tables, but not interesting here).

I got the following results:

--- clean.csv	2022-02-03 10:55:35.000000000 -0500
+++ matrixdotorg.csv	2022-02-03 10:55:35.000000000 -0500
@@ -3,6 +3,7 @@
 access_tokens,access_tokens_pkey,CREATE UNIQUE INDEX access_tokens_pkey ON access_tokens USING btree (id)
 access_tokens,access_tokens_token_key,CREATE UNIQUE INDEX access_tokens_token_key ON access_tokens USING btree (token)
 account_data,account_data_stream_id,"CREATE INDEX account_data_stream_id ON account_data USING btree (user_id, stream_id)"
+account_data,account_data_stream_idx,CREATE INDEX account_data_stream_idx ON account_data USING btree (stream_id)
 account_data,account_data_uniqueness,"CREATE UNIQUE INDEX account_data_uniqueness ON account_data USING btree (user_id, account_data_type)"
 account_validity,account_validity_pkey,CREATE UNIQUE INDEX account_validity_pkey ON account_validity USING btree (user_id)
 application_services_state,application_services_state_pkey,CREATE UNIQUE INDEX application_services_state_pkey ON application_services_state USING btree (as_id)
@@ -36,6 +37,7 @@
 device_lists_outbound_last_success,device_lists_outbound_last_success_unique_idx,"CREATE UNIQUE INDEX device_lists_outbound_last_success_unique_idx ON device_lists_outbound_last_success USING btree (destination, user_id)"
 device_lists_outbound_pokes,device_lists_outbound_pokes_id,"CREATE INDEX device_lists_outbound_pokes_id ON device_lists_outbound_pokes USING btree (destination, stream_id)"
 device_lists_outbound_pokes,device_lists_outbound_pokes_stream,CREATE INDEX device_lists_outbound_pokes_stream ON device_lists_outbound_pokes USING btree (stream_id)
+device_lists_outbound_pokes,device_lists_outbound_pokes_stream_dest,"CREATE INDEX device_lists_outbound_pokes_stream_dest ON device_lists_outbound_pokes USING btree (stream_id, destination)"
 device_lists_outbound_pokes,device_lists_outbound_pokes_user,"CREATE INDEX device_lists_outbound_pokes_user ON device_lists_outbound_pokes USING btree (destination, user_id)"
 device_lists_remote_cache,device_lists_remote_cache_unique_id,"CREATE UNIQUE INDEX device_lists_remote_cache_unique_id ON device_lists_remote_cache USING btree (user_id, device_id)"
 device_lists_remote_extremeties,device_lists_remote_extremeties_unique_idx,CREATE UNIQUE INDEX device_lists_remote_extremeties_unique_idx ON device_lists_remote_extremeties USING btree (user_id)
@@ -54,6 +56,7 @@
 e2e_room_keys_versions,e2e_room_keys_versions_idx,"CREATE UNIQUE INDEX e2e_room_keys_versions_idx ON e2e_room_keys_versions USING btree (user_id, version)"
 erased_users,erased_users_user,CREATE UNIQUE INDEX erased_users_user ON erased_users USING btree (user_id)
 event_auth,evauth_edges_id,CREATE INDEX evauth_edges_id ON event_auth USING btree (event_id)
+event_auth,evauth_uniq_idx,"CREATE UNIQUE INDEX evauth_uniq_idx ON event_auth USING btree (event_id, auth_id)"
 event_auth_chain_links,event_auth_chain_links_idx,"CREATE INDEX event_auth_chain_links_idx ON event_auth_chain_links USING btree (origin_chain_id, target_chain_id)"
 event_auth_chain_to_calculate,event_auth_chain_to_calculate_pkey,CREATE UNIQUE INDEX event_auth_chain_to_calculate_pkey ON event_auth_chain_to_calculate USING btree (event_id)
 event_auth_chain_to_calculate,event_auth_chain_to_calculate_rm_id,CREATE INDEX event_auth_chain_to_calculate_rm_id ON event_auth_chain_to_calculate USING btree (room_id)
@@ -62,45 +65,43 @@
 event_backward_extremities,ev_b_extrem_id,CREATE INDEX ev_b_extrem_id ON event_backward_extremities USING btree (event_id)
 event_backward_extremities,ev_b_extrem_room,CREATE INDEX ev_b_extrem_room ON event_backward_extremities USING btree (room_id)
 event_backward_extremities,event_backward_extremities_event_id_room_id_key,"CREATE UNIQUE INDEX event_backward_extremities_event_id_room_id_key ON event_backward_extremities USING btree (event_id, room_id)"
-event_edges,ev_edges_id,CREATE INDEX ev_edges_id ON event_edges USING btree (event_id)
 event_edges,ev_edges_prev_id,CREATE INDEX ev_edges_prev_id ON event_edges USING btree (prev_event_id)
-event_edges,event_edges_event_id_prev_event_id_room_id_is_state_key,"CREATE UNIQUE INDEX event_edges_event_id_prev_event_id_room_id_is_state_key ON event_edges USING btree (event_id, prev_event_id, room_id, is_state)"
+event_edges,event_edges_uniq,"CREATE UNIQUE INDEX event_edges_uniq ON event_edges USING btree (event_id, prev_event_id, is_state)"
 event_expiry,event_expiry_expiry_ts_idx,CREATE INDEX event_expiry_expiry_ts_idx ON event_expiry USING btree (expiry_ts)
 event_expiry,event_expiry_pkey,CREATE UNIQUE INDEX event_expiry_pkey ON event_expiry USING btree (event_id)
 event_forward_extremities,ev_extrem_id,CREATE INDEX ev_extrem_id ON event_forward_extremities USING btree (event_id)
 event_forward_extremities,ev_extrem_room,CREATE INDEX ev_extrem_room ON event_forward_extremities USING btree (room_id)
 event_forward_extremities,event_forward_extremities_event_id_room_id_key,"CREATE UNIQUE INDEX event_forward_extremities_event_id_room_id_key ON event_forward_extremities USING btree (event_id, room_id)"
-event_json,event_json_event_id_key,CREATE UNIQUE INDEX event_json_event_id_key ON event_json USING btree (event_id)
+event_json,evjson_uniq_idx,CREATE UNIQUE INDEX evjson_uniq_idx ON event_json USING btree (event_id)
 event_labels,event_labels_pkey,"CREATE UNIQUE INDEX event_labels_pkey ON event_labels USING btree (event_id, label)"
 event_labels,event_labels_room_id_label_idx,"CREATE INDEX event_labels_room_id_label_idx ON event_labels USING btree (room_id, label, topological_ordering)"
-event_push_actions,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,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,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,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,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,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,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,event_push_actions_u_highlight,"CREATE INDEX event_push_actions_u_highlight ON event_push_actions USING btree (user_id, stream_ordering, highlight)"
 event_push_actions_staging,event_push_actions_staging_id,CREATE INDEX event_push_actions_staging_id ON event_push_actions_staging USING btree (event_id)
 event_push_summary,event_push_summary_user_rm,"CREATE INDEX event_push_summary_user_rm ON event_push_summary USING btree (user_id, room_id)"
 event_push_summary_stream_ordering,event_push_summary_stream_ordering_lock_key,CREATE UNIQUE INDEX event_push_summary_stream_ordering_lock_key ON event_push_summary_stream_ordering USING btree (lock)
-event_reference_hashes,event_reference_hashes_event_id_algorithm_key,"CREATE UNIQUE INDEX event_reference_hashes_event_id_algorithm_key ON event_reference_hashes USING btree (event_id, algorithm)"
-event_reference_hashes,event_reference_hashes_id,CREATE INDEX event_reference_hashes_id ON event_reference_hashes USING btree (event_id)
+event_reference_hashes,event_reference_hashes_event_id_algorithm_idx,"CREATE UNIQUE INDEX event_reference_hashes_event_id_algorithm_idx ON event_reference_hashes USING btree (event_id, algorithm)"
 event_relations,event_relations_id,CREATE UNIQUE INDEX event_relations_id ON event_relations USING btree (event_id)
 event_relations,event_relations_relates,"CREATE INDEX event_relations_relates ON event_relations USING btree (relates_to_id, relation_type, aggregation_key)"
 event_reports,event_reports_pkey,CREATE UNIQUE INDEX event_reports_pkey ON event_reports USING btree (id)
-event_search,event_search_ev_ridx,CREATE INDEX event_search_ev_ridx ON event_search USING btree (room_id)
 event_search,event_search_event_id_idx,CREATE UNIQUE INDEX event_search_event_id_idx ON event_search USING btree (event_id)
 event_search,event_search_fts_idx,CREATE INDEX event_search_fts_idx ON event_search USING gin (vector)
+event_search,event_search_order,"CREATE INDEX event_search_order ON event_search USING btree (origin_server_ts NULLS FIRST, stream_ordering NULLS FIRST)"
+event_search,event_search_room_order,"CREATE INDEX event_search_room_order ON event_search USING btree (room_id, origin_server_ts NULLS FIRST, stream_ordering NULLS FIRST)"
 event_to_state_groups,event_to_state_groups_event_id_key,CREATE UNIQUE INDEX event_to_state_groups_event_id_key ON event_to_state_groups USING btree (event_id)
 event_to_state_groups,event_to_state_groups_sg_index,CREATE INDEX event_to_state_groups_sg_index ON event_to_state_groups USING btree (state_group)
 event_txn_id,event_txn_id_event_id,CREATE UNIQUE INDEX event_txn_id_event_id ON event_txn_id USING btree (event_id)
 event_txn_id,event_txn_id_ts,CREATE INDEX event_txn_id_ts ON event_txn_id USING btree (inserted_ts)
 event_txn_id,event_txn_id_txn_id,"CREATE UNIQUE INDEX event_txn_id_txn_id ON event_txn_id USING btree (room_id, user_id, token_id, txn_id)"
-events,event_contains_url_index,"CREATE INDEX event_contains_url_index ON events USING btree (room_id, topological_ordering, stream_ordering) WHERE ((contains_url = true) AND (outlier = false))"
+events,event_contains_url_index2,"CREATE INDEX event_contains_url_index2 ON events USING btree (room_id, topological_ordering, stream_ordering) WHERE ((contains_url = true) AND (outlier = false))"
 events,events_event_id_key,CREATE UNIQUE INDEX events_event_id_key ON events USING btree (event_id)
-events,events_order_room,"CREATE INDEX events_order_room ON events USING btree (room_id, topological_ordering, stream_ordering)"
-events,events_room_stream,"CREATE INDEX events_room_stream ON events USING btree (room_id, stream_ordering)"
+events,events_order_room2,"CREATE INDEX events_order_room2 ON events USING btree (room_id, topological_ordering, stream_ordering)"
+events,events_room_stream2,"CREATE INDEX events_room_stream2 ON events USING btree (room_id, stream_ordering)"
 events,events_stream_ordering,CREATE UNIQUE INDEX events_stream_ordering ON events USING btree (stream_ordering)
-events,events_ts,"CREATE INDEX events_ts ON events USING btree (origin_server_ts, stream_ordering)"
+events,events_ts2,"CREATE INDEX events_ts2 ON events USING btree (origin_server_ts, stream_ordering)"
 ex_outlier_stream,ex_outlier_stream_pkey,CREATE UNIQUE INDEX ex_outlier_stream_pkey ON ex_outlier_stream USING btree (event_stream_ordering)
 federation_inbound_events_staging,federation_inbound_events_staging_instance_event,"CREATE UNIQUE INDEX federation_inbound_events_staging_instance_event ON federation_inbound_events_staging USING btree (origin, event_id)"
 federation_inbound_events_staging,federation_inbound_events_staging_room,"CREATE INDEX federation_inbound_events_staging_room ON federation_inbound_events_staging USING btree (room_id, received_ts)"
@@ -141,6 +143,7 @@
 local_group_membership,local_group_membership_g_idx,CREATE INDEX local_group_membership_g_idx ON local_group_membership USING btree (group_id)
 local_group_membership,local_group_membership_u_idx,"CREATE INDEX local_group_membership_u_idx ON local_group_membership USING btree (user_id, group_id)"
 local_group_updates,local_group_updates_stream_id_index,CREATE UNIQUE INDEX local_group_updates_stream_id_index ON local_group_updates USING btree (stream_id)
+local_media_repository,local_media_repository_cache_ts,CREATE INDEX local_media_repository_cache_ts ON local_media_repository USING btree (created_ts) WHERE (url_cache IS NOT NULL)
 local_media_repository,local_media_repository_media_id_key,CREATE UNIQUE INDEX local_media_repository_media_id_key ON local_media_repository USING btree (media_id)
 local_media_repository,local_media_repository_url_idx,CREATE INDEX local_media_repository_url_idx ON local_media_repository USING btree (created_ts) WHERE (url_cache IS NOT NULL)
 local_media_repository,users_have_local_media,"CREATE INDEX users_have_local_media ON local_media_repository USING btree (user_id, created_ts)"
@@ -169,6 +175,7 @@
 pusher_throttle,pusher_throttle_pkey,"CREATE UNIQUE INDEX pusher_throttle_pkey ON pusher_throttle USING btree (pusher, room_id)"
 pushers,pushers2_app_id_pushkey_user_name_key,"CREATE UNIQUE INDEX pushers2_app_id_pushkey_user_name_key ON pushers USING btree (app_id, pushkey, user_name)"
 pushers,pushers2_pkey,CREATE UNIQUE INDEX pushers2_pkey ON pushers USING btree (id)
+pushers,pushers_user_idx,CREATE INDEX pushers_user_idx ON pushers USING btree (user_name)
 ratelimit_override,ratelimit_override_idx,CREATE UNIQUE INDEX ratelimit_override_idx ON ratelimit_override USING btree (user_id)
 receipts_graph,receipts_graph_uniqueness,"CREATE UNIQUE INDEX receipts_graph_uniqueness ON receipts_graph USING btree (room_id, receipt_type, user_id)"
 receipts_linearized,receipts_linearized_id,CREATE INDEX receipts_linearized_id ON receipts_linearized USING btree (stream_id)
@@ -189,6 +196,7 @@
 remote_profile_cache,remote_profile_cache_time,CREATE INDEX remote_profile_cache_time ON remote_profile_cache USING btree (last_check)
 remote_profile_cache,remote_profile_cache_user_id,CREATE UNIQUE INDEX remote_profile_cache_user_id ON remote_profile_cache USING btree (user_id)
 room_account_data,room_account_data_stream_id,"CREATE INDEX room_account_data_stream_id ON room_account_data USING btree (user_id, stream_id)"
+room_account_data,room_account_data_stream_idx,CREATE INDEX room_account_data_stream_idx ON room_account_data USING btree (stream_id)
 room_account_data,room_account_data_uniqueness,"CREATE UNIQUE INDEX room_account_data_uniqueness ON room_account_data USING btree (user_id, room_id, account_data_type)"
 room_alias_servers,room_alias_servers_alias,CREATE INDEX room_alias_servers_alias ON room_alias_servers USING btree (room_alias)
 room_aliases,room_aliases_id,CREATE INDEX room_aliases_id ON room_aliases USING btree (room_id)
@@ -200,11 +208,13 @@
 room_memberships,room_memberships_user_room_forgotten,"CREATE INDEX room_memberships_user_room_forgotten ON room_memberships USING btree (user_id, room_id) WHERE (forgotten = 1)"
 room_retention,room_retention_max_lifetime_idx,CREATE INDEX room_retention_max_lifetime_idx ON room_retention USING btree (max_lifetime)
 room_retention,room_retention_pkey,"CREATE UNIQUE INDEX room_retention_pkey ON room_retention USING btree (room_id, event_id)"
+room_stats_current,_erikj_joined_members,"CREATE INDEX _erikj_joined_members ON room_stats_current USING btree (joined_members, room_id)"
 room_stats_current,room_stats_current_pkey,CREATE UNIQUE INDEX room_stats_current_pkey ON room_stats_current USING btree (room_id)
 room_stats_earliest_token,room_stats_earliest_token_idx,CREATE UNIQUE INDEX room_stats_earliest_token_idx ON room_stats_earliest_token USING btree (room_id)
 room_stats_state,room_stats_state_room,CREATE UNIQUE INDEX room_stats_state_room ON room_stats_state USING btree (room_id)
 room_tags,room_tag_uniqueness,"CREATE UNIQUE INDEX room_tag_uniqueness ON room_tags USING btree (user_id, room_id, tag)"
 room_tags_revisions,room_tag_revisions_uniqueness,"CREATE UNIQUE INDEX room_tag_revisions_uniqueness ON room_tags_revisions USING btree (user_id, room_id)"
+room_tags_revisions,room_tags_revisions_stream_idx,CREATE INDEX room_tags_revisions_stream_idx ON room_tags_revisions USING btree (stream_id)
 rooms,public_room_index,CREATE INDEX public_room_index ON rooms USING btree (is_public)
 rooms,rooms_pkey,CREATE UNIQUE INDEX rooms_pkey ON rooms USING btree (room_id)
 schema_compat_version,schema_compat_version_lock_key,CREATE UNIQUE INDEX schema_compat_version_lock_key ON schema_compat_version USING btree (lock)
@@ -217,6 +227,7 @@
 state_group_edges,state_group_edges_prev_idx,CREATE INDEX state_group_edges_prev_idx ON state_group_edges USING btree (prev_state_group)
 state_groups,state_groups_pkey,CREATE UNIQUE INDEX state_groups_pkey ON state_groups USING btree (id)
 state_groups,state_groups_room_id_idx,CREATE INDEX state_groups_room_id_idx ON state_groups USING btree (room_id)
+state_groups_state,state_groups_state_room_id_idx,CREATE INDEX state_groups_state_room_id_idx ON state_groups_state USING brin (room_id) WITH (pages_per_range='1')
 state_groups_state,state_groups_state_type_idx,"CREATE INDEX state_groups_state_type_idx ON state_groups_state USING btree (state_group, type, state_key)"
 stats_incremental_position,stats_incremental_position_lock_key,CREATE UNIQUE INDEX stats_incremental_position_lock_key ON stats_incremental_position USING btree (lock)
 stream_ordering_to_exterm,stream_ordering_to_exterm_idx,CREATE INDEX stream_ordering_to_exterm_idx ON stream_ordering_to_exterm USING btree (stream_ordering)
@@ -246,11 +257,9 @@
 user_signature_stream,user_signature_stream_idx,CREATE UNIQUE INDEX user_signature_stream_idx ON user_signature_stream USING btree (stream_id)
 user_stats_current,user_stats_current_pkey,CREATE UNIQUE INDEX user_stats_current_pkey ON user_stats_current USING btree (user_id)
 user_threepid_id_server,user_threepid_id_server_idx,"CREATE UNIQUE INDEX user_threepid_id_server_idx ON user_threepid_id_server USING btree (user_id, medium, address, id_server)"
-user_threepids,medium_address,"CREATE UNIQUE INDEX medium_address ON user_threepids USING btree (medium, address)"
-user_threepids,user_threepids_medium_address,"CREATE INDEX user_threepids_medium_address ON user_threepids USING btree (medium, address)"
-user_threepids,user_threepids_user_id,CREATE INDEX user_threepids_user_id ON user_threepids USING btree (user_id)
 users,users_creation_ts,CREATE INDEX users_creation_ts ON users USING btree (creation_ts)
+users,users_lower_name,CREATE INDEX users_lower_name ON users USING btree (lower(name))
 users,users_name_key,CREATE UNIQUE INDEX users_name_key ON users USING btree (name)
 users_in_public_rooms,users_in_public_rooms_r_idx,CREATE INDEX users_in_public_rooms_r_idx ON users_in_public_rooms USING btree (room_id)
 users_in_public_rooms,users_in_public_rooms_u_idx,"CREATE UNIQUE INDEX users_in_public_rooms_u_idx ON users_in_public_rooms USING btree (user_id, room_id)"
 users_to_send_full_presence_to,users_to_send_full_presence_to_pkey,CREATE UNIQUE INDEX users_to_send_full_presence_to_pkey ON users_to_send_full_presence_to USING btree (user_id)

Quite a few of them seem to just have slightly different names (I would guess we added them on matrix.org first, then added them as background updates).

[callahad]

This is concerning and needs to be reconciled, but ostensibly homeservers with and without these indices are working sufficiently well that this is not a fire.

[anoadragon453]

The following index was added to the cache_invalidation_stream_by_instance table on matrix.org live:

 erikj_faster_cache                       | CREATE INDEX erikj_faster_cache ON matrix.cache_invalidation_stream_by_instance USING btree (instance_name, stream_id)

as the following query was taking ages and blocking cache replication:

SELECT stream_id, cache_func, keys, invalidation_ts FROM cache_invalidation_stream_by_instance WHERE stream_id > 537398913 AND instance_name = 'synchrotron-42' ORDER BY stream_id ASC LIMIT 100;

[richvdh]

The following index was added to the cache_invalidation_stream_by_instance table on matrix.org live:

 erikj_faster_cache                       | CREATE INDEX erikj_faster_cache ON matrix.cache_invalidation_stream_by_instance USING btree (instance_name, stream_id)

I believe this is added to Synapse proper by https://github.com/matrix-org/synapse/pull/12747. Once that is deployed to matrix.org we should drop the erikj_faster_cache index.

Edit: done on 2022/06/14

[Fizzadar]

~Was just looking at the indices on the e2e_room_keys table and spotted there's one missing in the above list e2e_room_keys_with_version_idx which was added in this delta.~ ignore me misread the above

[richvdh]

Was just looking at the indices on the e2e_room_keys table and spotted there's one missing in the above list e2e_room_keys_with_version_idx which was added in this delta.

I'm afraid I don't follow. The expected indexes on e2e_room_keys are:

synapse=# \d e2e_room_keys
...
Indexes:
    "e2e_room_keys_with_version_idx" UNIQUE, btree (user_id, version, room_id, session_id)

I see this both on matrix.org and my personal server. Do you see something different?

[Fizzadar]

I see this both on matrix.org and my personal server. Do you see something different?

😅please ignore me - I thought the list above was every index not a diff, can’t read apparently!

[richvdh]

Have done some quick cleanups on matrix.org:

alter index evjson_uniq_idx rename to event_json_event_id_key;
alter index event_push_actions_rm_id_ev_id rename to event_push_actions_room_id_user_id;
alter index event_contains_url_index2 rename to event_contains_url_index;
alter index events_order_room2 rename to events_order_room;
alter index events_room_stream2 rename to events_room_stream;
alter index events_ts2 rename to events_ts;

[richvdh]

the indexes on user_threepids are actually the same as normal. Due to reasons they are in a different namespace on matrix.org, so didn't appear in @clokep's report.

The current difference is therefore (in theory):

--- clean.csv	2022-02-03 10:55:35.000000000 -0500
+++ matrixdotorg.csv	2022-02-03 10:55:35.000000000 -0500
+account_data,account_data_stream_idx,CREATE INDEX account_data_stream_idx ON account_data USING btree (stream_id)
+device_lists_outbound_pokes,device_lists_outbound_pokes_stream_dest,"CREATE INDEX device_lists_outbound_pokes_stream_dest ON device_lists_outbound_pokes USING btree (stream_id, destination)"
+event_auth,evauth_uniq_idx,"CREATE UNIQUE INDEX evauth_uniq_idx ON event_auth USING btree (event_id, auth_id)"
-event_edges,ev_edges_id,CREATE INDEX ev_edges_id ON event_edges USING btree (event_id)
-event_edges,event_edges_event_id_prev_event_id_room_id_is_state_key,"CREATE UNIQUE INDEX event_edges_event_id_prev_event_id_room_id_is_state_key ON event_edges USING btree (event_id, prev_event_id, room_id, is_state)"
+event_edges,event_edges_uniq,"CREATE UNIQUE INDEX event_edges_uniq ON event_edges USING btree (event_id, prev_event_id, is_state)"
-event_push_actions,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,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,event_push_actions_u_highlight,"CREATE INDEX event_push_actions_u_highlight ON event_push_actions USING btree (user_id, stream_ordering, highlight)"
-event_reference_hashes,event_reference_hashes_event_id_algorithm_key,"CREATE UNIQUE INDEX event_reference_hashes_event_id_algorithm_key ON event_reference_hashes USING btree (event_id, algorithm)"
-event_reference_hashes,event_reference_hashes_id,CREATE INDEX event_reference_hashes_id ON event_reference_hashes USING btree (event_id)
+event_reference_hashes,event_reference_hashes_event_id_algorithm_idx,"CREATE UNIQUE INDEX event_reference_hashes_event_id_algorithm_idx ON event_reference_hashes USING btree (event_id, algorithm)"
-event_search,event_search_ev_ridx,CREATE INDEX event_search_ev_ridx ON event_search USING btree (room_id)
+event_search,event_search_order,"CREATE INDEX event_search_order ON event_search USING btree (origin_server_ts NULLS FIRST, stream_ordering NULLS FIRST)"
+event_search,event_search_room_order,"CREATE INDEX event_search_room_order ON event_search USING btree (room_id, origin_server_ts NULLS FIRST, stream_ordering NULLS FIRST)"
+local_media_repository,local_media_repository_cache_ts,CREATE INDEX local_media_repository_cache_ts ON local_media_repository USING btree (created_ts) WHERE (url_cache IS NOT NULL)
+pushers,pushers_user_idx,CREATE INDEX pushers_user_idx ON pushers USING btree (user_name)
+room_account_data,room_account_data_stream_idx,CREATE INDEX room_account_data_stream_idx ON room_account_data USING btree (stream_id)
+room_stats_current,_erikj_joined_members,"CREATE INDEX _erikj_joined_members ON room_stats_current USING btree (joined_members, room_id)"
+room_tags_revisions,room_tags_revisions_stream_idx,CREATE INDEX room_tags_revisions_stream_idx ON room_tags_revisions USING btree (stream_id)
+state_groups_state,state_groups_state_room_id_idx,CREATE INDEX state_groups_state_room_id_idx ON state_groups_state USING brin (room_id) WITH (pages_per_range='1')
+users,users_lower_name,CREATE INDEX users_lower_name ON users USING btree (lower(name))

event_reference_hashes will be sorted by https://github.com/matrix-org/synapse/issues/6574. event_edges will be easier once https://github.com/matrix-org/synapse/pull/12893 lands. The rest remain a source for concern as they may bring performance improvements which could benefit other deployments.

[clokep]

Not quite indexes, but https://github.com/matrix-org/synapse/pull/16505#discussion_r1366209678 notes that state_groups_state is slightly different than the schema files say.