Page MenuHomePhorge
Files F85803453

No OneTemporary
Actions

Size
6 KB
Referenced Files
None
Subscribers
None

View Options

diff --git a/changelog.d/directory-indexes-and-offset-cap.fix b/changelog.d/directory-indexes-and-offset-cap.fix
new file mode 100644
index 000000000..fc05e80eb
--- /dev/null
+++ b/changelog.d/directory-indexes-and-offset-cap.fix
@@ -0,0 +1,10 @@
+Add partial composite indexes and offset cap for the profile directory query.
+
+The directory endpoint filters on `is_discoverable = true` and `invisible = false`,
+then orders by `last_status_at DESC NULLS LAST` (or `id DESC`). Existing single-column
+indexes did not match this filter+sort pattern, causing parallel seq scans plus sorts
+on large instances.
+
+- Adds `users_directory_last_status_at_index` for the default `order=active` path
+- Adds `users_directory_id_index` for the `order=new` path
+- Caps directory offset at 1000 to prevent expensive deep-offset scans
\ No newline at end of file
diff --git a/lib/pleroma/web/mastodon_api/controllers/directory_controller.ex b/lib/pleroma/web/mastodon_api/controllers/directory_controller.ex
index f89425966..80469a576 100644
--- a/lib/pleroma/web/mastodon_api/controllers/directory_controller.ex
+++ b/lib/pleroma/web/mastodon_api/controllers/directory_controller.ex
@@ -1,82 +1,86 @@
# Pleroma: A lightweight social networking server
# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
# SPDX-License-Identifier: AGPL-3.0-only
defmodule Pleroma.Web.MastodonAPI.DirectoryController do
use Pleroma.Web, :controller
import Ecto.Query
alias Pleroma.Pagination
alias Pleroma.User
alias Pleroma.UserRelationship
alias Pleroma.Web.MastodonAPI.AccountView
require Logger
plug(Pleroma.Web.ApiSpec.CastAndValidate)
plug(:skip_auth when action == :index)
defdelegate open_api_operation(action), to: Pleroma.Web.ApiSpec.DirectoryOperation
@doc "GET /api/v1/directory"
+ @max_directory_offset 1000
+
def index(%{assigns: %{user: user}} = conn, params) do
with true <- Pleroma.Config.get([:instance, :profile_directory]) do
limit = Map.get(params, :limit, 20) |> min(80)
+ offset = Map.get(params, :offset, 0) |> min(@max_directory_offset)
+ params = Map.put(params, :offset, offset)
users =
User.Query.build(%{is_discoverable: true, invisible: false, limit: limit})
|> order_by_creation_date(params)
|> exclude_remote(params)
|> exclude_user(user)
|> exclude_relationships(user, [:block, :mute])
|> Pagination.fetch_paginated(params, :offset)
conn
|> put_view(AccountView)
|> render("index.json", for: user, users: users, as: :user)
else
_ -> json(conn, [])
end
end
defp order_by_creation_date(query, %{order: "new"}) do
query
end
defp order_by_creation_date(query, _params) do
query
|> order_by([u], desc_nulls_last: u.last_status_at)
end
defp exclude_remote(query, %{local: true}) do
where(query, [u], u.local == true)
end
defp exclude_remote(query, _params) do
query
end
defp exclude_user(query, %User{id: user_id}) do
where(query, [u], u.id != ^user_id)
end
defp exclude_user(query, _user) do
query
end
defp exclude_relationships(query, %User{id: user_id}, relationship_types) do
query
|> join(:left, [u], r in UserRelationship,
as: :user_relationships,
on:
r.target_id == u.id and r.source_id == ^user_id and
r.relationship_type in ^relationship_types
)
|> where([user_relationships: r], is_nil(r.target_id))
end
defp exclude_relationships(query, _user, _relationship_types) do
query
end
end
diff --git a/priv/repo/migrations/20260423092014_add_directory_composite_indexes.exs b/priv/repo/migrations/20260423092014_add_directory_composite_indexes.exs
new file mode 100644
index 000000000..9824dd4b0
--- /dev/null
+++ b/priv/repo/migrations/20260423092014_add_directory_composite_indexes.exs
@@ -0,0 +1,24 @@
+defmodule Pleroma.Repo.Migrations.AddDirectoryCompositeIndexes do
+ use Ecto.Migration
+
+ @disable_ddl_transaction true
+
+ def up do
+ execute("""
+ CREATE INDEX CONCURRENTLY IF NOT EXISTS users_directory_last_status_at_index
+ ON users (last_status_at DESC NULLS LAST, id DESC NULLS LAST)
+ WHERE is_discoverable = true AND invisible = false
+ """)
+
+ execute("""
+ CREATE INDEX CONCURRENTLY IF NOT EXISTS users_directory_id_index
+ ON users (id DESC NULLS LAST)
+ WHERE is_discoverable = true AND invisible = false
+ """)
+ end
+
+ def down do
+ execute("DROP INDEX CONCURRENTLY IF EXISTS users_directory_last_status_at_index")
+ execute("DROP INDEX CONCURRENTLY IF EXISTS users_directory_id_index")
+ end
+end
diff --git a/test/pleroma/web/mastodon_api/controllers/directory_controller_test.exs b/test/pleroma/web/mastodon_api/controllers/directory_controller_test.exs
index 40b23a5d6..16af1491d 100644
--- a/test/pleroma/web/mastodon_api/controllers/directory_controller_test.exs
+++ b/test/pleroma/web/mastodon_api/controllers/directory_controller_test.exs
@@ -1,50 +1,61 @@
# Pleroma: A lightweight social networking server
# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
# SPDX-License-Identifier: AGPL-3.0-only
defmodule Pleroma.Web.MastodonAPI.DirectoryControllerTest do
use Pleroma.Web.ConnCase
alias Pleroma.Web.CommonAPI
import Pleroma.Factory
test "GET /api/v1/directory with :profile_directory disabled returns empty array", %{conn: conn} do
clear_config([:instance, :profile_directory], false)
insert(:user, is_discoverable: true)
insert(:user, is_discoverable: true)
result =
conn
|> get("/api/v1/directory")
|> json_response_and_validate_schema(200)
assert result == []
end
test "GET /api/v1/directory returns discoverable users only", %{conn: conn} do
%{id: user_id} = insert(:user, is_discoverable: true)
insert(:user, is_discoverable: false)
result =
conn
|> get("/api/v1/directory")
|> json_response_and_validate_schema(200)
assert [%{"id" => ^user_id}] = result
end
test "GET /api/v1/directory returns users sorted by most recent statuses", %{conn: conn} do
insert(:user, is_discoverable: true)
%{id: user_id} = user = insert(:user, is_discoverable: true)
insert(:user, is_discoverable: true)
{:ok, _activity} = CommonAPI.post(user, %{status: "yay i'm discoverable"})
result =
conn
|> get("/api/v1/directory?order=active")
|> json_response_and_validate_schema(200)
assert [%{"id" => ^user_id} | _tail] = result
end
+
+ test "GET /api/v1/directory caps offset to prevent deep pagination scans", %{conn: conn} do
+ Enum.each(1..5, fn _ -> insert(:user, is_discoverable: true) end)
+
+ result =
+ conn
+ |> get("/api/v1/directory?offset=99999")
+ |> json_response_and_validate_schema(200)
+
+ assert result == []
+ end
end

File Metadata

Mime Type
text/x-diff
Expires
Fri, Oct 9, 7:49 AM (1 d, 13 h)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
1782781
Default Alt Text
(6 KB)

Event Timeline