Search: Move fts index to the activities.gin-speedup

2 files changed, 56 insertions, 2 deletions

diff --git a/lib/pleroma/activity/search.ex b/lib/pleroma/activity/search.ex
index 382c81118..4511b2533 100644
--- a/lib/pleroma/activity/search.ex
+++ b/lib/pleroma/activity/search.ex
@@ -57,8 +57,8 @@ defmodule Pleroma.Activity.Search do
     from([a, o] in q,
       where:
         fragment(
-          "to_tsvector('english', ?->>'content') @@ plainto_tsquery('english', ?)",
-          o.data,
+          "? @@ plainto_tsquery('english', ?)",
+          a.fts_content,
           ^search_query
         )
     )
diff --git a/priv/repo/migrations/20210120161014_move_fts_index.exs b/priv/repo/migrations/20210120161014_move_fts_index.exs
new file mode 100644
index 000000000..d32b56214
--- /dev/null
+++ b/priv/repo/migrations/20210120161014_move_fts_index.exs
@@ -0,0 +1,54 @@
+defmodule Pleroma.Repo.Migrations.MoveFtsIndex do
+  use Ecto.Migration
+
+  def up do
+    # Drop the old index
+    drop_if_exists(
+      index(:objects, ["(to_tsvector('english', data->>'content'))"],
+        using: :gin,
+        name: :objects_fts
+      )
+    )
+
+    alter table(:activities) do
+      add(:fts_content, :tsvector)
+    end
+
+    execute("CREATE FUNCTION activities_fts_update() RETURNS trigger AS $$
+    declare
+      content text := '';
+    begin
+      if new.data->>'type' = 'Create' then
+        
+        select objects.data->>'content'
+        from objects
+        into content
+        where objects.data->>'id' = new.data->>'object';
+
+        new.fts_content := to_tsvector('english', content);
+
+      end if;
+
+      return new;
+    end
+    $$ LANGUAGE plpgsql")
+
+    create_if_not_exists(index(:activities, ["fts_content"], using: :gin, name: :activities_fts))
+    execute("CREATE TRIGGER ts_vector_activities_update BEFORE INSERT ON activities
+      FOR EACH ROW EXECUTE PROCEDURE activities_fts_update()
+    ")
+  end
+
+  def down do
+    execute("drop function if exists actitivies_fts_update()")
+    execute("drop trigger if existis ts_vector_activities_update")
+    drop_if_exists(index(:activities, ["fts_content"], using: :gin, name: :activities_fts))
+
+    create_if_not_exists(
+      index(:objects, ["(to_tsvector('english', data->>'content'))"],
+        using: :gin,
+        name: :objects_fts
+      )
+    )
+  end
+end