explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iTIi : Optimization for: plan #u1c6

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 191.944 15,824.806 ↓ 49.2 347,821 1

Unique (cost=426,052.56..426,529.51 rows=7,066 width=158) (actual time=15,471.760..15,824.806 rows=347,821 loops=1)

2. 862.914 15,632.862 ↓ 49.2 347,821 1

Sort (cost=426,052.56..426,070.22 rows=7,066 width=158) (actual time=15,471.755..15,632.862 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,720.421 14,769.948 ↓ 49.2 347,821 1

HashAggregate (cost=425,123.85..425,600.80 rows=7,066 width=158) (actual time=13,988.272..14,769.948 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. 190.116 13,049.527 ↓ 101.4 716,239 1

Hash Join (cost=107,167.61..424,646.89 rows=7,066 width=158) (actual time=8,751.370..13,049.527 rows=716,239 loops=1)

  • Hash Cond: (addr.facility_id = f.facility_id)
5. 117.446 12,859.382 ↓ 101.4 716,239 1

Merge Join (cost=107,166.05..424,548.17 rows=7,066 width=143) (actual time=8,751.329..12,859.382 rows=716,239 loops=1)

  • Merge Cond: (st.state_id = addr.state_id)
6. 0.015 0.023 ↑ 2.7 6 1

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

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

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

8. 366.104 12,741.913 ↓ 101.4 716,239 1

Materialize (cost=107,164.55..431,527.62 rows=7,066 width=134) (actual time=8,751.300..12,741.913 rows=716,239 loops=1)

9. 2,008.866 12,375.809 ↓ 101.4 716,239 1

Nested Loop (cost=107,164.55..431,509.96 rows=7,066 width=134) (actual time=8,751.296..12,375.809 rows=716,239 loops=1)

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

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

11. 0.104 0.104 ↓ 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.006..0.104 rows=29 loops=1)

12. 1.566 1.566 ↑ 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.028..0.054 rows=1 loops=29)

  • Index Cond: (district_id = addr.district_id)
13. 2,320.237 10,365.151 ↓ 81.8 543,583 29

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

14. 363.744 8,044.914 ↓ 81.8 543,583 1

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

15. 495.847 7,681.170 ↓ 81.8 543,583 1

Nested Loop (cost=107,163.85..425,698.36 rows=6,643 width=113) (actual time=989.421..7,681.170 rows=543,583 loops=1)

16. 3,934.412 5,554.574 ↓ 81.8 543,583 1

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

  • Hash Cond: ((e.visit_id = v.visit_id) AND ((e.encounter_end)::date = (v.visit_date_time)::date))
17. 630.918 630.918 ↑ 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.002..630.918 rows=4,180,438 loops=1)

18. 161.397 989.244 ↑ 1.6 354,194 1

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

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

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

  • Hash Cond: (v.service_id = rs.service_id)
20. 237.234 237.234 ↑ 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.002..237.234 rows=1,713,678 loops=1)

21. 0.011 0.051 ↑ 1.0 27 1

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

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

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

  • Filter: (discipline = ANY ('{2,3}'::integer[]))
  • Rows Removed by Filter: 58
23. 1,630.749 1,630.749 ↑ 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.003..0.003 rows=1 loops=543,583)

  • Index Cond: (patient_id = v.patient_id)
24. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=543,583)

  • Index Cond: (v.appointment_id = appointment_id)
25. 0.015 0.029 ↓ 1.1 28 1

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

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

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