Fix performance on instances list in admin UI (#15282)

- Reduce duplicate queries
- Remove n+1 queries
- Add accounts count to detailed view
- Add separate action log entry for updating existing domain blocks

7 files changed, 88 insertions, 29 deletions

diff --git a/app/models/account.rb b/app/models/account.rb
index ed11a514d..e21b353e9 100644
--- a/app/models/account.rb
+++ b/app/models/account.rb
@@ -67,6 +67,7 @@ class Account < ApplicationRecord
   include Paginable
   include AccountCounters
   include DomainNormalizable
+  include DomainMaterializable
   include AccountMerging
 
   TRUST_LEVELS = {
@@ -103,7 +104,6 @@ class Account < ApplicationRecord
   scope :bots, -> { where(actor_type: %w(Application Service)) }
   scope :groups, -> { where(actor_type: 'Group') }
   scope :alphabetic, -> { order(domain: :asc, username: :asc) }
-  scope :by_domain_accounts, -> { group(:domain).select(:domain, 'COUNT(*) AS accounts_count').order('accounts_count desc') }
   scope :matches_username, ->(value) { where(arel_table[:username].matches("#{value}%")) }
   scope :matches_display_name, ->(value) { where(arel_table[:display_name].matches("#{value}%")) }
   scope :matches_domain, ->(value) { where(arel_table[:domain].matches("%#{value}%")) }
@@ -438,10 +438,6 @@ class Account < ApplicationRecord
       super - %w(statuses_count following_count followers_count)
     end
 
-    def domains
-      reorder(nil).pluck(Arel.sql('distinct accounts.domain'))
-    end
-
     def inboxes
       urls = reorder(nil).where(protocol: :activitypub).group(:preferred_inbox_url).pluck(Arel.sql("coalesce(nullif(accounts.shared_inbox_url, ''), accounts.inbox_url) AS preferred_inbox_url"))
       DeliveryFailureTracker.without_unavailable(urls)
diff --git a/app/models/concerns/domain_materializable.rb b/app/models/concerns/domain_materializable.rb
new file mode 100644
index 000000000..88337f8c0
--- /dev/null
+++ b/app/models/concerns/domain_materializable.rb
@@ -0,0 +1,13 @@
+# frozen_string_literal: true
+
+module DomainMaterializable
+  extend ActiveSupport::Concern
+
+  included do
+    after_create_commit :refresh_instances_view
+  end
+
+  def refresh_instances_view
+    Instance.refresh unless domain.nil? || Instance.where(domain: domain).exists?
+  end
+end
diff --git a/app/models/domain_allow.rb b/app/models/domain_allow.rb
index 5fe0e3a29..4b0a89c18 100644
--- a/app/models/domain_allow.rb
+++ b/app/models/domain_allow.rb
@@ -12,6 +12,7 @@
 
 class DomainAllow < ApplicationRecord
   include DomainNormalizable
+  include DomainMaterializable
 
   validates :domain, presence: true, uniqueness: true, domain: true
 
diff --git a/app/models/domain_block.rb b/app/models/domain_block.rb
index 2b18e01fa..829d7583b 100644
--- a/app/models/domain_block.rb
+++ b/app/models/domain_block.rb
@@ -16,6 +16,7 @@
 
 class DomainBlock < ApplicationRecord
   include DomainNormalizable
+  include DomainMaterializable
 
   enum severity: [:silence, :suspend, :noop]
 
diff --git a/app/models/instance.rb b/app/models/instance.rb
index 3c740f8a2..29be03662 100644
--- a/app/models/instance.rb
+++ b/app/models/instance.rb
@@ -1,26 +1,63 @@
 # frozen_string_literal: true
+# == Schema Information
+#
+# Table name: instances
+#
+#  domain         :string           primary key
+#  accounts_count :bigint(8)
+#
 
-class Instance
-  include ActiveModel::Model
+class Instance < ApplicationRecord
+  self.primary_key = :domain
 
-  attr_accessor :domain, :accounts_count, :domain_block
+  has_many :accounts, foreign_key: :domain, primary_key: :domain
 
-  def initialize(resource)
-    @domain         = resource.domain
-    @accounts_count = resource.respond_to?(:accounts_count) ? resource.accounts_count : nil
-    @domain_block   = resource.is_a?(DomainBlock) ? resource : DomainBlock.rule_for(domain)
-    @domain_allow   = resource.is_a?(DomainAllow) ? resource : DomainAllow.rule_for(domain)
+  belongs_to :domain_block, foreign_key: :domain, primary_key: :domain
+  belongs_to :domain_allow, foreign_key: :domain, primary_key: :domain
+
+  scope :matches_domain, ->(value) { where(arel_table[:domain].matches("%#{value}%")) }
+
+  def self.refresh
+    Scenic.database.refresh_materialized_view(table_name, concurrently: true, cascade: false)
   end
 
-  def countable?
-    @accounts_count.present?
+  def readonly?
+    true
   end
 
-  def to_param
-    domain
+  def delivery_failure_tracker
+    @delivery_failure_tracker ||= DeliveryFailureTracker.new(domain)
+  end
+
+  def following_count
+    @following_count ||= Follow.where(account: accounts).count
+  end
+
+  def followers_count
+    @followers_count ||= Follow.where(target_account: accounts).count
+  end
+
+  def reports_count
+    @reports_count ||= Report.where(target_account: accounts).count
   end
 
-  def cache_key
+  def blocks_count
+    @blocks_count ||= Block.where(target_account: accounts).count
+  end
+
+  def public_comment
+    domain_block&.public_comment
+  end
+
+  def private_comment
+    domain_block&.private_comment
+  end
+
+  def media_storage
+    @media_storage ||= MediaAttachment.where(account: accounts).sum(:file_file_size)
+  end
+
+  def to_param
     domain
   end
 end
diff --git a/app/models/instance_filter.rb b/app/models/instance_filter.rb
index 9c467bc27..0598d8fea 100644
--- a/app/models/instance_filter.rb
+++ b/app/models/instance_filter.rb
@@ -13,18 +13,27 @@ class InstanceFilter
   end
 
   def results
-    if params[:limited].present?
-      scope = DomainBlock
-      scope = scope.matches_domain(params[:by_domain]) if params[:by_domain].present?
-      scope.order(id: :desc)
-    elsif params[:allowed].present?
-      scope = DomainAllow
-      scope = scope.matches_domain(params[:by_domain]) if params[:by_domain].present?
-      scope.order(id: :desc)
+    scope = Instance.includes(:domain_block, :domain_allow).order(accounts_count: :desc)
+
+    params.each do |key, value|
+      scope.merge!(scope_for(key, value.to_s.strip)) if value.present?
+    end
+
+    scope
+  end
+
+  private
+
+  def scope_for(key, value)
+    case key.to_s
+    when 'limited'
+      Instance.joins(:domain_block).reorder(Arel.sql('domain_blocks.id desc'))
+    when 'allowed'
+      Instance.joins(:domain_allow).reorder(Arel.sql('domain_allows.id desc'))
+    when 'by_domain'
+      Instance.matches_domain(value)
     else
-      scope = Account.remote
-      scope = scope.matches_domain(params[:by_domain]) if params[:by_domain].present?
-      scope.by_domain_accounts
+      raise "Unknown filter: #{key}"
     end
   end
 end
diff --git a/app/models/unavailable_domain.rb b/app/models/unavailable_domain.rb
index e2918b586..5e8870bde 100644
--- a/app/models/unavailable_domain.rb
+++ b/app/models/unavailable_domain.rb
@@ -12,6 +12,8 @@
 class UnavailableDomain < ApplicationRecord
   include DomainNormalizable
 
+  validates :domain, presence: true, uniqueness: true
+
   after_commit :reset_cache!
 
   private