explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6e7H

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.416 ↑ 4.0 1 1

Limit (cost=1,372.49..1,591.66 rows=4 width=487) (actual time=0.416..0.416 rows=1 loops=1)

2.          

CTE all_notifications

3. 0.015 0.028 ↑ 13.1 20 1

Bitmap Heap Scan on notifications (cost=6.45..992.52 rows=261 width=109) (actual time=0.016..0.028 rows=20 loops=1)

  • Recheck Cond: (sent_to_account_id = 583323)
  • Heap Blocks: exact=17
4. 0.013 0.013 ↑ 13.1 20 1

Bitmap Index Scan on index_notifications_sent_to_account_id (cost=0.00..6.39 rows=261 width=0) (actual time=0.013..0.013 rows=20 loops=1)

  • Index Cond: (sent_to_account_id = 583323)
5. 0.006 0.415 ↑ 4.0 1 1

Result (cost=379.97..599.14 rows=4 width=487) (actual time=0.415..0.415 rows=1 loops=1)

6. 0.018 0.373 ↑ 4.0 1 1

Sort (cost=379.97..379.98 rows=4 width=459) (actual time=0.373..0.373 rows=1 loops=1)

  • Sort Key: all_notifications.id DESC, all_notifications.notified_at_timestamp DESC
  • Sort Method: quicksort Memory: 25kB
7. 0.003 0.355 ↑ 4.0 1 1

Nested Loop Left Join (cost=70.66..379.93 rows=4 width=459) (actual time=0.351..0.355 rows=1 loops=1)

8. 0.002 0.348 ↑ 4.0 1 1

Nested Loop Left Join (cost=70.37..369.56 rows=4 width=451) (actual time=0.345..0.348 rows=1 loops=1)

9. 0.002 0.342 ↑ 4.0 1 1

Nested Loop Left Join (cost=70.09..361.82 rows=4 width=443) (actual time=0.339..0.342 rows=1 loops=1)

10. 0.002 0.334 ↑ 4.0 1 1

Nested Loop Left Join (cost=69.81..328.60 rows=4 width=427) (actual time=0.331..0.334 rows=1 loops=1)

11. 0.002 0.326 ↑ 4.0 1 1

Nested Loop Left Join (cost=69.53..295.34 rows=4 width=411) (actual time=0.323..0.326 rows=1 loops=1)

12. 0.002 0.318 ↑ 4.0 1 1

Nested Loop Left Join (cost=68.54..246.98 rows=4 width=407) (actual time=0.315..0.318 rows=1 loops=1)

13. 0.001 0.312 ↑ 4.0 1 1

Nested Loop Left Join (cost=68.12..223.32 rows=4 width=397) (actual time=0.309..0.312 rows=1 loops=1)

14. 0.014 0.307 ↑ 4.0 1 1

Hash Left Join (cost=67.84..216.32 rows=4 width=389) (actual time=0.304..0.307 rows=1 loops=1)

  • Hash Cond: (all_notifications.id = custom_workout_follower_notifications.notification_id)
15. 0.002 0.219 ↑ 4.0 1 1

Nested Loop Left Join (cost=56.38..204.80 rows=4 width=373) (actual time=0.217..0.219 rows=1 loops=1)

16. 0.002 0.211 ↑ 4.0 1 1

Nested Loop Left Join (cost=55.11..165.76 rows=4 width=361) (actual time=0.209..0.211 rows=1 loops=1)

17. 0.002 0.205 ↑ 4.0 1 1

Nested Loop Left Join (cost=54.69..136.49 rows=4 width=351) (actual time=0.203..0.205 rows=1 loops=1)

18. 0.001 0.191 ↑ 4.0 1 1

Nested Loop Left Join (cost=54.39..128.25 rows=4 width=343) (actual time=0.189..0.191 rows=1 loops=1)

19. 0.003 0.184 ↑ 4.0 1 1

Nested Loop Left Join (cost=54.10..119.75 rows=4 width=343) (actual time=0.182..0.184 rows=1 loops=1)

20. 0.002 0.177 ↑ 4.0 1 1

Nested Loop Left Join (cost=53.68..108.30 rows=4 width=347) (actual time=0.176..0.177 rows=1 loops=1)

21. 0.001 0.171 ↑ 4.0 1 1

Nested Loop Left Join (cost=53.39..100.17 rows=4 width=331) (actual time=0.170..0.171 rows=1 loops=1)

22. 0.002 0.163 ↑ 4.0 1 1

Nested Loop Left Join (cost=53.10..66.92 rows=4 width=315) (actual time=0.162..0.163 rows=1 loops=1)

23. 0.033 0.151 ↑ 4.0 1 1

Hash Right Join (cost=52.81..58.63 rows=4 width=315) (actual time=0.150..0.151 rows=1 loops=1)

  • Hash Cond: (custom_workout_notifications.notification_id = all_notifications.id)
24. 0.023 0.023 ↑ 1.0 275 1

Seq Scan on custom_workout_notifications (cost=0.00..4.75 rows=275 width=24) (actual time=0.006..0.023 rows=275 loops=1)

25. 0.004 0.095 ↑ 4.0 1 1

Hash (cost=52.76..52.76 rows=4 width=299) (actual time=0.095..0.095 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.002 0.091 ↑ 4.0 1 1

Nested Loop Left Join (cost=2.71..52.76 rows=4 width=299) (actual time=0.081..0.091 rows=1 loops=1)

27. 0.002 0.082 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.28..40.24 rows=1 width=283) (actual time=0.072..0.082 rows=1 loops=1)

28. 0.002 0.073 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.98..38.16 rows=1 width=267) (actual time=0.064..0.073 rows=1 loops=1)

29. 0.001 0.065 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.70..29.84 rows=1 width=251) (actual time=0.056..0.065 rows=1 loops=1)

30. 0.001 0.060 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.27..27.63 rows=1 width=255) (actual time=0.051..0.060 rows=1 loops=1)

31. 0.002 0.055 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.85..25.39 rows=1 width=239) (actual time=0.046..0.055 rows=1 loops=1)

32. 0.002 0.046 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.42..16.94 rows=1 width=223) (actual time=0.037..0.046 rows=1 loops=1)

33. 0.037 0.037 ↑ 1.0 1 1

CTE Scan on all_notifications (cost=0.00..8.48 rows=1 width=109) (actual time=0.029..0.037 rows=1 loops=1)

  • Filter: ((created_at <= '2019-04-15 14:24:52'::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: 19
34. 0.007 0.007 ↑ 1.0 1 1

Index Scan using index_user_info_account_id on user_info (cost=0.42..8.44 rows=1 width=114) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (account_id = all_notifications.publisher_account_id)
35. 0.007 0.007 ↓ 0.0 0 1

Index Scan using index_feed_activity_reaction_notifications_notification_id on feed_activity_reaction_notifications (cost=0.42..8.44 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (notification_id = all_notifications.id)
36. 0.004 0.004 ↓ 0.0 0 1

Index Scan using feed_activity_reactions_pkey on feed_activity_reactions (cost=0.42..2.24 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = feed_activity_reaction_notifications.feed_activity_reaction_id)
37. 0.004 0.004 ↓ 0.0 0 1

Index Scan using reactions_pkey on reactions fa_reactions (cost=0.42..2.21 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = feed_activity_reactions.reaction_id)
38. 0.006 0.006 ↑ 1.0 1 1

Index Scan using index_feed_activity_comment_notifications_notification_id on feed_activity_comment_notifications (cost=0.29..8.30 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (notification_id = all_notifications.id)
39. 0.007 0.007 ↑ 1.0 1 1

Index Scan using feed_activity_comments_pkey on feed_activity_comments (cost=0.29..2.08 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (id = feed_activity_comment_notifications.feed_activity_comment_id)
40. 0.007 0.007 ↓ 0.0 0 1

Index Scan using index_feed_activity_notifications_notification_id on feed_activity_notifications (cost=0.43..12.48 rows=4 width=24) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (notification_id = all_notifications.id)
41. 0.010 0.010 ↑ 1.0 1 1

Index Only Scan using comments_pkey on comments (cost=0.29..2.07 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (id = feed_activity_comments.comment_id)
  • Heap Fetches: 1
42. 0.007 0.007 ↓ 0.0 0 1

Index Scan using index_comment_reaction_notifications_notification_id on comment_reaction_notifications (cost=0.29..8.30 rows=1 width=24) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (notification_id = all_notifications.id)
43. 0.004 0.004 ↓ 0.0 0 1

Index Scan using comment_reactions_pkey on comment_reactions (cost=0.29..2.03 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = comment_reaction_notifications.comment_reaction_id)
44. 0.004 0.004 ↓ 0.0 0 1

Index Scan using reactions_pkey on reactions c_reactions (cost=0.42..2.86 rows=1 width=12) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = comment_reactions.reaction_id)
45. 0.006 0.006 ↓ 0.0 0 1

Index Only Scan using comments_pkey on comments comment_reaction_comments (cost=0.29..2.13 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (id = comment_reactions.comment_id)
  • Heap Fetches: 0
46. 0.012 0.012 ↓ 0.0 0 1

Index Scan using index_feed_activity_comments_comment_id on feed_activity_comments comment_reaction_activity_comments (cost=0.29..2.05 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (comment_id = comment_reaction_comments.id)
47. 0.004 0.004 ↓ 0.0 0 1

Index Scan using custom_workouts_pkey on custom_workouts (cost=0.42..7.32 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = custom_workout_notifications.custom_workout_id)
48. 0.001 0.006 ↓ 0.0 0 1

Nested Loop (cost=1.28..9.75 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=1)

49. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.84..4.53 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)

50. 0.005 0.005 ↓ 0.0 0 1

Index Only Scan using custom_workouts_pkey on custom_workouts cw (cost=0.42..2.19 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

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

Index Scan using index_custom_workout_activities_custom_workout_id on custom_workout_activities (cost=0.42..2.32 rows=2 width=16) (never executed)

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

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

  • Index Cond: (id = custom_workout_activities.activity_id)
  • Filter: (activity_type = 'custom_workout'::activity_type)
53. 0.043 0.074 ↑ 1.0 376 1

Hash (cost=6.76..6.76 rows=376 width=24) (actual time=0.074..0.074 rows=376 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
54. 0.031 0.031 ↑ 1.0 376 1

Seq Scan on custom_workout_follower_notifications (cost=0.00..6.76 rows=376 width=24) (actual time=0.005..0.031 rows=376 loops=1)

55. 0.004 0.004 ↓ 0.0 0 1

Index Scan using custom_workout_followers_pkey on custom_workout_followers (cost=0.28..1.75 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = custom_workout_follower_notifications.custom_workout_follower_id)
56. 0.004 0.004 ↓ 0.0 0 1

Index Scan using custom_workouts_pkey on custom_workouts cw_follower_custom_workouts (cost=0.42..5.92 rows=1 width=18) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = custom_workout_followers.custom_workout_id)
57. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.99..12.08 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=1)

58. 0.001 0.006 ↓ 0.0 0 1

Nested Loop (cost=0.55..3.64 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1)

59. 0.005 0.005 ↓ 0.0 0 1

Index Only Scan using custom_workout_followers_pkey on custom_workout_followers custom_workout_followers_1 (cost=0.28..1.70 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (id = custom_workout_followers.id)
  • Heap Fetches: 0
60. 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.28..1.92 rows=1 width=16) (never executed)

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

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

  • Index Cond: (id = custom_workout_follower_activities.activity_id)
  • Filter: (activity_type = 'custom_workout_new_follower'::activity_type)
62. 0.006 0.006 ↓ 0.0 0 1

Index Scan using index_connection_notifications_notification_id on connection_notifications (cost=0.29..8.30 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (notification_id = all_notifications.id)
63. 0.006 0.006 ↓ 0.0 0 1

Index Scan using index_comment_mention_notification_notification_id on comment_mention_notifications (cost=0.28..8.29 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (notification_id = all_notifications.id)
64. 0.004 0.004 ↓ 0.0 0 1

Index Scan using comment_mentions_pkey on comment_mentions (cost=0.28..1.94 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (id = comment_mention_notifications.comment_mention_id)
65. 0.004 0.004 ↓ 0.0 0 1

Index Scan using index_feed_activity_comments_comment_id on feed_activity_comments comment_mentions_feed_activity_comments (cost=0.29..2.58 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

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

SubPlan (forResult)

67. 0.002 0.009 ↑ 1.0 1 1

Aggregate (cost=12.49..12.50 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

68. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using index_feed_activity_notifications_notification_id on feed_activity_notifications feed_activity_notifications_1 (cost=0.43..12.48 rows=4 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (notification_id = all_notifications.id)
  • Heap Fetches: 0
69. 0.001 0.005 ↓ 0.0 0 1

Limit (cost=0.44..8.46 rows=1 width=4) (actual time=0.004..0.005 rows=0 loops=1)

70. 0.004 0.004 ↓ 0.0 0 1

Index Scan using index_activities_feed_activity_id on activities activities_2 (cost=0.44..8.46 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (feed_activity_id = feed_activity_notifications.feed_activity_id)
71. 0.001 0.006 ↓ 0.0 0 1

Limit (cost=0.44..8.46 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

72. 0.005 0.005 ↓ 0.0 0 1

Index Scan using index_activities_feed_activity_id on activities activities_3 (cost=0.44..8.46 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (feed_activity_id = feed_activity_reactions.feed_activity_id)
73. 0.001 0.008 ↑ 1.0 1 1

Limit (cost=0.44..8.46 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

74. 0.007 0.007 ↑ 1.0 1 1

Index Scan using index_activities_feed_activity_id on activities activities_4 (cost=0.44..8.46 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (feed_activity_id = feed_activity_comments.feed_activity_id)
75. 0.000 0.004 ↓ 0.0 0 1

Limit (cost=0.44..8.46 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

76. 0.004 0.004 ↓ 0.0 0 1

Index Scan using index_activities_feed_activity_id on activities activities_5 (cost=0.44..8.46 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (feed_activity_id = comment_reaction_activity_comments.feed_activity_id)
77. 0.000 0.004 ↓ 0.0 0 1

Limit (cost=0.44..8.46 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

78. 0.004 0.004 ↓ 0.0 0 1

Index Scan using index_activities_feed_activity_id on activities activities_6 (cost=0.44..8.46 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (feed_activity_id = comment_mentions_feed_activity_comments.feed_activity_id)