explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pPXd

Settings
# exclusive inclusive rows x rows loops node
1. 5.730 104.628 ↑ 1.0 1 1

Aggregate (cost=963.92..963.93 rows=1 width=120) (actual time=104.628..104.628 rows=1 loops=1)

  • Output: count(patient.id), sum(CASE WHEN ((pc.id = 1) AND (patient.call_center_patient_category_id IS NULL)) THEN 1 ELSE 0 END), sum(CASE WHEN ((patient.call_center_patient_category_id = 2) OR ((patient.call_center_patient_category_id IS NULL) AND (pc.id (...)
  • Buffers: shared hit=125010
2. 1.144 24.054 ↓ 84.0 2,772 1

Nested Loop Left Join (cost=20.54..192.77 rows=33 width=20) (actual time=0.084..24.054 rows=2,772 loops=1)

  • Output: patient.id, pc.id, patient.call_center_patient_category_id, adt."time
  • Buffers: shared hit=32306
3. 1.125 17.366 ↓ 84.0 2,772 1

Nested Loop Left Join (cost=20.12..177.99 rows=33 width=20) (actual time=0.082..17.366 rows=2,772 loops=1)

  • Output: patient.id, patient.call_center_patient_category_id, pc.id, adt."time
  • Buffers: shared hit=23978
4. 3.533 13.469 ↓ 84.0 2,772 1

Nested Loop (cost=19.83..167.42 rows=33 width=12) (actual time=0.077..13.469 rows=2,772 loops=1)

  • Output: patient.id, patient.call_center_patient_category_id, pc.id
  • Join Filter: ((patient.created_at >= campaign.datefrom) AND (patient.created_at <= campaign.dateto))
  • Rows Removed by Join Filter: 14490
  • Buffers: shared hit=18434
5. 0.103 0.648 ↓ 12.3 344 1

Hash Join (cost=19.41..82.32 rows=28 width=24) (actual time=0.068..0.648 rows=344 loops=1)

  • Output: campaign.datefrom, campaign.dateto, spc.id, pc.id
  • Hash Cond: (spc.patient_category_id = pc.id)
  • Buffers: shared hit=493
6. 0.069 0.532 ↓ 12.3 344 1

Nested Loop (cost=18.23..80.76 rows=28 width=24) (actual time=0.047..0.532 rows=344 loops=1)

  • Output: campaign.datefrom, campaign.dateto, spc.id, spc.patient_category_id
  • Buffers: shared hit=492
7. 0.020 0.162 ↓ 14.3 43 1

Nested Loop (cost=17.81..77.52 rows=3 width=24) (actual time=0.042..0.162 rows=43 loops=1)

  • Output: campaign.study_id, campaign.datefrom, campaign.dateto, ccr.study_id
  • Buffers: shared hit=86
8. 0.014 0.042 ↑ 1.0 20 1

HashAggregate (cost=17.52..17.72 rows=20 width=4) (actual time=0.035..0.042 rows=20 loops=1)

  • Output: ccr.study_id
  • Group Key: ccr.study_id
  • Buffers: shared hit=6
9. 0.028 0.028 ↑ 1.0 20 1

Index Only Scan using call_center_role_study_id_idx on public.call_center_role ccr (cost=0.28..17.47 rows=20 width=4) (actual time=0.010..0.028 rows=20 loops=1)

  • Output: ccr.study_id, ccr.user_id
  • Index Cond: (ccr.user_id = 10424)
  • Heap Fetches: 0
  • Buffers: shared hit=6
10. 0.100 0.100 ↓ 2.0 2 20

Index Scan using campaign_study_id_idx on public.campaign (cost=0.29..2.98 rows=1 width=20) (actual time=0.003..0.005 rows=2 loops=20)

  • Output: campaign.study_id, campaign.datefrom, campaign.dateto
  • Index Cond: ((campaign.study_id = ccr.study_id) AND (campaign.study_id IS NOT NULL))
  • Filter: (campaign.patient_qualification_suite IS TRUE)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=80
11. 0.301 0.301 ↑ 1.0 8 43

Index Scan using study_id_patient_category_id_index on public.study_patient_category spc (cost=0.42..1.00 rows=8 width=12) (actual time=0.003..0.007 rows=8 loops=43)

  • Output: spc.order_num, spc.is_new_patient_default, spc.id, spc.study_id, spc.patient_category_id
  • Index Cond: (spc.study_id = campaign.study_id)
  • Buffers: shared hit=406
12. 0.006 0.013 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=4) (actual time=0.013..0.013 rows=8 loops=1)

  • Output: pc.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
13. 0.007 0.007 ↑ 1.0 8 1

Seq Scan on public.patient_category pc (cost=0.00..1.08 rows=8 width=4) (actual time=0.004..0.007 rows=8 loops=1)

  • Output: pc.id
  • Buffers: shared hit=1
14. 9.288 9.288 ↑ 1.5 50 344

Index Scan using patient_study_patient_category_idx on public.patient (cost=0.43..1.94 rows=73 width=20) (actual time=0.002..0.027 rows=50 loops=344)

  • Output: patient.first_name, patient.last_name, patient.email, patient.phone, patient.dob, patient.gender, patient.bmi, patient.created_at, patient.last_action, patient.updated_at, patient.unsubscribed, patient.is_unsubscribed_by_p (...)
  • Index Cond: (patient.study_patient_category_id = spc.id)
  • Buffers: shared hit=17941
15. 2.772 2.772 ↓ 0.0 0 2,772

Index Scan using user_patient_index on public.alert_dismiss_time adt (cost=0.29..0.31 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=2,772)

  • Output: adt."time", adt.id, adt.patient_id, adt.user_id
  • Index Cond: ((adt.user_id = 10432) AND (adt.patient_id = patient.id))
  • Buffers: shared hit=5544
16. 5.544 5.544 ↑ 1.0 1 2,772

Index Only Scan using patient_disposition_patient_study_idx on public.patient_disposition (cost=0.42..0.44 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,772)

  • Output: patient_disposition.patient_id, patient_disposition.study_id
  • Index Cond: (patient_disposition.patient_id = patient.id)
  • Heap Fetches: 0
  • Buffers: shared hit=8328
17.          

SubPlan (forAggregate)

18. 0.000 8.316 ↑ 1.0 1 2,772

Aggregate (cost=2.45..2.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,772)

  • Output: count(*)
  • Buffers: shared hit=10527
19. 8.316 8.316 ↓ 0.0 0 2,772

Index Scan using patient_metadata_patient_id_idx on public.patient_metadata (cost=0.42..2.45 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=2,772)

  • Index Cond: (patient_metadata.patient_id = patient.id)
  • Filter: ((patient_metadata.is_archived IS FALSE) AND ((patient_metadata.meta_key)::text = 'DNQ_REASON'::text) AND ((patient_metadata.meta_data ->> 'waiting_room'::text))::boolean)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=10527
20. 2.772 66.528 ↑ 1.0 1 2,772

Aggregate (cost=20.86..20.87 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=2,772)

  • Output: count(text_message.id)
  • Buffers: shared hit=82177
21. 1.167 63.756 ↑ 1.0 1 2,772

Nested Loop (cost=0.86..20.85 rows=1 width=4) (actual time=0.013..0.023 rows=1 loops=2,772)

  • Output: text_message.id
  • Buffers: shared hit=82177
22. 16.632 16.632 ↑ 1.2 6 2,772

Index Scan using text_message_patient_id_idx on public.text_message (cost=0.43..3.62 rows=7 width=8) (actual time=0.003..0.006 rows=6 loops=2,772)

  • Output: text_message.is_unread, text_message.id, text_message.study_id, text_message.patient_id, text_message.text_message_id, text_message.user_id
  • Index Cond: (text_message.patient_id = patient.id)
  • Buffers: shared hit=20815
23. 45.957 45.957 ↓ 0.0 0 15,319

Index Scan using twilio_text_message_pkey on public.twilio_text_message (cost=0.43..2.45 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=15,319)

  • Output: twilio_text_message.sid, twilio_text_message.date_created, twilio_text_message.date_updated, twilio_text_message.date_sent, twilio_text_message.account_sid, twilio_text_message.twilio_to, twilio_text_message.twilio_from, twili (...)
  • Index Cond: (twilio_text_message.id = text_message.text_message_id)
  • Filter: (((twilio_text_message.date_created > COALESCE(adt."time", '1970-01-01 00:00:00'::timestamp without time zone)) IS TRUE) AND ((twilio_text_message.direction)::text = 'inbound'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=61362
Planning time : 2.825 ms
Execution time : 104.832 ms