explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1mUC

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 6.063 ↓ 2.0 10 1

Limit (cost=578.52..578.54 rows=5 width=526) (actual time=6.042..6.063 rows=10 loops=1)

2.          

CTE all_notifications

3. 0.180 0.208 ↑ 1.0 160 1

Bitmap Heap Scan on notifications (cost=5.52..45.52 rows=160 width=109) (actual time=0.036..0.208 rows=160 loops=1)

  • Recheck Cond: (sent_to_account_id = 5607)
  • Heap Blocks: exact=22
4. 0.028 0.028 ↓ 1.0 161 1

Bitmap Index Scan on index_notifications_sent_to_account_id (cost=0.00..5.48 rows=160 width=0) (actual time=0.028..0.028 rows=161 loops=1)

  • Index Cond: (sent_to_account_id = 5607)
5. 0.036 6.046 ↓ 2.0 10 1

Sort (cost=533.01..533.02 rows=5 width=526) (actual time=6.039..6.046 rows=10 loops=1)

  • Sort Key: all_notifications.id DESC, all_notifications.notified_at_timestamp DESC
  • Sort Method: quicksort Memory: 27kB
6. 0.086 6.010 ↓ 2.0 10 1

Nested Loop Left Join (cost=11.19..532.95 rows=5 width=526) (actual time=1.262..6.010 rows=10 loops=1)

7. 0.207 5.744 ↓ 2.0 10 1

Nested Loop Left Join (cost=11.04..221.92 rows=5 width=518) (actual time=1.228..5.744 rows=10 loops=1)

  • Join Filter: (comment_mentions.id = comment_mention_notifications.comment_mention_id)
  • Rows Removed by Join Filter: 250
8. 0.123 5.307 ↓ 2.0 10 1

Nested Loop Left Join (cost=11.04..218.74 rows=5 width=510) (actual time=1.145..5.307 rows=10 loops=1)

  • Join Filter: (comment_mention_notifications.notification_id = all_notifications.id)
  • Rows Removed by Join Filter: 140
9. 0.032 5.044 ↓ 2.0 10 1

Hash Left Join (cost=11.04..216.42 rows=5 width=494) (actual time=1.096..5.044 rows=10 loops=1)

  • Hash Cond: (all_notifications.id = connection_notifications.notification_id)
10. 0.027 4.709 ↓ 2.0 10 1

Nested Loop Left Join (cost=4.95..210.26 rows=5 width=478) (actual time=0.783..4.709 rows=10 loops=1)

11. 0.026 4.642 ↓ 2.0 10 1

Nested Loop Left Join (cost=4.25..158.36 rows=5 width=474) (actual time=0.774..4.642 rows=10 loops=1)

12. 0.186 4.606 ↓ 2.0 10 1

Nested Loop Left Join (cost=3.96..118.12 rows=5 width=444) (actual time=0.771..4.606 rows=10 loops=1)

  • Join Filter: (custom_workout_followers.id = custom_workout_follower_notifications.custom_workout_follower_id)
  • Rows Removed by Join Filter: 170
13. 0.148 4.270 ↓ 2.0 10 1

Nested Loop Left Join (cost=3.96..115.81 rows=5 width=436) (actual time=0.712..4.270 rows=10 loops=1)

  • Join Filter: (custom_workout_follower_notifications.notification_id = all_notifications.id)
  • Rows Removed by Join Filter: 160
14. 0.032 3.982 ↓ 2.0 10 1

Nested Loop Left Join (cost=3.96..113.58 rows=5 width=420) (actual time=0.658..3.982 rows=10 loops=1)

15. 0.065 3.920 ↓ 2.0 10 1

Nested Loop Left Join (cost=2.68..52.76 rows=5 width=378) (actual time=0.644..3.920 rows=10 loops=1)

  • Join Filter: (custom_workout_notifications.notification_id = all_notifications.id)
  • Rows Removed by Join Filter: 50
16. 0.035 3.805 ↓ 2.0 10 1

Nested Loop Left Join (cost=2.68..51.32 rows=5 width=362) (actual time=0.623..3.805 rows=10 loops=1)

17. 0.123 3.760 ↓ 2.0 10 1

Nested Loop Left Join (cost=2.53..49.92 rows=5 width=362) (actual time=0.618..3.760 rows=10 loops=1)

  • Join Filter: (comment_reaction_notifications.notification_id = all_notifications.id)
  • Rows Removed by Join Filter: 90
18. 0.031 3.317 ↓ 2.0 10 1

Nested Loop Left Join (cost=1.00..39.36 rows=5 width=326) (actual time=0.344..3.317 rows=10 loops=1)

19. 0.026 3.256 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.72..20.16 rows=1 width=310) (actual time=0.336..3.256 rows=10 loops=1)

20. 0.603 3.220 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.58..19.77 rows=1 width=294) (actual time=0.334..3.220 rows=10 loops=1)

  • Join Filter: (feed_activity_comment_notifications.notification_id = all_notifications.id)
  • Rows Removed by Join Filter: 750
21. 0.026 2.047 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.58..17.12 rows=1 width=278) (actual time=0.216..2.047 rows=10 loops=1)

22. 0.023 2.011 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.43..16.92 rows=1 width=282) (actual time=0.212..2.011 rows=10 loops=1)

23. 0.762 1.978 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.28..16.47 rows=1 width=266) (actual time=0.209..1.978 rows=10 loops=1)

  • Join Filter: (feed_activity_reaction_notifications.notification_id = all_notifications.id)
  • Rows Removed by Join Filter: 940
24. 0.043 0.516 ↓ 10.0 10 1

Nested Loop Left Join (cost=0.28..13.51 rows=1 width=250) (actual time=0.061..0.516 rows=10 loops=1)

25. 0.433 0.433 ↓ 10.0 10 1

CTE Scan on all_notifications (cost=0.00..5.20 rows=1 width=109) (actual time=0.049..0.433 rows=10 loops=1)

  • Filter: ((created_at <= '2019-04-12 09:22:25'::timestamp without time zone) AND (device_type = ANY ('{all,android}'::notification_device_type[])) AND (notification_type <> ALL ('{connection_achievement_multiple,connection_achievement,connection_external_workout_multiple,connection_external_workout,connection_seven_workout_multiple,connection_seven_workout}'::notification_type[])))
  • Rows Removed by Filter: 150
26. 0.040 0.040 ↑ 1.0 1 10

Index Scan using index_user_info_account_id on user_info (cost=0.28..8.30 rows=1 width=141) (actual time=0.003..0.004 rows=1 loops=10)

  • Index Cond: (account_id = all_notifications.publisher_account_id)
27. 0.700 0.700 ↓ 1.1 94 10

Seq Scan on feed_activity_reaction_notifications (cost=0.00..1.87 rows=87 width=24) (actual time=0.003..0.070 rows=94 loops=10)

28. 0.010 0.010 ↓ 0.0 0 10

Index Scan using feed_activity_reactions_pkey on feed_activity_reactions (cost=0.15..0.44 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = feed_activity_reaction_notifications.feed_activity_reaction_id)
29. 0.010 0.010 ↓ 0.0 0 10

Index Scan using reactions_pkey on reactions fa_reactions (cost=0.14..0.19 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = feed_activity_reactions.reaction_id)
30. 0.570 0.570 ↓ 1.0 75 10

Seq Scan on feed_activity_comment_notifications (cost=0.00..1.73 rows=73 width=24) (actual time=0.003..0.057 rows=75 loops=10)

31. 0.010 0.010 ↓ 0.0 0 10

Index Scan using feed_activity_comments_pkey on feed_activity_comments (cost=0.14..0.38 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = feed_activity_comment_notifications.feed_activity_comment_id)
32. 0.030 0.030 ↓ 0.0 0 10

Index Scan using index_feed_activity_notifications_notification_id on feed_activity_notifications (cost=0.28..19.16 rows=5 width=24) (actual time=0.003..0.003 rows=0 loops=10)

  • Index Cond: (notification_id = all_notifications.id)
33. 0.075 0.320 ↓ 2.2 9 10

Materialize (cost=1.53..10.27 rows=4 width=44) (actual time=0.008..0.032 rows=9 loops=10)

34. 0.023 0.245 ↓ 2.2 9 1

Nested Loop Left Join (cost=1.53..10.25 rows=4 width=44) (actual time=0.065..0.245 rows=9 loops=1)

35. 0.029 0.195 ↓ 2.2 9 1

Nested Loop Left Join (cost=1.38..9.21 rows=4 width=36) (actual time=0.060..0.195 rows=9 loops=1)

36. 0.024 0.139 ↓ 2.2 9 1

Nested Loop Left Join (cost=1.24..5.56 rows=4 width=36) (actual time=0.054..0.139 rows=9 loops=1)

37. 0.045 0.088 ↓ 2.2 9 1

Hash Right Join (cost=1.09..2.50 rows=4 width=40) (actual time=0.048..0.088 rows=9 loops=1)

  • Hash Cond: (comment_reactions.id = comment_reaction_notifications.comment_reaction_id)
38. 0.024 0.024 ↓ 1.1 31 1

Seq Scan on comment_reactions (cost=0.00..1.27 rows=27 width=24) (actual time=0.004..0.024 rows=31 loops=1)

39. 0.007 0.019 ↓ 2.2 9 1

Hash (cost=1.04..1.04 rows=4 width=24) (actual time=0.019..0.019 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.012 0.012 ↓ 2.2 9 1

Seq Scan on comment_reaction_notifications (cost=0.00..1.04 rows=4 width=24) (actual time=0.003..0.012 rows=9 loops=1)

41. 0.027 0.027 ↑ 1.0 1 9

Index Scan using reactions_pkey on reactions c_reactions (cost=0.14..0.76 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=9)

  • Index Cond: (id = comment_reactions.reaction_id)
42. 0.027 0.027 ↑ 1.0 1 9

Index Only Scan using comments_pkey on comments comment_reaction_comments (cost=0.14..0.90 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=9)

  • Index Cond: (id = comment_reactions.comment_id)
  • Heap Fetches: 9
43. 0.027 0.027 ↑ 1.0 1 9

Index Scan using index_feed_activity_comments_comment_id on feed_activity_comments comment_reaction_activity_comments (cost=0.14..0.25 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=9)

  • Index Cond: (comment_id = comment_reaction_comments.id)
44. 0.010 0.010 ↓ 0.0 0 10

Index Only Scan using comments_pkey on comments (cost=0.14..0.27 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = feed_activity_comments.comment_id)
  • Heap Fetches: 0
45. 0.045 0.050 ↑ 1.0 5 10

Materialize (cost=0.00..1.07 rows=5 width=24) (actual time=0.001..0.005 rows=5 loops=10)

46. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on custom_workout_notifications (cost=0.00..1.05 rows=5 width=24) (actual time=0.002..0.005 rows=5 loops=1)

47. 0.020 0.030 ↓ 0.0 0 10

Nested Loop Left Join (cost=1.29..12.15 rows=1 width=50) (actual time=0.003..0.003 rows=0 loops=10)

48. 0.010 0.010 ↓ 0.0 0 10

Index Scan using custom_workouts_pkey on custom_workouts (cost=0.29..8.30 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = custom_workout_notifications.custom_workout_id)
49. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.00..3.84 rows=1 width=20) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..0.72 rows=1 width=16) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Only Scan using custom_workouts_pkey on custom_workouts cw (cost=0.29..0.37 rows=1 width=8) (never executed)

  • Index Cond: (id = custom_workouts.id)
  • Heap Fetches: 0
52. 0.000 0.000 ↓ 0.0 0

Index Scan using index_custom_workout_activities_custom_workout_id on custom_workout_activities (cost=0.29..0.34 rows=1 width=16) (never executed)

  • Index Cond: (custom_workout_id = cw.id)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using activities_pkey on activities (cost=0.42..3.11 rows=1 width=20) (never executed)

  • Index Cond: (id = custom_workout_activities.activity_id)
  • Filter: (activity_type = 'custom_workout'::activity_type)
54. 0.124 0.140 ↓ 1.1 16 10

Materialize (cost=0.00..1.21 rows=14 width=24) (actual time=0.001..0.014 rows=16 loops=10)

55. 0.016 0.016 ↓ 1.1 16 1

Seq Scan on custom_workout_follower_notifications (cost=0.00..1.14 rows=14 width=24) (actual time=0.003..0.016 rows=16 loops=1)

56. 0.134 0.150 ↓ 1.1 17 10

Materialize (cost=0.00..1.22 rows=15 width=16) (actual time=0.001..0.015 rows=17 loops=10)

57. 0.016 0.016 ↓ 1.1 17 1

Seq Scan on custom_workout_followers (cost=0.00..1.15 rows=15 width=16) (actual time=0.003..0.016 rows=17 loops=1)

58. 0.010 0.010 ↓ 0.0 0 10

Index Scan using custom_workouts_pkey on custom_workouts cw_follower_custom_workouts (cost=0.29..8.04 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = custom_workout_followers.custom_workout_id)
59. 0.020 0.040 ↓ 0.0 0 10

Nested Loop (cost=0.70..10.37 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=10)

60. 0.010 0.020 ↓ 0.0 0 10

Nested Loop (cost=0.27..1.91 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=10)

61. 0.010 0.010 ↓ 0.0 0 10

Index Only Scan using custom_workout_followers_pkey on custom_workout_followers custom_workout_followers_1 (cost=0.14..0.95 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (id = custom_workout_followers.id)
  • Heap Fetches: 0
62. 0.000 0.000 ↓ 0.0 0

Index Scan using index_custom_workout_follower_activities_custom_workout_followe on custom_workout_follower_activities (cost=0.14..0.95 rows=1 width=16) (never executed)

  • Index Cond: (custom_workout_follower_id = custom_workout_followers_1.id)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using activities_pkey on activities activities_1 (cost=0.42..8.45 rows=1 width=20) (never executed)

  • Index Cond: (id = custom_workout_follower_activities.activity_id)
  • Filter: (activity_type = 'custom_workout_new_follower'::activity_type)
64. 0.142 0.303 ↑ 1.0 177 1

Hash (cost=3.82..3.82 rows=182 width=24) (actual time=0.303..0.303 rows=177 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
65. 0.161 0.161 ↑ 1.0 177 1

Seq Scan on connection_notifications (cost=0.00..3.82 rows=182 width=24) (actual time=0.006..0.161 rows=177 loops=1)

66. 0.125 0.140 ↑ 1.1 14 10

Materialize (cost=0.00..1.22 rows=15 width=24) (actual time=0.001..0.014 rows=14 loops=10)

67. 0.015 0.015 ↑ 1.1 14 1

Seq Scan on comment_mention_notifications (cost=0.00..1.15 rows=15 width=24) (actual time=0.003..0.015 rows=14 loops=1)

68. 0.204 0.230 ↑ 1.0 25 10

Materialize (cost=0.00..1.38 rows=25 width=16) (actual time=0.001..0.023 rows=25 loops=10)

69. 0.026 0.026 ↑ 1.0 25 1

Seq Scan on comment_mentions (cost=0.00..1.25 rows=25 width=16) (actual time=0.004..0.026 rows=25 loops=1)

70. 0.010 0.010 ↓ 0.0 0 10

Index Scan using index_feed_activity_comments_comment_id on feed_activity_comments comment_mentions_feed_activity_comments (cost=0.14..0.80 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (comment_id = comment_mentions.comment_id)
71.          

SubPlan (forNested Loop Left Join)

72. 0.030 0.050 ↑ 1.0 1 10

Aggregate (cost=19.17..19.18 rows=1 width=0) (actual time=0.004..0.005 rows=1 loops=10)

73. 0.020 0.020 ↓ 0.0 0 10

Index Only Scan using index_feed_activity_notifications_notification_id on feed_activity_notifications feed_activity_notifications_1 (cost=0.28..19.16 rows=5 width=0) (actual time=0.002..0.002 rows=0 loops=10)

  • Index Cond: (notification_id = all_notifications.id)
  • Heap Fetches: 0
74. 0.020 0.030 ↓ 0.0 0 10

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=10)

75. 0.010 0.010 ↓ 0.0 0 10

Index Scan using index_activities_feed_activity_id on activities activities_2 (cost=0.42..8.44 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (feed_activity_id = feed_activity_notifications.feed_activity_id)
76. 0.020 0.020 ↓ 0.0 0 10

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=10)

77. 0.000 0.000 ↓ 0.0 0 10

Index Scan using index_activities_feed_activity_id on activities activities_3 (cost=0.42..8.44 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=10)

  • Index Cond: (feed_activity_id = feed_activity_reactions.feed_activity_id)
78. 0.020 0.020 ↓ 0.0 0 10

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=10)

79. 0.000 0.000 ↓ 0.0 0 10

Index Scan using index_activities_feed_activity_id on activities activities_4 (cost=0.42..8.44 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=10)

  • Index Cond: (feed_activity_id = feed_activity_comments.feed_activity_id)
80. 0.010 0.020 ↓ 0.0 0 10

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=10)

81. 0.010 0.010 ↓ 0.0 0 10

Index Scan using index_activities_feed_activity_id on activities activities_5 (cost=0.42..8.44 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (feed_activity_id = comment_reaction_activity_comments.feed_activity_id)
82. 0.020 0.030 ↓ 0.0 0 10

Limit (cost=0.42..8.44 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=10)

83. 0.010 0.010 ↓ 0.0 0 10

Index Scan using index_activities_feed_activity_id on activities activities_6 (cost=0.42..8.44 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: (feed_activity_id = comment_mentions_feed_activity_comments.feed_activity_id)