explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AYkS

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=636.54..636.55 rows=3 width=1,663) (actual rows= loops=)

2.          

CTE post_upvotes

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=16.27..16.29 rows=1 width=12) (actual rows= loops=)

  • Group Key: u_1.id
4. 0.000 0.000 ↓ 0.0

Sort (cost=16.27..16.27 rows=1 width=4) (actual rows= loops=)

  • Sort Key: u_1.id
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..16.25 rows=1 width=4) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..14.83 rows=1 width=12) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..10.53 rows=1 width=16) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Seq Scan on upvotes up (cost=0.00..2.35 rows=1 width=8) (actual rows= loops=)

  • Filter: ((created_at >= '2019-12-10 11:16:10.926791'::timestamp without time zone) AND (created_at <= '2020-01-10 11:16:10.926868'::timestamp without time zone) AND ((reactable_type)::text = 'Post'::text))
9. 0.000 0.000 ↓ 0.0

Index Scan using posts_pkey on posts p (cost=0.15..8.17 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = up.reactable_id)
  • Filter: (community_id = 9)
10. 0.000 0.000 ↓ 0.0

Index Only Scan using users_pkey on users u_1 (cost=0.28..4.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = p.user_id)
11. 0.000 0.000 ↓ 0.0

Seq Scan on communities com (cost=0.00..1.41 rows=1 width=8) (actual rows= loops=)

  • Filter: (id = 9)
12.          

CTE post_comments

13. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=24.56..24.58 rows=1 width=12) (actual rows= loops=)

  • Group Key: u_2.id
14. 0.000 0.000 ↓ 0.0

Sort (cost=24.56..24.57 rows=1 width=4) (actual rows= loops=)

  • Sort Key: u_2.id
15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..24.55 rows=1 width=4) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..23.13 rows=1 width=12) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..18.82 rows=1 width=16) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on comments c (cost=0.00..10.64 rows=1 width=8) (actual rows= loops=)

  • Filter: ((parent_id IS NULL) AND (created_at >= '2019-12-10 11:16:10.926791'::timestamp without time zone) AND (created_at <= '2020-01-10 11:16:10.926868'::timestamp without time zone))
19. 0.000 0.000 ↓ 0.0

Index Scan using posts_pkey on posts p_1 (cost=0.15..8.17 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = c.post_id)
  • Filter: (community_id = 9)
20. 0.000 0.000 ↓ 0.0

Index Only Scan using users_pkey on users u_2 (cost=0.28..4.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = p_1.user_id)
21. 0.000 0.000 ↓ 0.0

Seq Scan on communities com_1 (cost=0.00..1.41 rows=1 width=8) (actual rows= loops=)

  • Filter: (id = 9)
22.          

CTE comment_upvotes

23. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=12.26..12.28 rows=1 width=16) (actual rows= loops=)

  • Group Key: c_1.user_id
24. 0.000 0.000 ↓ 0.0

Sort (cost=12.26..12.27 rows=1 width=8) (actual rows= loops=)

  • Sort Key: c_1.user_id
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..12.25 rows=1 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..10.83 rows=1 width=16) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..10.52 rows=1 width=16) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on upvotes up_1 (cost=0.00..2.35 rows=1 width=8) (actual rows= loops=)

  • Filter: ((created_at >= '2019-12-10 11:16:10.926791'::timestamp without time zone) AND (created_at <= '2020-01-10 11:16:10.926868'::timestamp without time zone) AND ((reactable_type)::text = 'Comment'::text))
29. 0.000 0.000 ↓ 0.0

Index Scan using comments_pkey on comments c_1 (cost=0.14..8.16 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = up_1.reactable_id)
30. 0.000 0.000 ↓ 0.0

Index Scan using posts_pkey on posts p_2 (cost=0.15..0.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = c_1.post_id)
  • Filter: (community_id = 9)
31. 0.000 0.000 ↓ 0.0

Seq Scan on communities com_2 (cost=0.00..1.41 rows=1 width=8) (actual rows= loops=)

  • Filter: (id = 9)
32.          

CTE comment_replies

33. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=28.43..28.45 rows=1 width=16) (actual rows= loops=)

  • Group Key: c_2.user_id
34. 0.000 0.000 ↓ 0.0

Sort (cost=28.43..28.43 rows=1 width=8) (actual rows= loops=)

  • Sort Key: c_2.user_id
35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..28.42 rows=1 width=8) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..27.00 rows=1 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..18.82 rows=1 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on comments cc (cost=0.00..10.64 rows=1 width=12) (actual rows= loops=)

  • Filter: ((created_at >= '2019-12-10 11:16:10.926791'::timestamp without time zone) AND (created_at <= '2020-01-10 11:16:10.926868'::timestamp without time zone))
39. 0.000 0.000 ↓ 0.0

Index Scan using comments_pkey on comments c_2 (cost=0.14..8.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = cc.parent_id)
40. 0.000 0.000 ↓ 0.0

Index Scan using posts_pkey on posts p_3 (cost=0.15..8.17 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = cc.post_id)
  • Filter: (community_id = 9)
41. 0.000 0.000 ↓ 0.0

Seq Scan on communities com_3 (cost=0.00..1.41 rows=1 width=8) (actual rows= loops=)

  • Filter: (id = 9)
42. 0.000 0.000 ↓ 0.0

Sort (cost=554.95..555.18 rows=95 width=1,663) (actual rows= loops=)

  • Sort Key: ((((COALESCE(pu.count, '0'::bigint) + COALESCE(pc.count, '0'::bigint)) + COALESCE(cu.count, '0'::bigint)) + COALESCE(cr.count, '0'::bigint))) DESC
43. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.30..553.72 rows=95 width=1,663) (actual rows= loops=)

  • Join Filter: (u.id = cr.user_id)
44. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.30..550.86 rows=95 width=1,679) (actual rows= loops=)

  • Join Filter: (u.id = cu.user_id)
45. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.30..548.71 rows=95 width=1,671) (actual rows= loops=)

  • Join Filter: (u.id = pc.user_id)
46. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.30..546.56 rows=95 width=1,663) (actual rows= loops=)

  • Join Filter: (u.id = pu.user_id)
47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5.30..544.42 rows=95 width=1,655) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on community_users cus (cost=5.02..55.20 rows=95 width=8) (actual rows= loops=)

  • Recheck Cond: (community_id = 9)
49. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_community_users_on_community_id_and_user_id (cost=0.00..4.99 rows=95 width=0) (actual rows= loops=)

  • Index Cond: (community_id = 9)
50. 0.000 0.000 ↓ 0.0

Index Scan using users_pkey on users u (cost=0.28..5.14 rows=1 width=1,655) (actual rows= loops=)

  • Index Cond: (id = cus.user_id)
51. 0.000 0.000 ↓ 0.0

CTE Scan on post_upvotes pu (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

CTE Scan on post_comments pc (cost=0.00..0.02 rows=1 width=12) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

CTE Scan on comment_upvotes cu (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

CTE Scan on comment_replies cr (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)