explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VnZL

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 59,359.539 ↑ 1.0 1 1

Limit (cost=526,205.26..526,205.26 rows=1 width=158) (actual time=59,359.538..59,359.539 rows=1 loops=1)

2. 654.313 59,359.535 ↑ 17,561.0 1 1

Sort (cost=526,205.26..526,249.16 rows=17,561 width=158) (actual time=59,359.535..59,359.535 rows=1 loops=1)

  • Sort Key: v.visit_date_time
  • Sort Method: top-N heapsort Memory: 25kB
3. 2,835.391 58,705.222 ↓ 70.5 1,237,942 1

HashAggregate (cost=524,932.08..526,117.45 rows=17,561 width=158) (actual time=57,848.788..58,705.222 rows=1,237,942 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,817.627 55,869.831 ↓ 70.5 1,237,942 1

HashAggregate (cost=522,605.25..523,790.62 rows=17,561 width=158) (actual time=53,108.403..55,869.831 rows=1,237,942 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. 914.709 49,052.204 ↓ 190.7 3,349,242 1

Hash Join (cost=11.11..521,419.88 rows=17,561 width=158) (actual time=25,982.728..49,052.204 rows=3,349,242 loops=1)

  • Hash Cond: (addr.facility_id = f.facility_id)
6. 543.681 48,137.476 ↓ 190.7 3,349,242 1

Merge Join (cost=9.55..521,176.86 rows=17,561 width=143) (actual time=25,982.687..48,137.476 rows=3,349,242 loops=1)

  • Merge Cond: (st.state_id = addr.state_id)
7. 0.065 0.065 ↑ 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.004..0.065 rows=6 loops=1)

8. 1,599.015 47,593.730 ↓ 190.7 3,349,242 1

Materialize (cost=7.84..532,534.86 rows=17,561 width=134) (actual time=25,982.638..47,593.730 rows=3,349,242 loops=1)

9. 11,759.216 45,994.715 ↓ 190.7 3,349,242 1

Nested Loop (cost=7.84..532,490.95 rows=17,561 width=134) (actual time=25,982.626..45,994.715 rows=3,349,242 loops=1)

  • Join Filter: (addr.facility_id = v.clinic_id)
  • Rows Removed by Join Filter: 89657847
10. 0.573 1.434 ↓ 1.1 29 1

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

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

12. 0.485 0.522 ↓ 1.5 128 29

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

13. 0.037 0.037 ↓ 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.037 rows=128 loops=1)

14. 12,323.981 34,234.065 ↓ 195.0 3,207,141 29

Materialize (cost=7.71..526,072.24 rows=16,451 width=113) (actual time=0.005..1,180.485 rows=3,207,141 loops=29)

15. 1,024.072 21,910.084 ↓ 195.0 3,207,141 1

Nested Loop (cost=7.71..525,989.99 rows=16,451 width=113) (actual time=0.036..21,910.084 rows=3,207,141 loops=1)

16. 1,968.598 17,678.871 ↓ 195.0 3,207,141 1

Nested Loop Left Join (cost=7.57..523,193.23 rows=16,451 width=92) (actual time=0.032..17,678.871 rows=3,207,141 loops=1)

17. 2,937.860 15,710.273 ↓ 195.0 3,207,141 1

Nested Loop (cost=7.14..515,247.07 rows=16,451 width=92) (actual time=0.031..15,710.273 rows=3,207,141 loops=1)

18. 3,263.957 6,358.131 ↓ 195.0 3,207,141 1

Merge Join (cost=6.72..507,517.04 rows=16,451 width=72) (actual time=0.024..6,358.131 rows=3,207,141 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: 134134
19. 894.746 894.746 ↑ 1.1 1,363,164 1

Index Scan using trx_pm_patient_visit_visit_id_idx on trx_pm_patient_visit v (cost=0.43..134,859.16 rows=1,563,463 width=56) (actual time=0.005..894.746 rows=1,363,164 loops=1)

20. 2,199.428 2,199.428 ↓ 1.0 3,341,275 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,199.428 rows=3,341,275 loops=1)

21. 6,414.282 6,414.282 ↑ 1.0 1 3,207,141

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

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

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=3,207,141)

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

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

  • Index Cond: (service_id = v.service_id)
24. 0.011 0.019 ↓ 1.1 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
25. 0.008 0.008 ↓ 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.008 rows=28 loops=1)