diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/migrate/20200302214442_modify_status_index_20200301.rb | 21 | ||||
| -rw-r--r-- | db/structure.sql | 54 |
2 files changed, 70 insertions, 5 deletions
diff --git a/db/migrate/20200302214442_modify_status_index_20200301.rb b/db/migrate/20200302214442_modify_status_index_20200301.rb new file mode 100644 index 000000000..9aef315c2 --- /dev/null +++ b/db/migrate/20200302214442_modify_status_index_20200301.rb @@ -0,0 +1,21 @@ +class ModifyStatusIndex20200301 < ActiveRecord::Migration[5.2] + def up + safety_assured do + remove_index :statuses, name: :index_statuses_20200301 + remove_index :statuses, name: :index_statuses_curated_20200301 + remove_index :statuses, name: :index_statuses_public_20200301 + remove_index :statuses, name: :index_statuses_local_20200301 + remove_index :statuses, name: :index_statuses_hidden_20200301 + + add_index :statuses, [:id, :account_id, :visibility, :created_at, :updated_at], where: 'deleted_at IS NULL', order: { id: :desc }, name: :index_statuses_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: '(curated) OR (curated = TRUE) OR (curated IS TRUE)', order: { id: :desc }, name: :index_statuses_curated_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: '(network) OR (network = TRUE) OR (network IS TRUE)', order: { id: :desc }, name: :index_statuses_network_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: '((local) OR (local = TRUE) OR (uri IS NULL)) AND (deleted_at IS NULL)', order: { id: :desc }, name: :index_statuses_local_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: 'visibility IN (0, 5) OR (visibility = 0) OR (visibility = 5)', order: { id: :desc }, name: :index_statuses_public_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: 'visibility IN (0, 1, 5) OR (visibility = 0) OR (visibility = 1) OR (visibility = 5)', order: { id: :desc }, name: :index_statuses_public_unlisted_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: '(NOT reply) OR (reply = FALSE) OR (reply IS FALSE) OR (in_reply_to_account_id = account_id)', name: :index_statuses_without_replies_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: 'reblog_of_id IS NULL', name: :index_statuses_without_reblogs_20200301 + add_index :statuses, [:id, :account_id, :visibility], where: '(hidden) OR (hidden = FALSE) OR (NOT hidden)', name: :index_statuses_hidden_20200301 + end + end +end diff --git a/db/structure.sql b/db/structure.sql index 2b485cfb4..86d0432d1 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -3750,6 +3750,13 @@ CREATE INDEX index_custom_filters_on_account_id ON public.custom_filters USING b -- +-- Name: index_custom_filters_on_account_id_and_phrase; Type: INDEX; Schema: public; Owner: - +-- + +CREATE UNIQUE INDEX index_custom_filters_on_account_id_and_phrase ON public.custom_filters USING btree (account_id, phrase); + + +-- -- Name: index_defederating_statuses_on_defederate_after; Type: INDEX; Schema: public; Owner: - -- @@ -3792,6 +3799,13 @@ CREATE UNIQUE INDEX index_domain_blocks_on_domain ON public.domain_blocks USING -- +-- Name: index_domain_blocks_on_severity; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_domain_blocks_on_severity ON public.domain_blocks USING btree (severity); + + +-- -- Name: index_email_domain_blocks_on_domain; Type: INDEX; Schema: public; Owner: - -- @@ -4243,14 +4257,14 @@ CREATE INDEX index_statuses_20190820 ON public.statuses USING btree (account_id, -- Name: index_statuses_20200301; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_statuses_20200301 ON public.statuses USING btree (account_id, id DESC, visibility, updated_at) WHERE ((deleted_at IS NULL) AND (NOT hidden)); +CREATE INDEX index_statuses_20200301 ON public.statuses USING btree (id DESC, account_id, visibility, created_at, updated_at) WHERE (deleted_at IS NULL); -- -- Name: index_statuses_curated_20200301; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_statuses_curated_20200301 ON public.statuses USING btree (id DESC, account_id) WHERE (curated AND (NOT hidden) AND (deleted_at IS NULL)); +CREATE INDEX index_statuses_curated_20200301 ON public.statuses USING btree (id DESC, account_id, visibility) WHERE (curated OR (curated = true) OR (curated IS TRUE)); -- @@ -4264,7 +4278,14 @@ CREATE INDEX index_statuses_hidden_20200301 ON public.statuses USING btree (id, -- Name: index_statuses_local_20200301; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_statuses_local_20200301 ON public.statuses USING btree (id DESC, account_id) WHERE (network AND (NOT hidden) AND ((local OR (uri IS NULL)) AND (deleted_at IS NULL) AND (visibility = ANY (ARRAY[0, 5])) AND (reblog_of_id IS NULL) AND ((NOT reply) OR (in_reply_to_account_id = account_id)))); +CREATE INDEX index_statuses_local_20200301 ON public.statuses USING btree (id DESC, account_id, visibility) WHERE ((local OR (local = true) OR (uri IS NULL)) AND (deleted_at IS NULL)); + + +-- +-- Name: index_statuses_network_20200301; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_statuses_network_20200301 ON public.statuses USING btree (id DESC, account_id, visibility) WHERE (network OR (network = true) OR (network IS TRUE)); -- @@ -4320,7 +4341,14 @@ CREATE UNIQUE INDEX index_statuses_on_uri ON public.statuses USING btree (uri); -- Name: index_statuses_public_20200301; Type: INDEX; Schema: public; Owner: - -- -CREATE INDEX index_statuses_public_20200301 ON public.statuses USING btree (id DESC, account_id) WHERE ((NOT hidden) AND ((deleted_at IS NULL) AND (visibility = ANY (ARRAY[0, 1, 5])) AND (reblog_of_id IS NULL) AND ((NOT reply) OR (in_reply_to_account_id = account_id)))); +CREATE INDEX index_statuses_public_20200301 ON public.statuses USING btree (id DESC, account_id, visibility) WHERE ((visibility = ANY (ARRAY[0, 5])) OR (visibility = 0) OR (visibility = 5)); + + +-- +-- Name: index_statuses_public_unlisted_20200301; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_statuses_public_unlisted_20200301 ON public.statuses USING btree (id DESC, account_id, visibility) WHERE ((visibility = ANY (ARRAY[0, 1, 5])) OR (visibility = 0) OR (visibility = 1) OR (visibility = 5)); -- @@ -4338,6 +4366,20 @@ CREATE UNIQUE INDEX index_statuses_tags_on_tag_id_and_status_id ON public.status -- +-- Name: index_statuses_without_reblogs_20200301; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_statuses_without_reblogs_20200301 ON public.statuses USING btree (id, account_id, visibility) WHERE (reblog_of_id IS NULL); + + +-- +-- Name: index_statuses_without_replies_20200301; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_statuses_without_replies_20200301 ON public.statuses USING btree (id, account_id, visibility) WHERE ((NOT reply) OR (reply = false) OR (reply IS FALSE) OR (in_reply_to_account_id = account_id)); + + +-- -- Name: index_tags_on_name; Type: INDEX; Schema: public; Owner: - -- @@ -5525,6 +5567,8 @@ INSERT INTO "schema_migrations" (version) VALUES ('20200218070510'), ('20200224150903'), ('20200227214439'), -('20200227214440'); +('20200227214440'), +('20200227214441'), +('20200302214442'); |