From 01c45ddc9ead715131b3c583caa14fcf20845354 Mon Sep 17 00:00:00 2001
From: lain <lain@soykaf.club>
Date: Sat, 11 May 2019 11:26:46 +0200
Subject: [PATCH] Search: Use RUM index.

---
 .../mastodon_api/mastodon_api_controller.ex   |  6 ++--
 ...510135645_add_fts_index_to_objects_two.exs | 33 +++++++++++++++++++
 2 files changed, 36 insertions(+), 3 deletions(-)
 create mode 100644 priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs

diff --git a/lib/pleroma/web/mastodon_api/mastodon_api_controller.ex b/lib/pleroma/web/mastodon_api/mastodon_api_controller.ex
index 956736780..32677df95 100644
--- a/lib/pleroma/web/mastodon_api/mastodon_api_controller.ex
+++ b/lib/pleroma/web/mastodon_api/mastodon_api_controller.ex
@@ -1019,12 +1019,12 @@ defmodule Pleroma.Web.MastodonAPI.MastodonAPIController do
         where: "https://www.w3.org/ns/activitystreams#Public" in a.recipients,
         where:
           fragment(
-            "to_tsvector('english', ?->>'content') @@ plainto_tsquery('english', ?)",
-            o.data,
+            "? @@ plainto_tsquery('english', ?)",
+            o.fts_content,
             ^query
           ),
         limit: 20,
-        order_by: [desc: :id]
+        order_by: [fragment("? <=> now()::date", o.inserted_at)]
       )
 
     Repo.all(q) ++ fetched
diff --git a/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs b/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs
new file mode 100644
index 000000000..14b964847
--- /dev/null
+++ b/priv/repo/migrations/20190510135645_add_fts_index_to_objects_two.exs
@@ -0,0 +1,33 @@
+defmodule Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo do
+  use Ecto.Migration
+
+  def up do
+    drop_if_exists index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts)
+    alter table(:objects) do
+      add(:fts_content, :tsvector)
+    end
+
+    execute("CREATE FUNCTION objects_fts_update() RETURNS trigger AS $$
+    begin
+      new.fts_content := to_tsvector('english', new.data->>'content');
+      return new;
+    end
+    $$ LANGUAGE plpgsql")
+    execute("create index objects_fts on objects using RUM (fts_content rum_tsvector_addon_ops, inserted_at) with (attach = 'inserted_at', to = 'fts_content');")
+
+    execute("CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON objects
+    FOR EACH ROW EXECUTE PROCEDURE objects_fts_update()")
+
+    execute("UPDATE objects SET updated_at = NOW()")
+  end
+
+  def down do
+    execute "drop index objects_fts"
+    execute "drop trigger tsvectorupdate on objects"
+    execute "drop function objects_fts_update()"
+    alter table(:objects) do
+      remove(:fts_content, :tsvector)
+    end
+    create index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts)
+  end
+end
-- 
2.45.2