sql.sql

lemmy=# explain analyze update community_aggregates ca
    set comments = comments + 1 from comment c, post p
    where p.id = c.post_id
    and p.id = 1233
    and ca.community_id = p.community_id;
                                                                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on community_aggregates ca  (cost=0.98..12.94 rows=0 width=0) (actual time=0.112..0.114 rows=0 loops=1)
   ->  Nested Loop  (cost=0.98..12.94 rows=30 width=26) (actual time=0.051..0.054 rows=1 loops=1)
         ->  Nested Loop  (cost=0.56..5.01 rows=1 width=24) (actual time=0.033..0.035 rows=1 loops=1)
               ->  Index Scan using post_pkey on post p  (cost=0.29..2.51 rows=1 width=14) (actual time=0.019..0.020 rows=1 loops=1)
                     Index Cond: (id = 1233)
               ->  Index Scan using community_aggregates_community_id_key on community_aggregates ca  (cost=0.27..2.49 rows=1 width=18) (actual time=0.012..0.012 rows=1 loops=1)
                     Index Cond: (community_id = p.community_id)
         ->  Index Scan using idx_comment_post on comment c  (cost=0.42..7.56 rows=30 width=10) (actual time=0.016..0.017 rows=1 loops=1)
               Index Cond: (post_id = 1233)
 Planning Time: 0.635 ms
 Execution Time: 0.188 ms
(11 rows)

lemmy=# update community_aggregates ca
    set comments = comments + 1 from post p
    where p.id = 1234
    and ca.community_id = p.community_id;
UPDATE 1
lemmy=# explain analyze update community_aggregates ca
    set comments = comments + 1 from post p
    where p.id = 1234
    and ca.community_id = p.community_id;
                                                                                 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on community_aggregates ca  (cost=0.56..5.01 rows=0 width=0) (actual time=0.107..0.109 rows=0 loops=1)
   ->  Nested Loop  (cost=0.56..5.01 rows=1 width=20) (actual time=0.045..0.048 rows=1 loops=1)
         ->  Index Scan using post_pkey on post p  (cost=0.29..2.51 rows=1 width=10) (actual time=0.020..0.022 rows=1 loops=1)
               Index Cond: (id = 1234)
         ->  Index Scan using community_aggregates_community_id_key on community_aggregates ca  (cost=0.27..2.49 rows=1 width=18) (actual time=0.020..0.020 rows=1 loops=1)
               Index Cond: (community_id = p.community_id)
 Planning Time: 0.545 ms
 Execution Time: 0.176 ms
(8 rows)