From d0fc69d7213cab03c57947cac94bad1fd9cc6dba Mon Sep 17 00:00:00 2001 From: abcang Date: Tue, 27 Apr 2021 01:57:46 +0900 Subject: Further improve the media attached status query for accounts (#16106) --- app/controllers/accounts_controller.rb | 2 +- app/controllers/admin/statuses_controller.rb | 2 +- app/controllers/api/v1/accounts/statuses_controller.rb | 2 +- ...2_add_index_on_media_attachments_account_id_status_id.rb | 13 +++++++++++++ db/schema.rb | 4 ++-- 5 files changed, 18 insertions(+), 5 deletions(-) create mode 100644 db/migrate/20210425135952_add_index_on_media_attachments_account_id_status_id.rb diff --git a/app/controllers/accounts_controller.rb b/app/controllers/accounts_controller.rb index f15961552..8210918d8 100644 --- a/app/controllers/accounts_controller.rb +++ b/app/controllers/accounts_controller.rb @@ -77,7 +77,7 @@ class AccountsController < ApplicationController end def only_media_scope - Status.joins(:media_attachments).group(:id) + Status.joins(:media_attachments).merge(@account.media_attachments.reorder(nil)).group(:id) end def no_replies_scope diff --git a/app/controllers/admin/statuses_controller.rb b/app/controllers/admin/statuses_controller.rb index df1bd1bc7..ef279509d 100644 --- a/app/controllers/admin/statuses_controller.rb +++ b/app/controllers/admin/statuses_controller.rb @@ -14,7 +14,7 @@ module Admin @statuses = @account.statuses.where(visibility: [:public, :unlisted]) if params[:media] - @statuses.merge!(Status.joins(:media_attachments).group(:id)) + @statuses.merge!(Status.joins(:media_attachments).merge(@account.media_attachments.reorder(nil)).group(:id)) end @statuses = @statuses.preload(:media_attachments, :mentions).page(params[:page]).per(PER_PAGE) diff --git a/app/controllers/api/v1/accounts/statuses_controller.rb b/app/controllers/api/v1/accounts/statuses_controller.rb index b1e478f8d..92ccb8061 100644 --- a/app/controllers/api/v1/accounts/statuses_controller.rb +++ b/app/controllers/api/v1/accounts/statuses_controller.rb @@ -42,7 +42,7 @@ class Api::V1::Accounts::StatusesController < Api::BaseController end def only_media_scope - Status.joins(:media_attachments).group(:id) + Status.joins(:media_attachments).merge(@account.media_attachments.reorder(nil)).group(:id) end def pinned_scope diff --git a/db/migrate/20210425135952_add_index_on_media_attachments_account_id_status_id.rb b/db/migrate/20210425135952_add_index_on_media_attachments_account_id_status_id.rb new file mode 100644 index 000000000..5ef2d3c39 --- /dev/null +++ b/db/migrate/20210425135952_add_index_on_media_attachments_account_id_status_id.rb @@ -0,0 +1,13 @@ +class AddIndexOnMediaAttachmentsAccountIdStatusId < ActiveRecord::Migration[5.2] + disable_ddl_transaction! + + def up + add_index :media_attachments, [:account_id, :status_id], order: { status_id: :desc }, algorithm: :concurrently + remove_index :media_attachments, :account_id, algorithm: :concurrently + end + + def down + add_index :media_attachments, :account_id, algorithm: :concurrently + remove_index :media_attachments, [:account_id, :status_id], order: { status_id: :desc }, algorithm: :concurrently + end +end diff --git a/db/schema.rb b/db/schema.rb index 8dc0661fc..0d951ee95 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -10,7 +10,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 2021_04_21_121431) do +ActiveRecord::Schema.define(version: 2021_04_25_135952) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -541,7 +541,7 @@ ActiveRecord::Schema.define(version: 2021_04_21_121431) do t.integer "thumbnail_file_size" t.datetime "thumbnail_updated_at" t.string "thumbnail_remote_url" - t.index ["account_id"], name: "index_media_attachments_on_account_id" + t.index ["account_id", "status_id"], name: "index_media_attachments_on_account_id_and_status_id", order: { status_id: :desc } t.index ["scheduled_status_id"], name: "index_media_attachments_on_scheduled_status_id" t.index ["shortcode"], name: "index_media_attachments_on_shortcode", unique: true t.index ["status_id"], name: "index_media_attachments_on_status_id" -- cgit