search

glitch-soc / mastodon

A glitchy but lovable microblogging server
https://glitch-soc.github.io/docs/
GNU Affero General Public License v3.0
707 stars 182 forks source link

Horrific performance after upgrading to latest from 4.1.7 #2645

Closed thatcosmonaut closed 9 months ago

thatcosmonaut commented 9 months ago

Steps to reproduce the problem

  1. Upgraded postgres to 12
  2. Upgraded to latest glitch-soc

Expected behaviour

Performance should have been roughly the same

Actual behaviour

Cpu usage increased by factor of 4

Detailed description

Ever since updating to 4.3.0+glitch the CPU usage is regularly maxed out on both cores. Used to hover around 40%. Postgres seems to be using a ton of CPU time and it gets so bad that puma is starved for CPU.

I did run ANALYZE and REINDEX on the database but it didn't seem to have much effect.

Mastodon instance

monads.online

Mastodon version

v4.3.0-alpha.1+glitch

Browser name and version

Firefox

Operating system

Windows 10

Technical details

No response

ClearlyClaire commented 9 months ago

Which version of postgresql are you currently using? Have you managed to apply all migrations without issues?

Can you give the output of:

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;
thatcosmonaut commented 9 months ago 
                       List of relations
 Schema |                Name                | Type  |  Owner   
--------+------------------------------------+-------+----------
 public | account_aliases                    | table | mastodon
 public | account_conversations              | table | mastodon
 public | account_deletion_requests          | table | mastodon
 public | account_domain_blocks              | table | mastodon
 public | account_migrations                 | table | mastodon
 public | account_moderation_notes           | table | mastodon
 public | account_notes                      | table | mastodon
 public | account_pins                       | table | mastodon
 public | account_stats                      | table | mastodon
 public | account_statuses_cleanup_policies  | table | mastodon
 public | account_warning_presets            | table | mastodon
 public | account_warnings                   | table | mastodon
 public | accounts                           | table | mastodon
 public | accounts_tags                      | table | mastodon
 public | admin_action_logs                  | table | mastodon
 public | announcement_mutes                 | table | mastodon
 public | announcement_reactions             | table | mastodon
 public | announcements                      | table | mastodon
 public | appeals                            | table | mastodon
 public | ar_internal_metadata               | table | mastodon
 public | backups                            | table | mastodon
 public | blocks                             | table | mastodon
 public | bookmarks                          | table | mastodon
 public | bulk_import_rows                   | table | mastodon
 public | bulk_imports                       | table | mastodon
 public | canonical_email_blocks             | table | mastodon
 public | conversation_mutes                 | table | mastodon
 public | conversations                      | table | mastodon
 public | custom_emoji_categories            | table | mastodon
 public | custom_emojis                      | table | mastodon
 public | custom_filter_keywords             | table | mastodon
 public | custom_filter_statuses             | table | mastodon
 public | custom_filters                     | table | mastodon
 public | devices                            | table | mastodon
 public | domain_allows                      | table | mastodon
 public | domain_blocks                      | table | mastodon
 public | email_domain_blocks                | table | mastodon
 public | encrypted_messages                 | table | mastodon
 public | favourites                         | table | mastodon
 public | featured_tags                      | table | mastodon
 public | follow_recommendation_mutes        | table | mastodon
 public | follow_recommendation_suppressions | table | mastodon
 public | follow_requests                    | table | mastodon
 public | follows                            | table | mastodon
 public | generated_annual_reports           | table | mastodon
 public | identities                         | table | mastodon
 public | imports                            | table | mastodon
 public | invites                            | table | mastodon
 public | ip_blocks                          | table | mastodon
 public | list_accounts                      | table | mastodon
 public | lists                              | table | mastodon
 public | login_activities                   | table | mastodon
 public | markers                            | table | mastodon
 public | media_attachments                  | table | mastodon
 public | mentions                           | table | mastodon
 public | mutes                              | table | mastodon
 public | notifications                      | table | mastodon
 public | oauth_access_grants                | table | mastodon
 public | oauth_access_tokens                | table | mastodon
 public | oauth_applications                 | table | mastodon
 public | one_time_keys                      | table | mastodon
 public | pghero_space_stats                 | table | mastodon
 public | poll_votes                         | table | mastodon
 public | polls                              | table | mastodon
 public | preview_card_providers             | table | mastodon
 public | preview_card_trends                | table | mastodon
 public | preview_cards                      | table | mastodon
 public | preview_cards_statuses             | table | mastodon
 public | relays                             | table | mastodon
 public | report_notes                       | table | mastodon
 public | reports                            | table | mastodon
 public | rules                              | table | mastodon
 public | scheduled_statuses                 | table | mastodon
 public | schema_migrations                  | table | mastodon
 public | session_activations                | table | mastodon
 public | settings                           | table | mastodon
 public | site_uploads                       | table | mastodon
 public | software_updates                   | table | mastodon
 public | status_edits                       | table | mastodon
 public | status_pins                        | table | mastodon
 public | status_stats                       | table | mastodon
 public | status_trends                      | table | mastodon
 public | statuses                           | table | mastodon
 public | statuses_tags                      | table | mastodon
 public | system_keys                        | table | mastodon
 public | tag_follows                        | table | mastodon
 public | tags                               | table | mastodon
 public | tombstones                         | table | mastodon
 public | unavailable_domains                | table | mastodon
 public | user_invite_requests               | table | mastodon
 public | user_roles                         | table | mastodon
 public | users                              | table | mastodon
 public | web_push_subscriptions             | table | mastodon
 public | web_settings                       | table | mastodon
 public | webauthn_credentials               | table | mastodon
 public | webhooks                           | table | mastodon
(96 rows)

             tablename              |                           indexname                            |                                                                                                                                                                 indexdef                                                                                                                                                                 
------------------------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 account_aliases                    | account_aliases_pkey                                           | CREATE UNIQUE INDEX account_aliases_pkey ON public.account_aliases USING btree (id)
 account_aliases                    | index_account_aliases_on_account_id                            | CREATE INDEX index_account_aliases_on_account_id ON public.account_aliases USING btree (account_id)
 account_conversations              | account_conversations_pkey                                     | CREATE UNIQUE INDEX account_conversations_pkey ON public.account_conversations USING btree (id)
 account_conversations              | index_account_conversations_on_conversation_id                 | CREATE INDEX index_account_conversations_on_conversation_id ON public.account_conversations USING btree (conversation_id)
 account_conversations              | index_unique_conversations                                     | CREATE UNIQUE INDEX index_unique_conversations ON public.account_conversations USING btree (account_id, conversation_id, participant_account_ids)
 account_deletion_requests          | account_deletion_requests_pkey                                 | CREATE UNIQUE INDEX account_deletion_requests_pkey ON public.account_deletion_requests USING btree (id)
 account_deletion_requests          | index_account_deletion_requests_on_account_id                  | CREATE INDEX index_account_deletion_requests_on_account_id ON public.account_deletion_requests USING btree (account_id)
 account_domain_blocks              | account_domain_blocks_pkey                                     | CREATE UNIQUE INDEX account_domain_blocks_pkey ON public.account_domain_blocks USING btree (id)
 account_domain_blocks              | index_account_domain_blocks_on_account_id_and_domain           | CREATE UNIQUE INDEX index_account_domain_blocks_on_account_id_and_domain ON public.account_domain_blocks USING btree (account_id, domain)
 account_migrations                 | account_migrations_pkey                                        | CREATE UNIQUE INDEX account_migrations_pkey ON public.account_migrations USING btree (id)
 account_migrations                 | index_account_migrations_on_account_id                         | CREATE INDEX index_account_migrations_on_account_id ON public.account_migrations USING btree (account_id)
 account_migrations                 | index_account_migrations_on_target_account_id                  | CREATE INDEX index_account_migrations_on_target_account_id ON public.account_migrations USING btree (target_account_id) WHERE (target_account_id IS NOT NULL)
 account_moderation_notes           | account_moderation_notes_pkey                                  | CREATE UNIQUE INDEX account_moderation_notes_pkey ON public.account_moderation_notes USING btree (id)
 account_moderation_notes           | index_account_moderation_notes_on_account_id                   | CREATE INDEX index_account_moderation_notes_on_account_id ON public.account_moderation_notes USING btree (account_id)
 account_moderation_notes           | index_account_moderation_notes_on_target_account_id            | CREATE INDEX index_account_moderation_notes_on_target_account_id ON public.account_moderation_notes USING btree (target_account_id)
 account_notes                      | account_notes_pkey                                             | CREATE UNIQUE INDEX account_notes_pkey ON public.account_notes USING btree (id)
 account_notes                      | index_account_notes_on_account_id_and_target_account_id        | CREATE UNIQUE INDEX index_account_notes_on_account_id_and_target_account_id ON public.account_notes USING btree (account_id, target_account_id)
 account_notes                      | index_account_notes_on_target_account_id                       | CREATE INDEX index_account_notes_on_target_account_id ON public.account_notes USING btree (target_account_id)
 account_pins                       | account_pins_pkey                                              | CREATE UNIQUE INDEX account_pins_pkey ON public.account_pins USING btree (id)
 account_pins                       | index_account_pins_on_account_id_and_target_account_id         | CREATE UNIQUE INDEX index_account_pins_on_account_id_and_target_account_id ON public.account_pins USING btree (account_id, target_account_id)
 account_pins                       | index_account_pins_on_target_account_id                        | CREATE INDEX index_account_pins_on_target_account_id ON public.account_pins USING btree (target_account_id)
 account_stats                      | account_stats_pkey                                             | CREATE UNIQUE INDEX account_stats_pkey ON public.account_stats USING btree (id)
 account_stats                      | index_account_stats_on_account_id                              | CREATE UNIQUE INDEX index_account_stats_on_account_id ON public.account_stats USING btree (account_id)
 account_stats                      | index_account_stats_on_last_status_at_and_account_id           | CREATE INDEX index_account_stats_on_last_status_at_and_account_id ON public.account_stats USING btree (last_status_at DESC NULLS LAST, account_id)
 account_statuses_cleanup_policies  | account_statuses_cleanup_policies_pkey                         | CREATE UNIQUE INDEX account_statuses_cleanup_policies_pkey ON public.account_statuses_cleanup_policies USING btree (id)
 account_statuses_cleanup_policies  | index_account_statuses_cleanup_policies_on_account_id          | CREATE INDEX index_account_statuses_cleanup_policies_on_account_id ON public.account_statuses_cleanup_policies USING btree (account_id)
 account_summaries                  | idx_on_account_id_language_sensitive_250461e1eb                | CREATE INDEX idx_on_account_id_language_sensitive_250461e1eb ON public.account_summaries USING btree (account_id, language, sensitive)
 account_summaries                  | index_account_summaries_on_account_id                          | CREATE UNIQUE INDEX index_account_summaries_on_account_id ON public.account_summaries USING btree (account_id)
 account_warning_presets            | account_warning_presets_pkey                                   | CREATE UNIQUE INDEX account_warning_presets_pkey ON public.account_warning_presets USING btree (id)
 account_warnings                   | account_warnings_pkey                                          | CREATE UNIQUE INDEX account_warnings_pkey ON public.account_warnings USING btree (id)
 account_warnings                   | index_account_warnings_on_account_id                           | CREATE INDEX index_account_warnings_on_account_id ON public.account_warnings USING btree (account_id)
 account_warnings                   | index_account_warnings_on_target_account_id                    | CREATE INDEX index_account_warnings_on_target_account_id ON public.account_warnings USING btree (target_account_id)
 accounts                           | accounts_pkey                                                  | CREATE UNIQUE INDEX accounts_pkey ON public.accounts USING btree (id)
 accounts                           | index_accounts_on_domain_and_id                                | CREATE INDEX index_accounts_on_domain_and_id ON public.accounts USING btree (domain, id)
 accounts                           | index_accounts_on_moved_to_account_id                          | CREATE INDEX index_accounts_on_moved_to_account_id ON public.accounts USING btree (moved_to_account_id) WHERE (moved_to_account_id IS NOT NULL)
 accounts                           | index_accounts_on_uri                                          | CREATE INDEX index_accounts_on_uri ON public.accounts USING btree (uri)
 accounts                           | index_accounts_on_url                                          | CREATE INDEX index_accounts_on_url ON public.accounts USING btree (url text_pattern_ops) WHERE (url IS NOT NULL)
 accounts                           | search_index                                                   | CREATE INDEX search_index ON public.accounts USING gin ((((setweight(to_tsvector('simple'::regconfig, (display_name)::text), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, (username)::text), 'B'::"char")) || setweight(to_tsvector('simple'::regconfig, (COALESCE(domain, ''::character varying))::text), 'C'::"char"))))
 accounts_tags                      | accounts_tags_pkey                                             | CREATE UNIQUE INDEX accounts_tags_pkey ON public.accounts_tags USING btree (tag_id, account_id)
 accounts_tags                      | index_accounts_tags_on_account_id_and_tag_id                   | CREATE INDEX index_accounts_tags_on_account_id_and_tag_id ON public.accounts_tags USING btree (account_id, tag_id)
 admin_action_logs                  | admin_action_logs_pkey                                         | CREATE UNIQUE INDEX admin_action_logs_pkey ON public.admin_action_logs USING btree (id)
 admin_action_logs                  | index_admin_action_logs_on_account_id                          | CREATE INDEX index_admin_action_logs_on_account_id ON public.admin_action_logs USING btree (account_id)
 admin_action_logs                  | index_admin_action_logs_on_target_type_and_target_id           | CREATE INDEX index_admin_action_logs_on_target_type_and_target_id ON public.admin_action_logs USING btree (target_type, target_id)
 announcement_mutes                 | announcement_mutes_pkey                                        | CREATE UNIQUE INDEX announcement_mutes_pkey ON public.announcement_mutes USING btree (id)
 announcement_mutes                 | index_announcement_mutes_on_account_id_and_announcement_id     | CREATE UNIQUE INDEX index_announcement_mutes_on_account_id_and_announcement_id ON public.announcement_mutes USING btree (account_id, announcement_id)
 announcement_mutes                 | index_announcement_mutes_on_announcement_id                    | CREATE INDEX index_announcement_mutes_on_announcement_id ON public.announcement_mutes USING btree (announcement_id)
 announcement_reactions             | announcement_reactions_pkey                                    | CREATE UNIQUE INDEX announcement_reactions_pkey ON public.announcement_reactions USING btree (id)
 announcement_reactions             | index_announcement_reactions_on_account_id_and_announcement_id | CREATE UNIQUE INDEX index_announcement_reactions_on_account_id_and_announcement_id ON public.announcement_reactions USING btree (account_id, announcement_id, name)
 announcement_reactions             | index_announcement_reactions_on_announcement_id                | CREATE INDEX index_announcement_reactions_on_announcement_id ON public.announcement_reactions USING btree (announcement_id)
 announcement_reactions             | index_announcement_reactions_on_custom_emoji_id                | CREATE INDEX index_announcement_reactions_on_custom_emoji_id ON public.announcement_reactions USING btree (custom_emoji_id) WHERE (custom_emoji_id IS NOT NULL)
 announcements                      | announcements_pkey                                             | CREATE UNIQUE INDEX announcements_pkey ON public.announcements USING btree (id)
 appeals                            | appeals_pkey                                                   | CREATE UNIQUE INDEX appeals_pkey ON public.appeals USING btree (id)
 appeals                            | index_appeals_on_account_id                                    | CREATE INDEX index_appeals_on_account_id ON public.appeals USING btree (account_id)
 appeals                            | index_appeals_on_account_warning_id                            | CREATE UNIQUE INDEX index_appeals_on_account_warning_id ON public.appeals USING btree (account_warning_id)
 appeals                            | index_appeals_on_approved_by_account_id                        | CREATE INDEX index_appeals_on_approved_by_account_id ON public.appeals USING btree (approved_by_account_id) WHERE (approved_by_account_id IS NOT NULL)
 appeals                            | index_appeals_on_rejected_by_account_id                        | CREATE INDEX index_appeals_on_rejected_by_account_id ON public.appeals USING btree (rejected_by_account_id) WHERE (rejected_by_account_id IS NOT NULL)
 ar_internal_metadata               | ar_internal_metadata_pkey                                      | CREATE UNIQUE INDEX ar_internal_metadata_pkey ON public.ar_internal_metadata USING btree (key)
 backups                            | backups_pkey                                                   | CREATE UNIQUE INDEX backups_pkey ON public.backups USING btree (id)
 backups                            | index_backups_on_user_id                                       | CREATE INDEX index_backups_on_user_id ON public.backups USING btree (user_id)
 blocks                             | blocks_pkey                                                    | CREATE UNIQUE INDEX blocks_pkey ON public.blocks USING btree (id)
 blocks                             | index_blocks_on_account_id_and_target_account_id               | CREATE UNIQUE INDEX index_blocks_on_account_id_and_target_account_id ON public.blocks USING btree (account_id, target_account_id)
 blocks                             | index_blocks_on_target_account_id                              | CREATE INDEX index_blocks_on_target_account_id ON public.blocks USING btree (target_account_id)
 bookmarks                          | bookmarks_pkey                                                 | CREATE UNIQUE INDEX bookmarks_pkey ON public.bookmarks USING btree (id)
 bookmarks                          | index_bookmarks_on_account_id_and_status_id                    | CREATE UNIQUE INDEX index_bookmarks_on_account_id_and_status_id ON public.bookmarks USING btree (account_id, status_id)
 bookmarks                          | index_bookmarks_on_status_id                                   | CREATE INDEX index_bookmarks_on_status_id ON public.bookmarks USING btree (status_id)
 bulk_import_rows                   | bulk_import_rows_pkey                                          | CREATE UNIQUE INDEX bulk_import_rows_pkey ON public.bulk_import_rows USING btree (id)
 bulk_import_rows                   | index_bulk_import_rows_on_bulk_import_id                       | CREATE INDEX index_bulk_import_rows_on_bulk_import_id ON public.bulk_import_rows USING btree (bulk_import_id)
 bulk_imports                       | bulk_imports_pkey                                              | CREATE UNIQUE INDEX bulk_imports_pkey ON public.bulk_imports USING btree (id)
 bulk_imports                       | index_bulk_imports_on_account_id                               | CREATE INDEX index_bulk_imports_on_account_id ON public.bulk_imports USING btree (account_id)
 bulk_imports                       | index_bulk_imports_unconfirmed                                 | CREATE INDEX index_bulk_imports_unconfirmed ON public.bulk_imports USING btree (id) WHERE (state = 0)
 canonical_email_blocks             | canonical_email_blocks_pkey                                    | CREATE UNIQUE INDEX canonical_email_blocks_pkey ON public.canonical_email_blocks USING btree (id)
 canonical_email_blocks             | index_canonical_email_blocks_on_canonical_email_hash           | CREATE UNIQUE INDEX index_canonical_email_blocks_on_canonical_email_hash ON public.canonical_email_blocks USING btree (canonical_email_hash)
 canonical_email_blocks             | index_canonical_email_blocks_on_reference_account_id           | CREATE INDEX index_canonical_email_blocks_on_reference_account_id ON public.canonical_email_blocks USING btree (reference_account_id)
 conversation_mutes                 | conversation_mutes_pkey                                        | CREATE UNIQUE INDEX conversation_mutes_pkey ON public.conversation_mutes USING btree (id)
 conversation_mutes                 | index_conversation_mutes_on_account_id_and_conversation_id     | CREATE UNIQUE INDEX index_conversation_mutes_on_account_id_and_conversation_id ON public.conversation_mutes USING btree (account_id, conversation_id)
 conversations                      | conversations_pkey                                             | CREATE UNIQUE INDEX conversations_pkey ON public.conversations USING btree (id)
 conversations                      | index_conversations_on_uri                                     | CREATE UNIQUE INDEX index_conversations_on_uri ON public.conversations USING btree (uri text_pattern_ops) WHERE (uri IS NOT NULL)
 custom_emoji_categories            | custom_emoji_categories_pkey                                   | CREATE UNIQUE INDEX custom_emoji_categories_pkey ON public.custom_emoji_categories USING btree (id)
 custom_emoji_categories            | index_custom_emoji_categories_on_name                          | CREATE UNIQUE INDEX index_custom_emoji_categories_on_name ON public.custom_emoji_categories USING btree (name)
 custom_emojis                      | custom_emojis_pkey                                             | CREATE UNIQUE INDEX custom_emojis_pkey ON public.custom_emojis USING btree (id)
 custom_filter_keywords             | custom_filter_keywords_pkey                                    | CREATE UNIQUE INDEX custom_filter_keywords_pkey ON public.custom_filter_keywords USING btree (id)
 custom_filter_keywords             | index_custom_filter_keywords_on_custom_filter_id               | CREATE INDEX index_custom_filter_keywords_on_custom_filter_id ON public.custom_filter_keywords USING btree (custom_filter_id)
 custom_filter_statuses             | custom_filter_statuses_pkey                                    | CREATE UNIQUE INDEX custom_filter_statuses_pkey ON public.custom_filter_statuses USING btree (id)
 custom_filter_statuses             | index_custom_filter_statuses_on_custom_filter_id               | CREATE INDEX index_custom_filter_statuses_on_custom_filter_id ON public.custom_filter_statuses USING btree (custom_filter_id)
 custom_filter_statuses             | index_custom_filter_statuses_on_status_id                      | CREATE INDEX index_custom_filter_statuses_on_status_id ON public.custom_filter_statuses USING btree (status_id)
 custom_filters                     | custom_filters_pkey                                            | CREATE UNIQUE INDEX custom_filters_pkey ON public.custom_filters USING btree (id)
 custom_filters                     | index_custom_filters_on_account_id                             | CREATE INDEX index_custom_filters_on_account_id ON public.custom_filters USING btree (account_id)
 devices                            | devices_pkey                                                   | CREATE UNIQUE INDEX devices_pkey ON public.devices USING btree (id)
 devices                            | index_devices_on_access_token_id                               | CREATE INDEX index_devices_on_access_token_id ON public.devices USING btree (access_token_id)
 devices                            | index_devices_on_account_id                                    | CREATE INDEX index_devices_on_account_id ON public.devices USING btree (account_id)
 domain_allows                      | domain_allows_pkey                                             | CREATE UNIQUE INDEX domain_allows_pkey ON public.domain_allows USING btree (id)
 domain_allows                      | index_domain_allows_on_domain                                  | CREATE UNIQUE INDEX index_domain_allows_on_domain ON public.domain_allows USING btree (domain)
 domain_blocks                      | domain_blocks_pkey                                             | CREATE UNIQUE INDEX domain_blocks_pkey ON public.domain_blocks USING btree (id)
 domain_blocks                      | index_domain_blocks_on_domain                                  | CREATE UNIQUE INDEX index_domain_blocks_on_domain ON public.domain_blocks USING btree (domain)
 email_domain_blocks                | email_domain_blocks_pkey                                       | CREATE UNIQUE INDEX email_domain_blocks_pkey ON public.email_domain_blocks USING btree (id)
 email_domain_blocks                | index_email_domain_blocks_on_domain                            | CREATE UNIQUE INDEX index_email_domain_blocks_on_domain ON public.email_domain_blocks USING btree (domain)
 encrypted_messages                 | encrypted_messages_pkey                                        | CREATE UNIQUE INDEX encrypted_messages_pkey ON public.encrypted_messages USING btree (id)
 encrypted_messages                 | index_encrypted_messages_on_device_id                          | CREATE INDEX index_encrypted_messages_on_device_id ON public.encrypted_messages USING btree (device_id)
 encrypted_messages                 | index_encrypted_messages_on_from_account_id                    | CREATE INDEX index_encrypted_messages_on_from_account_id ON public.encrypted_messages USING btree (from_account_id)
 favourites                         | favourites_pkey                                                | CREATE UNIQUE INDEX favourites_pkey ON public.favourites USING btree (id)
 favourites                         | index_favourites_on_account_id_and_id                          | CREATE INDEX index_favourites_on_account_id_and_id ON public.favourites USING btree (account_id, id)
 favourites                         | index_favourites_on_account_id_and_status_id                   | CREATE UNIQUE INDEX index_favourites_on_account_id_and_status_id ON public.favourites USING btree (account_id, status_id)
 favourites                         | index_favourites_on_status_id                                  | CREATE INDEX index_favourites_on_status_id ON public.favourites USING btree (status_id)
 featured_tags                      | featured_tags_pkey                                             | CREATE UNIQUE INDEX featured_tags_pkey ON public.featured_tags USING btree (id)
 featured_tags                      | index_featured_tags_on_account_id_and_tag_id                   | CREATE UNIQUE INDEX index_featured_tags_on_account_id_and_tag_id ON public.featured_tags USING btree (account_id, tag_id)
 featured_tags                      | index_featured_tags_on_tag_id                                  | CREATE INDEX index_featured_tags_on_tag_id ON public.featured_tags USING btree (tag_id)
 follow_recommendation_mutes        | follow_recommendation_mutes_pkey                               | CREATE UNIQUE INDEX follow_recommendation_mutes_pkey ON public.follow_recommendation_mutes USING btree (id)
 follow_recommendation_mutes        | idx_on_account_id_target_account_id_a8c8ddf44e                 | CREATE UNIQUE INDEX idx_on_account_id_target_account_id_a8c8ddf44e ON public.follow_recommendation_mutes USING btree (account_id, target_account_id)
 follow_recommendation_mutes        | index_follow_recommendation_mutes_on_target_account_id         | CREATE INDEX index_follow_recommendation_mutes_on_target_account_id ON public.follow_recommendation_mutes USING btree (target_account_id)
 follow_recommendation_suppressions | follow_recommendation_suppressions_pkey                        | CREATE UNIQUE INDEX follow_recommendation_suppressions_pkey ON public.follow_recommendation_suppressions USING btree (id)
 follow_recommendation_suppressions | index_follow_recommendation_suppressions_on_account_id         | CREATE UNIQUE INDEX index_follow_recommendation_suppressions_on_account_id ON public.follow_recommendation_suppressions USING btree (account_id)
 follow_requests                    | follow_requests_pkey                                           | CREATE UNIQUE INDEX follow_requests_pkey ON public.follow_requests USING btree (id)
 follow_requests                    | index_follow_requests_on_account_id_and_target_account_id      | CREATE UNIQUE INDEX index_follow_requests_on_account_id_and_target_account_id ON public.follow_requests USING btree (account_id, target_account_id)
 follows                            | follows_pkey                                                   | CREATE UNIQUE INDEX follows_pkey ON public.follows USING btree (id)
 follows                            | index_follows_on_account_id_and_target_account_id              | CREATE UNIQUE INDEX index_follows_on_account_id_and_target_account_id ON public.follows USING btree (account_id, target_account_id)
 follows                            | index_follows_on_target_account_id                             | CREATE INDEX index_follows_on_target_account_id ON public.follows USING btree (target_account_id)
 generated_annual_reports           | generated_annual_reports_pkey                                  | CREATE UNIQUE INDEX generated_annual_reports_pkey ON public.generated_annual_reports USING btree (id)
 generated_annual_reports           | index_generated_annual_reports_on_account_id_and_year          | CREATE UNIQUE INDEX index_generated_annual_reports_on_account_id_and_year ON public.generated_annual_reports USING btree (account_id, year)
 global_follow_recommendations      | index_global_follow_recommendations_on_account_id              | CREATE UNIQUE INDEX index_global_follow_recommendations_on_account_id ON public.global_follow_recommendations USING btree (account_id)
 identities                         | identities_pkey                                                | CREATE UNIQUE INDEX identities_pkey ON public.identities USING btree (id)
 identities                         | index_identities_on_user_id                                    | CREATE INDEX index_identities_on_user_id ON public.identities USING btree (user_id)
 imports                            | imports_pkey                                                   | CREATE UNIQUE INDEX imports_pkey ON public.imports USING btree (id)
 instances                          | index_instances_on_domain                                      | CREATE UNIQUE INDEX index_instances_on_domain ON public.instances USING btree (domain)
 instances                          | index_instances_on_reverse_domain                              | CREATE INDEX index_instances_on_reverse_domain ON public.instances USING btree (reverse(('.'::text || (domain)::text)), domain)
 invites                            | index_invites_on_code                                          | CREATE UNIQUE INDEX index_invites_on_code ON public.invites USING btree (code)
 invites                            | index_invites_on_user_id                                       | CREATE INDEX index_invites_on_user_id ON public.invites USING btree (user_id)
 invites                            | invites_pkey                                                   | CREATE UNIQUE INDEX invites_pkey ON public.invites USING btree (id)
 ip_blocks                          | index_ip_blocks_on_ip                                          | CREATE UNIQUE INDEX index_ip_blocks_on_ip ON public.ip_blocks USING btree (ip)
 ip_blocks                          | ip_blocks_pkey                                                 | CREATE UNIQUE INDEX ip_blocks_pkey ON public.ip_blocks USING btree (id)
 list_accounts                      | index_list_accounts_on_account_id_and_list_id                  | CREATE UNIQUE INDEX index_list_accounts_on_account_id_and_list_id ON public.list_accounts USING btree (account_id, list_id)
 list_accounts                      | index_list_accounts_on_follow_id                               | CREATE INDEX index_list_accounts_on_follow_id ON public.list_accounts USING btree (follow_id) WHERE (follow_id IS NOT NULL)
 list_accounts                      | index_list_accounts_on_follow_request_id                       | CREATE INDEX index_list_accounts_on_follow_request_id ON public.list_accounts USING btree (follow_request_id) WHERE (follow_request_id IS NOT NULL)
 list_accounts                      | index_list_accounts_on_list_id_and_account_id                  | CREATE INDEX index_list_accounts_on_list_id_and_account_id ON public.list_accounts USING btree (list_id, account_id)
 list_accounts                      | list_accounts_pkey                                             | CREATE UNIQUE INDEX list_accounts_pkey ON public.list_accounts USING btree (id)
 lists                              | index_lists_on_account_id                                      | CREATE INDEX index_lists_on_account_id ON public.lists USING btree (account_id)
 lists                              | lists_pkey                                                     | CREATE UNIQUE INDEX lists_pkey ON public.lists USING btree (id)
 login_activities                   | index_login_activities_on_user_id                              | CREATE INDEX index_login_activities_on_user_id ON public.login_activities USING btree (user_id)
 login_activities                   | login_activities_pkey                                          | CREATE UNIQUE INDEX login_activities_pkey ON public.login_activities USING btree (id)
 markers                            | index_markers_on_user_id_and_timeline                          | CREATE UNIQUE INDEX index_markers_on_user_id_and_timeline ON public.markers USING btree (user_id, timeline)
 markers                            | markers_pkey                                                   | CREATE UNIQUE INDEX markers_pkey ON public.markers USING btree (id)
 media_attachments                  | index_media_attachments_on_account_id_and_status_id            | CREATE INDEX index_media_attachments_on_account_id_and_status_id ON public.media_attachments USING btree (account_id, status_id DESC)
 media_attachments                  | index_media_attachments_on_scheduled_status_id                 | CREATE INDEX index_media_attachments_on_scheduled_status_id ON public.media_attachments USING btree (scheduled_status_id) WHERE (scheduled_status_id IS NOT NULL)
 media_attachments                  | index_media_attachments_on_shortcode                           | CREATE UNIQUE INDEX index_media_attachments_on_shortcode ON public.media_attachments USING btree (shortcode text_pattern_ops) WHERE (shortcode IS NOT NULL)
 media_attachments                  | index_media_attachments_on_status_id                           | CREATE INDEX index_media_attachments_on_status_id ON public.media_attachments USING btree (status_id)
 media_attachments                  | media_attachments_pkey                                         | CREATE UNIQUE INDEX media_attachments_pkey ON public.media_attachments USING btree (id)
 mentions                           | index_mentions_on_account_id_and_status_id                     | CREATE UNIQUE INDEX index_mentions_on_account_id_and_status_id ON public.mentions USING btree (account_id, status_id)
 mentions                           | index_mentions_on_status_id                                    | CREATE INDEX index_mentions_on_status_id ON public.mentions USING btree (status_id)
 mentions                           | mentions_pkey                                                  | CREATE UNIQUE INDEX mentions_pkey ON public.mentions USING btree (id)
 mutes                              | index_mutes_on_account_id_and_target_account_id                | CREATE UNIQUE INDEX index_mutes_on_account_id_and_target_account_id ON public.mutes USING btree (account_id, target_account_id)
 mutes                              | index_mutes_on_target_account_id                               | CREATE INDEX index_mutes_on_target_account_id ON public.mutes USING btree (target_account_id)
 mutes                              | mutes_pkey                                                     | CREATE UNIQUE INDEX mutes_pkey ON public.mutes USING btree (id)
 notifications                      | index_notifications_on_account_id_and_id_and_type              | CREATE INDEX index_notifications_on_account_id_and_id_and_type ON public.notifications USING btree (account_id, id DESC, type)
 notifications                      | index_notifications_on_activity_id_and_activity_type           | CREATE INDEX index_notifications_on_activity_id_and_activity_type ON public.notifications USING btree (activity_id, activity_type)
 notifications                      | index_notifications_on_from_account_id                         | CREATE INDEX index_notifications_on_from_account_id ON public.notifications USING btree (from_account_id)
 notifications                      | notifications_pkey                                             | CREATE UNIQUE INDEX notifications_pkey ON public.notifications USING btree (id)
 oauth_access_grants                | index_oauth_access_grants_on_resource_owner_id                 | CREATE INDEX index_oauth_access_grants_on_resource_owner_id ON public.oauth_access_grants USING btree (resource_owner_id)
 oauth_access_grants                | index_oauth_access_grants_on_token                             | CREATE UNIQUE INDEX index_oauth_access_grants_on_token ON public.oauth_access_grants USING btree (token)
 oauth_access_grants                | oauth_access_grants_pkey                                       | CREATE UNIQUE INDEX oauth_access_grants_pkey ON public.oauth_access_grants USING btree (id)
 oauth_access_tokens                | index_oauth_access_tokens_on_refresh_token                     | CREATE UNIQUE INDEX index_oauth_access_tokens_on_refresh_token ON public.oauth_access_tokens USING btree (refresh_token text_pattern_ops) WHERE (refresh_token IS NOT NULL)
 oauth_access_tokens                | index_oauth_access_tokens_on_resource_owner_id                 | CREATE INDEX index_oauth_access_tokens_on_resource_owner_id ON public.oauth_access_tokens USING btree (resource_owner_id) WHERE (resource_owner_id IS NOT NULL)
 oauth_access_tokens                | index_oauth_access_tokens_on_token                             | CREATE UNIQUE INDEX index_oauth_access_tokens_on_token ON public.oauth_access_tokens USING btree (token)
 oauth_access_tokens                | oauth_access_tokens_pkey                                       | CREATE UNIQUE INDEX oauth_access_tokens_pkey ON public.oauth_access_tokens USING btree (id)
 oauth_applications                 | index_oauth_applications_on_owner_id_and_owner_type            | CREATE INDEX index_oauth_applications_on_owner_id_and_owner_type ON public.oauth_applications USING btree (owner_id, owner_type)
 oauth_applications                 | index_oauth_applications_on_superapp                           | CREATE INDEX index_oauth_applications_on_superapp ON public.oauth_applications USING btree (superapp) WHERE (superapp = true)
 oauth_applications                 | index_oauth_applications_on_uid                                | CREATE UNIQUE INDEX index_oauth_applications_on_uid ON public.oauth_applications USING btree (uid)
 oauth_applications                 | oauth_applications_pkey                                        | CREATE UNIQUE INDEX oauth_applications_pkey ON public.oauth_applications USING btree (id)
 one_time_keys                      | index_one_time_keys_on_device_id                               | CREATE INDEX index_one_time_keys_on_device_id ON public.one_time_keys USING btree (device_id)
 one_time_keys                      | index_one_time_keys_on_key_id                                  | CREATE INDEX index_one_time_keys_on_key_id ON public.one_time_keys USING btree (key_id)
 one_time_keys                      | one_time_keys_pkey                                             | CREATE UNIQUE INDEX one_time_keys_pkey ON public.one_time_keys USING btree (id)
 pghero_space_stats                 | index_pghero_space_stats_on_database_and_captured_at           | CREATE INDEX index_pghero_space_stats_on_database_and_captured_at ON public.pghero_space_stats USING btree (database, captured_at)
 pghero_space_stats                 | pghero_space_stats_pkey                                        | CREATE UNIQUE INDEX pghero_space_stats_pkey ON public.pghero_space_stats USING btree (id)
 poll_votes                         | index_poll_votes_on_account_id                                 | CREATE INDEX index_poll_votes_on_account_id ON public.poll_votes USING btree (account_id)
 poll_votes                         | index_poll_votes_on_poll_id                                    | CREATE INDEX index_poll_votes_on_poll_id ON public.poll_votes USING btree (poll_id)
 poll_votes                         | poll_votes_pkey                                                | CREATE UNIQUE INDEX poll_votes_pkey ON public.poll_votes USING btree (id)
 polls                              | index_polls_on_account_id                                      | CREATE INDEX index_polls_on_account_id ON public.polls USING btree (account_id)
 polls                              | index_polls_on_status_id                                       | CREATE INDEX index_polls_on_status_id ON public.polls USING btree (status_id)
 polls                              | polls_pkey                                                     | CREATE UNIQUE INDEX polls_pkey ON public.polls USING btree (id)
 preview_card_providers             | index_preview_card_providers_on_domain                         | CREATE UNIQUE INDEX index_preview_card_providers_on_domain ON public.preview_card_providers USING btree (domain)
 preview_card_providers             | preview_card_providers_pkey                                    | CREATE UNIQUE INDEX preview_card_providers_pkey ON public.preview_card_providers USING btree (id)
 preview_card_trends                | index_preview_card_trends_on_preview_card_id                   | CREATE UNIQUE INDEX index_preview_card_trends_on_preview_card_id ON public.preview_card_trends USING btree (preview_card_id)
 preview_card_trends                | preview_card_trends_pkey                                       | CREATE UNIQUE INDEX preview_card_trends_pkey ON public.preview_card_trends USING btree (id)
 preview_cards                      | preview_cards_pkey                                             | CREATE UNIQUE INDEX preview_cards_pkey ON public.preview_cards USING btree (id)
 preview_cards_statuses             | preview_cards_statuses_pkey                                    | CREATE UNIQUE INDEX preview_cards_statuses_pkey ON public.preview_cards_statuses USING btree (status_id, preview_card_id)
 relays                             | relays_pkey                                                    | CREATE UNIQUE INDEX relays_pkey ON public.relays USING btree (id)
 report_notes                       | index_report_notes_on_account_id                               | CREATE INDEX index_report_notes_on_account_id ON public.report_notes USING btree (account_id)
 report_notes                       | index_report_notes_on_report_id                                | CREATE INDEX index_report_notes_on_report_id ON public.report_notes USING btree (report_id)
 report_notes                       | report_notes_pkey                                              | CREATE UNIQUE INDEX report_notes_pkey ON public.report_notes USING btree (id)
 reports                            | index_reports_on_account_id                                    | CREATE INDEX index_reports_on_account_id ON public.reports USING btree (account_id)
 reports                            | index_reports_on_action_taken_by_account_id                    | CREATE INDEX index_reports_on_action_taken_by_account_id ON public.reports USING btree (action_taken_by_account_id) WHERE (action_taken_by_account_id IS NOT NULL)
 reports                            | index_reports_on_assigned_account_id                           | CREATE INDEX index_reports_on_assigned_account_id ON public.reports USING btree (assigned_account_id) WHERE (assigned_account_id IS NOT NULL)
 reports                            | index_reports_on_target_account_id                             | CREATE INDEX index_reports_on_target_account_id ON public.reports USING btree (target_account_id)
 reports                            | reports_pkey                                                   | CREATE UNIQUE INDEX reports_pkey ON public.reports USING btree (id)
 rules                              | rules_pkey                                                     | CREATE UNIQUE INDEX rules_pkey ON public.rules USING btree (id)
 scheduled_statuses                 | index_scheduled_statuses_on_account_id                         | CREATE INDEX index_scheduled_statuses_on_account_id ON public.scheduled_statuses USING btree (account_id)
 scheduled_statuses                 | index_scheduled_statuses_on_scheduled_at                       | CREATE INDEX index_scheduled_statuses_on_scheduled_at ON public.scheduled_statuses USING btree (scheduled_at)
 scheduled_statuses                 | scheduled_statuses_pkey                                        | CREATE UNIQUE INDEX scheduled_statuses_pkey ON public.scheduled_statuses USING btree (id)
 schema_migrations                  | schema_migrations_pkey                                         | CREATE UNIQUE INDEX schema_migrations_pkey ON public.schema_migrations USING btree (version)
 session_activations                | index_session_activations_on_access_token_id                   | CREATE INDEX index_session_activations_on_access_token_id ON public.session_activations USING btree (access_token_id)
 session_activations                | index_session_activations_on_session_id                        | CREATE UNIQUE INDEX index_session_activations_on_session_id ON public.session_activations USING btree (session_id)
 session_activations                | index_session_activations_on_user_id                           | CREATE INDEX index_session_activations_on_user_id ON public.session_activations USING btree (user_id)
 session_activations                | session_activations_pkey                                       | CREATE UNIQUE INDEX session_activations_pkey ON public.session_activations USING btree (id)
 settings                           | index_settings_on_thing_type_and_thing_id_and_var              | CREATE UNIQUE INDEX index_settings_on_thing_type_and_thing_id_and_var ON public.settings USING btree (thing_type, thing_id, var)
 settings                           | settings_pkey                                                  | CREATE UNIQUE INDEX settings_pkey ON public.settings USING btree (id)
 site_uploads                       | index_site_uploads_on_var                                      | CREATE UNIQUE INDEX index_site_uploads_on_var ON public.site_uploads USING btree (var)
 site_uploads                       | site_uploads_pkey                                              | CREATE UNIQUE INDEX site_uploads_pkey ON public.site_uploads USING btree (id)
 software_updates                   | index_software_updates_on_version                              | CREATE UNIQUE INDEX index_software_updates_on_version ON public.software_updates USING btree (version)
 software_updates                   | software_updates_pkey                                          | CREATE UNIQUE INDEX software_updates_pkey ON public.software_updates USING btree (id)
 status_edits                       | index_status_edits_on_account_id                               | CREATE INDEX index_status_edits_on_account_id ON public.status_edits USING btree (account_id)
 status_edits                       | index_status_edits_on_status_id                                | CREATE INDEX index_status_edits_on_status_id ON public.status_edits USING btree (status_id)
 status_edits                       | status_edits_pkey                                              | CREATE UNIQUE INDEX status_edits_pkey ON public.status_edits USING btree (id)
 status_pins                        | index_status_pins_on_account_id_and_status_id                  | CREATE UNIQUE INDEX index_status_pins_on_account_id_and_status_id ON public.status_pins USING btree (account_id, status_id)
 status_pins                        | index_status_pins_on_status_id                                 | CREATE INDEX index_status_pins_on_status_id ON public.status_pins USING btree (status_id)
 status_pins                        | status_pins_pkey                                               | CREATE UNIQUE INDEX status_pins_pkey ON public.status_pins USING btree (id)
 status_stats                       | index_status_stats_on_status_id                                | CREATE UNIQUE INDEX index_status_stats_on_status_id ON public.status_stats USING btree (status_id)
 status_stats                       | status_stats_pkey                                              | CREATE UNIQUE INDEX status_stats_pkey ON public.status_stats USING btree (id)
 status_trends                      | index_status_trends_on_account_id                              | CREATE INDEX index_status_trends_on_account_id ON public.status_trends USING btree (account_id)
 status_trends                      | index_status_trends_on_status_id                               | CREATE UNIQUE INDEX index_status_trends_on_status_id ON public.status_trends USING btree (status_id)
 status_trends                      | status_trends_pkey                                             | CREATE UNIQUE INDEX status_trends_pkey ON public.status_trends USING btree (id)
 statuses                           | index_statuses_20190820                                        | CREATE INDEX index_statuses_20190820 ON public.statuses USING btree (account_id, id DESC, visibility, updated_at) WHERE (deleted_at IS NULL)
 statuses                           | index_statuses_local_20190824                                  | CREATE INDEX index_statuses_local_20190824 ON public.statuses USING btree (id DESC, account_id) WHERE ((local OR (uri IS NULL)) AND (deleted_at IS NULL) AND (visibility = 0) AND (reblog_of_id IS NULL) AND ((NOT reply) OR (in_reply_to_account_id = account_id)))
 statuses                           | index_statuses_on_account_id                                   | CREATE INDEX index_statuses_on_account_id ON public.statuses USING btree (account_id)
 statuses                           | index_statuses_on_deleted_at                                   | CREATE INDEX index_statuses_on_deleted_at ON public.statuses USING btree (deleted_at) WHERE (deleted_at IS NOT NULL)
 statuses                           | index_statuses_on_in_reply_to_account_id                       | CREATE INDEX index_statuses_on_in_reply_to_account_id ON public.statuses USING btree (in_reply_to_account_id) WHERE (in_reply_to_account_id IS NOT NULL)
 statuses                           | index_statuses_on_in_reply_to_id                               | CREATE INDEX index_statuses_on_in_reply_to_id ON public.statuses USING btree (in_reply_to_id) WHERE (in_reply_to_id IS NOT NULL)
 statuses                           | index_statuses_on_reblog_of_id_and_account_id                  | CREATE INDEX index_statuses_on_reblog_of_id_and_account_id ON public.statuses USING btree (reblog_of_id, account_id)
 statuses                           | index_statuses_on_uri                                          | CREATE UNIQUE INDEX index_statuses_on_uri ON public.statuses USING btree (uri text_pattern_ops) WHERE (uri IS NOT NULL)
 statuses                           | index_statuses_public_20200119                                 | CREATE INDEX index_statuses_public_20200119 ON public.statuses USING btree (id DESC, account_id) WHERE ((deleted_at IS NULL) AND (visibility = 0) AND (reblog_of_id IS NULL) AND ((NOT reply) OR (in_reply_to_account_id = account_id)))
 statuses                           | statuses_pkey                                                  | CREATE UNIQUE INDEX statuses_pkey ON public.statuses USING btree (id)
 statuses_tags                      | index_statuses_tags_on_status_id                               | CREATE INDEX index_statuses_tags_on_status_id ON public.statuses_tags USING btree (status_id)
 statuses_tags                      | statuses_tags_pkey                                             | CREATE UNIQUE INDEX statuses_tags_pkey ON public.statuses_tags USING btree (tag_id, status_id)
 system_keys                        | system_keys_pkey                                               | CREATE UNIQUE INDEX system_keys_pkey ON public.system_keys USING btree (id)
 tag_follows                        | index_tag_follows_on_account_id_and_tag_id                     | CREATE UNIQUE INDEX index_tag_follows_on_account_id_and_tag_id ON public.tag_follows USING btree (account_id, tag_id)
 tag_follows                        | index_tag_follows_on_tag_id                                    | CREATE INDEX index_tag_follows_on_tag_id ON public.tag_follows USING btree (tag_id)
 tag_follows                        | tag_follows_pkey                                               | CREATE UNIQUE INDEX tag_follows_pkey ON public.tag_follows USING btree (id)
 tags                               | index_tags_on_name_lower_btree                                 | CREATE UNIQUE INDEX index_tags_on_name_lower_btree ON public.tags USING btree (lower((name)::text) text_pattern_ops)
 tags                               | tags_pkey                                                      | CREATE UNIQUE INDEX tags_pkey ON public.tags USING btree (id)
 tombstones                         | index_tombstones_on_account_id                                 | CREATE INDEX index_tombstones_on_account_id ON public.tombstones USING btree (account_id)
 tombstones                         | index_tombstones_on_uri                                        | CREATE INDEX index_tombstones_on_uri ON public.tombstones USING btree (uri)
 tombstones                         | tombstones_pkey                                                | CREATE UNIQUE INDEX tombstones_pkey ON public.tombstones USING btree (id)
 unavailable_domains                | index_unavailable_domains_on_domain                            | CREATE UNIQUE INDEX index_unavailable_domains_on_domain ON public.unavailable_domains USING btree (domain)
 unavailable_domains                | unavailable_domains_pkey                                       | CREATE UNIQUE INDEX unavailable_domains_pkey ON public.unavailable_domains USING btree (id)
 user_invite_requests               | index_user_invite_requests_on_user_id                          | CREATE INDEX index_user_invite_requests_on_user_id ON public.user_invite_requests USING btree (user_id)
 user_invite_requests               | user_invite_requests_pkey                                      | CREATE UNIQUE INDEX user_invite_requests_pkey ON public.user_invite_requests USING btree (id)
 user_roles                         | user_roles_pkey                                                | CREATE UNIQUE INDEX user_roles_pkey ON public.user_roles USING btree (id)
 users                              | index_users_on_account_id                                      | CREATE INDEX index_users_on_account_id ON public.users USING btree (account_id)
 users                              | index_users_on_confirmation_token                              | CREATE UNIQUE INDEX index_users_on_confirmation_token ON public.users USING btree (confirmation_token)
 users                              | index_users_on_created_by_application_id                       | CREATE INDEX index_users_on_created_by_application_id ON public.users USING btree (created_by_application_id) WHERE (created_by_application_id IS NOT NULL)
 users                              | index_users_on_email                                           | CREATE UNIQUE INDEX index_users_on_email ON public.users USING btree (email)
 users                              | index_users_on_reset_password_token                            | CREATE UNIQUE INDEX index_users_on_reset_password_token ON public.users USING btree (reset_password_token text_pattern_ops) WHERE (reset_password_token IS NOT NULL)
 users                              | index_users_on_role_id                                         | CREATE INDEX index_users_on_role_id ON public.users USING btree (role_id) WHERE (role_id IS NOT NULL)
 users                              | index_users_on_unconfirmed_email                               | CREATE INDEX index_users_on_unconfirmed_email ON public.users USING btree (unconfirmed_email) WHERE (unconfirmed_email IS NOT NULL)
 users                              | users_pkey                                                     | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
 web_push_subscriptions             | index_web_push_subscriptions_on_access_token_id                | CREATE INDEX index_web_push_subscriptions_on_access_token_id ON public.web_push_subscriptions USING btree (access_token_id) WHERE (access_token_id IS NOT NULL)
 web_push_subscriptions             | index_web_push_subscriptions_on_user_id                        | CREATE INDEX index_web_push_subscriptions_on_user_id ON public.web_push_subscriptions USING btree (user_id)
 web_push_subscriptions             | web_push_subscriptions_pkey                                    | CREATE UNIQUE INDEX web_push_subscriptions_pkey ON public.web_push_subscriptions USING btree (id)
 web_settings                       | index_web_settings_on_user_id                                  | CREATE UNIQUE INDEX index_web_settings_on_user_id ON public.web_settings USING btree (user_id)
 web_settings                       | web_settings_pkey                                              | CREATE UNIQUE INDEX web_settings_pkey ON public.web_settings USING btree (id)
 webauthn_credentials               | index_webauthn_credentials_on_external_id                      | CREATE UNIQUE INDEX index_webauthn_credentials_on_external_id ON public.webauthn_credentials USING btree (external_id)
 webauthn_credentials               | index_webauthn_credentials_on_user_id                          | CREATE INDEX index_webauthn_credentials_on_user_id ON public.webauthn_credentials USING btree (user_id)
 webauthn_credentials               | webauthn_credentials_pkey                                      | CREATE UNIQUE INDEX webauthn_credentials_pkey ON public.webauthn_credentials USING btree (id)
 webhooks                           | index_webhooks_on_url                                          | CREATE UNIQUE INDEX index_webhooks_on_url ON public.webhooks USING btree (url)
 webhooks                           | webhooks_pkey                                                  | CREATE UNIQUE INDEX webhooks_pkey ON public.webhooks USING btree (id)
(262 rows)
ClearlyClaire commented 9 months ago

You seem to be missing a few indexes…

CREATE UNIQUE INDEX index_accounts_on_username_and_domain_lower ON public.accounts USING btree (lower((username)::text), COALESCE(lower((domain)::text), ''::text));
CREATE UNIQUE INDEX index_custom_emojis_on_shortcode_and_domain ON public.custom_emojis USING btree (shortcode, domain);
CREATE UNIQUE INDEX index_preview_cards_on_url ON public.preview_cards USING btree (url);
thatcosmonaut commented 9 months ago

Re-ran the db migrations but nothing came up. I'll add the indices manually and see if it helps

thatcosmonaut commented 9 months ago 
ERROR:  could not create unique index "index_accounts_on_username_and_domain_lower"
DETAIL:  Key (lower(username::text), COALESCE(lower(domain::text), ''::text))=(andis, chaos.social) is duplicated.

ERROR:  could not create unique index "index_custom_emojis_on_shortcode_and_domain"
DETAIL:  Key (shortcode, domain)=(war_hammer, dragonscave.space) is duplicated.

ERROR:  could not create unique index "index_preview_cards_on_url"
DETAIL:  Key (url)=(https://cash.app/$NiyahLee1990) is duplicated.
ClearlyClaire commented 9 months ago

Your database seems corrupted :grimacing:

I suggest stopping your server immediately and trying tootctl maintenance fix-duplicates

thatcosmonaut commented 9 months ago

Will do.

thatcosmonaut commented 9 months ago

fix-duplicates finished running and I restarted the server, confirmed that the indices are there now. db CPU usage seems much improved so far. Thank you!