explain.depesz.com

PostgreSQL's explain analyze made readable

Result: z3sr

Settings
# exclusive inclusive rows x rows loops node
1. 41.213 675.873 ↑ 1.2 15,875 1

Sort (cost=129,426.88..129,473.01 rows=18,451 width=228) (actual time=666.272..675.873 rows=15,875 loops=1)

  • Sort Key: event_stream.unix DESC, event_stream.event_type
  • Sort Method: quicksort Memory: 10868kB
  • Buffers: shared hit=69958 read=398
2. 56.263 634.660 ↑ 1.2 15,875 1

Subquery Scan on event_stream (cost=0.86..128,119.5 rows=18,451 width=228) (actual time=0.233..634.66 rows=15,875 loops=1)

  • Buffers: shared hit=69952 read=398
3. 17.618 578.397 ↑ 1.2 15,875 1

Append (cost=0.86..127,658.22 rows=18,451 width=132) (actual time=0.143..578.397 rows=15,875 loops=1)

  • Buffers: shared hit=69952 read=398
4. 6.734 17.483 ↓ 12.0 985 1

Nested Loop (cost=0.86..1,248.62 rows=82 width=132) (actual time=0.142..17.483 rows=985 loops=1)

  • Buffers: shared hit=4584
5. 4.008 4.008 ↓ 11.7 963 1

Index Scan using index_comments_on_user_id on comments comments (cost=0.43..269.66 rows=82 width=274) (actual time=0.042..4.008 rows=963 loops=1)

  • Index Cond: (comments.user_id = 10460)
  • Buffers: shared hit=882
6. 6.741 6.741 ↓ 0.0 0 963

Index Scan using index_actions_on_resource_id on actions actions (cost=0.43..11.92 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=963)

  • Index Cond: (actions.resource_id = comments.id)
  • Filter: ((actions.resource_type)::text = 'Comment'::text)
  • Buffers: shared hit=3702
7. 5.126 24.008 ↑ 1.1 392 1

Hash Join (cost=575.23..2,257.93 rows=439 width=132) (actual time=17.478..24.008 rows=392 loops=1)

  • Buffers: shared hit=714
8. 1.610 1.703 ↑ 1.1 392 1

Bitmap Heap Scan on email_messages email_messages (cost=16.37..1,695.73 rows=439 width=625) (actual time=0.141..1.703 rows=392 loops=1)

  • Buffers: shared hit=393
9. 0.002 0.093 ↓ 0.0 0 1

BitmapOr (cost=16.37..16.37 rows=439 width=0) (actual time=0.093..0.093 rows=0 loops=1)

  • Buffers: shared hit=8
10. 0.073 0.073 ↑ 1.1 392 1

Bitmap Index Scan on index_email_messages_on_user_id (cost=0..11.72 rows=439 width=0) (actual time=0.073..0.073 rows=392 loops=1)

  • Index Cond: (email_messages.user_id = 10460)
  • Buffers: shared hit=5
11. 0.018 0.018 ↓ 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.018..0.018 rows=0 loops=1)

  • Index Cond: (email_messages.subject_user_id = 10460)
  • Buffers: shared hit=3
12. 8.564 17.179 ↓ 1.0 11,570 1

Hash (cost=414.49..414.49 rows=11,549 width=27) (actual time=17.178..17.179 rows=11,570 loops=1)

  • Buffers: shared hit=299
13. 8.615 8.615 ↓ 1.0 11,570 1

Seq Scan on clinics clinics (cost=0..414.49 rows=11,549 width=27) (actual time=0.017..8.615 rows=11,570 loops=1)

  • Buffers: shared hit=299
14. 13.077 492.676 ↑ 1.3 11,168 1

Subquery Scan on *SELECT* 3 (cost=1,000.99..97,399.6 rows=14,408 width=132) (actual time=8.328..492.676 rows=11,168 loops=1)

  • Buffers: shared hit=54092 read=398
15. 0.000 479.599 ↑ 1.3 11,168 1

Gather (cost=1,000.99..97,255.52 rows=14,408 width=85) (actual time=8.327..479.599 rows=11,168 loops=1)

  • Buffers: shared hit=54092 read=398
16. 385.807 503.170 ↑ 1.5 5,584 2

Nested Loop (cost=0.99..94,770.23 rows=8,475 width=85) (actual time=4.602..503.17 rows=5,584 loops=2)

  • Filter: (((events.resource_type)::text <> 'Delivery'::text) OR ((deliveries.status)::text <> 'placeholder'::text))
  • Buffers: shared hit=54092 read=398
17. 117.299 117.299 ↑ 1.5 5,584 2

Index Scan using index_events_on_user_id_and_created_at on events events (cost=0.56..45,428.55 rows=8,475 width=322) (actual time=4.514..117.299 rows=5,584 loops=2)

  • Index Cond: (events.user_id = 10460)
  • Filter: (((events.resource_type)::text <> 'Employee'::text) AND ((events.event_type)::text = ANY ('{create,update,rph_clarification_init,rph_clarification_success,rph_clarification_cancel,rph_clarification_manual_fail,rph_clarification_attempt,rph_clarification_timeout,rph_clarification_update}'::text[])))
  • Buffers: shared hit=9552 read=74
18. 0.064 0.064 ↑ 1.0 1 11,168

Index Scan using deliveries_pkey on deliveries deliveries (cost=0.43..5.8 rows=1 width=13) (actual time=0.064..0.064 rows=1 loops=11,168)

  • Index Cond: (events.resource_id = deliveries.id)
  • Buffers: shared hit=44160 read=324
19. 0.004 0.210 ↓ 2.0 2 1

Subquery Scan on *SELECT* 4 (cost=9.59..9.63 rows=1 width=132) (actual time=0.173..0.21 rows=2 loops=1)

  • Buffers: shared hit=9
20. 0.054 0.206 ↓ 2.0 2 1

Aggregate (cost=9.59..9.62 rows=1 width=588) (actual time=0.171..0.206 rows=2 loops=1)

  • Buffers: shared hit=9
21. 0.080 0.152 ↓ 22.0 22 1

Sort (cost=9.59..9.59 rows=1 width=508) (actual time=0.128..0.152 rows=22 loops=1)

  • Sort Key: incidents.id
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=9
22. 0.032 0.072 ↓ 22.0 22 1

Nested Loop (cost=0.29..9.58 rows=1 width=508) (actual time=0.024..0.072 rows=22 loops=1)

  • Buffers: shared hit=6
23. 0.020 0.020 ↓ 2.0 2 1

Index Scan using index_incidents_on_user_id on incidents incidents (cost=0.29..8.3 rows=1 width=480) (actual time=0.013..0.02 rows=2 loops=1)

  • Index Cond: (incidents.user_id = 10460)
  • Buffers: shared hit=4
24. 0.020 0.020 ↑ 1.0 11 2

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

  • Buffers: shared hit=2
25. 0.099 0.118 ↑ 73.0 2 1

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

  • Filter: (phone_call_legacies.is_voicemail IS TRUE)
  • Buffers: shared hit=26
26. 0.019 0.019 ↑ 8.1 36 1

Bitmap Index Scan on index_phone_call_legacies_on_user_id (cost=0..14.6 rows=292 width=0) (actual time=0.018..0.019 rows=36 loops=1)

  • Index Cond: (phone_call_legacies.user_id = 10460)
  • Buffers: shared hit=3
27. 0.001 0.009 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
28. 0.008 0.008 ↓ 0.0 0 1

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

  • Filter: (partnerships_secure_requests.user_id = 10460)
  • Buffers: shared hit=1
29. 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)
30. 0.549 1.333 ↓ 4.5 36 1

Nested Loop (cost=1.69..168.92 rows=8 width=132) (actual time=0.078..1.333 rows=36 loops=1)

  • Buffers: shared hit=459
31. 0.089 0.676 ↓ 4.5 36 1

Nested Loop (cost=1.27..165.01 rows=8 width=469) (actual time=0.04..0.676 rows=36 loops=1)

  • Buffers: shared hit=315
32. 0.086 0.299 ↓ 4.5 36 1

Nested Loop (cost=0.84..97.45 rows=8 width=465) (actual time=0.025..0.299 rows=36 loops=1)

  • Buffers: shared hit=170
33. 0.105 0.105 ↓ 4.5 36 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.012..0.105 rows=36 loops=1)

  • Index Cond: (phone_call_dispositions.user_id = 10460)
  • Buffers: shared hit=39
34. 0.108 0.108 ↑ 1.0 1 36

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=1 loops=36)

  • Index Cond: (spoke_to_users.id = phone_call_dispositions.spoke_with_id)
  • Buffers: shared hit=131
35. 0.288 0.288 ↑ 1.0 1 36

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.008..0.008 rows=1 loops=36)

  • Index Cond: (phone_call_dispositions.phone_call_leg_id = phone_call_legs.id)
  • Buffers: shared hit=145
36. 0.108 0.108 ↑ 1.0 1 36

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

  • Index Cond: (phone_call_legs.phone_call_id = phone_calls.id)
  • Buffers: shared hit=144
37. 0.018 0.018 ↓ 0.0 0 1

Index Scan using index_attachments_on_user_id on attachments attachments (cost=0.29..14.32 rows=1 width=132) (actual time=0.017..0.018 rows=0 loops=1)

  • Index Cond: (attachments.user_id = 10460)
  • Filter: (attachments.provider_user_id IS NOT NULL)
  • Buffers: shared hit=5
38. 0.247 0.247 ↓ 3.0 24 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.028..0.247 rows=24 loops=1)

  • Index Cond: (provider_wundercom_messages.user_id = 10460)
  • Buffers: shared hit=22
39. 0.001 0.030 ↓ 0.0 0 1

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

  • Buffers: shared hit=2
40. 0.002 0.029 ↓ 0.0 0 1

Aggregate (cost=90.2..90.76 rows=13 width=205) (actual time=0.028..0.029 rows=0 loops=1)

  • Buffers: shared hit=2
41. 0.013 0.027 ↓ 0.0 0 1

Sort (cost=90.2..90.24 rows=13 width=97) (actual time=0.026..0.027 rows=0 loops=1)

  • Sort Key: push_notification_messages.grouped_message_id, push_notification_messages.body, push_notification_messages.title
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2
42. 0.001 0.014 ↓ 0.0 0 1

Nested Loop (cost=0.71..89.96 rows=13 width=97) (actual time=0.014..0.014 rows=0 loops=1)

  • Buffers: shared hit=2
43. 0.013 0.013 ↓ 0.0 0 1

Index Scan using index_devices_on_user_id on devices devices (cost=0.29..8.3 rows=1 width=12) (actual time=0.012..0.013 rows=0 loops=1)

  • Index Cond: (devices.user_id = 10460)
  • Buffers: shared hit=2
44. 0.000 0.000 ↓ 0.0 0 0

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) (never executed)

  • 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))
45. 0.007 0.621 ↓ 4.0 4 1

Subquery Scan on *SELECT* 11 (cost=0.57..128.31 rows=1 width=132) (actual time=0.06..0.621 rows=4 loops=1)

  • Buffers: shared hit=285
46. 0.033 0.614 ↓ 4.0 4 1

Nested Loop (cost=0.57..128.3 rows=1 width=112) (actual time=0.058..0.614 rows=4 loops=1)

  • Buffers: shared hit=285
47. 0.561 0.561 ↓ 4.0 4 1

Index Scan using index_actions_on_user_id on actions actions_2 (cost=0.43..120.07 rows=1 width=138) (actual time=0.03..0.561 rows=4 loops=1)

  • Index Cond: (actions_2.user_id = 10460)
  • Filter: (((actions_2.tag)::text = ANY ('{renewal_send_provider_request,renewal_update_patient}'::text[])) AND ((actions_2.resource_type)::text = 'RenewalRequest'::text))
  • Buffers: shared hit=277
48. 0.020 0.020 ↑ 1.0 1 4

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

  • Index Cond: (actions_2.task_threshold_id = task_thresholds.id)
  • Buffers: shared hit=8
49. 0.079 0.079 ↓ 4.0 8 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.03..0.079 rows=8 loops=1)

  • Index Cond: (review_scores.user_id = 10460)
  • Buffers: shared hit=11
50. 0.739 0.739 ↓ 1.0 173 1

Index Scan using index_sleeps_on_user_id on sleeps sleeps (cost=0.42..601.28 rows=165 width=132) (actual time=0.03..0.739 rows=173 loops=1)

  • Index Cond: (sleeps.user_id = 10460)
  • Buffers: shared hit=122
51. 2.656 2.656 ↓ 7.5 507 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.029..2.656 rows=507 loops=1)

  • Index Cond: (sms_messages.user_id = 10460)
  • Buffers: shared hit=486
52. 1.987 16.427 ↑ 1.4 1,833 1

Subquery Scan on *SELECT* 15 (cost=1,065.04..22,880.68 rows=2,604 width=132) (actual time=1.741..16.427 rows=1,833 loops=1)

  • Buffers: shared hit=8410
53. 0.000 14.440 ↑ 1.4 1,833 1

Gather (cost=1,065.04..22,854.64 rows=2,604 width=106) (actual time=1.739..14.44 rows=1,833 loops=1)

  • Buffers: shared hit=8410
54. 12.173 15.679 ↑ 1.7 916 2

Nested Loop (cost=65.04..21,588.88 rows=1,532 width=106) (actual time=0.543..15.679 rows=916 loops=2)

  • Filter: (((state_transitions.resource_type)::text <> 'Delivery'::text) OR ((deliveries_1.status)::text <> 'placeholder'::text))
  • Buffers: shared hit=8410
55. 2.822 3.499 ↑ 1.7 916 2

Bitmap Heap Scan on state_transitions state_transitions (cost=64.62..9,783.91 rows=1,532 width=87) (actual time=0.453..3.499 rows=916 loops=2)

  • Buffers: shared hit=1077
56. 0.677 0.677 ↑ 1.4 1,833 1

Bitmap Index Scan on index_state_transitions_on_user_id (cost=0..63.97 rows=2,604 width=0) (actual time=0.676..0.677 rows=1,833 loops=1)

  • Index Cond: (state_transitions.user_id = 10460)
  • Buffers: shared hit=9
57. 0.007 0.007 ↑ 1.0 1 1,833

Index Scan using deliveries_pkey on deliveries deliveries_1 (cost=0.43..7.68 rows=1 width=13) (actual time=0.007..0.007 rows=1 loops=1,833)

  • Index Cond: (state_transitions.resource_id = deliveries_1.id)
  • Buffers: shared hit=7330
58. 0.076 0.771 ↓ 2.0 2 1

Nested Loop (cost=1.14..77.2 rows=1 width=132) (actual time=0.137..0.771 rows=2 loops=1)

  • Buffers: shared hit=232
59. 0.046 0.443 ↓ 4.6 23 1

Nested Loop (cost=0.71..61.71 rows=5 width=76) (actual time=0.074..0.443 rows=23 loops=1)

  • Buffers: shared hit=104
60. 0.121 0.121 ↓ 4.6 23 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.042..0.121 rows=23 loops=1)

  • Index Cond: (phone_calls_users.user_id = 10460)
  • Buffers: shared hit=12
61. 0.276 0.276 ↑ 1.0 1 23

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

  • Index Cond: (phone_calls_1.id = phone_calls_users.phone_call_id)
  • Buffers: shared hit=92
62. 0.230 0.230 ↓ 0.0 0 23

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.01..0.01 rows=0 loops=23)

  • 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=122
63.          

SubPlan (forNested Loop)

64. 0.008 0.022 ↑ 1.0 1 2

Aggregate (cost=8.44..8.45 rows=1 width=32) (actual time=0.01..0.011 rows=1 loops=2)

  • Buffers: shared hit=6
65. 0.014 0.014 ↓ 0.0 0 2

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) (actual time=0.007..0.007 rows=0 loops=2)

  • Index Cond: (phone_call_dispositions_1.phone_call_leg_id = phone_call_legs_1.id)
  • Buffers: shared hit=6
66. 0.461 1.673 ↓ 5.9 403 1

Subquery Scan on *SELECT* 17 (cost=4.95..270.71 rows=68 width=114) (actual time=0.082..1.673 rows=403 loops=1)

  • Buffers: shared hit=215
67. 1.167 1.212 ↓ 5.9 403 1

Bitmap Heap Scan on workflow_events workflow_events (cost=4.95..270.03 rows=68 width=106) (actual time=0.08..1.212 rows=403 loops=1)

  • Buffers: shared hit=215
68. 0.045 0.045 ↓ 5.9 403 1

Bitmap Index Scan on index_workflow_events_on_user_id (cost=0..4.94 rows=68 width=0) (actual time=0.044..0.045 rows=403 loops=1)

  • Index Cond: (workflow_events.user_id = 10460)
  • Buffers: shared hit=4
69. 1.681 1.681 ↑ 1.3 336 1

Index Scan using index_wundercom_messages_on_user_id on wundercom_messages wundercom_messages (cost=0.42..1,344.03 rows=435 width=132) (actual time=0.043..1.681 rows=336 loops=1)

  • Index Cond: (wundercom_messages.user_id = 10460)
  • Buffers: shared hit=277