explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4xgI

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.596 ↓ 0.0 0 1

Hash Left Join (cost=525.63..2,327.11 rows=2,311 width=1,894) (actual time=0.595..0.596 rows=0 loops=1)

  • Hash Cond: ((pw.facility_ids)::text = (facility_d.facility_id)::text)
2. 0.001 0.594 ↓ 0.0 0 1

Hash Left Join (cost=496.86..2,141.23 rows=1,690 width=1,100) (actual time=0.594..0.594 rows=0 loops=1)

  • Hash Cond: ((pw.provider_ids)::text = ((pd_1.provider_id)::character varying(40))::text)
3. 0.001 0.593 ↓ 0.0 0 1

Nested Loop Left Join (cost=48.56..1,591.38 rows=66 width=1,087) (actual time=0.592..0.593 rows=0 loops=1)

  • Join Filter: (((pw.waitlist_status)::character varying(4))::text = (system_code_detail_1.system_code_value)::text)
4. 0.002 0.592 ↓ 0.0 0 1

Nested Loop Left Join (cost=44.24..1,578.84 rows=66 width=1,073) (actual time=0.591..0.592 rows=0 loops=1)

  • Join Filter: (pw.appt_ctgry_id = acd.appt_ctgry_id)
5. 0.001 0.590 ↓ 0.0 0 1

Nested Loop (cost=44.24..1,569.57 rows=66 width=1,041) (actual time=0.590..0.590 rows=0 loops=1)

6. 0.002 0.589 ↓ 0.0 0 1

Nested Loop (cost=43.81..1,160.43 rows=66 width=986) (actual time=0.588..0.589 rows=0 loops=1)

7. 0.001 0.587 ↓ 0.0 0 1

Hash Left Join (cost=43.38..610.36 rows=65 width=979) (actual time=0.587..0.587 rows=0 loops=1)

  • Hash Cond: ((pw.service_id)::text = (sd.service_id)::text)
8. 0.001 0.586 ↓ 0.0 0 1

Hash Left Join (cost=30.23..596.32 rows=65 width=947) (actual time=0.585..0.586 rows=0 loops=1)

  • Hash Cond: (pw.reason_id = ard.reason_id)
9. 0.138 0.585 ↓ 0.0 0 1

Nested Loop (cost=0.56..566.49 rows=65 width=929) (actual time=0.584..0.585 rows=0 loops=1)

10. 0.057 0.057 ↑ 1.0 65 1

Seq Scan on patient_waitlist pw (cost=0.00..7.65 rows=65 width=917) (actual time=0.005..0.057 rows=65 loops=1)

11. 0.390 0.390 ↓ 0.0 0 65

Index Scan using indx_appt_d_app_id on appointments_d appt (cost=0.56..8.59 rows=1 width=21) (actual time=0.006..0.006 rows=0 loops=65)

  • Index Cond: ((appointment_id)::text = (pw.appointment_id)::text)
  • Filter: (eff_end_ts > now())
12. 0.000 0.000 ↓ 0.0 0

Hash (cost=19.85..19.85 rows=785 width=20) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Seq Scan on appt_reason_d ard (cost=0.00..19.85 rows=785 width=20) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.40..11.40 rows=140 width=70) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Seq Scan on service_d sd (cost=0.00..11.40 rows=140 width=70) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_ehr_patient_d on ehr_patient_d pd (cost=0.43..8.45 rows=1 width=14) (never executed)

  • Index Cond: ((patient_id)::text = (pw.patient_id)::text)
  • Filter: (eff_end_ts > now())
17. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_person_d_person_id on person_d pr (cost=0.43..6.19 rows=1 width=69) (never executed)

  • Index Cond: ((person_id)::text = (pd.person_id)::text)
  • Filter: (eff_end_ts > now())
18. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.14 rows=9 width=34) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Seq Scan on appt_ctgry_d acd (cost=0.00..1.09 rows=9 width=34) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Materialize (cost=4.32..11.06 rows=1 width=18) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on system_code_detail system_code_detail_1 (cost=4.32..11.06 rows=1 width=18) (never executed)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '25'::text) AND (eff_end_ts > now()))
  • Filter: (flag1 = 1)
22. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.32 rows=2 width=0) (never executed)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '25'::text) AND (eff_end_ts > now()))
23. 0.000 0.000 ↓ 0.0 0

Hash (cost=384.30..384.30 rows=5,120 width=17) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=310.91..384.30 rows=5,120 width=17) (never executed)

  • Hash Cond: ((system_code_detail.system_code_value)::text = COALESCE((pd_1.gender)::text, 'U'::text))
25. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on system_code_detail (cost=4.71..46.33 rows=24 width=4) (never executed)

  • Recheck Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
  • Filter: (is_active = 'Y'::bpchar)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on system_code_detail_pkey (cost=0.00..4.71 rows=30 width=0) (never executed)

  • Index Cond: (((system_code_id)::text = '200'::text) AND ((system_code_type)::text = '01'::text) AND (eff_end_ts > now()))
27. 0.000 0.000 ↓ 0.0 0

Hash (cost=242.20..242.20 rows=5,120 width=19) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on provider pd_1 (cost=0.00..242.20 rows=5,120 width=19) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Hash (cost=23.82..23.82 rows=396 width=29) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on facility_d (cost=0.00..23.82 rows=396 width=29) (never executed)

  • Filter: ((now() >= eff_bgn_ts) AND (now() <= eff_end_ts))