Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

Postgres indexes differ on matrix.org #11893

Open
clokep opened this issue Feb 2, 2022 · 13 comments
Open

Postgres indexes differ on matrix.org #11893

clokep opened this issue Feb 2, 2022 · 13 comments
Labels
A-Database DB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the db O-Uncommon Most users are unlikely to come across this or unexpected workflow S-Tolerable Minor significance, cosmetic issues, low or no impact to users. T-Task Refactoring, removal, replacement, enabling or disabling functionality, other engineering tasks. Z-Cleanup Things we want to get rid of, but aren't actively causing pain Z-Dev-Wishlist Makes developers' lives better, but doesn't have direct user impact

Comments

@clokep
Copy link
Member

clokep commented Feb 2, 2022

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)
@clokep clokep added T-Task Refactoring, removal, replacement, enabling or disabling functionality, other engineering tasks. X-Needs-Discussion labels Feb 2, 2022
@clokep clokep mentioned this issue Feb 2, 2022
Closed
@callahad
Copy link
Contributor

callahad commented Feb 3, 2022 •
edited
Loading

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

@richvdh
Copy link
Member

richvdh commented Feb 3, 2022

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
Copy link
Contributor

callahad commented Feb 3, 2022

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

@clokep
Copy link
Member Author

clokep commented Feb 3, 2022

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 callahad added P3 (OBSOLETE: use S- labels.) Approved backlog: not yet scheduled, will accept patches and removed X-Needs-Discussion labels Feb 10, 2022
@callahad
Copy link
Contributor

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
Copy link
Member

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
Copy link
Member

richvdh commented May 17, 2022 •
edited
Loading

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 #12747. Once that is deployed to matrix.org we should drop the erikj_faster_cache index.

Edit: done on 2022/06/14

@richvdh richvdh changed the title Indexes on event_push_actions differ on matrix.org Postgres indexes differ on matrix.org May 17, 2022
@Fizzadar
Copy link
Contributor

Fizzadar commented Jun 14, 2022 •
edited
Loading

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
Copy link
Member

richvdh commented Jun 14, 2022

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
Copy link
Contributor

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
Copy link
Member

richvdh commented Jun 14, 2022

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
Copy link
Member

richvdh commented Jun 14, 2022 •
edited
Loading

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 #6574. event_edges will be easier once #12893 lands. The rest remain a source for concern as they may bring performance improvements which could benefit other deployments.

@MadLittleMods MadLittleMods added the A-Database DB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the db label Oct 6, 2022
@DMRobertson DMRobertson mentioned this issue Nov 18, 2022
Closed
@DMRobertson DMRobertson added Z-Dev-Wishlist Makes developers' lives better, but doesn't have direct user impact Z-Cleanup Things we want to get rid of, but aren't actively causing pain S-Tolerable Minor significance, cosmetic issues, low or no impact to users. O-Uncommon Most users are unlikely to come across this or unexpected workflow and removed P3 (OBSOLETE: use S- labels.) Approved backlog: not yet scheduled, will accept patches labels Nov 21, 2022
@realtyem realtyem mentioned this issue Jul 5, 2023
Open
@clokep
Copy link
Member Author

clokep commented Oct 23, 2023

Not quite indexes, but #16505 (comment) notes that state_groups_state is slightly different than the schema files say.

@clokep clokep mentioned this issue Oct 23, 2023
Merged
@matrixbot matrixbot mentioned this issue Dec 21, 2023
Open
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
A-Database DB stuff like queries, migrations, new/remove columns, indexes, unexpected entries in the db O-Uncommon Most users are unlikely to come across this or unexpected workflow S-Tolerable Minor significance, cosmetic issues, low or no impact to users. T-Task Refactoring, removal, replacement, enabling or disabling functionality, other engineering tasks. Z-Cleanup Things we want to get rid of, but aren't actively causing pain Z-Dev-Wishlist Makes developers' lives better, but doesn't have direct user impact
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
7 participants
@callahad @clokep @MadLittleMods @Fizzadar @anoadragon453 @richvdh @DMRobertson