explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Eem7

Settings
# exclusive inclusive rows x rows loops node
1. 96.084 615.882 ↓ 1.2 38,654 1

Sort (cost=113,474.04..113,553.04 rows=31,601 width=228) (actual time=592.054..615.882 rows=38,654 loops=1)

  • Sort Key: event_stream.unix DESC, event_stream.event_type
  • Sort Method: quicksort Memory: 26728kB
  • Buffers: shared hit=47610
2. 135.526 519.798 ↓ 1.2 38,654 1

Subquery Scan on event_stream (cost=1,066.57..111,112.23 rows=31,601 width=228) (actual time=2.663..519.798 rows=38,654 loops=1)

  • Buffers: shared hit=47604
3. 41.637 384.272 ↓ 1.2 38,654 1

Append (cost=1,066.57..110,322.2 rows=31,601 width=132) (actual time=2.57..384.272 rows=38,654 loops=1)

  • Buffers: shared hit=47604
4. 0.000 34.971 ↓ 1.1 3,023 1

Gather (cost=1,066.57..29,721.05 rows=2,802 width=132) (actual time=2.569..34.971 rows=3,023 loops=1)

  • Buffers: shared hit=13294
5. 26.958 37.400 ↑ 1.1 1,512 2

Nested Loop (cost=66.57..28,435.08 rows=1,648 width=132) (actual time=0.995..37.4 rows=1,512 loops=2)

  • Buffers: shared hit=13294
6. 9.246 10.431 ↑ 1.1 1,476 2

Bitmap Heap Scan on comments comments (cost=66.15..9,789.91 rows=1,648 width=274) (actual time=0.835..10.431 rows=1,476 loops=2)

  • Buffers: shared hit=2714
7. 1.185 1.185 ↓ 1.1 2,952 1

Bitmap Index Scan on index_comments_on_user_id (cost=0..65.45 rows=2,802 width=0) (actual time=1.185..1.185 rows=2,952 loops=1)

  • Index Cond: (comments.user_id = 30815)
  • Buffers: shared hit=14
8. 0.011 0.011 ↓ 0.0 0 2,952

Index Scan using index_actions_on_resource_id on actions actions (cost=0.43..11.3 rows=1 width=8) (actual time=0.01..0.011 rows=0 loops=2,952)

  • Index Cond: (actions.resource_id = comments.id)
  • Filter: ((actions.resource_type)::text = 'Comment'::text)
  • Buffers: shared hit=10454
9. 8.200 29.286 ↑ 1.1 666 1

Hash Join (cost=581.68..3,390.93 rows=746 width=132) (actual time=17.595..29.286 rows=666 loops=1)

  • Buffers: shared hit=966
10. 3.743 3.905 ↑ 1.1 666 1

Bitmap Heap Scan on email_messages email_messages (cost=22.83..2,826.39 rows=746 width=625) (actual time=0.303..3.905 rows=666 loops=1)

  • Buffers: shared hit=667
11. 0.002 0.162 ↓ 0.0 0 1

BitmapOr (cost=22.83..22.83 rows=746 width=0) (actual time=0.162..0.162 rows=0 loops=1)

  • Buffers: shared hit=9
12. 0.137 0.137 ↑ 1.1 666 1

Bitmap Index Scan on index_email_messages_on_user_id (cost=0..18.02 rows=746 width=0) (actual time=0.137..0.137 rows=666 loops=1)

  • Index Cond: (email_messages.user_id = 30815)
  • Buffers: shared hit=6
13. 0.023 0.023 ↓ 0.0 0 1

Bitmap Index Scan on index_email_messages_on_subject_user_id (cost=0..4.44 rows=1 width=0) (actual time=0.022..0.023 rows=0 loops=1)

  • Index Cond: (email_messages.subject_user_id = 30815)
  • Buffers: shared hit=3
14. 8.402 17.181 ↓ 1.0 11,591 1

Hash (cost=414.49..414.49 rows=11,549 width=27) (actual time=17.18..17.181 rows=11,591 loops=1)

  • Buffers: shared hit=299
15. 8.779 8.779 ↓ 1.0 11,591 1

Seq Scan on clinics clinics (cost=0..414.49 rows=11,549 width=27) (actual time=0.019..8.779 rows=11,591 loops=1)

  • Buffers: shared hit=299
16. 29.713 189.610 ↓ 1.3 25,642 1

Subquery Scan on *SELECT* 3 (cost=0.56..47,878.57 rows=19,661 width=132) (actual time=0.066..189.61 rows=25,642 loops=1)

  • Buffers: shared hit=23832
17. 159.897 159.897 ↓ 1.3 25,642 1

Index Scan using index_events_on_user_id on events events (cost=0.56..47,681.96 rows=19,661 width=85) (actual time=0.063..159.897 rows=25,642 loops=1)

  • Index Cond: (events.user_id = 30815)
  • Filter: (((events.resource_type)::text <> 'Employee'::text) AND (((events.resource_type)::text <> 'Delivery'::text) OR (events.resource_id <> ALL ('{100,200}'::integer[]))))
  • Buffers: shared hit=23832
18. 0.012 0.728 ↑ 1.0 8 1

Subquery Scan on *SELECT* 4 (cost=37.4..37.74 rows=8 width=132) (actual time=0.555..0.728 rows=8 loops=1)

  • Buffers: shared hit=14
19. 0.160 0.716 ↑ 1.0 8 1

Aggregate (cost=37.4..37.66 rows=8 width=588) (actual time=0.552..0.716 rows=8 loops=1)

  • Buffers: shared hit=14
20. 0.253 0.556 ↓ 11.0 88 1

Sort (cost=37.4..37.42 rows=8 width=508) (actual time=0.507..0.556 rows=88 loops=1)

  • Sort Key: incidents.id
  • Sort Method: quicksort Memory: 82kB
  • Buffers: shared hit=14
21. 0.118 0.303 ↓ 11.0 88 1

Nested Loop (cost=4.35..37.28 rows=8 width=508) (actual time=0.05..0.303 rows=88 loops=1)

  • Buffers: shared hit=11
22. 0.086 0.105 ↑ 1.0 8 1

Bitmap Heap Scan on incidents incidents (cost=4.35..34.61 rows=8 width=480) (actual time=0.03..0.105 rows=8 loops=1)

  • Buffers: shared hit=10
23. 0.019 0.019 ↑ 1.0 8 1

Bitmap Index Scan on index_incidents_on_user_id (cost=0..4.34 rows=8 width=0) (actual time=0.018..0.019 rows=8 loops=1)

  • Index Cond: (incidents.user_id = 30815)
  • Buffers: shared hit=2
24. 0.062 0.080 ↑ 1.0 11 8

Materialize (cost=0..1.17 rows=11 width=36) (actual time=0.002..0.01 rows=11 loops=8)

  • Buffers: shared hit=1
25. 0.018 0.018 ↑ 1.0 11 1

Seq Scan on incident_categories incident_categories (cost=0..1.11 rows=11 width=36) (actual time=0.01..0.018 rows=11 loops=1)

  • Buffers: shared hit=1
26. 0.023 0.046 ↓ 0.0 0 1

Bitmap Heap Scan on phone_call_legacies phone_call_legacies (cost=14.64..865.95 rows=146 width=132) (actual time=0.046..0.046 rows=0 loops=1)

  • Filter: (phone_call_legacies.is_voicemail IS TRUE)
  • Buffers: shared hit=8
27. 0.023 0.023 ↑ 41.7 7 1

Bitmap Index Scan on index_phone_call_legacies_on_user_id (cost=0..14.6 rows=292 width=0) (actual time=0.023..0.023 rows=7 loops=1)

  • Index Cond: (phone_call_legacies.user_id = 30815)
  • Buffers: shared hit=3
28. 0.002 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.43..21.61 rows=1 width=132) (actual time=0.012..0.013 rows=0 loops=1)

  • Buffers: shared hit=1
29. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on partnerships_secure_requests partnerships_secure_requests (cost=0..13.5 rows=1 width=16) (actual time=0.01..0.011 rows=0 loops=1)

  • Filter: (partnerships_secure_requests.user_id = 30815)
  • Buffers: shared hit=1
30. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_actions_on_resource_type on actions actions_1 (cost=0.43..8.1 rows=1 width=88) (never executed)

  • Index Cond: ((actions_1.resource_type)::text = 'PartnershipsSecureRequest'::text)
31. 1.748 5.124 ↓ 13.2 106 1

Nested Loop (cost=1.69..168.92 rows=8 width=132) (actual time=0.253..5.124 rows=106 loops=1)

  • Buffers: shared hit=1252
32. 0.202 3.058 ↓ 13.2 106 1

Nested Loop (cost=1.27..165.01 rows=8 width=469) (actual time=0.084..3.058 rows=106 loops=1)

  • Buffers: shared hit=872
33. 0.267 1.160 ↓ 13.2 106 1

Nested Loop (cost=0.84..97.45 rows=8 width=465) (actual time=0.046..1.16 rows=106 loops=1)

  • Buffers: shared hit=459
34. 0.575 0.575 ↓ 13.2 106 1

Index Scan using index_phone_call_dispositions_on_user_id on phone_call_dispositions phone_call_dispositions (cost=0.42..29.97 rows=8 width=451) (actual time=0.026..0.575 rows=106 loops=1)

  • Index Cond: (phone_call_dispositions.user_id = 30815)
  • Buffers: shared hit=111
35. 0.318 0.318 ↓ 0.0 0 106

Index Scan using users_pkey on users spoke_to_users (cost=0.42..8.44 rows=1 width=18) (actual time=0.003..0.003 rows=0 loops=106)

  • Index Cond: (spoke_to_users.id = phone_call_dispositions.spoke_with_id)
  • Buffers: shared hit=348
36. 1.696 1.696 ↑ 1.0 1 106

Index Scan using phone_call_legs_pkey on phone_call_legs phone_call_legs (cost=0.43..8.45 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=106)

  • Index Cond: (phone_call_dispositions.phone_call_leg_id = phone_call_legs.id)
  • Buffers: shared hit=413
37. 0.318 0.318 ↑ 1.0 1 106

Index Scan using phone_calls_pkey on phone_calls phone_calls (cost=0.42..0.48 rows=1 width=59) (actual time=0.003..0.003 rows=1 loops=106)

  • Index Cond: (phone_call_legs.phone_call_id = phone_calls.id)
  • Buffers: shared hit=380
38. 0.003 0.283 ↓ 0.0 0 1

Bitmap Heap Scan on attachments attachments (cost=28.77..40.4 rows=3 width=132) (actual time=0.283..0.283 rows=0 loops=1)

  • Buffers: shared hit=8
39. 0.014 0.280 ↓ 0.0 0 1

BitmapAnd (cost=28.77..28.77 rows=3 width=0) (actual time=0.279..0.28 rows=0 loops=1)

  • Buffers: shared hit=8
40. 0.042 0.042 ↓ 1.0 289 1

Bitmap Index Scan on index_attachments_on_user_id (cost=0..10.38 rows=278 width=0) (actual time=0.041..0.042 rows=289 loops=1)

  • Index Cond: (attachments.user_id = 30815)
  • Buffers: shared hit=3
41. 0.224 0.224 ↓ 1.0 796 1

Bitmap Index Scan on index_attachments_on_provider_user_id (cost=0..18.14 rows=780 width=0) (actual time=0.223..0.224 rows=796 loops=1)

  • Index Cond: (attachments.provider_user_id IS NOT NULL)
  • Buffers: shared hit=5
42. 0.204 0.204 ↓ 2.0 16 1

Index Scan using index_provider_wundercom_messages_on_user_id on provider_wundercom_messages provider_wundercom_messages (cost=0.42..23.23 rows=8 width=132) (actual time=0.032..0.204 rows=16 loops=1)

  • Index Cond: (provider_wundercom_messages.user_id = 30815)
  • Buffers: shared hit=17
43. 0.546 11.749 ↓ 35.5 462 1

Subquery Scan on *SELECT* 10 (cost=90.2..90.89 rows=13 width=132) (actual time=6.483..11.749 rows=462 loops=1)

  • Buffers: shared hit=961
44. 4.224 11.203 ↓ 35.5 462 1

Aggregate (cost=90.2..90.76 rows=13 width=205) (actual time=6.481..11.203 rows=462 loops=1)

  • Buffers: shared hit=961
45. 2.664 6.979 ↓ 73.6 957 1

Sort (cost=90.2..90.24 rows=13 width=97) (actual time=6.449..6.979 rows=957 loops=1)

  • Sort Key: push_notification_messages.grouped_message_id, push_notification_messages.body, push_notification_messages.title
  • Sort Method: quicksort Memory: 160kB
  • Buffers: shared hit=961
46. 1.123 4.315 ↓ 73.6 957 1

Nested Loop (cost=0.71..89.96 rows=13 width=97) (actual time=0.052..4.315 rows=957 loops=1)

  • Buffers: shared hit=961
47. 0.020 0.020 ↓ 2.0 2 1

Index Scan using index_devices_on_user_id on devices devices (cost=0.29..8.3 rows=1 width=12) (actual time=0.014..0.02 rows=2 loops=1)

  • Index Cond: (devices.user_id = 30815)
  • Buffers: shared hit=4
48. 3.172 3.172 ↓ 19.1 478 2

Index Scan using index_push_notification_messages_on_device_id on push_notification_messages push_notification_messages (cost=0.42..81.41 rows=25 width=89) (actual time=0.024..1.586 rows=478 loops=2)

  • Index Cond: (push_notification_messages.device_id = devices.id)
  • Filter: ((push_notification_messages.pushed_at IS NOT NULL) AND (push_notification_messages.body IS NOT NULL))
  • Buffers: shared hit=957
49. 0.044 6.244 ↓ 33.0 33 1

Subquery Scan on *SELECT* 11 (cost=1,299.97..1,367.64 rows=1 width=132) (actual time=5.695..6.244 rows=33 loops=1)

  • Buffers: shared hit=489
50. 0.205 6.200 ↓ 33.0 33 1

Nested Loop (cost=1,299.97..1,367.63 rows=1 width=112) (actual time=5.693..6.2 rows=33 loops=1)

  • Buffers: shared hit=489
51. 0.287 5.929 ↓ 33.0 33 1

Bitmap Heap Scan on actions actions_2 (cost=1,299.83..1,359.41 rows=1 width=138) (actual time=5.666..5.929 rows=33 loops=1)

  • Filter: ((actions_2.tag)::text = ANY ('{renewal_send_provider_request,renewal_update_patient}'::text[]))
  • Buffers: shared hit=423
52. 0.105 5.642 ↓ 0.0 0 1

BitmapAnd (cost=1,299.83..1,299.83 rows=15 width=0) (actual time=5.641..5.642 rows=0 loops=1)

  • Buffers: shared hit=390
53. 0.419 0.419 ↓ 1.4 1,845 1

Bitmap Index Scan on index_actions_on_user_id (cost=0..38.37 rows=1,325 width=0) (actual time=0.418..0.419 rows=1,845 loops=1)

  • Index Cond: (actions_2.user_id = 30815)
  • Buffers: shared hit=10
54. 5.118 5.118 ↓ 1.1 27,057 1

Bitmap Index Scan on index_actions_on_resource_type (cost=0..1,261.21 rows=23,571 width=0) (actual time=5.117..5.118 rows=27,057 loops=1)

  • Index Cond: ((actions_2.resource_type)::text = 'RenewalRequest'::text)
  • Buffers: shared hit=380
55. 0.066 0.066 ↑ 1.0 1 33

Index Scan using task_thresholds_pkey on task_thresholds task_thresholds (cost=0.15..8.17 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=33)

  • Index Cond: (actions_2.task_threshold_id = task_thresholds.id)
  • Buffers: shared hit=66
56. 0.056 0.056 ↓ 2.5 5 1

Index Scan using index_review_scores_on_user_id on review_scores review_scores (cost=0.29..10.3 rows=2 width=132) (actual time=0.027..0.056 rows=5 loops=1)

  • Index Cond: (review_scores.user_id = 30815)
  • Buffers: shared hit=7
57. 1.698 1.760 ↑ 1.1 300 1

Bitmap Heap Scan on sleeps sleeps (cost=11.07..1,199.2 rows=342 width=132) (actual time=0.095..1.76 rows=300 loops=1)

  • Buffers: shared hit=246
58. 0.062 0.062 ↑ 1.1 300 1

Bitmap Index Scan on index_sleeps_on_user_id (cost=0..10.99 rows=342 width=0) (actual time=0.062..0.062 rows=300 loops=1)

  • Index Cond: (sleeps.user_id = 30815)
  • Buffers: shared hit=4
59. 4.293 4.293 ↓ 6.3 426 1

Index Scan using index_sms_messages_on_user_id on sms_messages sms_messages (cost=0.43..231.44 rows=68 width=132) (actual time=0.051..4.293 rows=426 loops=1)

  • Index Cond: (sms_messages.user_id = 30815)
  • Buffers: shared hit=401
60. 7.041 43.894 ↓ 1.0 5,950 1

Subquery Scan on *SELECT* 15 (cost=0.43..18,370.78 rows=5,730 width=132) (actual time=0.029..43.894 rows=5,950 loops=1)

  • Buffers: shared hit=4166
61. 36.853 36.853 ↓ 1.0 5,950 1

Index Scan using index_state_transitions_on_user_id on state_transitions state_transitions (cost=0.43..18,313.48 rows=5,730 width=106) (actual time=0.027..36.853 rows=5,950 loops=1)

  • Index Cond: (state_transitions.user_id = 30815)
  • Filter: (((state_transitions.resource_type)::text <> 'Delivery'::text) OR (state_transitions.resource_id <> ALL ('{100,200}'::integer[])))
  • Buffers: shared hit=4166
62. 0.037 1.111 ↓ 0.0 0 1

Nested Loop (cost=1.14..77.2 rows=1 width=132) (actual time=1.111..1.111 rows=0 loops=1)

  • Buffers: shared hit=255
63. 0.066 0.599 ↓ 5.0 25 1

Nested Loop (cost=0.71..61.71 rows=5 width=76) (actual time=0.093..0.599 rows=25 loops=1)

  • Buffers: shared hit=123
64. 0.183 0.183 ↓ 5.0 25 1

Index Only Scan using index_phone_calls_users_on_user_id_and_phone_call_id on phone_calls_users phone_calls_users (cost=0.29..19.51 rows=5 width=8) (actual time=0.054..0.183 rows=25 loops=1)

  • Index Cond: (phone_calls_users.user_id = 30815)
  • Buffers: shared hit=23
65. 0.350 0.350 ↑ 1.0 1 25

Index Scan using phone_calls_pkey on phone_calls phone_calls_1 (cost=0.42..8.44 rows=1 width=68) (actual time=0.014..0.014 rows=1 loops=25)

  • Index Cond: (phone_calls_1.id = phone_calls_users.phone_call_id)
  • Buffers: shared hit=100
66. 0.475 0.475 ↓ 0.0 0 25

Index Scan using index_phone_call_legs_on_phone_call_id on phone_call_legs phone_call_legs_1 (cost=0.43..1.4 rows=1 width=39) (actual time=0.019..0.019 rows=0 loops=25)

  • Index Cond: (phone_call_legs_1.phone_call_id = phone_calls_1.id)
  • Filter: ((phone_call_legs_1.kind)::text = 'voicemail'::text)
  • Buffers: shared hit=132
67.          

SubPlan (forNested Loop)

68. 0.000 0.000 ↓ 0.0 0 0

Aggregate (cost=8.44..8.45 rows=1 width=32) (never executed)

69. 0.000 0.000 ↓ 0.0 0 0

Index Scan using index_phone_call_dispositions_on_phone_call_leg_id on phone_call_dispositions phone_call_dispositions_1 (cost=0.42..8.44 rows=1 width=4) (never executed)

  • Index Cond: (phone_call_dispositions_1.phone_call_leg_id = phone_call_legs_1.id)
70. 1.341 7.023 ↓ 1.2 1,165 1

Subquery Scan on *SELECT* 17 (cost=19.96..3,452.81 rows=972 width=114) (actual time=0.28..7.023 rows=1,165 loops=1)

  • Buffers: shared hit=857
71. 5.540 5.682 ↓ 1.2 1,165 1

Bitmap Heap Scan on workflow_events workflow_events (cost=19.96..3,443.09 rows=972 width=106) (actual time=0.278..5.682 rows=1,165 loops=1)

  • Buffers: shared hit=857
72. 0.142 0.142 ↓ 1.2 1,165 1

Bitmap Index Scan on index_workflow_events_on_user_id (cost=0..19.72 rows=972 width=0) (actual time=0.142..0.142 rows=1,165 loops=1)

  • Index Cond: (workflow_events.user_id = 30815)
  • Buffers: shared hit=6
73. 6.240 6.240 ↑ 1.3 852 1

Index Scan using index_wundercom_messages_on_user_id on wundercom_messages wundercom_messages (cost=0.42..3,321.38 rows=1,089 width=132) (actual time=0.034..6.24 rows=852 loops=1)

  • Index Cond: (wundercom_messages.user_id = 30815)
  • Buffers: shared hit=830