From 42a1231245410a74435db0342047a991e4c92109 Mon Sep 17 00:00:00 2001 From: Akihiko Odaki Date: Mon, 14 May 2018 19:50:45 +0900 Subject: Improve index on statuses for api/v1/accounts account_id statuses (#7476) Queries with the combination of account_id, id, and visibility can be categorized in three types: 1. Querying for public and unlisted to enumerate statuses visible to anyone. 2. Querying for public, unlisted, and private to enumerate statuses visible to follower. 3. Querying for direct to enumerate own direct statuses. 1 and 2 is covered by the index with condition 'visibility IN (0, 1, 2)'. It would bring better performance in case that there are many direct statuses. The index with condition 'visibility = 3' is just for 3. It would be much faster to query direct statuses thanks to this query. The total size of those two indexes are expected to be smaller than the deleted one because they are partial and does not have to cover all the table. --- ...dex_on_statuses_for_api_v1_accounts_account_id_statuses.rb | 11 +++++++++++ 1 file changed, 11 insertions(+) create mode 100644 db/migrate/20180514130000_improve_index_on_statuses_for_api_v1_accounts_account_id_statuses.rb (limited to 'db/migrate') diff --git a/db/migrate/20180514130000_improve_index_on_statuses_for_api_v1_accounts_account_id_statuses.rb b/db/migrate/20180514130000_improve_index_on_statuses_for_api_v1_accounts_account_id_statuses.rb new file mode 100644 index 000000000..2573bdf94 --- /dev/null +++ b/db/migrate/20180514130000_improve_index_on_statuses_for_api_v1_accounts_account_id_statuses.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +class ImproveIndexOnStatusesForApiV1AccountsAccountIdStatuses < ActiveRecord::Migration[5.1] + disable_ddl_transaction! + + def change + add_index :statuses, [:account_id, :id, :visibility], where: 'visibility IN (0, 1, 2)', algorithm: :concurrently + add_index :statuses, [:account_id, :id], where: 'visibility = 3', algorithm: :concurrently + remove_index :statuses, column: [:account_id, :id, :visibility, :updated_at], order: { id: :desc }, algorithm: :concurrently, name: :index_statuses_20180106 + end +end -- cgit From ed4bae182bd82ed424211bfb219ffa347058e8b2 Mon Sep 17 00:00:00 2001 From: Akihiko Odaki Date: Tue, 15 May 2018 02:56:17 +0900 Subject: Revert index change on statuses for api/v1/accounts account_id statuses (#7484) --- ..._on_statuses_for_api_v1_accounts_account_id_statuses.rb | 14 ++++++++++++++ db/schema.rb | 5 ++--- 2 files changed, 16 insertions(+), 3 deletions(-) create mode 100644 db/migrate/20180514140000_revert_index_change_on_statuses_for_api_v1_accounts_account_id_statuses.rb (limited to 'db/migrate') diff --git a/db/migrate/20180514140000_revert_index_change_on_statuses_for_api_v1_accounts_account_id_statuses.rb b/db/migrate/20180514140000_revert_index_change_on_statuses_for_api_v1_accounts_account_id_statuses.rb new file mode 100644 index 000000000..b6b9f96ae --- /dev/null +++ b/db/migrate/20180514140000_revert_index_change_on_statuses_for_api_v1_accounts_account_id_statuses.rb @@ -0,0 +1,14 @@ +# frozen_string_literal: true + +class RevertIndexChangeOnStatusesForApiV1AccountsAccountIdStatuses < ActiveRecord::Migration[5.1] + disable_ddl_transaction! + + def change + safety_assured do + add_index :statuses, [:account_id, :id, :visibility, :updated_at], order: { id: :desc }, algorithm: :concurrently, name: :index_statuses_20180106 + end + + remove_index :statuses, column: [:account_id, :id, :visibility], where: 'visibility IN (0, 1, 2)', algorithm: :concurrently + remove_index :statuses, column: [:account_id, :id], where: 'visibility = 3', algorithm: :concurrently + end +end diff --git a/db/schema.rb b/db/schema.rb index 5bfc4df04..7435b6cc9 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: 2018_05_14_130000) do +ActiveRecord::Schema.define(version: 2018_05_14_140000) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -447,8 +447,7 @@ ActiveRecord::Schema.define(version: 2018_05_14_130000) do t.bigint "account_id", null: false t.bigint "application_id" t.bigint "in_reply_to_account_id" - t.index ["account_id", "id", "visibility"], name: "index_statuses_on_account_id_and_id_and_visibility", where: "(visibility = ANY (ARRAY[0, 1, 2]))" - t.index ["account_id", "id"], name: "index_statuses_on_account_id_and_id", where: "(visibility = 3)" + t.index ["account_id", "id", "visibility", "updated_at"], name: "index_statuses_20180106", order: { id: :desc } t.index ["conversation_id"], name: "index_statuses_on_conversation_id" t.index ["in_reply_to_id"], name: "index_statuses_on_in_reply_to_id" t.index ["reblog_of_id", "account_id"], name: "index_statuses_on_reblog_of_id_and_account_id" -- cgit