explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tPxp

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

HashAggregate (cost=109.03..109.04 rows=1 width=4) (actual rows= loops=)

  • Group Key: pa_requests.id
2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.92..109.03 rows=1 width=4) (actual rows= loops=)

  • Filter: ((pa_requests.pa_message_id IS NULL) OR (pa_messages.message_text IS NULL))
3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.77..100.86 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (((pharmacy_restrictions.phone_number)::text = (pharmacy_contacts.phone_number)::text) OR ((pharmacy_restrictions.location_matcher IS NOT NULL) AND ((pharmacy_contacts.name)::text ~~ (pharmacy_restrictions.location_matcher)::text)))
  • Filter: ((pharmacy_restrictions.id IS NULL) OR (pa_requests.next_step_type_id <> ALL ('{1,41,42,4,45,46}'::integer[])))
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.63..92.68 rows=1 width=278) (actual rows= loops=)

  • Filter: (((pa_requests.next_step_type_id = ANY ('{1,41,42,4,45,46}'::integer[])) AND (pharmacy_contacts.phone_number IS NOT NULL)) OR ((pa_requests.next_step_type_id = ANY ('{3,43,6,44}'::integer[])) AND ((plan_detail_contacts.phone IS NOT NULL) OR (pa_forms.contact_phone IS NOT NULL) OR (pa_actual_form_backups.contact_phone IS NOT NULL))))
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.49..84.50 rows=1 width=160) (actual rows= loops=)

  • Join Filter: (plan_and_pharmacy_queue_drug_ddid_feature_overrides.drug_ddid_id = pa_requests.drug_ddid_id)
  • Filter: (plan_and_pharmacy_queue_drug_ddid_feature_overrides.id IS NULL)
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.34..76.31 rows=1 width=164) (actual rows= loops=)

  • Join Filter: ((((plan_restrictions.phone_number)::text <> ''::text) AND (((plan_restrictions.phone_number)::text = (plan_detail_contacts.phone)::text) OR ((plan_restrictions.phone_number)::text = (pa_forms.contact_phone)::text) OR ((plan_restrictions.phone_number)::text = (pa_actual_form_backups.contact_phone)::text))) OR ((plan_restrictions.location_matcher IS NOT NULL) AND (((plan_detail_contacts.label)::text ~~ (plan_restrictions.location_matcher)::text) OR ((pa_forms.description)::text ~~ (plan_restrictions.location_matcher)::text))))
  • Filter: ((plan_restrictions.id IS NULL) OR (pa_requests.next_step_type_id <> ALL ('{3,43,6,44}'::integer[])))
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5.20..68.12 rows=1 width=998) (actual rows= loops=)

  • Join Filter: (pa_queues_pa_requests.pa_request_id = pa_requests_1.id)
8. 0.000 0.000 ↓ 0.0

Seq Scan on pa_requests pa_requests_1 (cost=0.00..10.75 rows=1 width=4) (actual rows= loops=)

  • Filter: (user_id = 2861)
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.20..57.28 rows=7 width=1,002) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.06..55.90 rows=7 width=958) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.85..41.48 rows=1 width=954) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.72..33.31 rows=1 width=394) (actual rows= loops=)

  • Join Filter: (plan_detail_contacts.id = pa_requests.plan_contact_id)
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.58..25.14 rows=1 width=32) (actual rows= loops=)

  • Filter: ((outcomes.pa_request_id IS NULL) OR ((pa_requests.conversion_type_id = 1) AND (outcome_types.id = 7)))
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..24.60 rows=1 width=44) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..16.42 rows=1 width=36) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Scan using index_pa_requests_on_hub_id on pa_requests (cost=0.14..8.25 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (hub_id = 5)
  • Filter: ((NOT requires_help) AND (NOT is_fax_failure) AND ((NOT is_confirmed_duplicate) OR (is_confirmed_duplicate IS NULL)) AND (snooze_until IS NULL) AND (NOT is_archived) AND (NOT is_shredded) AND (requested_state_at IS NULL) AND (next_step_type_id = 1) AND (next_step_type_id = ANY ('{1,41,42,4,45,46,3,43,6,44}'::integer[])) AND ((next_step_type_id = ANY ('{1,41,42,4,45,46}'::integer[])) OR (next_step_type_id = ANY ('{3,43,6,44}'::integer[]))) AND (next_step_type_id = ANY ('{1,41,42,2,3,43,4,5,6,44,45,46}'::integer[])) AND ((timezone_type_id = 8) OR (timezone_type_id IS NULL) OR ((next_step_type_id = ANY ('{1,41,42,4,45,46}'::integer[])) AND (timezone_type_id = ANY ('{1,2,3,4,5,6,7,8}'::integer[]))) OR ((next_step_type_id = ANY ('{3,43,6,44}'::integer[])) AND (timezone_type_id = ANY ('{1,2,3,4,5,6,7,8}'::integer[]))) OR ((next_step_type_id = ANY ('{2,5}'::integer[])) AND (timezone_type_id = ANY ('{1,2,3,4,5,6,7,8}'::integer[])))))
17. 0.000 0.000 ↓ 0.0

Index Scan using index_pa_queues_pa_requests_on_pa_request_id on pa_queues_pa_requests (cost=0.14..8.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (pa_request_id = pa_requests.id)
  • Filter: (pa_queue_id = 1)
18. 0.000 0.000 ↓ 0.0

Index Scan using index_outcomes_on_pa_request_id on outcomes (cost=0.14..8.16 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (pa_requests.id = pa_request_id)
19. 0.000 0.000 ↓ 0.0

Index Only Scan using outcome_types_pkey on outcome_types (cost=0.14..0.53 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = outcomes.outcome_type_id)
20. 0.000 0.000 ↓ 0.0

Index Scan using index_plan_contacts_on_deleted_at on plan_contacts plan_detail_contacts (cost=0.14..8.16 rows=1 width=370) (actual rows= loops=)

  • Index Cond: (deleted_at IS NULL)
21. 0.000 0.000 ↓ 0.0

Index Scan using forms_pkey on forms pa_forms (cost=0.14..8.15 rows=1 width=568) (actual rows= loops=)

  • Index Cond: (id = pa_requests.form_id)
22. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on pa_request_form_backups pa_form_backups (cost=4.21..14.35 rows=7 width=8) (actual rows= loops=)

  • Recheck Cond: (pa_request_id = pa_requests.id)
23. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on index_pa_request_form_backups_on_pa_request_id (cost=0.00..4.21 rows=7 width=0) (actual rows= loops=)

  • Index Cond: (pa_request_id = pa_requests.id)
24. 0.000 0.000 ↓ 0.0

Index Scan using forms_pkey on forms pa_actual_form_backups (cost=0.14..0.19 rows=1 width=52) (actual rows= loops=)

  • Index Cond: (pa_form_backups.form_id = id)
25. 0.000 0.000 ↓ 0.0

Index Scan using index_call_restrictions_on_call_location_type_id on call_restrictions plan_restrictions (cost=0.14..8.16 rows=1 width=270) (actual rows= loops=)

  • Index Cond: (call_location_type_id = 3)
26. 0.000 0.000 ↓ 0.0

Index Scan using indx_drg_ddid_ftrs_on_all_the_things on drug_ddid_feature_overrides plan_and_pharmacy_queue_drug_ddid_feature_overrides (cost=0.15..8.17 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((drug_ddid_feature_id = 4) AND (hub_id = 5))
27. 0.000 0.000 ↓ 0.0

Index Scan using pharmacies_pkey on pharmacies pharmacy_contacts (cost=0.14..8.16 rows=1 width=270) (actual rows= loops=)

  • Index Cond: (id = pa_requests.pharmacy_id)
28. 0.000 0.000 ↓ 0.0

Index Scan using index_call_restrictions_on_call_location_type_id on call_restrictions pharmacy_restrictions (cost=0.14..8.16 rows=1 width=270) (actual rows= loops=)

  • Index Cond: (call_location_type_id = 1)
29. 0.000 0.000 ↓ 0.0

Index Scan using pa_messages_pkey on pa_messages (cost=0.14..8.16 rows=1 width=520) (actual rows= loops=)

  • Index Cond: (pa_requests.pa_message_id = id)