From 7f0c49c58ab75bbd6b4dc44d47a8c4ab57db9d51 Mon Sep 17 00:00:00 2001 From: abcang Date: Sun, 25 Apr 2021 13:33:28 +0900 Subject: Improve tag search query (#16104) --- ...210421121431_add_case_insensitive_btree_index_to_tags.rb | 13 +++++++++++++ 1 file changed, 13 insertions(+) create mode 100644 db/migrate/20210421121431_add_case_insensitive_btree_index_to_tags.rb (limited to 'db/migrate') diff --git a/db/migrate/20210421121431_add_case_insensitive_btree_index_to_tags.rb b/db/migrate/20210421121431_add_case_insensitive_btree_index_to_tags.rb new file mode 100644 index 000000000..ed359e8cd --- /dev/null +++ b/db/migrate/20210421121431_add_case_insensitive_btree_index_to_tags.rb @@ -0,0 +1,13 @@ +class AddCaseInsensitiveBtreeIndexToTags < ActiveRecord::Migration[5.2] + disable_ddl_transaction! + + def up + safety_assured { execute 'CREATE UNIQUE INDEX CONCURRENTLY index_tags_on_name_lower_btree ON tags (lower(name) text_pattern_ops)' } + remove_index :tags, name: 'index_tags_on_name_lower' + end + + def down + safety_assured { execute 'CREATE UNIQUE INDEX CONCURRENTLY index_tags_on_name_lower ON tags (lower(name))' } + remove_index :tags, name: 'index_tags_on_name_lower_btree' + end +end -- cgit 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 (limited to 'db/migrate') 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 From d9ae3db8d5543cf0b7fa44186c191c9bb2472d23 Mon Sep 17 00:00:00 2001 From: Claire Date: Wed, 5 May 2021 22:04:52 +0200 Subject: Improve performance of follow recommendation scheduler (#16159) Express follow_recommendations in terms of account_summaries rather than accounts, integrate filters that are unconditionally used, and materialize the resulting view. This should result in the bulk of the computation being performed only once instead of **once per recommendation language**. --- app/models/follow_recommendation.rb | 6 ++-- .../scheduler/follow_recommendations_scheduler.rb | 5 ++-- ...6_update_follow_recommendations_to_version_2.rb | 18 ++++++++++++ db/schema.rb | 28 ++++++++++-------- db/views/follow_recommendations_v02.sql | 34 ++++++++++++++++++++++ 5 files changed, 75 insertions(+), 16 deletions(-) create mode 100644 db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb create mode 100644 db/views/follow_recommendations_v02.sql (limited to 'db/migrate') diff --git a/app/models/follow_recommendation.rb b/app/models/follow_recommendation.rb index 6670b6560..1ed6dc49b 100644 --- a/app/models/follow_recommendation.rb +++ b/app/models/follow_recommendation.rb @@ -14,9 +14,11 @@ class FollowRecommendation < ApplicationRecord belongs_to :account_summary, foreign_key: :account_id belongs_to :account, foreign_key: :account_id - scope :safe, -> { joins(:account_summary).merge(AccountSummary.safe) } scope :localized, ->(locale) { joins(:account_summary).merge(AccountSummary.localized(locale)) } - scope :filtered, -> { joins(:account_summary).merge(AccountSummary.filtered) } + + def self.refresh + Scenic.database.refresh_materialized_view(table_name, concurrently: true, cascade: false) + end def readonly? true diff --git a/app/workers/scheduler/follow_recommendations_scheduler.rb b/app/workers/scheduler/follow_recommendations_scheduler.rb index 0a0286496..cb1e15961 100644 --- a/app/workers/scheduler/follow_recommendations_scheduler.rb +++ b/app/workers/scheduler/follow_recommendations_scheduler.rb @@ -14,13 +14,14 @@ class Scheduler::FollowRecommendationsScheduler def perform # Maintaining a materialized view speeds-up subsequent queries significantly AccountSummary.refresh + FollowRecommendation.refresh - fallback_recommendations = FollowRecommendation.safe.filtered.limit(SET_SIZE).index_by(&:account_id) + fallback_recommendations = FollowRecommendation.limit(SET_SIZE).index_by(&:account_id) I18n.available_locales.each do |locale| recommendations = begin if AccountSummary.safe.filtered.localized(locale).exists? # We can skip the work if no accounts with that language exist - FollowRecommendation.safe.filtered.localized(locale).limit(SET_SIZE).index_by(&:account_id) + FollowRecommendation.localized(locale).limit(SET_SIZE).index_by(&:account_id) else {} end diff --git a/db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb b/db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb new file mode 100644 index 000000000..9b2a284e4 --- /dev/null +++ b/db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb @@ -0,0 +1,18 @@ +class UpdateFollowRecommendationsToVersion2 < ActiveRecord::Migration[6.1] + # We're switching from a normal to a materialized view so we need + # custom `up` and `down` paths. + + def up + drop_view :follow_recommendations + create_view :follow_recommendations, version: 2, materialized: true + + # To be able to refresh the view concurrently, + # at least one unique index is required + safety_assured { add_index :follow_recommendations, :account_id, unique: true } + end + + def down + drop_view :follow_recommendations, materialized: true + create_view :follow_recommendations, version: 1 + end +end diff --git a/db/schema.rb b/db/schema.rb index 0d951ee95..88e906079 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_25_135952) do +ActiveRecord::Schema.define(version: 2021_05_05_174616) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -1114,30 +1114,34 @@ ActiveRecord::Schema.define(version: 2021_04_25_135952) do SQL add_index "account_summaries", ["account_id"], name: "index_account_summaries_on_account_id", unique: true - create_view "follow_recommendations", sql_definition: <<-SQL + create_view "follow_recommendations", materialized: true, sql_definition: <<-SQL SELECT t0.account_id, sum(t0.rank) AS rank, array_agg(t0.reason) AS reason - FROM ( SELECT accounts.id AS account_id, + FROM ( SELECT account_summaries.account_id, ((count(follows.id))::numeric / (1.0 + (count(follows.id))::numeric)) AS rank, 'most_followed'::text AS reason - FROM ((follows - JOIN accounts ON ((accounts.id = follows.target_account_id))) + FROM (((follows + JOIN account_summaries ON ((account_summaries.account_id = follows.target_account_id))) JOIN users ON ((users.account_id = follows.account_id))) - WHERE ((users.current_sign_in_at >= (now() - 'P30D'::interval)) AND (accounts.suspended_at IS NULL) AND (accounts.moved_to_account_id IS NULL) AND (accounts.silenced_at IS NULL) AND (accounts.locked = false) AND (accounts.discoverable = true)) - GROUP BY accounts.id + LEFT JOIN follow_recommendation_suppressions ON ((follow_recommendation_suppressions.account_id = follows.target_account_id))) + WHERE ((users.current_sign_in_at >= (now() - 'P30D'::interval)) AND (account_summaries.sensitive = false) AND (follow_recommendation_suppressions.id IS NULL)) + GROUP BY account_summaries.account_id HAVING (count(follows.id) >= 5) UNION ALL - SELECT accounts.id AS account_id, + SELECT account_summaries.account_id, (sum((status_stats.reblogs_count + status_stats.favourites_count)) / (1.0 + sum((status_stats.reblogs_count + status_stats.favourites_count)))) AS rank, 'most_interactions'::text AS reason - FROM ((status_stats + FROM (((status_stats JOIN statuses ON ((statuses.id = status_stats.status_id))) - JOIN accounts ON ((accounts.id = statuses.account_id))) - WHERE ((statuses.id >= (((date_part('epoch'::text, (now() - 'P30D'::interval)) * (1000)::double precision))::bigint << 16)) AND (accounts.suspended_at IS NULL) AND (accounts.moved_to_account_id IS NULL) AND (accounts.silenced_at IS NULL) AND (accounts.locked = false) AND (accounts.discoverable = true)) - GROUP BY accounts.id + JOIN account_summaries ON ((account_summaries.account_id = statuses.account_id))) + LEFT JOIN follow_recommendation_suppressions ON ((follow_recommendation_suppressions.account_id = statuses.account_id))) + WHERE ((statuses.id >= (((date_part('epoch'::text, (now() - 'P30D'::interval)) * (1000)::double precision))::bigint << 16)) AND (account_summaries.sensitive = false) AND (follow_recommendation_suppressions.id IS NULL)) + GROUP BY account_summaries.account_id HAVING (sum((status_stats.reblogs_count + status_stats.favourites_count)) >= (5)::numeric)) t0 GROUP BY t0.account_id ORDER BY (sum(t0.rank)) DESC; SQL + add_index "follow_recommendations", ["account_id"], name: "index_follow_recommendations_on_account_id", unique: true + end diff --git a/db/views/follow_recommendations_v02.sql b/db/views/follow_recommendations_v02.sql new file mode 100644 index 000000000..673c5cc85 --- /dev/null +++ b/db/views/follow_recommendations_v02.sql @@ -0,0 +1,34 @@ +SELECT + account_id, + sum(rank) AS rank, + array_agg(reason) AS reason +FROM ( + SELECT + account_summaries.account_id AS account_id, + count(follows.id) / (1.0 + count(follows.id)) AS rank, + 'most_followed' AS reason + FROM follows + INNER JOIN account_summaries ON account_summaries.account_id = follows.target_account_id + INNER JOIN users ON users.account_id = follows.account_id + LEFT OUTER JOIN follow_recommendation_suppressions ON follow_recommendation_suppressions.account_id = follows.target_account_id + WHERE users.current_sign_in_at >= (now() - interval '30 days') + AND account_summaries.sensitive = 'f' + AND follow_recommendation_suppressions.id IS NULL + GROUP BY account_summaries.account_id + HAVING count(follows.id) >= 5 + UNION ALL + SELECT account_summaries.account_id AS account_id, + sum(reblogs_count + favourites_count) / (1.0 + sum(reblogs_count + favourites_count)) AS rank, + 'most_interactions' AS reason + FROM status_stats + INNER JOIN statuses ON statuses.id = status_stats.status_id + INNER JOIN account_summaries ON account_summaries.account_id = statuses.account_id + LEFT OUTER JOIN follow_recommendation_suppressions ON follow_recommendation_suppressions.account_id = statuses.account_id + WHERE statuses.id >= ((date_part('epoch', now() - interval '30 days') * 1000)::bigint << 16) + AND account_summaries.sensitive = 'f' + AND follow_recommendation_suppressions.id IS NULL + GROUP BY account_summaries.account_id + HAVING sum(reblogs_count + favourites_count) >= 5 +) t0 +GROUP BY account_id +ORDER BY rank DESC -- cgit From d8e0c8a89e1f1dd1c4ce1513deaeb3c85c6e4a42 Mon Sep 17 00:00:00 2001 From: Claire Date: Fri, 7 May 2021 18:14:29 +0200 Subject: Do not populate AccountSummaries and FollowRecommendation views on migration (#16173) Materializing those views can take a while, and they are currently refreshed anyway each time they are actually used, in the FollowRecommendationsScheduler. --- db/migrate/20210322164601_create_account_summaries.rb | 2 +- db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'db/migrate') diff --git a/db/migrate/20210322164601_create_account_summaries.rb b/db/migrate/20210322164601_create_account_summaries.rb index b9faf180d..bc9011113 100644 --- a/db/migrate/20210322164601_create_account_summaries.rb +++ b/db/migrate/20210322164601_create_account_summaries.rb @@ -1,6 +1,6 @@ class CreateAccountSummaries < ActiveRecord::Migration[5.2] def change - create_view :account_summaries, materialized: true + create_view :account_summaries, materialized: { no_data: true } # To be able to refresh the view concurrently, # at least one unique index is required diff --git a/db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb b/db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb index 9b2a284e4..56c0b4cb0 100644 --- a/db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb +++ b/db/migrate/20210505174616_update_follow_recommendations_to_version_2.rb @@ -4,7 +4,7 @@ class UpdateFollowRecommendationsToVersion2 < ActiveRecord::Migration[6.1] def up drop_view :follow_recommendations - create_view :follow_recommendations, version: 2, materialized: true + create_view :follow_recommendations, version: 2, materialized: { no_data: true } # To be able to refresh the view concurrently, # at least one unique index is required -- cgit