explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gS1P

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 45.689 ↑ 1.0 100 1

Nested Loop Left Join (cost=42,148.55..44,552.91 rows=100 width=261) (actual time=45.411..45.689 rows=100 loops=1)

2.          

CTE all_notifications

3. 0.010 45.249 ↑ 1.0 100 1

Limit (cost=42,142.95..42,143.20 rows=100 width=109) (actual time=45.232..45.249 rows=100 loops=1)

4. 1.373 45.239 ↑ 44.3 100 1

Sort (cost=42,142.95..42,154.03 rows=4,433 width=109) (actual time=45.231..45.239 rows=100 loops=1)

  • Sort Key: notifications.id DESC, notifications.notified_at_timestamp DESC
  • Sort Method: top-N heapsort Memory: 48kB
5. 42.459 43.866 ↓ 2.6 11,638 1

Bitmap Heap Scan on notifications (cost=552.39..41,973.52 rows=4,433 width=109) (actual time=2.586..43.866 rows=11,638 loops=1)

  • Recheck Cond: (sent_to_account_id = 140141)
  • 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
  • Heap Blocks: exact=13139
6. 1.407 1.407 ↓ 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.406..1.407 rows=29,878 loops=1)

  • Index Cond: (sent_to_account_id = 140141)
7. 0.000 45.588 ↑ 1.0 100 1

Nested Loop Left Join (cost=3.67..849.67 rows=100 width=239) (actual time=45.377..45.588 rows=100 loops=1)

8. 0.053 45.388 ↑ 1.0 100 1

Hash Right Join (cost=3.25..12.42 rows=100 width=125) (actual time=45.367..45.388 rows=100 loops=1)

  • Hash Cond: (custom_workout_follower_notifications.notification_id = all_notifications.id)
9. 0.035 0.035 ↑ 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.035 rows=376 loops=1)

10. 0.025 45.300 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=109) (actual time=45.300..45.300 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
11. 45.275 45.275 ↑ 1.0 100 1

CTE Scan on all_notifications (cost=0.00..2.00 rows=100 width=109) (actual time=45.235..45.275 rows=100 loops=1)

12. 0.200 0.200 ↑ 1.0 1 100

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=100)

  • Index Cond: (account_id = all_notifications.publisher_account_id)
13. 0.000 0.100 ↓ 0.0 0 100

Nested Loop Left Join (cost=1.68..15.59 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=100)

14. 0.078 0.100 ↓ 0.0 0 100

Nested Loop Left Join (cost=1.26..9.58 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=100)

15. 0.000 0.000 ↓ 0.0 0 100

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=100)

  • Index Cond: (id = custom_workout_follower_notifications.custom_workout_follower_id)
16. 0.001 0.022 ↑ 1.0 1 1

Nested Loop (cost=0.99..9.15 rows=1 width=20) (actual time=0.021..0.022 rows=1 loops=1)

17. 0.002 0.007 ↑ 1.0 1 1

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

18. 0.002 0.002 ↑ 1.0 1 1

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.002..0.002 rows=1 loops=1)

  • Index Cond: (id = custom_workout_followers.id)
  • Heap Fetches: 1
19. 0.003 0.003 ↑ 1.0 1 1

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

  • Index Cond: (custom_workout_follower_id = custom_workout_followers_1.id)
20. 0.014 0.014 ↑ 1.0 1 1

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

  • Index Cond: (id = custom_workout_follower_activities.activity_id)
  • Filter: (activity_type = 'custom_workout_new_follower'::activity_type)
21. 0.005 0.005 ↑ 1.0 1 1

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.005..0.005 rows=1 loops=1)

  • Index Cond: (id = custom_workout_followers.custom_workout_id)