Skip to content

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

Ivan Tashkinov requested to merge chore/user-relationships-target-id-rel-type-index into develop

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

Merge request reports