explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 189.200 15,661.682 ↓ 49.2 347,834 1

Unique (cost=426,052.56..426,529.51 rows=7,066 width=158) (actual time=15,317.658..15,661.682 rows=347,834 loops=1)

2. 805.789 15,472.482 ↓ 49.2 347,834 1

Sort (cost=426,052.56..426,070.22 rows=7,066 width=158) (actual time=15,317.653..15,472.482 rows=347,834 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: 96304kB
3. 1,672.476 14,666.693 ↓ 49.2 347,834 1

HashAggregate (cost=425,123.85..425,600.80 rows=7,066 width=158) (actual time=13,911.491..14,666.693 rows=347,834 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. 191.654 12,994.217 ↓ 101.4 716,278 1

Hash Join (cost=107,167.61..424,646.89 rows=7,066 width=158) (actual time=8,689.218..12,994.217 rows=716,278 loops=1)

  • Hash Cond: (addr.facility_id = f.facility_id)
5. 117.850 12,802.543 ↓ 101.4 716,278 1

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

  • Merge Cond: (st.state_id = addr.state_id)
6. 0.018 0.025 ↑ 2.7 6 1

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

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

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

8. 357.082 12,684.668 ↓ 101.4 716,278 1

Materialize (cost=107,164.55..431,527.62 rows=7,066 width=134) (actual time=8,689.147..12,684.668 rows=716,278 loops=1)

9. 2,082.220 12,327.586 ↓ 101.4 716,278 1

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

  • Join Filter: (addr.facility_id = v.clinic_id)
  • Rows Removed by Join Filter: 15048412
10. 0.132 0.594 ↓ 1.1 29 1

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

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

12. 0.377 0.377 ↑ 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.008..0.013 rows=1 loops=29)

  • Index Cond: (district_id = addr.district_id)
13. 2,278.118 10,244.772 ↓ 81.8 543,610 29

Materialize (cost=107,164.27..428,916.78 rows=6,643 width=113) (actual time=31.931..353.268 rows=543,610 loops=29)

14. 376.730 7,966.654 ↓ 81.8 543,610 1

Nested Loop Left Join (cost=107,164.27..428,883.57 rows=6,643 width=113) (actual time=925.871..7,966.654 rows=543,610 loops=1)

15. 552.691 7,589.924 ↓ 81.8 543,610 1

Nested Loop (cost=107,163.85..425,698.36 rows=6,643 width=113) (actual time=925.862..7,589.924 rows=543,610 loops=1)

16. 3,788.856 5,406.403 ↓ 81.8 543,610 1

Hash Join (cost=107,163.42..422,589.83 rows=6,643 width=93) (actual time=925.825..5,406.403 rows=543,610 loops=1)

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

18. 150.334 925.564 ↑ 1.6 354,197 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 39101kB
19. 549.433 775.230 ↑ 1.6 354,197 1

Hash Join (cost=4.40..98,658.80 rows=566,975 width=77) (actual time=0.052..775.230 rows=354,197 loops=1)

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

21. 0.008 0.033 ↑ 1.0 27 1

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

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

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

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

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,610)

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

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,610)

  • Index Cond: (v.appointment_id = appointment_id)
25. 0.007 0.020 ↓ 1.1 28 1

Hash (cost=1.25..1.25 rows=25 width=23) (actual time=0.020..0.020 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)

Planning time : 4.215 ms
Execution time : 15,807.142 ms