explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eSMk

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 36,670.745 ↑ 1.0 100 1

Limit (cost=48,474.32..53,953.50 rows=100 width=487) (actual time=36,532.246..36,670.745 rows=100 loops=1)

2.          

CTE all_notifications

3. 1,429.339 1,448.459 ↓ 1.1 17,981 1

Bitmap Heap Scan on notifications (cost=319.03..32,596.38 rows=16,851 width=109) (actual time=20.295..1,448.459 rows=17,981 loops=1)

  • Recheck Cond: (sent_to_account_id = 221263)
  • Heap Blocks: exact=9299
4. 19.120 19.120 ↓ 1.1 17,981 1

Bitmap Index Scan on index_notifications_sent_to_account_id (cost=0.00..314.81 rows=16,851 width=0) (actual time=19.120..19.120 rows=17,981 loops=1)

  • Index Cond: (sent_to_account_id = 221263)
5. 0.495 36,670.711 ↑ 2.4 100 1

Result (cost=15,877.93..28,754.02 rows=235 width=487) (actual time=36,532.245..36,670.711 rows=100 loops=1)

6. 15.544 36,519.416 ↑ 2.4 100 1

Sort (cost=15,877.93..15,878.52 rows=235 width=459) (actual time=36,519.376..36,519.416 rows=100 loops=1)

  • Sort Key: all_notifications.id DESC, all_notifications.notified_at_timestamp DESC
  • Sort Method: top-N heapsort Memory: 87kB
7. 2.444 36,503.872 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=1,531.32..15,868.95 rows=235 width=459) (actual time=74.273..36,503.872 rows=8,828 loops=1)

8. 5.676 36,474.944 ↓ 37.6 8,828 1

Hash Left Join (cost=1,531.03..15,259.60 rows=235 width=451) (actual time=74.265..36,474.944 rows=8,828 loops=1)

  • Hash Cond: (comment_mention_notifications.comment_mention_id = comment_mentions.id)
9. 5.812 36,467.996 ↓ 37.6 8,828 1

Hash Left Join (cost=1,431.50..15,159.46 rows=235 width=443) (actual time=72.970..36,467.996 rows=8,828 loops=1)

  • Hash Cond: (all_notifications.id = comment_mention_notifications.notification_id)
10. 6.083 36,460.449 ↓ 37.6 8,828 1

Hash Left Join (cost=1,374.22..15,098.66 rows=235 width=427) (actual time=71.227..36,460.449 rows=8,828 loops=1)

  • Hash Cond: (all_notifications.id = connection_notifications.notification_id)
11. 8.061 36,435.814 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=470.22..14,191.42 rows=235 width=411) (actual time=52.644..36,435.814 rows=8,828 loops=1)

12. 4.327 36,357.129 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=469.23..11,350.71 rows=235 width=407) (actual time=52.635..36,357.129 rows=8,828 loops=1)

13. 5.028 36,335.146 ↓ 37.6 8,828 1

Hash Left Join (cost=468.81..9,960.64 rows=235 width=397) (actual time=52.629..36,335.146 rows=8,828 loops=1)

  • Hash Cond: (custom_workout_follower_notifications.custom_workout_follower_id = custom_workout_followers.id)
14. 4.821 36,329.202 ↓ 37.6 8,828 1

Hash Left Join (cost=444.15..9,935.36 rows=235 width=389) (actual time=51.700..36,329.202 rows=8,828 loops=1)

  • Hash Cond: (all_notifications.id = custom_workout_follower_notifications.notification_id)
15. 5.468 36,323.914 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=432.69..9,920.67 rows=235 width=373) (actual time=51.221..36,323.914 rows=8,828 loops=1)

16. 8.118 36,309.618 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=431.41..7,626.72 rows=235 width=361) (actual time=51.217..36,309.618 rows=8,828 loops=1)

17. 5.054 36,301.500 ↓ 37.6 8,828 1

Hash Left Join (cost=430.99..5,907.11 rows=235 width=351) (actual time=51.213..36,301.500 rows=8,828 loops=1)

  • Hash Cond: (all_notifications.id = custom_workout_notifications.notification_id)
18. 4.122 36,296.399 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=422.81..5,895.69 rows=235 width=335) (actual time=51.161..36,296.399 rows=8,828 loops=1)

19. 4.746 36,018.609 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=422.51..5,411.96 rows=235 width=327) (actual time=51.158..36,018.609 rows=8,828 loops=1)

20. 5.638 35,872.615 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=422.22..4,912.26 rows=235 width=327) (actual time=51.154..35,872.615 rows=8,828 loops=1)

21. 11.507 35,098.941 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=421.80..4,239.63 rows=235 width=331) (actual time=51.151..35,098.941 rows=8,828 loops=1)

22. 9.260 34,884.390 ↓ 37.6 8,828 1

Hash Left Join (cost=421.51..3,761.73 rows=235 width=315) (actual time=51.146..34,884.390 rows=8,828 loops=1)

  • Hash Cond: (all_notifications.id = comment_reaction_notifications.notification_id)
23. 8.592 34,864.958 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=3.00..3,339.99 rows=235 width=299) (actual time=40.934..34,864.958 rows=8,828 loops=1)

24. 8.602 34,644.494 ↓ 37.6 8,828 1

Nested Loop Left Join (cost=2.71..2,853.41 rows=235 width=299) (actual time=40.929..34,644.494 rows=8,828 loops=1)

25. 8.821 22,153.100 ↓ 163.5 8,828 1

Nested Loop Left Join (cost=2.28..2,178.42 rows=54 width=283) (actual time=34.153..22,153.100 rows=8,828 loops=1)

26. 5.603 21,923.579 ↓ 163.5 8,828 1

Nested Loop Left Join (cost=1.98..2,065.99 rows=54 width=267) (actual time=34.146..21,923.579 rows=8,828 loops=1)

27. 13.864 20,973.380 ↓ 163.5 8,828 1

Nested Loop Left Join (cost=1.70..1,696.98 rows=54 width=251) (actual time=34.138..20,973.380 rows=8,828 loops=1)

28. 14.434 17,869.716 ↓ 163.5 8,828 1

Nested Loop Left Join (cost=1.27..1,577.45 rows=54 width=255) (actual time=34.096..17,869.716 rows=8,828 loops=1)

29. 7.025 13,141.130 ↓ 163.5 8,828 1

Nested Loop Left Join (cost=0.85..1,456.39 rows=54 width=239) (actual time=34.061..13,141.130 rows=8,828 loops=1)

30. 16.816 2,054.965 ↓ 163.5 8,828 1

Nested Loop Left Join (cost=0.42..1,004.09 rows=54 width=223) (actual time=20.312..2,054.965 rows=8,828 loops=1)

31. 1,473.157 1,473.157 ↓ 163.5 8,828 1

CTE Scan on all_notifications (cost=0.00..547.66 rows=54 width=109) (actual time=20.301..1,473.157 rows=8,828 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: 9153
32. 564.992 564.992 ↑ 1.0 1 8,828

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

  • Index Cond: (account_id = all_notifications.publisher_account_id)
33. 11,079.140 11,079.140 ↑ 1.0 1 8,828

Index Scan using index_feed_activity_reaction_notifications_notification_id on feed_activity_reaction_notifications (cost=0.42..8.37 rows=1 width=24) (actual time=1.255..1.255 rows=1 loops=8,828)

  • Index Cond: (notification_id = all_notifications.id)
34. 4,714.152 4,714.152 ↑ 1.0 1 8,828

Index Scan using feed_activity_reactions_pkey on feed_activity_reactions (cost=0.42..2.24 rows=1 width=24) (actual time=0.534..0.534 rows=1 loops=8,828)

  • Index Cond: (id = feed_activity_reaction_notifications.feed_activity_reaction_id)
35. 3,089.800 3,089.800 ↑ 1.0 1 8,828

Index Scan using reactions_pkey on reactions fa_reactions (cost=0.42..2.21 rows=1 width=12) (actual time=0.350..0.350 rows=1 loops=8,828)

  • Index Cond: (id = feed_activity_reactions.reaction_id)
36. 944.596 944.596 ↓ 0.0 0 8,828

Index Scan using index_feed_activity_comment_notifications_notification_id on feed_activity_comment_notifications (cost=0.29..6.82 rows=1 width=24) (actual time=0.107..0.107 rows=0 loops=8,828)

  • Index Cond: (notification_id = all_notifications.id)
37. 220.700 220.700 ↓ 0.0 0 8,828

Index Scan using feed_activity_comments_pkey on feed_activity_comments (cost=0.29..2.08 rows=1 width=24) (actual time=0.025..0.025 rows=0 loops=8,828)

  • Index Cond: (id = feed_activity_comment_notifications.feed_activity_comment_id)
38. 12,482.792 12,482.792 ↓ 0.0 0 8,828

Index Scan using index_feed_activity_notifications_notification_id on feed_activity_notifications (cost=0.43..12.46 rows=4 width=24) (actual time=1.414..1.414 rows=0 loops=8,828)

  • Index Cond: (notification_id = all_notifications.id)
39. 211.872 211.872 ↓ 0.0 0 8,828

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

  • Index Cond: (id = feed_activity_comments.comment_id)
  • Heap Fetches: 1012
40. 1.362 10.172 ↑ 1.0 14,467 1

Hash (cost=237.67..237.67 rows=14,467 width=24) (actual time=10.172..10.172 rows=14,467 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 920kB
41. 8.810 8.810 ↑ 1.0 14,467 1

Seq Scan on comment_reaction_notifications (cost=0.00..237.67 rows=14,467 width=24) (actual time=7.024..8.810 rows=14,467 loops=1)

42. 203.044 203.044 ↓ 0.0 0 8,828

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

  • Index Cond: (id = comment_reaction_notifications.comment_reaction_id)
43. 768.036 768.036 ↓ 0.0 0 8,828

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

  • Index Cond: (id = comment_reactions.reaction_id)
44. 141.248 141.248 ↓ 0.0 0 8,828

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

  • Index Cond: (id = comment_reactions.comment_id)
  • Heap Fetches: 884
45. 273.668 273.668 ↓ 0.0 0 8,828

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.031..0.031 rows=0 loops=8,828)

  • Index Cond: (comment_id = comment_reaction_comments.id)
46. 0.024 0.047 ↑ 1.0 275 1

Hash (cost=4.75..4.75 rows=275 width=24) (actual time=0.047..0.047 rows=275 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
47. 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)

48. 0.000 0.000 ↓ 0.0 0 8,828

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

  • Index Cond: (id = custom_workout_notifications.custom_workout_id)
49. 8.828 8.828 ↓ 0.0 0 8,828

Nested Loop (cost=1.28..9.75 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=8,828)

50. 0.000 0.000 ↓ 0.0 0 8,828

Nested Loop (cost=0.84..4.53 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=8,828)

51. 0.000 0.000 ↓ 0.0 0 8,828

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

  • 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.42..2.32 rows=2 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.44..5.22 rows=1 width=20) (never executed)

  • Index Cond: (id = custom_workout_activities.activity_id)
  • Filter: (activity_type = 'custom_workout'::activity_type)
54. 0.051 0.467 ↑ 1.0 376 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
55. 0.416 0.416 ↑ 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.416 rows=376 loops=1)

56. 0.104 0.916 ↑ 1.0 785 1

Hash (cost=14.85..14.85 rows=785 width=16) (actual time=0.916..0.916 rows=785 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
57. 0.812 0.812 ↑ 1.0 785 1

Seq Scan on custom_workout_followers (cost=0.00..14.85 rows=785 width=16) (actual time=0.006..0.812 rows=785 loops=1)

58. 17.656 17.656 ↓ 0.0 0 8,828

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.002..0.002 rows=0 loops=8,828)

  • Index Cond: (id = custom_workout_followers.custom_workout_id)
59. 0.000 70.624 ↓ 0.0 0 8,828

Nested Loop (cost=0.99..12.08 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=8,828)

60. 7.956 8.828 ↓ 0.0 0 8,828

Nested Loop (cost=0.55..3.64 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=8,828)

61. 0.000 0.000 ↓ 0.0 0 8,828

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.000..0.000 rows=0 loops=8,828)

  • Index Cond: (id = custom_workout_followers.id)
  • Heap Fetches: 2
62. 0.872 0.872 ↑ 1.0 1 2

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) (actual time=0.435..0.436 rows=1 loops=2)

  • Index Cond: (custom_workout_follower_id = custom_workout_followers_1.id)
63. 65.076 65.076 ↑ 1.0 1 2

Index Scan using activities_pkey on activities activities_1 (cost=0.44..8.43 rows=1 width=20) (actual time=32.538..32.538 rows=1 loops=2)

  • Index Cond: (id = custom_workout_follower_activities.activity_id)
  • Filter: (activity_type = 'custom_workout_new_follower'::activity_type)
64. 4.090 18.552 ↑ 1.0 31,289 1

Hash (cost=512.89..512.89 rows=31,289 width=24) (actual time=18.552..18.552 rows=31,289 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1968kB
65. 14.462 14.462 ↑ 1.0 31,289 1

Seq Scan on connection_notifications (cost=0.00..512.89 rows=31,289 width=24) (actual time=10.131..14.462 rows=31,289 loops=1)

66. 0.321 1.735 ↑ 1.0 1,968 1

Hash (cost=32.68..32.68 rows=1,968 width=24) (actual time=1.735..1.735 rows=1,968 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 124kB
67. 1.414 1.414 ↑ 1.0 1,968 1

Seq Scan on comment_mention_notifications (cost=0.00..32.68 rows=1,968 width=24) (actual time=0.399..1.414 rows=1,968 loops=1)

68. 0.343 1.272 ↑ 1.0 2,601 1

Hash (cost=67.01..67.01 rows=2,601 width=16) (actual time=1.271..1.272 rows=2,601 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 154kB
69. 0.929 0.929 ↑ 1.0 2,601 1

Seq Scan on comment_mentions (cost=0.00..67.01 rows=2,601 width=16) (actual time=0.006..0.929 rows=2,601 loops=1)

70. 26.484 26.484 ↓ 0.0 0 8,828

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.003..0.003 rows=0 loops=8,828)

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

SubPlan (forResult)

72. 0.100 0.400 ↑ 1.0 1 100

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

73. 0.300 0.300 ↓ 0.0 0 100

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.003..0.003 rows=0 loops=100)

  • Index Cond: (notification_id = all_notifications.id)
  • Heap Fetches: 0
74. 0.000 0.000 ↓ 0.0 0 100

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

75. 0.000 0.000 ↓ 0.0 0 100

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

  • Index Cond: (feed_activity_id = feed_activity_notifications.feed_activity_id)
76. 0.000 77.800 ↑ 1.0 1 100

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

77. 77.800 77.800 ↑ 1.0 1 100

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

  • Index Cond: (feed_activity_id = feed_activity_reactions.feed_activity_id)
78. 0.000 6.100 ↓ 0.0 0 100

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

79. 6.100 6.100 ↓ 0.0 0 100

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

  • Index Cond: (feed_activity_id = feed_activity_comments.feed_activity_id)
80. 0.000 41.300 ↓ 0.0 0 100

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

81. 41.300 41.300 ↓ 0.0 0 100

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

  • Index Cond: (feed_activity_id = comment_reaction_activity_comments.feed_activity_id)
82. 0.100 25.200 ↓ 0.0 0 100

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

83. 25.100 25.100 ↓ 0.0 0 100

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

  • Index Cond: (feed_activity_id = comment_mentions_feed_activity_comments.feed_activity_id)