explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 189.027 15,465.983 ↓ 49.2 347,838 1

Unique (cost=425,446.88..425,923.91 rows=7,067 width=158) (actual time=15,124.407..15,465.983 rows=347,838 loops=1)

2. 800.054 15,276.956 ↓ 49.2 347,838 1

Sort (cost=425,446.88..425,464.55 rows=7,067 width=158) (actual time=15,124.404..15,276.956 rows=347,838 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,687.333 14,476.902 ↓ 49.2 347,838 1

HashAggregate (cost=424,518.04..424,995.06 rows=7,067 width=158) (actual time=13,706.020..14,476.902 rows=347,838 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.379 12,789.569 ↓ 101.4 716,290 1

Hash Join (cost=106,548.01..424,041.01 rows=7,067 width=158) (actual time=8,492.196..12,789.569 rows=716,290 loops=1)

  • Hash Cond: (addr.facility_id = f.facility_id)
5. 117.703 12,598.172 ↓ 101.4 716,290 1

Merge Join (cost=106,546.44..423,942.28 rows=7,067 width=143) (actual time=8,492.163..12,598.172 rows=716,290 loops=1)

  • Merge Cond: (st.state_id = addr.state_id)
6. 0.016 0.019 ↑ 2.7 6 1

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

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

8. 350.328 12,480.450 ↓ 101.4 716,290 1

Materialize (cost=106,544.94..430,921.00 rows=7,067 width=134) (actual time=8,492.135..12,480.450 rows=716,290 loops=1)

9. 2,035.484 12,130.122 ↓ 101.4 716,290 1

Nested Loop (cost=106,544.94..430,903.33 rows=7,067 width=134) (actual time=8,492.127..12,130.122 rows=716,290 loops=1)

  • Join Filter: (addr.facility_id = v.clinic_id)
  • Rows Removed by Join Filter: 15048719
10. 0.115 0.550 ↓ 1.1 29 1

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

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

12. 0.348 0.348 ↑ 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.007..0.012 rows=1 loops=29)

  • Index Cond: (district_id = addr.district_id)
13. 2,300.111 10,094.088 ↓ 81.8 543,621 29

Materialize (cost=106,544.66..428,309.77 rows=6,644 width=113) (actual time=31.888..348.072 rows=543,621 loops=29)

14. 418.446 7,793.977 ↓ 81.8 543,621 1

Nested Loop Left Join (cost=106,544.66..428,276.55 rows=6,644 width=113) (actual time=924.620..7,793.977 rows=543,621 loops=1)

15. 549.321 7,375.531 ↓ 81.8 543,621 1

Nested Loop (cost=106,544.24..425,087.67 rows=6,644 width=113) (actual time=924.609..7,375.531 rows=543,621 loops=1)

16. 3,642.479 5,195.347 ↓ 81.8 543,621 1

Hash Join (cost=106,543.82..421,977.01 rows=6,644 width=93) (actual time=924.558..5,195.347 rows=543,621 loops=1)

  • Hash Cond: ((e.visit_id = v.visit_id) AND ((e.encounter_end)::date = (v.visit_date_time)::date))
17. 628.562 628.562 ↑ 1.0 4,180,705 1

Seq Scan on trx_pm_visit_encounter e (cost=0.00..252,621.30 rows=4,183,030 width=24) (actual time=0.003..628.562 rows=4,180,705 loops=1)

18. 147.613 924.306 ↑ 1.6 354,197 1

Hash (cost=98,175.30..98,175.30 rows=557,901 width=77) (actual time=924.306..924.306 rows=354,197 loops=1)

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

Hash Join (cost=4.40..98,175.30 rows=557,901 width=77) (actual time=0.057..776.693 rows=354,197 loops=1)

  • Hash Cond: (v.service_id = rs.service_id)
20. 227.164 227.164 ↑ 1.0 1,713,729 1

Seq Scan on trx_pm_patient_visit v (cost=0.00..86,005.56 rows=1,756,356 width=56) (actual time=0.002..227.164 rows=1,713,729 loops=1)

21. 0.006 0.039 ↑ 1.0 27 1

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

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

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

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

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

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

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

  • Index Cond: (v.appointment_id = appointment_id)
25. 0.008 0.018 ↓ 1.1 28 1

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

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