explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ul9v : Optimization for: Optimization for: plan #u1c6; plan #iTIi

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 203.880 19,698.154 ↓ 49.2 347,821 1

Unique (cost=426,052.56..426,529.51 rows=7,066 width=158) (actual time=19,323.430..19,698.154 rows=347,821 loops=1)

2. 896.736 19,494.274 ↓ 49.2 347,821 1

Sort (cost=426,052.56..426,070.22 rows=7,066 width=158) (actual time=19,323.426..19,494.274 rows=347,821 loops=1)

  • Sort Key: v.visit_date_time, ((v.visit_date_time)::date), p.name, (CASE WHEN (v.arrival_date_time IS NOT NULL) THEN (min(e.encounter_start) - v.arrival_date_time) ELSE NULL::interval END), ((v.arrival_date_time)::time without time zone), (CASE WHEN (v.arrival_date_time IS NOT NULL) THEN (v.visit_date_time - v.arrival_date_time) ELSE NULL::interval END), ((v.visit_date_time)::time without time zone), (CASE WHEN (min(e.encounter_start) IS NOT NULL) THEN (min(e.encounter_start) - v.visit_date_time) ELSE NULL::interval END), ((a.appointment_date)::time without time zone), (CASE WHEN (a.appointment_date IS NOT NULL) THEN (min(e.encounter_start) - a.appointment_date) ELSE NULL::interval END), (CASE WHEN ((v.visit_date_time)::date = (min(e.encounter_start))::date) THEN min(e.encounter_start) ELSE NULL::timestamp without time zone END), (CASE WHEN (v.checkout_date_time IS NOT NULL) THEN (v.checkout_date_time - min(e.encounter_start)) ELSE NULL::interval END), ((v.checkout_date_time)::time without time zone), (CASE WHEN (((v.visit_date_time)::date = (min(e.encounter_start))::date) AND ((v.visit_date_time)::date = (max(e.encounter_end))::date)) THEN (max(e.encounter_end) - min(e.encounter_start)) ELSE NULL::interval END), (CASE WHEN ((v.visit_date_time)::date = (max(e.encounter_end))::date) THEN max(e.encounter_end) ELSE NULL::timestamp without time zone END), v.checkout_date_time, v.visit_id, p.pc_id, v.clinic_id, addr.state_id, addr.district_id, f.facility_name, st.state_name, dt.district_name, rs.service_id, rs.service_name
  • Sort Method: external merge Disk: 96296kB
3. 1,827.935 18,597.538 ↓ 49.2 347,821 1

HashAggregate (cost=425,123.85..425,600.80 rows=7,066 width=158) (actual time=17,805.342..18,597.538 rows=347,821 loops=1)

  • Group Key: v.visit_date_time, p.name, a.appointment_date, v.arrival_date_time, v.checkout_date_time, v.visit_id, p.pc_id, v.clinic_id, addr.state_id, addr.district_id, f.facility_name, st.state_name, dt.district_name, rs.service_id
4. 216.421 16,769.603 ↓ 101.4 716,239 1

Hash Join (cost=107,167.61..424,646.89 rows=7,066 width=158) (actual time=11,833.042..16,769.603 rows=716,239 loops=1)

  • Hash Cond: (addr.facility_id = f.facility_id)
5. 134.086 16,553.155 ↓ 101.4 716,239 1

Merge Join (cost=107,166.05..424,548.17 rows=7,066 width=143) (actual time=11,832.995..16,553.155 rows=716,239 loops=1)

  • Merge Cond: (st.state_id = addr.state_id)
6. 0.022 0.033 ↑ 2.7 6 1

Sort (cost=1.48..1.52 rows=16 width=13) (actual time=0.025..0.033 rows=6 loops=1)

  • Sort Key: st.state_id
  • Sort Method: quicksort Memory: 25kB
7. 0.011 0.011 ↑ 1.0 16 1

Seq Scan on ref_co_state st (cost=0.00..1.16 rows=16 width=13) (actual time=0.004..0.011 rows=16 loops=1)

8. 396.166 16,419.036 ↓ 101.4 716,239 1

Materialize (cost=107,164.55..431,527.62 rows=7,066 width=134) (actual time=11,832.957..16,419.036 rows=716,239 loops=1)

9. 2,416.533 16,022.870 ↓ 101.4 716,239 1

Nested Loop (cost=107,164.55..431,509.96 rows=7,066 width=134) (actual time=11,832.950..16,022.870 rows=716,239 loops=1)

  • Join Filter: (addr.facility_id = v.clinic_id)
  • Rows Removed by Join Filter: 15047668
10. 0.138 0.697 ↓ 1.1 29 1

Nested Loop (cost=0.28..19.01 rows=26 width=21) (actual time=0.046..0.697 rows=29 loops=1)

11. 0.124 0.124 ↓ 1.1 29 1

Index Scan using mst_am_facility_address_state_id_district_id_idx on mst_am_facility_address addr (cost=0.14..6.53 rows=26 width=12) (actual time=0.036..0.124 rows=29 loops=1)

12. 0.435 0.435 ↑ 1.0 1 29

Index Scan using ref_co_district_pkey on ref_co_district dt (cost=0.14..0.47 rows=1 width=13) (actual time=0.009..0.015 rows=1 loops=29)

  • Index Cond: (district_id = addr.district_id)
13. 2,670.024 13,605.640 ↓ 81.8 543,583 29

Materialize (cost=107,164.27..428,916.78 rows=6,643 width=113) (actual time=35.102..469.160 rows=543,583 loops=29)

14. 82.067 10,935.616 ↓ 81.8 543,583 1

Nested Loop Left Join (cost=107,164.27..428,883.57 rows=6,643 width=113) (actual time=1,017.829..10,935.616 rows=543,583 loops=1)

15. 694.547 10,309.966 ↓ 81.8 543,583 1

Nested Loop (cost=107,163.85..425,698.36 rows=6,643 width=113) (actual time=1,017.816..10,309.966 rows=543,583 loops=1)

16. 4,685.224 6,897.504 ↓ 81.8 543,583 1

Hash Join (cost=107,163.42..422,589.83 rows=6,643 width=93) (actual time=1,017.774..6,897.504 rows=543,583 loops=1)

  • Hash Cond: ((e.visit_id = v.visit_id) AND ((e.encounter_end)::date = (v.visit_date_time)::date))
17. 1,194.844 1,194.844 ↑ 1.0 4,180,438 1

Seq Scan on trx_pm_visit_encounter e (cost=0.00..252,618.59 rows=4,182,759 width=24) (actual time=0.025..1,194.844 rows=4,180,438 loops=1)

18. 170.742 1,017.436 ↑ 1.6 354,194 1

Hash (cost=98,658.80..98,658.80 rows=566,975 width=77) (actual time=1,017.436..1,017.436 rows=354,194 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 39101kB
19. 584.950 846.694 ↑ 1.6 354,194 1

Hash Join (cost=4.40..98,658.80 rows=566,975 width=77) (actual time=0.081..846.694 rows=354,194 loops=1)

  • Hash Cond: (v.service_id = rs.service_id)
20. 261.692 261.692 ↑ 1.0 1,713,678 1

Seq Scan on trx_pm_patient_visit v (cost=0.00..86,291.20 rows=1,784,920 width=56) (actual time=0.003..261.692 rows=1,713,678 loops=1)

21. 0.008 0.052 ↑ 1.0 27 1

Hash (cost=4.06..4.06 rows=27 width=25) (actual time=0.052..0.052 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
22. 0.044 0.044 ↑ 1.0 27 1

Seq Scan on ref_pm_service rs (cost=0.00..4.06 rows=27 width=25) (actual time=0.006..0.044 rows=27 loops=1)

  • Filter: (discipline = ANY ('{2,3}'::integer[]))
  • Rows Removed by Filter: 58
23. 2,717.915 2,717.915 ↑ 1.0 1 543,583

Index Scan using mst_pm_patient_patient_id_idx on mst_pm_patient p (cost=0.42..0.46 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=543,583)

  • Index Cond: (patient_id = v.patient_id)
24. 543.583 543.583 ↓ 0.0 0 543,583

Index Scan using mst_pm_appointment_pkey on mst_pm_appointment a (cost=0.42..0.47 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=543,583)

  • Index Cond: (v.appointment_id = appointment_id)
25. 0.014 0.027 ↓ 1.1 28 1

Hash (cost=1.25..1.25 rows=25 width=23) (actual time=0.027..0.027 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
26. 0.013 0.013 ↓ 1.1 28 1

Seq Scan on mst_am_facility f (cost=0.00..1.25 rows=25 width=23) (actual time=0.004..0.013 rows=28 loops=1)