Skip to content
GitLab
Projects Groups Topics Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Register
  • Sign in
  • pleroma pleroma
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributor statistics
    • Graph
    • Compare revisions
  • Issues 646
    • Issues 646
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 88
    • Merge requests 88
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages and registries
    • Packages and registries
    • Container Registry
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Repository
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar
  • PleromaPleroma
  • pleromapleroma
  • Merge requests
  • !3588

Added index on [:target_id, :relationship_type] to :user_relationships

  • Review changes

  • Download
  • Email patches
  • Plain diff
Merged Ivan Tashkinov requested to merge chore/user-relationships-target-id-rel-type-index into develop Dec 29, 2021
  • Overview 1
  • Commits 2
  • Pipelines 2
  • Changes 1

Added index on [:target_id, :relationship_type] to :user_relationships (speeds up Notification.exclude_blockers/_).

EXPLAIN ANALYZE SELECT DISTINCT u1."ap_id" FROM "user_relationships" AS u0 INNER JOIN "users" AS u1 ON u1."id" = u0."source_id" WHERE (u0."target_id" = '0000017d-c3fc-5c73-4f4a-0d45ea1d0000'::uuid) AND (u0."relationship_type" = ANY(ARRAY[1]::integer[]))

Without index:

Unique  (cost=110.36..110.37 rows=1 width=37) (actual time=0.741..0.742 rows=0 loops=1)
  ->  Sort  (cost=110.36..110.37 rows=1 width=37) (actual time=0.741..0.742 rows=0 loops=1)
        Sort Key: u1.ap_id
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.42..110.35 rows=1 width=37) (actual time=0.735..0.735 rows=0 loops=1)
              ->  Seq Scan on user_relationships u0  (cost=0.00..101.91 rows=1 width=16) (actual time=0.734..0.734 rows=0 loops=1)
                    Filter: ((relationship_type = ANY ('{1}'::integer[])) AND (target_id = '0000017d-c3fc-5c73-4f4a-0d45ea1d0000'::uuid))
                    Rows Removed by Filter: 4212
              ->  Index Scan using users_pkey on users u1  (cost=0.42..8.44 rows=1 width=53) (never executed)
                    Index Cond: (id = u0.source_id)
Planning Time: 0.521 ms
Execution Time: 0.788 ms

With index:

Unique  (cost=16.75..16.76 rows=1 width=37) (actual time=0.026..0.027 rows=0 loops=1)
  ->  Sort  (cost=16.75..16.76 rows=1 width=37) (actual time=0.025..0.026 rows=0 loops=1)
        Sort Key: u1.ap_id
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.70..16.74 rows=1 width=37) (actual time=0.022..0.023 rows=0 loops=1)
              ->  Index Scan using user_relationships_target_id_relationship_type_index on user_relationships u0  (cost=0.28..8.30 rows=1 width=16) (actual time=0.022..0.022 rows=0 loops=1)
                    Index Cond: ((target_id = '0000017d-c3fc-5c73-4f4a-0d45ea1d0000'::uuid) AND (relationship_type = ANY ('{1}'::integer[])))
              ->  Index Scan using users_pkey on users u1  (cost=0.42..8.44 rows=1 width=53) (never executed)
                    Index Cond: (id = u0.source_id)
Planning Time: 0.580 ms
Execution Time: 0.077 ms
Assignee
Assign to
Reviewers
Request review from
Time tracking
Source branch: chore/user-relationships-target-id-rel-type-index