Skip to content

Greatly speed up recipients query

lain requested to merge recipients-query-speedup into develop

Old Query

explain analyze 
SELECT distinct u0.* FROM "users" AS u0 
LEFT OUTER JOIN "following_relationships" AS f1 ON f1."follower_id" = u0."id" 
LEFT OUTER JOIN "users" AS u2 ON u2."id" = f1."following_id" 
WHERE (u0."deactivated" != true) 
AND (u0."local" = true) 
AND (NOT (u0."nickname" IS NULL)) 
AND 
  (u0."ap_id" = ANY('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}') 
  OR 
  (u2."follower_address" = ANY('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}') AND (f1."state" = 2)));

 Unique  (cost=3282.24..3282.40 rows=1 width=1988) (actual time=446.145..746.954 rows=1 loops=1)
   ->  Sort  (cost=3282.24..3282.24 rows=1 width=1988) (actual time=446.142..446.644 rows=2797 loops=1)
         Sort Key: u0.id, u0.email, u0.password_hash, u0.name, u0.nickname, u0.bio, u0.inserted_at, u0.updated_at, u0.ap_id, u0.avat
ar, u0.local, u0.follower_address, u0.last_refreshed_at, u0.tags, u0.last_digest_emailed_at, u0.following_address, u0.keys, u0.banner, u0.background, u0.source_data, u0.note_count, u0.follower_count, u0.following_count, u0.locked, u0.confirmation_pending, u0.password_reset_pending, u0.confirmation_token, u0.default_scope, u0.blocks, u0.domain_blocks, u0.mutes, u0.muted_reblogs, u0.muted_notifications, u0.subscribers, u0.deactivated, u0.no_rich_text, u0.ap_enabled, u0.is_moderator, u0.is_admin, u0.show_role, u0.settings, u0.magic_key, u0.uri, u0.hide_followers_count, u0.hide_follows_count, u0.hide_followers, u0.hide_follows, u0.hide_favorites, u0.unread_conversation_count, u0.pinned_activities, u0.email_notifications, u0.mascot, u0.emoji, u0.pleroma_settings_store, u0.fields, u0.raw_fields, u0.discoverable, u0.invisible, u0.notification_settings, u0.skip_thread_containment, u0.also_known_as, u0.allow_following_move, u0.actor_type
         Sort Method: quicksort  Memory: 5734kB
         ->  Nested Loop Left Join  (cost=466.96..3282.23 rows=1 width=1988) (actual time=7.549..72.918 rows=2797 loops=1)
               Filter: (((u0.ap_id)::text = ANY ('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}'::text[])) OR (((u2.follower_address)::text = ANY ('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}'::text[])) AND (f1.state = 2)))
               Rows Removed by Filter: 7445
               ->  Hash Right Join  (cost=466.54..1652.56 rows=406 width=2008) (actual time=3.886..27.339 rows=10242 loops=1)
                     Hash Cond: (f1.follower_id = u0.id)
                     ->  Seq Scan on following_relationships f1  (cost=0.00..1056.48 rows=49348 width=36) (actual time=0.013..5.314 rows=49390 loops=1)
                     ->  Hash  (cost=461.47..461.47 rows=406 width=1988) (actual time=3.796..3.797 rows=451 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 461kB
                           ->  Index Scan using users_local_index on users u0  (cost=0.42..461.47 rows=406 width=1988) (actual time=0.050..1.738 rows=451 loops=1)
                                 Index Cond: (local = true)
                                 Filter: ((NOT deactivated) AND local AND (nickname IS NOT NULL))
                                 Rows Removed by Filter: 20
               ->  Index Scan using users_pkey on users u2  (cost=0.42..3.99 rows=1 width=66) (actual time=0.004..0.004 rows=1 loops=10242)
                     Index Cond: (id = f1.following_id)
 Planning Time: 2.248 ms
 Execution Time: 747.826 ms

New Query

explain analyze SELECT  u0.*
 FROM users AS u0 WHERE (u0."deactivated" != true)
  AND (u0."local" = true) 
  AND (NOT (u0.nickname IS NULL)) 
  AND 
    (u0.ap_id = ANY('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}') 
    OR 
    u0.id = ANY((SELECT f1.follower_id AS follower_id FROM users AS u0 INNER JOIN following_relationships AS f1 ON u0.id = f1.following_id WHERE (f1.state = 2) AND (u0.follower_address = ANY('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}')))));

 Index Scan using users_local_index on users u0  (cost=83.49..547.08 rows=203 width=1988) (actual time=0.219..1.291 rows=1 loops=1)
   Index Cond: (local = true)
   Filter: ((NOT deactivated) AND local AND (nickname IS NOT NULL) AND (((ap_id)::text = ANY ('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}'::text[])) OR (hashed SubPlan 1)))
   Rows Removed by Filter: 470
   SubPlan 1
     ->  Nested Loop  (cost=4.74..83.07 rows=1 width=16) (actual time=0.157..0.164 rows=2 loops=1)
           ->  Index Scan using users_follower_address_index on users u0_1  (cost=0.42..24.84 rows=3 width=16) (actual time=0.132..0.133 rows=1 loops=1)
                 Index Cond: ((follower_address)::text = ANY ('{https://pl.istukiy.com/users/itsukia/followers,https://pleroma.soykaf.com/users/lain,https://pl.istukiy.com/users/itsukia}'::text[]))
           ->  Bitmap Heap Scan on following_relationships f1  (cost=4.32..19.37 rows=4 width=32) (actual time=0.021..0.025 rows=2 loops=1)
                 Recheck Cond: (following_id = u0_1.id)
                 Filter: (state = 2)
                 Heap Blocks: exact=2
                 ->  Bitmap Index Scan on following_relationships_following_id_index  (cost=0.00..4.32 rows=4 width=0) (actual time=0.011..0.012 rows=2 loops=1)
                       Index Cond: (following_id = u0_1.id)
 Planning Time: 1.036 ms
 Execution Time: 1.389 ms

Merge request reports