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) --- ...2_add_index_on_media_attachments_account_id_status_id.rb | 13 +++++++++++++ db/schema.rb | 4 ++-- 2 files changed, 15 insertions(+), 2 deletions(-) create mode 100644 db/migrate/20210425135952_add_index_on_media_attachments_account_id_status_id.rb (limited to 'db') 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