explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jsxs : Optimization for: This is a test; plan #pwpm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 45,574.769 ↑ 1.2 4 1

Limit (cost=5,908.27..5,908.29 rows=5 width=1,165) (actual time=45,574.768..45,574.769 rows=4 loops=1)

2. 0.019 45,574.767 ↑ 1.2 4 1

Sort (cost=5,908.27..5,908.29 rows=5 width=1,165) (actual time=45,574.767..45,574.767 rows=4 loops=1)

  • Sort Key: posts.date_created DESC
  • Sort Method: quicksort Memory: 61kB
3. 0.344 45,574.748 ↑ 1.2 4 1

Hash Join (cost=5,842.05..5,908.22 rows=5 width=1,165) (actual time=45,574.379..45,574.748 rows=4 loops=1)

  • Hash Cond: (posts_1.id = posts_9.id)
4. 0.010 45,392.202 ↑ 1.2 4 1

Nested Loop Left Join (cost=4,819.14..4,884.74 rows=5 width=15,437) (actual time=45,392.067..45,392.202 rows=4 loops=1)

5. 0.009 45,391.964 ↑ 1.2 4 1

Nested Loop Left Join (cost=4,815.32..4,835.34 rows=5 width=15,405) (actual time=45,391.930..45,391.964 rows=4 loops=1)

6. 0.008 45,391.955 ↑ 1.2 4 1

Nested Loop Left Join (cost=4,815.18..4,828.51 rows=5 width=14,897) (actual time=45,391.925..45,391.955 rows=4 loops=1)

7. 0.010 45,391.947 ↑ 1.2 4 1

Nested Loop Left Join (cost=4,815.04..4,826.90 rows=5 width=11,315) (actual time=45,391.920..45,391.947 rows=4 loops=1)

8. 0.007 45,391.937 ↑ 1.2 4 1

Nested Loop Left Join (cost=4,814.90..4,820.06 rows=5 width=10,807) (actual time=45,391.914..45,391.937 rows=4 loops=1)

9. 0.012 45,391.926 ↑ 1.2 4 1

Nested Loop (cost=4,814.76..4,818.28 rows=5 width=7,225) (actual time=45,391.907..45,391.926 rows=4 loops=1)

10. 0.032 45,391.906 ↑ 1.2 4 1

Merge Join (cost=4,814.62..4,816.17 rows=5 width=7,201) (actual time=45,391.896..45,391.906 rows=4 loops=1)

  • Merge Cond: (posts_1.id = posts_5.id)
11. 0.030 45,391.200 ↑ 1.2 4 1

Sort (cost=4,746.96..4,746.97 rows=5 width=4,463) (actual time=45,391.199..45,391.200 rows=4 loops=1)

  • Sort Key: posts.id
  • Sort Method: quicksort Memory: 41kB
12. 0.020 45,391.170 ↑ 1.2 4 1

Hash Left Join (cost=1,342.22..4,746.90 rows=5 width=4,463) (actual time=11,346.612..45,391.170 rows=4 loops=1)

  • Hash Cond: (posts.id = posts_posted_attachments.id)
13. 0.749 45,379.644 ↑ 1.2 4 1

Nested Loop (cost=254.23..3,658.90 rows=5 width=4,431) (actual time=11,335.095..45,379.644 rows=4 loops=1)

  • Join Filter: (posts_1.id = posts_2.id)
  • Rows Removed by Join Filter: 1454
14. 0.007 0.185 ↑ 2.0 2 1

Nested Loop Left Join (cost=1.87..69.42 rows=4 width=3,561) (actual time=0.107..0.185 rows=2 loops=1)

15. 0.007 0.176 ↑ 2.0 2 1

Nested Loop Left Join (cost=1.73..65.93 rows=4 width=3,053) (actual time=0.103..0.176 rows=2 loops=1)

16. 0.008 0.167 ↑ 2.0 2 1

Nested Loop (cost=1.58..64.53 rows=4 width=2,323) (actual time=0.100..0.167 rows=2 loops=1)

17. 0.010 0.153 ↑ 2.0 2 1

Nested Loop Left Join (cost=1.44..62.84 rows=4 width=2,299) (actual time=0.093..0.153 rows=2 loops=1)

18. 0.009 0.139 ↑ 2.0 2 1

Nested Loop (cost=1.16..57.83 rows=4 width=1,061) (actual time=0.087..0.139 rows=2 loops=1)

19. 0.007 0.124 ↑ 2.0 2 1

Nested Loop Left Join (cost=1.01..56.14 rows=4 width=1,037) (actual time=0.082..0.124 rows=2 loops=1)

20. 0.011 0.107 ↑ 2.0 2 1

Nested Loop Left Join (cost=0.87..55.22 rows=4 width=1,033) (actual time=0.074..0.107 rows=2 loops=1)

21. 0.006 0.084 ↑ 2.0 2 1

Nested Loop Left Join (cost=0.73..54.09 rows=4 width=872) (actual time=0.067..0.084 rows=2 loops=1)

22. 0.004 0.043 ↑ 4.0 1 1

Nested Loop Left Join (cost=0.58..52.19 rows=4 width=815) (actual time=0.037..0.043 rows=1 loops=1)

23. 0.003 0.031 ↑ 4.0 1 1

Nested Loop Left Join (cost=0.44..49.80 rows=4 width=701) (actual time=0.029..0.031 rows=1 loops=1)

24. 0.004 0.023 ↑ 4.0 1 1

Nested Loop (cost=0.29..48.83 rows=4 width=701) (actual time=0.021..0.023 rows=1 loops=1)

25. 0.017 0.017 ↑ 4.0 1 1

Index Scan using posts_pkey on posts (cost=0.15..24.13 rows=4 width=685) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: (id = ANY ('{167,167,167,167}'::integer[]))
  • Filter: ((date_archived IS NULL) AND (client_id = '1'::bigint))
26. 0.002 0.002 ↑ 1.0 1 1

Index Scan using posts_pkey on posts posts_1 (cost=0.15..6.17 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (id = posts.id)
27. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using users_pkey on users (cost=0.14..0.24 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (id = posts_1.created_by_user_id)
  • Heap Fetches: 0
28. 0.008 0.008 ↑ 1.0 1 1

Index Scan using users_profile_user_id_idx on users_profile (cost=0.14..0.59 rows=1 width=122) (actual time=0.006..0.008 rows=1 loops=1)

  • Index Cond: (user_id = users.id)
29. 0.035 0.035 ↓ 2.0 2 1

Index Scan using unique_user_client_memberships on users_client_memberships (cost=0.14..0.47 rows=1 width=65) (actual time=0.027..0.035 rows=2 loops=1)

  • Index Cond: (user_id = users.id)
30. 0.012 0.012 ↑ 1.0 1 2

Index Scan using users_media_pkey on users_media user_avatar_media (cost=0.14..0.28 rows=1 width=161) (actual time=0.006..0.006 rows=1 loops=2)

  • Index Cond: (users_profile.avatar_media_id = id)
31. 0.010 0.010 ↑ 1.0 1 2

Index Scan using roles_pkey on roles user_roles (cost=0.14..0.23 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (users_client_memberships.role_id = id)
32. 0.006 0.006 ↑ 1.0 1 2

Index Scan using posts_pkey on posts posts_4 (cost=0.15..0.42 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (id = posts_1.id)
33. 0.002 0.004 ↓ 0.0 0 2

Nested Loop Left Join (cost=0.28..1.24 rows=1 width=1,246) (actual time=0.002..0.002 rows=0 loops=2)

34. 0.002 0.002 ↓ 0.0 0 2

Index Scan using classes_pkey on classes posted_as_classes (cost=0.14..0.35 rows=1 width=738) (actual time=0.001..0.001 rows=0 loops=2)

  • Index Cond: (id = posts_4.posted_as_class_id)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using clients_colors_pkey on clients_colors posted_as_classes_colors (cost=0.14..0.86 rows=1 width=524) (never executed)

  • Index Cond: (id = posted_as_classes.client_color_id)
36. 0.006 0.006 ↑ 1.0 1 2

Index Scan using posts_pkey on posts posts_3 (cost=0.15..0.42 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (id = posts_1.id)
37. 0.002 0.002 ↓ 0.0 0 2

Index Scan using classes_pkey on classes posted_to_classes (cost=0.14..0.35 rows=1 width=738) (actual time=0.001..0.001 rows=0 loops=2)

  • Index Cond: (id = posts_4.posted_to_class_id)
38. 0.002 0.002 ↓ 0.0 0 2

Index Scan using clients_colors_pkey on clients_colors posted_to_classes_colors (cost=0.14..0.86 rows=1 width=524) (actual time=0.001..0.001 rows=0 loops=2)

  • Index Cond: (id = posted_to_classes.client_color_id)
39. 11.295 45,378.710 ↓ 2.1 729 2

Materialize (cost=252.36..3,569.30 rows=351 width=870) (actual time=47.530..22,689.355 rows=729 loops=2)

40. 53.745 45,367.415 ↓ 2.1 729 1

Nested Loop Left Join (cost=252.36..3,567.55 rows=351 width=870) (actual time=95.053..45,367.415 rows=729 loops=1)

  • Join Filter: (u.id = posted_as_users.id)
  • Rows Removed by Join Filter: 143613
41. 1.489 30.377 ↓ 2.1 729 1

Hash Left Join (cost=127.75..163.45 rows=351 width=750) (actual time=2.301..30.377 rows=729 loops=1)

  • Hash Cond: (posted_to_memberships.role_id = posted_to_users_role.id)
42. 1.708 28.870 ↓ 2.1 729 1

Hash Left Join (cost=125.39..159.92 rows=351 width=723) (actual time=2.273..28.870 rows=729 loops=1)

  • Hash Cond: (posted_to_users.id = posted_to_memberships.user_id)
43. 0.976 27.054 ↓ 1.4 466 1

Hash Left Join (cost=107.48..136.50 rows=322 width=666) (actual time=2.151..27.054 rows=466 loops=1)

  • Hash Cond: (posted_to_users_profile.avatar_media_id = posted_to_users_media.id)
44. 21.681 24.968 ↓ 1.4 466 1

Hash Left Join (cost=101.14..129.31 rows=322 width=505) (actual time=1.029..24.968 rows=466 loops=1)

  • Hash Cond: (posted_as_users_profile.avatar_media_id = posted_as_users_media.id)
45. 1.180 3.197 ↓ 1.4 466 1

Hash Left Join (cost=94.80..122.12 rows=322 width=344) (actual time=0.931..3.197 rows=466 loops=1)

  • Hash Cond: (posts_2.posted_as_user_id = posted_as_users.id)
46. 0.794 1.405 ↓ 1.0 301 1

Hash Left Join (cost=35.43..58.42 rows=295 width=146) (actual time=0.307..1.405 rows=301 loops=1)

  • Hash Cond: (posts_2.posted_to_user_id = posted_to_users.id)
47. 0.319 0.319 ↓ 1.0 301 1

Seq Scan on posts posts_2 (cost=0.00..19.95 rows=295 width=32) (actual time=0.006..0.319 rows=301 loops=1)

48. 0.066 0.292 ↑ 1.0 198 1

Hash (cost=32.95..32.95 rows=198 width=122) (actual time=0.292..0.292 rows=198 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
49. 0.089 0.226 ↑ 1.0 198 1

Hash Right Join (cost=11.46..32.95 rows=198 width=122) (actual time=0.101..0.226 rows=198 loops=1)

  • Hash Cond: (posted_to_users_profile.user_id = posted_to_users.id)
50. 0.047 0.047 ↑ 1.0 197 1

Seq Scan on users_profile posted_to_users_profile (cost=0.00..20.97 rows=197 width=122) (actual time=0.005..0.047 rows=197 loops=1)

51. 0.041 0.090 ↑ 1.0 198 1

Hash (cost=8.98..8.98 rows=198 width=8) (actual time=0.090..0.090 rows=198 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
52. 0.049 0.049 ↑ 1.0 198 1

Seq Scan on users posted_to_users (cost=0.00..8.98 rows=198 width=8) (actual time=0.008..0.049 rows=198 loops=1)

53. 0.100 0.612 ↑ 1.0 215 1

Hash (cost=56.67..56.67 rows=216 width=206) (actual time=0.612..0.612 rows=215 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
54. 0.108 0.512 ↑ 1.0 215 1

Hash Right Join (cost=37.79..56.67 rows=216 width=206) (actual time=0.283..0.512 rows=215 loops=1)

  • Hash Cond: (posted_as_memberships.user_id = posted_as_users.id)
55. 0.101 0.159 ↑ 1.0 215 1

Hash Left Join (cost=2.36..18.27 rows=218 width=92) (actual time=0.029..0.159 rows=215 loops=1)

  • Hash Cond: (posted_as_memberships.role_id = posted_as_users_role.id)
56. 0.041 0.041 ↑ 1.0 215 1

Seq Scan on users_client_memberships posted_as_memberships (cost=0.00..15.18 rows=218 width=65) (actual time=0.006..0.041 rows=215 loops=1)

57. 0.004 0.017 ↑ 2.0 8 1

Hash (cost=2.16..2.16 rows=16 width=43) (actual time=0.016..0.017 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.013 0.013 ↑ 2.0 8 1

Seq Scan on roles posted_as_users_role (cost=0.00..2.16 rows=16 width=43) (actual time=0.012..0.013 rows=8 loops=1)

59. 0.060 0.245 ↑ 1.0 198 1

Hash (cost=32.95..32.95 rows=198 width=122) (actual time=0.245..0.245 rows=198 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
60. 0.078 0.185 ↑ 1.0 198 1

Hash Right Join (cost=11.46..32.95 rows=198 width=122) (actual time=0.084..0.185 rows=198 loops=1)

  • Hash Cond: (posted_as_users_profile.user_id = posted_as_users.id)
61. 0.030 0.030 ↑ 1.0 197 1

Seq Scan on users_profile posted_as_users_profile (cost=0.00..20.97 rows=197 width=122) (actual time=0.002..0.030 rows=197 loops=1)

62. 0.038 0.077 ↑ 1.0 198 1

Hash (cost=8.98..8.98 rows=198 width=8) (actual time=0.077..0.077 rows=198 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
63. 0.039 0.039 ↑ 1.0 198 1

Seq Scan on users posted_as_users (cost=0.00..8.98 rows=198 width=8) (actual time=0.004..0.039 rows=198 loops=1)

64. 0.051 0.090 ↓ 1.0 107 1

Hash (cost=5.04..5.04 rows=104 width=161) (actual time=0.090..0.090 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
65. 0.039 0.039 ↓ 1.0 107 1

Seq Scan on users_media posted_as_users_media (cost=0.00..5.04 rows=104 width=161) (actual time=0.007..0.039 rows=107 loops=1)

66. 1.074 1.110 ↓ 1.0 107 1

Hash (cost=5.04..5.04 rows=104 width=161) (actual time=1.110..1.110 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
67. 0.036 0.036 ↓ 1.0 107 1

Seq Scan on users_media posted_to_users_media (cost=0.00..5.04 rows=104 width=161) (actual time=0.003..0.036 rows=107 loops=1)

68. 0.049 0.108 ↑ 1.0 215 1

Hash (cost=15.18..15.18 rows=218 width=65) (actual time=0.108..0.108 rows=215 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
69. 0.059 0.059 ↑ 1.0 215 1

Seq Scan on users_client_memberships posted_to_memberships (cost=0.00..15.18 rows=218 width=65) (actual time=0.007..0.059 rows=215 loops=1)

70. 0.005 0.018 ↑ 2.0 8 1

Hash (cost=2.16..2.16 rows=16 width=43) (actual time=0.018..0.018 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 0.013 0.013 ↑ 2.0 8 1

Seq Scan on roles posted_to_users_role (cost=0.00..2.16 rows=16 width=43) (actual time=0.012..0.013 rows=8 loops=1)

72. 174.231 45,283.293 ↑ 1.0 198 729

Hash Left Join (cost=124.62..131.49 rows=198 width=136) (actual time=58.426..62.117 rows=198 loops=729)

  • Hash Cond: (u.id = joined_group_roles.user_id)
73. 96.957 38,651.580 ↑ 1.0 198 729

Hash Left Join (cost=114.20..119.77 rows=198 width=104) (actual time=49.560..53.020 rows=198 loops=729)

  • Hash Cond: (u.id = joined_community_roles.user_id)
74. 231.922 37,956.114 ↑ 1.0 198 729

Hash Left Join (cost=87.55..92.36 rows=198 width=72) (actual time=48.718..52.066 rows=198 loops=729)

  • Hash Cond: (u.id = joined_user_roles.user_id)
75. 163.210 37,722.105 ↑ 1.0 198 729

Hash Right Join (cost=60.64..64.93 rows=198 width=40) (actual time=48.709..51.745 rows=198 loops=729)

  • Hash Cond: (cm.user_id = u.id)
76. 36,514.881 37,558.809 ↑ 1.1 155 729

HashAggregate (cost=49.19..51.31 rows=170 width=48) (actual time=48.703..51.521 rows=155 loops=729)

  • Group Key: cm.user_id, cm.client_id
77. 415.510 1,043.928 ↑ 1.1 629 729

Hash Left Join (cost=2.50..42.29 rows=690 width=188) (actual time=0.046..1.432 rows=629 loops=729)

  • Hash Cond: (cm.role_id = r.id)
78. 628.398 628.398 ↑ 1.1 629 729

Seq Scan on classes_memberships cm (cost=0.00..37.64 rows=690 width=150) (actual time=0.040..0.862 rows=629 loops=729)

  • Filter: ((date_archived IS NULL) AND (client_id = posts_2.client_id))
  • Rows Removed by Filter: 62
79. 0.005 0.020 ↑ 2.0 11 1

Hash (cost=2.22..2.22 rows=22 width=54) (actual time=0.019..0.020 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
80. 0.015 0.015 ↑ 2.0 11 1

Seq Scan on classes_roles r (cost=0.00..2.22 rows=22 width=54) (actual time=0.013..0.015 rows=11 loops=1)

81. 0.030 0.086 ↑ 1.0 198 1

Hash (cost=8.98..8.98 rows=198 width=8) (actual time=0.086..0.086 rows=198 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
82. 0.056 0.056 ↑ 1.0 198 1

Seq Scan on users u (cost=0.00..8.98 rows=198 width=8) (actual time=0.021..0.056 rows=198 loops=1)

83. 0.061 2.087 ↑ 1.0 198 1

Hash (cost=24.40..24.40 rows=200 width=40) (actual time=2.087..2.087 rows=198 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
84. 0.031 2.026 ↑ 1.0 198 1

Subquery Scan on joined_user_roles (cost=19.90..24.40 rows=200 width=40) (actual time=1.917..2.026 rows=198 loops=1)

85. 1.798 1.995 ↑ 1.0 198 1

HashAggregate (cost=19.90..22.40 rows=200 width=40) (actual time=1.916..1.995 rows=198 loops=1)

  • Group Key: ucm.user_id
86. 0.087 0.197 ↑ 1.0 215 1

Hash Left Join (cost=2.36..18.27 rows=218 width=96) (actual time=0.047..0.197 rows=215 loops=1)

  • Hash Cond: (ucm.role_id = r_1.id)
87. 0.079 0.079 ↑ 1.0 215 1

Seq Scan on users_client_memberships ucm (cost=0.00..15.18 rows=218 width=65) (actual time=0.011..0.079 rows=215 loops=1)

  • Filter: (date_archived IS NULL)
88. 0.017 0.031 ↑ 2.0 8 1

Hash (cost=2.16..2.16 rows=16 width=47) (actual time=0.031..0.031 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
89. 0.014 0.014 ↑ 2.0 8 1

Seq Scan on roles r_1 (cost=0.00..2.16 rows=16 width=47) (actual time=0.012..0.014 rows=8 loops=1)

90. 11.664 598.509 ↓ 5.0 5 729

Hash (cost=26.64..26.64 rows=1 width=40) (actual time=0.821..0.821 rows=5 loops=729)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
91. 2.916 586.845 ↓ 5.0 5 729

Subquery Scan on joined_community_roles (cost=26.61..26.64 rows=1 width=40) (actual time=0.183..0.805 rows=5 loops=729)

92. 498.636 583.929 ↓ 5.0 5 729

GroupAggregate (cost=26.61..26.63 rows=1 width=48) (actual time=0.180..0.801 rows=5 loops=729)

  • Group Key: cm_1.user_id, cm_1.client_id
93. 16.038 85.293 ↓ 9.0 9 729

Sort (cost=26.61..26.61 rows=1 width=1,608) (actual time=0.116..0.117 rows=9 loops=729)

  • Sort Key: cm_1.user_id
  • Sort Method: quicksort Memory: 26kB
94. 19.060 69.255 ↓ 9.0 9 729

Nested Loop Left Join (cost=0.14..26.60 rows=1 width=1,608) (actual time=0.065..0.095 rows=9 loops=729)

95. 3.645 3.645 ↓ 9.0 9 729

Seq Scan on communities_memberships cm_1 (cost=0.00..18.12 rows=1 width=64) (actual time=0.003..0.005 rows=9 loops=729)

  • Filter: ((date_archived IS NULL) AND (client_id = posts_2.client_id))
  • Rows Removed by Filter: 1
96. 46.550 46.550 ↑ 1.0 1 6,650

Index Scan using communities_roles_pkey on communities_roles r_2 (cost=0.14..8.16 rows=1 width=1,560) (actual time=0.007..0.007 rows=1 loops=6,650)

  • Index Cond: (id = cm_1.role_id)
97. 52.488 6,457.482 ↑ 1.3 43 729

Hash (cost=9.71..9.71 rows=56 width=40) (actual time=8.858..8.858 rows=43 loops=729)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
98. 43.740 6,404.994 ↑ 1.3 43 729

Subquery Scan on joined_group_roles (cost=8.45..9.71 rows=56 width=40) (actual time=8.365..8.786 rows=43 loops=729)

99. 6,111.207 6,361.254 ↑ 1.3 43 729

HashAggregate (cost=8.45..9.15 rows=56 width=48) (actual time=8.339..8.726 rows=43 loops=729)

  • Group Key: gm.user_id, gm.client_id
100. 126.107 250.047 ↓ 1.4 150 729

Hash Left Join (cost=1.09..7.37 rows=108 width=71) (actual time=0.040..0.343 rows=150 loops=729)

  • Hash Cond: (gm.role_id = r_3.id)
101. 123.930 123.930 ↓ 1.4 150 729

Seq Scan on groups_memberships gm (cost=0.00..5.70 rows=108 width=37) (actual time=0.030..0.170 rows=150 loops=729)

  • Filter: ((date_archived IS NULL) AND (client_id = posts_2.client_id))
  • Rows Removed by Filter: 20
102. 0.003 0.010 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=50) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
103. 0.007 0.007 ↑ 1.0 4 1

Seq Scan on groups_roles r_3 (cost=0.00..1.04 rows=4 width=50) (actual time=0.006..0.007 rows=4 loops=1)

104. 0.031 11.506 ↑ 4.6 64 1

Hash (cost=1,084.30..1,084.30 rows=295 width=40) (actual time=11.506..11.506 rows=64 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
105. 0.011 11.475 ↑ 4.6 64 1

Subquery Scan on posts_posted_attachments (cost=1,077.66..1,084.30 rows=295 width=40) (actual time=11.325..11.475 rows=64 loops=1)

106. 10.771 11.464 ↑ 4.6 64 1

HashAggregate (cost=1,077.66..1,081.35 rows=295 width=40) (actual time=11.324..11.464 rows=64 loops=1)

  • Group Key: posts_8.id
107. 0.106 0.693 ↑ 343.0 86 1

Hash Left Join (cost=6.34..782.66 rows=29,500 width=271) (actual time=0.167..0.693 rows=86 loops=1)

  • Hash Cond: (((joined_attachments.elem ->> 'post_media_id'::text))::integer = posts_media.id)
108. 0.065 0.450 ↑ 343.0 86 1

Nested Loop (cost=0.00..683.70 rows=29,500 width=40) (actual time=0.021..0.450 rows=86 loops=1)

109. 0.084 0.084 ↓ 1.0 301 1

Seq Scan on posts posts_8 (cost=0.00..19.95 rows=295 width=99) (actual time=0.008..0.084 rows=301 loops=1)

110. 0.301 0.301 ↓ 0.0 0 301

Function Scan on jsonb_array_elements joined_attachments (cost=0.00..1.25 rows=100 width=32) (actual time=0.001..0.001 rows=0 loops=301)

  • Filter: ((elem ->> 'post_media_id'::text) IS NOT NULL)
111. 0.035 0.137 ↓ 1.1 116 1

Hash (cost=5.04..5.04 rows=104 width=239) (actual time=0.137..0.137 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
112. 0.102 0.102 ↓ 1.1 116 1

Seq Scan on posts_media (cost=0.00..5.04 rows=104 width=239) (actual time=0.023..0.102 rows=116 loops=1)

113. 0.123 0.674 ↑ 2.6 113 1

Sort (cost=67.66..68.40 rows=295 width=2,738) (actual time=0.665..0.674 rows=113 loops=1)

  • Sort Key: posts_5.id
  • Sort Method: quicksort Memory: 64kB
114. 0.081 0.551 ↓ 1.0 301 1

Hash Left Join (cost=32.36..55.56 rows=295 width=2,738) (actual time=0.159..0.551 rows=301 loops=1)

  • Hash Cond: (posted_to_groups.client_color_id = posted_to_groups_colors.id)
115. 0.086 0.458 ↓ 1.0 301 1

Hash Left Join (cost=21.24..43.59 rows=295 width=2,230) (actual time=0.141..0.458 rows=301 loops=1)

  • Hash Cond: (posts_5.posted_to_group_id = posted_to_groups.id)
116. 0.069 0.330 ↓ 1.0 301 1

Hash Left Join (cost=16.18..37.75 rows=295 width=1,381) (actual time=0.093..0.330 rows=301 loops=1)

  • Hash Cond: (posted_as_groups.client_color_id = posted_as_groups_colors.id)
117. 0.117 0.248 ↓ 1.0 301 1

Hash Left Join (cost=5.06..25.78 rows=295 width=873) (actual time=0.074..0.248 rows=301 loops=1)

  • Hash Cond: (posts_5.posted_as_group_id = posted_as_groups.id)
118. 0.077 0.077 ↓ 1.0 301 1

Seq Scan on posts posts_5 (cost=0.00..19.95 rows=295 width=24) (actual time=0.009..0.077 rows=301 loops=1)

119. 0.024 0.054 ↓ 1.1 53 1

Hash (cost=4.47..4.47 rows=47 width=857) (actual time=0.054..0.054 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
120. 0.030 0.030 ↓ 1.1 53 1

Seq Scan on groups posted_as_groups (cost=0.00..4.47 rows=47 width=857) (actual time=0.006..0.030 rows=53 loops=1)

121. 0.005 0.013 ↑ 2.8 18 1

Hash (cost=10.50..10.50 rows=50 width=524) (actual time=0.013..0.013 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
122. 0.008 0.008 ↑ 2.8 18 1

Seq Scan on clients_colors posted_as_groups_colors (cost=0.00..10.50 rows=50 width=524) (actual time=0.004..0.008 rows=18 loops=1)

123. 0.022 0.042 ↓ 1.1 53 1

Hash (cost=4.47..4.47 rows=47 width=857) (actual time=0.041..0.042 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
124. 0.020 0.020 ↓ 1.1 53 1

Seq Scan on groups posted_to_groups (cost=0.00..4.47 rows=47 width=857) (actual time=0.002..0.020 rows=53 loops=1)

125. 0.006 0.012 ↑ 2.8 18 1

Hash (cost=10.50..10.50 rows=50 width=524) (actual time=0.012..0.012 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
126. 0.006 0.006 ↑ 2.8 18 1

Seq Scan on clients_colors posted_to_groups_colors (cost=0.00..10.50 rows=50 width=524) (actual time=0.002..0.006 rows=18 loops=1)

127. 0.008 0.008 ↑ 1.0 1 4

Index Scan using posts_pkey on posts posts_6 (cost=0.15..0.42 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=4)

  • Index Cond: (id = posts_1.id)
128. 0.004 0.004 ↓ 0.0 0 4

Index Scan using communities_pkey on communities posted_as_communities (cost=0.14..0.32 rows=1 width=3,590) (actual time=0.001..0.001 rows=0 loops=4)

  • Index Cond: (id = posts_6.posted_as_community_id)
129. 0.000 0.000 ↓ 0.0 0 4

Index Scan using clients_colors_pkey on clients_colors posted_as_communities_colors (cost=0.14..1.36 rows=1 width=524) (actual time=0.000..0.000 rows=0 loops=4)

  • Index Cond: (id = posted_as_communities.client_color_id)
130. 0.000 0.000 ↓ 0.0 0 4

Index Scan using communities_pkey on communities posted_to_communities (cost=0.14..0.32 rows=1 width=3,590) (actual time=0.000..0.000 rows=0 loops=4)

  • Index Cond: (id = posts_6.posted_to_community_id)
131. 0.000 0.000 ↓ 0.0 0 4

Index Scan using clients_colors_pkey on clients_colors posted_to_communities_colors (cost=0.14..1.36 rows=1 width=524) (actual time=0.000..0.000 rows=0 loops=4)

  • Index Cond: (id = posted_to_communities.client_color_id)
132. 0.008 0.228 ↑ 1.0 1 4

Nested Loop Left Join (cost=3.82..9.87 rows=1 width=40) (actual time=0.056..0.057 rows=1 loops=4)

133. 0.008 0.008 ↑ 1.0 1 4

Index Scan using posts_pkey on posts posts_7 (cost=0.15..6.17 rows=1 width=36) (actual time=0.001..0.002 rows=1 loops=4)

  • Index Cond: (id = posts.id)
134. 0.112 0.212 ↑ 1.0 1 4

Aggregate (cost=3.67..3.68 rows=1 width=32) (actual time=0.053..0.053 rows=1 loops=4)

135. 0.018 0.100 ↑ 100.0 1 4

Hash Left Join (cost=1.86..3.42 rows=100 width=142) (actual time=0.025..0.025 rows=1 loops=4)

  • Hash Cond: (((joined_pins.elem ->> 'post_pin_id'::text))::integer = posts_pins.id)
136. 0.020 0.020 ↑ 100.0 1 4

Function Scan on jsonb_array_elements joined_pins (cost=0.00..1.25 rows=100 width=32) (actual time=0.005..0.005 rows=1 loops=4)

  • Filter: ((elem ->> 'post_pin_id'::text) IS NOT NULL)
137. 0.012 0.062 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=150) (actual time=0.062..0.062 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
138. 0.050 0.050 ↑ 1.0 38 1

Seq Scan on posts_pins (cost=0.00..1.38 rows=38 width=150) (actual time=0.027..0.050 rows=38 loops=1)

139. 0.270 182.202 ↑ 1.4 301 1

Hash (cost=1,017.47..1,017.47 rows=435 width=80) (actual time=182.202..182.202 rows=301 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 554kB
140. 0.133 181.932 ↑ 1.4 301 1

Hash Join (cost=1,009.31..1,017.47 rows=435 width=80) (actual time=178.003..181.932 rows=301 loops=1)

  • Hash Cond: (posts_9.id = posts_posted_likes.id)
141. 65.945 75.283 ↓ 1.0 301 1

HashAggregate (cost=506.42..510.84 rows=295 width=40) (actual time=71.481..75.283 rows=301 loops=1)

  • Group Key: posts_9.id
142. 0.125 9.338 ↑ 1.0 356 1

Hash Left Join (cost=79.38..501.97 rows=356 width=3,194) (actual time=1.100..9.338 rows=356 loops=1)

  • Hash Cond: (posts_comments.created_by_group_id = comment_created_by_group.id)
143. 0.162 9.128 ↑ 1.0 356 1

Hash Left Join (cost=74.33..495.97 rows=356 width=2,171) (actual time=1.009..9.128 rows=356 loops=1)

  • Hash Cond: (posts_comments.created_by_user_id = comment_created_by_user_client_memberships.user_id)
144. 0.159 8.696 ↓ 1.1 336 1

Hash Left Join (cost=53.33..469.58 rows=295 width=1,979) (actual time=0.731..8.696 rows=336 loops=1)

  • Hash Cond: (posts_comments.created_by_user_id = comment_created_by_user_profile.user_id)
145. 1.253 8.179 ↓ 1.1 336 1

Hash Left Join (cost=23.04..435.23 rows=295 width=1,387) (actual time=0.365..8.179 rows=336 loops=1)

  • Hash Cond: (posts_comments.created_by_class_id = comment_created_by_class.id)
146. 5.786 6.808 ↓ 1.1 336 1

Nested Loop Left Join (cost=9.51..420.93 rows=295 width=72) (actual time=0.241..6.808 rows=336 loops=1)

147. 0.119 0.119 ↓ 1.0 301 1

Seq Scan on posts posts_9 (cost=0.00..19.95 rows=295 width=76) (actual time=0.007..0.119 rows=301 loops=1)

148. 0.388 0.903 ↓ 0.0 0 301

Hash Join (cost=9.51..10.83 rows=1 width=64) (actual time=0.003..0.003 rows=0 loops=301)

  • Hash Cond: (((joined_comments.elem ->> 'post_comment_id'::text))::integer = posts_comments.id)
  • Join Filter: (posts_comments.post_id = posts_9.id)
149. 0.301 0.301 ↓ 0.0 0 301

Function Scan on jsonb_array_elements joined_comments (cost=0.00..1.00 rows=100 width=32) (actual time=0.001..0.001 rows=0 loops=301)

150. 0.066 0.214 ↓ 1.2 188 1

Hash (cost=7.56..7.56 rows=156 width=72) (actual time=0.214..0.214 rows=188 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
151. 0.148 0.148 ↓ 1.2 188 1

Seq Scan on posts_comments (cost=0.00..7.56 rows=156 width=72) (actual time=0.009..0.148 rows=188 loops=1)

152. 0.026 0.118 ↑ 1.0 68 1

Hash (cost=12.68..12.68 rows=68 width=1,331) (actual time=0.118..0.118 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
153. 0.092 0.092 ↑ 1.0 68 1

Seq Scan on classes comment_created_by_class (cost=0.00..12.68 rows=68 width=1,331) (actual time=0.023..0.092 rows=68 loops=1)

154. 0.065 0.358 ↑ 1.0 197 1

Hash (cost=27.83..27.83 rows=197 width=600) (actual time=0.358..0.358 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
155. 0.043 0.293 ↑ 1.0 197 1

Hash Left Join (cost=6.34..27.83 rows=197 width=600) (actual time=0.121..0.293 rows=197 loops=1)

  • Hash Cond: (comment_created_by_user_profile.avatar_media_id = comment_created_by_user_avatar_media.id)
156. 0.144 0.144 ↑ 1.0 197 1

Seq Scan on users_profile comment_created_by_user_profile (cost=0.00..20.97 rows=197 width=380) (actual time=0.011..0.144 rows=197 loops=1)

157. 0.034 0.106 ↓ 1.0 107 1

Hash (cost=5.04..5.04 rows=104 width=236) (actual time=0.105..0.106 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
158. 0.072 0.072 ↓ 1.0 107 1

Seq Scan on users_media comment_created_by_user_avatar_media (cost=0.00..5.04 rows=104 width=236) (actual time=0.014..0.072 rows=107 loops=1)

159. 0.075 0.270 ↑ 1.0 215 1

Hash (cost=18.27..18.27 rows=218 width=208) (actual time=0.270..0.270 rows=215 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
160. 0.061 0.195 ↑ 1.0 215 1

Hash Left Join (cost=2.36..18.27 rows=218 width=208) (actual time=0.039..0.195 rows=215 loops=1)

  • Hash Cond: (comment_created_by_user_client_memberships.role_id = comment_created_by_user_role.id)
161. 0.113 0.113 ↑ 1.0 215 1

Seq Scan on users_client_memberships comment_created_by_user_client_memberships (cost=0.00..15.18 rows=218 width=145) (actual time=0.012..0.113 rows=215 loops=1)

162. 0.003 0.021 ↑ 2.0 8 1

Hash (cost=2.16..2.16 rows=16 width=79) (actual time=0.021..0.021 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
163. 0.018 0.018 ↑ 2.0 8 1

Seq Scan on roles comment_created_by_user_role (cost=0.00..2.16 rows=16 width=79) (actual time=0.015..0.018 rows=8 loops=1)

164. 0.020 0.085 ↓ 1.1 53 1

Hash (cost=4.47..4.47 rows=47 width=1,039) (actual time=0.085..0.085 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
165. 0.065 0.065 ↓ 1.1 53 1

Seq Scan on groups comment_created_by_group (cost=0.00..4.47 rows=47 width=1,039) (actual time=0.013..0.065 rows=53 loops=1)

166. 0.178 106.516 ↓ 1.0 301 1

Hash (cost=499.20..499.20 rows=295 width=40) (actual time=106.516..106.516 rows=301 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 339kB
167. 0.053 106.338 ↓ 1.0 301 1

Subquery Scan on posts_posted_likes (cost=491.83..499.20 rows=295 width=40) (actual time=106.091..106.338 rows=301 loops=1)

168. 75.261 106.285 ↓ 1.0 301 1

HashAggregate (cost=491.83..496.25 rows=295 width=40) (actual time=106.090..106.285 rows=301 loops=1)

  • Group Key: posts_10.id
169. 0.157 31.024 ↓ 1.4 448 1

Hash Left Join (cost=69.04..487.90 rows=314 width=3,194) (actual time=1.073..31.024 rows=448 loops=1)

  • Hash Cond: (posts_likes.created_by_group_id = like_created_by_group.id)
170. 0.165 30.525 ↓ 1.4 448 1

Hash Left Join (cost=63.99..482.02 rows=314 width=2,171) (actual time=0.727..30.525 rows=448 loops=1)

  • Hash Cond: (like_created_by_user_client_memberships.role_id = like_created_by_user_role.id)
171. 13.327 30.343 ↓ 1.4 448 1

Hash Left Join (cost=61.63..478.62 rows=314 width=2,108) (actual time=0.704..30.343 rows=448 loops=1)

  • Hash Cond: (posts_likes.created_by_user_id = like_created_by_user_client_memberships.user_id)
172. 0.113 16.846 ↓ 1.5 436 1

Hash Left Join (cost=43.72..455.73 rows=295 width=1,979) (actual time=0.527..16.846 rows=436 loops=1)

  • Hash Cond: (posts_likes.created_by_class_id = like_created_by_class.id)
173. 0.155 16.607 ↓ 1.5 436 1

Hash Left Join (cost=30.19..441.42 rows=295 width=664) (actual time=0.394..16.607 rows=436 loops=1)

  • Hash Cond: (like_created_by_user_profile.avatar_media_id = like_created_by_user_avatar_media.id)
174. 0.205 16.347 ↓ 1.5 436 1

Hash Left Join (cost=23.85..434.31 rows=295 width=444) (actual time=0.280..16.347 rows=436 loops=1)

  • Hash Cond: (posts_likes.created_by_user_id = like_created_by_user_profile.user_id)
175. 0.471 15.924 ↓ 1.5 436 1

Nested Loop Left Join (cost=0.42..406.82 rows=295 width=72) (actual time=0.057..15.924 rows=436 loops=1)

176. 0.102 0.102 ↓ 1.0 301 1

Seq Scan on posts posts_10 (cost=0.00..19.95 rows=295 width=117) (actual time=0.008..0.102 rows=301 loops=1)

177. 13.966 15.351 ↑ 1.0 1 301

Hash Join (cost=0.42..1.72 rows=1 width=64) (actual time=0.051..0.051 rows=1 loops=301)

  • Hash Cond: (((joined_likes.elem ->> 'post_like_id'::text))::integer = posts_likes.id)
178. 0.285 0.285 ↑ 50.0 2 95

Function Scan on jsonb_array_elements joined_likes (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.003 rows=2 loops=95)

179. 0.275 1.100 ↑ 4.0 1 275

Hash (cost=0.37..0.37 rows=4 width=72) (actual time=0.004..0.004 rows=1 loops=275)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
180. 0.825 0.825 ↑ 4.0 1 275

Index Scan using posts_likes_post_id_idx on posts_likes (cost=0.15..0.37 rows=4 width=72) (actual time=0.002..0.003 rows=1 loops=275)

  • Index Cond: (post_id = posts_10.id)
181. 0.072 0.218 ↑ 1.0 197 1

Hash (cost=20.97..20.97 rows=197 width=380) (actual time=0.218..0.218 rows=197 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
182. 0.146 0.146 ↑ 1.0 197 1

Seq Scan on users_profile like_created_by_user_profile (cost=0.00..20.97 rows=197 width=380) (actual time=0.013..0.146 rows=197 loops=1)

183. 0.030 0.105 ↓ 1.0 107 1

Hash (cost=5.04..5.04 rows=104 width=236) (actual time=0.105..0.105 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
184. 0.075 0.075 ↓ 1.0 107 1

Seq Scan on users_media like_created_by_user_avatar_media (cost=0.00..5.04 rows=104 width=236) (actual time=0.012..0.075 rows=107 loops=1)

185. 0.038 0.126 ↑ 1.0 68 1

Hash (cost=12.68..12.68 rows=68 width=1,331) (actual time=0.126..0.126 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
186. 0.088 0.088 ↑ 1.0 68 1

Seq Scan on classes like_created_by_class (cost=0.00..12.68 rows=68 width=1,331) (actual time=0.014..0.088 rows=68 loops=1)

187. 0.052 0.170 ↑ 1.0 215 1

Hash (cost=15.18..15.18 rows=218 width=145) (actual time=0.170..0.170 rows=215 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
188. 0.118 0.118 ↑ 1.0 215 1

Seq Scan on users_client_memberships like_created_by_user_client_memberships (cost=0.00..15.18 rows=218 width=145) (actual time=0.011..0.118 rows=215 loops=1)

189. 0.002 0.017 ↑ 2.0 8 1

Hash (cost=2.16..2.16 rows=16 width=79) (actual time=0.017..0.017 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
190. 0.015 0.015 ↑ 2.0 8 1

Seq Scan on roles like_created_by_user_role (cost=0.00..2.16 rows=16 width=79) (actual time=0.011..0.015 rows=8 loops=1)

191. 0.032 0.342 ↓ 1.1 53 1

Hash (cost=4.47..4.47 rows=47 width=1,039) (actual time=0.342..0.342 rows=53 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
192. 0.310 0.310 ↓ 1.1 53 1

Seq Scan on groups like_created_by_group (cost=0.00..4.47 rows=47 width=1,039) (actual time=0.009..0.310 rows=53 loops=1)

Planning time : 228.869 ms
Execution time : 45,576.365 ms