Skip to content

Fix favorites query performance

lain requested to merge fav-speedup into develop

old (warm cache, have seen it take 60+ seconds)

explain analyze SELECT a2."id", a2."data", a2."local", a2."actor", a2."recipients", a2."inserted_at", a2."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at" FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>'id') = COALESCE(a0."data"->'object'->>'id', a0."data"->>'object') INNER JOIN "activities" AS a2 ON COALESCE(a2."data"->'object'->>'id', a2."data"->>'object') = (o1."data" ->> 'id') AND (a2."data"->>'type' = 'Create')  WHERE ((a0."data")->>'actor' = 'https://pleroma.soykaf.com/users/lain') AND ((a0."data")->>'type' = 'Like') ORDER BY a0."id" DESC LIMIT 20;

------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1001.98..41694.08 rows=20 width=1881) (actual time=140.192..3779.357 rows=20 loops=1)
   ->  Gather Merge  (cost=1001.98..37419428.08 rows=18391 width=1881) (actual time=140.189..3779.326 rows=20 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Nested Loop  (cost=1.96..37416305.28 rows=7663 width=1881) (actual time=88.515..3097.335 rows=8 loops=3)
               ->  Nested Loop  (cost=1.26..36735550.19 rows=12961 width=1650) (actual time=88.304..3096.182 rows=8 loops=3)
                     ->  Parallel Index Scan Backward using activities_pkey on activities a0  (cost=0.56..36623305.06 rows=12961 width=886) (actual time=88.063..3094.954 rows=8 loops=3)
                           Filter: (((data ->> 'actor'::text) = 'https://pleroma.soykaf.com/users/lain'::text) AND ((data ->> 'type'::text) = 'Like'::text))
                           Rows Removed by Filter: 1095299
                     ->  Index Scan using objects_unique_apid_index on objects o1  (cost=0.70..8.66 rows=1 width=764) (actual time=0.134..0.134 rows=1 loops=25)
                           Index Cond: ((data ->> 'id'::text) = COALESCE(((a0.data -> 'object'::text) ->> 'id'::text), (a0.data ->> 'object'::text)))
               ->  Index Scan using activities_create_objects_index on activities a2  (cost=0.69..52.43 rows=9 width=1101) (actual time=0.129..0.130 rows=1 loops=25)
                     Index Cond: (COALESCE(((data -> 'object'::text) ->> 'id'::text), (data ->> 'object'::text)) = (o1.data ->> 'id'::text))
                     Filter: ((data ->> 'type'::text) = 'Create'::text)
                     Rows Removed by Filter: 3
 Planning Time: 3.816 ms
 Execution Time: 3779.966 ms
(17 rows)

New

explain analyze SELECT a2."id", a2."data", a2."local", a2."actor", a2."recipients", a2."inserted_at", a2."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at" FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>'id') = COALESCE(a0."data"->'object'->>'id', a0."data"->>'object') INNER JOIN "activities" AS a2 ON COALESCE(a2."data"->'object'->>'id', a2."data"->>'object') = (o1."data" ->> 'id') AND (a2."data"->>'type' = 'Create')  WHERE (a0.actor = 'https://pleroma.soykaf.com/users/lain') AND ((a0."data")->>'type' = 'Like') ORDER BY a0."id" DESC nulls last LIMIT 20;

------------------------------------------------------------------
 Limit  (cost=1001.98..2949.57 rows=20 width=1881) (actual time=20.313..33.639 rows=20 loops=1)
   ->  Gather Merge  (cost=1001.98..1791906.31 rows=18391 width=1881) (actual time=20.311..33.621 rows=20 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Nested Loop  (cost=1.96..1788783.51 rows=7663 width=1881) (actual time=1.519..6.362 rows=9 loops=3)
               ->  Nested Loop  (cost=1.26..1108028.42 rows=12961 width=1650) (actual time=1.061..4.377 rows=9 loops=3)
                     ->  Parallel Index Scan using "activities_actor_id_DESC_NULLS_LAST_index" on activities a0  (cost=0.56..995783.29 rows=12961 width=886) (actual time=0.761..2.689 rows=9 loops=3)
                           Index Cond: ((actor)::text = 'https://pleroma.soykaf.com/users/lain'::text)
                           Filter: ((data ->> 'type'::text) = 'Like'::text)
                           Rows Removed by Filter: 75
                     ->  Index Scan using objects_unique_apid_index on objects o1  (cost=0.70..8.66 rows=1 width=764) (actual time=0.172..0.172 rows=1 loops=28)
                           Index Cond: ((data ->> 'id'::text) = COALESCE(((a0.data -> 'object'::text) ->> 'id'::text), (a0.data ->> 'object'::text)))
               ->  Index Scan using activities_create_objects_index on activities a2  (cost=0.69..52.43 rows=9 width=1101) (actual time=0.206..0.207 rows=1 loops=28)
                     Index Cond: (COALESCE(((data -> 'object'::text) ->> 'id'::text), (data ->> 'object'::text)) = (o1.data ->> 'id'::text))
                     Filter: ((data ->> 'type'::text) = 'Create'::text)
                     Rows Removed by Filter: 4
 Planning Time: 2.381 ms
 Execution Time: 33.832 ms
(18 rows)

Merge request reports