hopefully this is the last of changes to `statuses` table indexes

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');