explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0blk : Optimization for: plan #VnZL

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 59,107.245 ↑ 1.0 1 1

Limit (cost=522,896.90..522,896.91 rows=1 width=158) (actual time=59,107.244..59,107.245 rows=1 loops=1)

2. 638.239 59,107.242 ↑ 17,561.0 1 1

Sort (cost=522,896.90..522,940.81 rows=17,561 width=158) (actual time=59,107.242..59,107.242 rows=1 loops=1)

  • Sort Key: v.visit_date_time
  • Sort Method: top-N heapsort Memory: 25kB
3. 2,840.469 58,469.003 ↓ 70.5 1,237,955 1

HashAggregate (cost=521,623.73..522,809.10 rows=17,561 width=158) (actual time=57,604.298..58,469.003 rows=1,237,955 loops=1)

  • Group 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
4. 6,678.615 55,628.534 ↓ 70.5 1,237,955 1

HashAggregate (cost=519,296.90..520,482.27 rows=17,561 width=158) (actual time=52,935.179..55,628.534 rows=1,237,955 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
5. 886.558 48,949.919 ↓ 190.7 3,349,267 1

Hash Join (cost=11.11..518,111.53 rows=17,561 width=158) (actual time=26,213.280..48,949.919 rows=3,349,267 loops=1)

  • Hash Cond: (addr.facility_id = f.facility_id)
6. 548.151 48,063.341 ↓ 190.7 3,349,267 1

Merge Join (cost=9.55..517,868.50 rows=17,561 width=143) (actual time=26,213.250..48,063.341 rows=3,349,267 loops=1)

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

Index Scan using ref_co_state_pkey on ref_co_state st (cost=0.14..6.38 rows=16 width=13) (actual time=0.003..0.023 rows=6 loops=1)

8. 1,601.913 47,515.167 ↓ 190.7 3,349,267 1

Materialize (cost=7.84..529,152.94 rows=17,561 width=134) (actual time=26,213.226..47,515.167 rows=3,349,267 loops=1)

9. 11,588.813 45,913.254 ↓ 190.7 3,349,267 1

Nested Loop (cost=7.84..529,109.03 rows=17,561 width=134) (actual time=26,213.218..45,913.254 rows=3,349,267 loops=1)

  • Join Filter: (addr.facility_id = v.clinic_id)
  • Rows Removed by Join Filter: 89658518
10. 0.522 1.172 ↓ 1.1 29 1

Nested Loop (cost=0.14..43.95 rows=26 width=21) (actual time=0.034..1.172 rows=29 loops=1)

  • Join Filter: (addr.district_id = dt.district_id)
  • Rows Removed by Join Filter: 3683
11. 0.244 0.244 ↓ 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.013..0.244 rows=29 loops=1)

12. 0.373 0.406 ↓ 1.5 128 29

Materialize (cost=0.00..3.32 rows=88 width=13) (actual time=0.000..0.014 rows=128 loops=29)

13. 0.033 0.033 ↓ 1.5 128 1

Seq Scan on ref_co_district dt (cost=0.00..2.88 rows=88 width=13) (actual time=0.006..0.033 rows=128 loops=1)

14. 12,259.332 34,323.269 ↓ 195.0 3,207,165 29

Materialize (cost=7.71..522,690.32 rows=16,451 width=113) (actual time=0.005..1,183.561 rows=3,207,165 loops=29)

15. 1,051.573 22,063.937 ↓ 195.0 3,207,165 1

Nested Loop (cost=7.71..522,608.07 rows=16,451 width=113) (actual time=0.032..22,063.937 rows=3,207,165 loops=1)

16. 1,943.044 17,805.199 ↓ 195.0 3,207,165 1

Nested Loop Left Join (cost=7.57..519,811.30 rows=16,451 width=92) (actual time=0.029..17,805.199 rows=3,207,165 loops=1)

17. 3,010.813 15,862.155 ↓ 195.0 3,207,165 1

Nested Loop (cost=7.14..511,785.32 rows=16,451 width=92) (actual time=0.028..15,862.155 rows=3,207,165 loops=1)

18. 3,233.317 6,437.012 ↓ 195.0 3,207,165 1

Merge Join (cost=6.72..504,013.28 rows=16,451 width=72) (actual time=0.022..6,437.012 rows=3,207,165 loops=1)

  • Merge Cond: (v.visit_id = e.visit_id)
  • Join Filter: ((e.encounter_end)::date = (v.visit_date_time)::date)
  • Rows Removed by Join Filter: 134135
19. 914.180 914.180 ↑ 1.0 1,363,171 1

Index Scan using trx_pm_patient_visit_visit_id_idx on trx_pm_patient_visit v (cost=0.43..131,854.78 rows=1,363,171 width=56) (actual time=0.004..914.180 rows=1,363,171 loops=1)

20. 2,289.515 2,289.515 ↓ 1.0 3,341,300 1

Index Scan using trx_pm_visit_encounter_visit_id_idx on trx_pm_visit_encounter e (cost=0.43..302,997.66 rows=3,290,245 width=24) (actual time=0.007..2,289.515 rows=3,341,300 loops=1)

21. 6,414.330 6,414.330 ↑ 1.0 1 3,207,165

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.002..0.002 rows=1 loops=3,207,165)

  • Index Cond: (patient_id = v.patient_id)
22. 0.000 0.000 ↓ 0.0 0 3,207,165

Index Scan using mst_pm_appointment_pkey on mst_pm_appointment a (cost=0.42..0.48 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=3,207,165)

  • Index Cond: (v.appointment_id = appointment_id)
23. 3,207.165 3,207.165 ↑ 1.0 1 3,207,165

Index Scan using ref_pm_service_pkey on ref_pm_service rs (cost=0.14..0.16 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=3,207,165)

  • Index Cond: (service_id = v.service_id)
24. 0.006 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
25. 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)