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