summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorTusooa Zhu <tusooa@kazv.moe>2021-08-27 21:43:50 -0400
committerHaelwenn (lanodan) Monnier <contact@hacktivis.me>2021-08-28 18:26:04 +0200
commitbd0eb1c675c5a70e7ae97c17f6fa88e534776ca5 (patch)
treec3d22a4177d03fad2996a7a9be8c94c29593310d
parent53b0dd4ecc9f7f23983082cf610156f0840443a9 (diff)
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 Backport of: https://git.pleroma.social/pleroma/pleroma/-/merge_requests/3519 Closes: https://git.pleroma.social/pleroma/pleroma/-/issues/2758
-rw-r--r--CHANGELOG.md1
-rw-r--r--lib/pleroma/activity/search.ex18
2 files changed, 17 insertions, 2 deletions
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 90e0ca82a..660920565 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -28,6 +28,7 @@ The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/).
- Mastodon API: Activity Search fallbacks on status fetching after a DB Timeout/Error
- Mastodon API: Fix crash in Streamer related to reblogging
- AdminAPI: List available frontends when `static/frontends` folder is missing
+- Make activity search properly use language-aware GIN indexes
## 2.4.0 - 2021-08-08
diff --git a/lib/pleroma/activity/search.ex b/lib/pleroma/activity/search.ex
index a5923519c..09671f621 100644
--- 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
)