explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kyym

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 125.434 ↓ 1.0 100 1

Limit (cost=44,890.16..44,890.40 rows=96 width=261) (actual time=125.414..125.434 rows=100 loops=1)

2.          

CTE all_notifications

3. 58.373 60.283 ↓ 1.0 29,878 1

Bitmap Heap Scan on notifications (cost=558.71..41,608.43 rows=29,713 width=109) (actual time=3.120..60.283 rows=29,878 loops=1)

  • Recheck Cond: (sent_to_account_id = 140141)
  • Heap Blocks: exact=13139
4. 1.910 1.910 ↓ 1.0 29,878 1

Bitmap Index Scan on index_notifications_sent_to_account_id (cost=0.00..551.28 rows=29,713 width=0) (actual time=1.910..1.910 rows=29,878 loops=1)

  • Index Cond: (sent_to_account_id = 140141)
5. 4.713 125.422 ↓ 1.0 100 1

Sort (cost=3,281.73..3,281.97 rows=96 width=261) (actual time=125.414..125.422 rows=100 loops=1)

  • Sort Key: all_notifications.id DESC, all_notifications.notified_at_timestamp DESC
  • Sort Method: top-N heapsort Memory: 72kB
6. 5.073 120.709 ↓ 121.2 11,638 1

Nested Loop Left Join (cost=13.57..3,278.57 rows=96 width=261) (actual time=3.216..120.709 rows=11,638 loops=1)

7. 0.421 115.636 ↓ 121.2 11,638 1

Nested Loop Left Join (cost=13.15..2,701.42 rows=96 width=251) (actual time=3.214..115.636 rows=11,638 loops=1)

8. 8.541 91.939 ↓ 121.2 11,638 1

Nested Loop Left Join (cost=12.72..1,897.98 rows=96 width=137) (actual time=3.206..91.939 rows=11,638 loops=1)

9. 5.121 83.398 ↓ 121.2 11,638 1

Nested Loop Left Join (cost=11.74..1,018.79 rows=96 width=133) (actual time=3.203..83.398 rows=11,638 loops=1)

10. 3.138 78.277 ↓ 121.2 11,638 1

Hash Left Join (cost=11.46..978.45 rows=96 width=125) (actual time=3.199..78.277 rows=11,638 loops=1)

  • Hash Cond: (all_notifications.id = custom_workout_follower_notifications.notification_id)
11. 75.072 75.072 ↓ 121.2 11,638 1

CTE Scan on all_notifications (cost=0.00..965.67 rows=96 width=109) (actual time=3.125..75.072 rows=11,638 loops=1)

  • Filter: ((created_at <= '2019-04-15 13:29: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: 18240
12. 0.034 0.067 ↑ 1.0 376 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
13. 0.033 0.033 ↑ 1.0 376 1

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

14. 0.000 0.000 ↓ 0.0 0 11,638

Index Scan using custom_workout_followers_pkey on custom_workout_followers (cost=0.28..0.42 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=11,638)

  • Index Cond: (id = custom_workout_follower_notifications.custom_workout_follower_id)
15. 0.000 0.000 ↓ 0.0 0 11,638

Nested Loop (cost=0.99..9.15 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=11,638)

16. 0.000 0.000 ↓ 0.0 0 11,638

Nested Loop (cost=0.55..0.71 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=11,638)

17. 0.000 0.000 ↓ 0.0 0 11,638

Index Only Scan using custom_workout_followers_pkey on custom_workout_followers custom_workout_followers_1 (cost=0.28..0.35 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=11,638)

  • Index Cond: (id = custom_workout_followers.id)
  • Heap Fetches: 32
18. 0.096 0.096 ↑ 1.0 1 32

Index Scan using index_custom_workout_follower_activities_custom_workout_followe on custom_workout_follower_activities (cost=0.28..0.34 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=32)

  • Index Cond: (custom_workout_follower_id = custom_workout_followers_1.id)
19. 0.416 0.416 ↑ 1.0 1 32

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

  • Index Cond: (id = custom_workout_follower_activities.activity_id)
  • Filter: (activity_type = 'custom_workout_new_follower'::activity_type)
20. 23.276 23.276 ↑ 1.0 1 11,638

Index Scan using index_user_info_account_id on user_info (cost=0.42..8.36 rows=1 width=114) (actual time=0.001..0.002 rows=1 loops=11,638)

  • Index Cond: (account_id = all_notifications.publisher_account_id)
21. 0.000 0.000 ↓ 0.0 0 11,638

Index Scan using custom_workouts_pkey on custom_workouts cw_follower_custom_workouts (cost=0.42..6.01 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=11,638)

  • Index Cond: (id = custom_workout_followers.custom_workout_id)