explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u1c6

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 199.993 15,711.347 ↓ 59.9 347,813 1

Unique (cost=406,005.15..406,397.19 rows=5,808 width=158) (actual time=15,344.367..15,711.347 rows=347,813 loops=1)

2. 856.488 15,511.354 ↓ 59.9 347,813 1

Sort (cost=406,005.15..406,019.67 rows=5,808 width=158) (actual time=15,344.361..15,511.354 rows=347,813 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,783.534 14,654.866 ↓ 59.9 347,813 1

HashAggregate (cost=405,250.00..405,642.04 rows=5,808 width=158) (actual time=13,833.382..14,654.866 rows=347,813 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. 188.447 12,871.332 ↓ 123.3 716,218 1

Hash Join (cost=107,167.61..404,857.96 rows=5,808 width=158) (actual time=8,605.202..12,871.332 rows=716,218 loops=1)

  • Hash Cond: (addr.facility_id = f.facility_id)
5. 114.882 12,682.866 ↓ 123.3 716,218 1

Merge Join (cost=107,166.05..404,776.54 rows=5,808 width=143) (actual time=8,605.164..12,682.866 rows=716,218 loops=1)

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

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

  • Sort Key: st.state_id
  • Sort Method: quicksort Memory: 25kB
7. 0.005 0.005 ↑ 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.005 rows=16 loops=1)

8. 364.077 12,567.962 ↓ 123.3 716,218 1

Materialize (cost=107,164.55..411,300.37 rows=5,808 width=134) (actual time=8,605.136..12,567.962 rows=716,218 loops=1)

9. 1,992.636 12,203.885 ↓ 123.3 716,218 1

Nested Loop (cost=107,164.55..411,285.85 rows=5,808 width=134) (actual time=8,605.129..12,203.885 rows=716,218 loops=1)

  • Join Filter: (addr.facility_id = v.clinic_id)
  • Rows Removed by Join Filter: 15047283
10. 0.136 0.639 ↓ 1.1 29 1

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

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

12. 0.406 0.406 ↑ 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.014 rows=1 loops=29)

  • Index Cond: (district_id = addr.district_id)
13. 2,303.286 10,210.610 ↓ 99.6 543,569 29

Materialize (cost=107,164.27..409,151.09 rows=5,460 width=113) (actual time=31.989..352.090 rows=543,569 loops=29)

14. 375.852 7,907.324 ↓ 99.6 543,569 1

Nested Loop Left Join (cost=107,164.27..409,123.79 rows=5,460 width=113) (actual time=927.558..7,907.324 rows=543,569 loops=1)

15. 567.598 7,531.472 ↓ 99.6 543,569 1

Nested Loop (cost=107,163.85..406,505.80 rows=5,460 width=113) (actual time=927.555..7,531.472 rows=543,569 loops=1)

16. 3,765.502 5,333.167 ↓ 99.6 543,569 1

Hash Join (cost=107,163.42..403,950.85 rows=5,460 width=93) (actual time=927.538..5,333.167 rows=543,569 loops=1)

  • Hash Cond: ((e.visit_id = v.visit_id) AND ((e.encounter_end)::date = (v.visit_date_time)::date))
17. 640.417 640.417 ↓ 1.2 4,180,348 1

Seq Scan on trx_pm_visit_encounter e (cost=0.00..245,167.73 rows=3,437,673 width=24) (actual time=0.002..640.417 rows=4,180,348 loops=1)

18. 150.515 927.248 ↑ 1.6 354,192 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 39101kB
19. 549.207 776.733 ↑ 1.6 354,192 1

Hash Join (cost=4.40..98,658.80 rows=566,975 width=77) (actual time=0.055..776.733 rows=354,192 loops=1)

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

21. 0.006 0.032 ↑ 1.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
22. 0.026 0.026 ↑ 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.026 rows=27 loops=1)

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

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

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

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

  • Index Cond: (v.appointment_id = appointment_id)
25. 0.008 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
26. 0.011 0.011 ↓ 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.011 rows=28 loops=1)