commit: a9984c6da78f27d56c67e7fcec12dbad0a1ebc67
parent 84ec0fbeaadc8bdbce256212258a932530088346
Author: Tusooa Zhu <tusooa@kazv.moe>
Date: Fri, 27 Aug 2021 21:43:50 -0400
Make activity search properly use GIN indexes
The original approach to search in GIN indexes is to use
`to_tsvector(text)` in the WHERE clause of the query. According to
postgres docs [pdoc], this method does not make use of the index,
while `to_tsvector(config, text)` does. This commit changed the
query to use the two-argument `to_tsvector()`.
[pdoc]: https://www.postgresql.org/docs/12/textsearch-tables.html
To obtain the search config in use, we make a query to the db first.
The `::regconfig::oid` hack is needed because Postgrex does not support
regconfig type directly [postgrexbug]. I use the conversion from and to
`oid` instead of `text` because I tested in the actual DB and querying
using the conversion via `text` is slow just as the one-argument
`to_tsvector()` variant.
[postgrexbug]: https://github.com/elixir-ecto/postgrex/issues/502
BUG: https://git.pleroma.social/pleroma/pleroma/-/issues/2758
Diffstat:
1 file changed, 16 insertions(+), 2 deletions(-)
diff --git a/lib/pleroma/activity/search.ex b/lib/pleroma/activity/search.ex
@@ -65,10 +65,17 @@ defmodule Pleroma.Activity.Search do
end
defp query_with(q, :gin, search_query, :plain) do
+ %{rows: [[tsc]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ "select current_setting('default_text_search_config')::regconfig::oid;"
+ )
+
from([a, o] in q,
where:
fragment(
- "to_tsvector(?->>'content') @@ plainto_tsquery(?)",
+ "to_tsvector(?::oid::regconfig, ?->>'content') @@ plainto_tsquery(?)",
+ ^tsc,
o.data,
^search_query
)
@@ -76,10 +83,17 @@ defmodule Pleroma.Activity.Search do
end
defp query_with(q, :gin, search_query, :websearch) do
+ %{rows: [[tsc]]} =
+ Ecto.Adapters.SQL.query!(
+ Pleroma.Repo,
+ "select current_setting('default_text_search_config')::regconfig::oid;"
+ )
+
from([a, o] in q,
where:
fragment(
- "to_tsvector(?->>'content') @@ websearch_to_tsquery(?)",
+ "to_tsvector(?::oid::regconfig, ?->>'content') @@ websearch_to_tsquery(?)",
+ ^tsc,
o.data,
^search_query
)