explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yLOh : Optimization for: plan #klOH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,762.446 16,850.417 ↑ 1.0 1,058,032 1

Sort (cost=640,949.19..643,597.78 rows=1,059,433 width=210) (actual time=16,263.176..16,850.417 rows=1,058,032 loops=1)

  • Sort Key: v.visit_date_time
  • Sort Method: external merge Disk: 250928kB
2. 1,300.912 12,087.971 ↑ 1.0 1,058,032 1

Hash Left Join (cost=316,748.88..457,329.67 rows=1,059,433 width=210) (actual time=8,168.630..12,087.971 rows=1,058,032 loops=1)

  • Hash Cond: (p.religion_religion_id = rf.religion_id)
3. 297.134 10,787.048 ↑ 1.0 1,058,032 1

Hash Join (cost=316,747.61..427,957.74 rows=1,059,433 width=205) (actual time=8,168.581..10,787.048 rows=1,058,032 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
4. 296.964 10,489.907 ↑ 1.0 1,058,032 1

Hash Left Join (cost=316,746.54..413,389.47 rows=1,059,433 width=201) (actual time=8,168.560..10,489.907 rows=1,058,032 loops=1)

  • Hash Cond: (p.ethnic_group_ethnic_group_id = eg.ethnic_group_id)
5. 302.473 10,192.931 ↑ 1.0 1,058,032 1

Hash Join (cost=316,745.25..398,919.85 rows=1,059,433 width=193) (actual time=8,168.533..10,192.931 rows=1,058,032 loops=1)

  • Hash Cond: (v.visit_type_id = vt.visit_type_id)
6. 1,412.256 9,890.456 ↑ 1.0 1,058,032 1

Hash Right Join (cost=316,744.20..384,351.61 rows=1,059,433 width=187) (actual time=8,168.517..9,890.456 rows=1,058,032 loops=1)

  • Hash Cond: (pa.patient_id = p.patient_id)
7. 92.978 310.088 ↓ 1.0 367,140 1

Hash Left Join (cost=6.24..23,534.00 rows=361,337 width=26) (actual time=0.131..310.088 rows=367,140 loops=1)

  • Hash Cond: (pa.district_district_id = rd2.district_id)
8. 160.518 217.057 ↓ 1.0 367,140 1

Hash Left Join (cost=1.36..18,850.04 rows=361,337 width=21) (actual time=0.066..217.057 rows=367,140 loops=1)

  • Hash Cond: (pa.state_id = rs2.state_id)
9. 56.522 56.522 ↓ 1.0 367,140 1

Seq Scan on mst_pm_address pa (cost=0.00..14,169.37 rows=361,337 width=16) (actual time=0.014..56.522 rows=367,140 loops=1)

10. 0.007 0.017 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=13) (actual time=0.017..0.017 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
11. 0.010 0.010 ↑ 1.0 16 1

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

12. 0.021 0.053 ↑ 1.0 128 1

Hash (cost=3.28..3.28 rows=128 width=13) (actual time=0.053..0.053 rows=128 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
13. 0.032 0.032 ↑ 1.0 128 1

Seq Scan on ref_co_district rd2 (cost=0.00..3.28 rows=128 width=13) (actual time=0.005..0.032 rows=128 loops=1)

14. 1,903.051 8,168.112 ↑ 1.0 1,046,526 1

Hash (cost=277,629.05..277,629.05 rows=1,059,433 width=169) (actual time=8,168.112..8,168.112 rows=1,046,526 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 49816kB
15. 329.360 6,265.061 ↑ 1.0 1,046,526 1

Hash Join (cost=141,213.69..277,629.05 rows=1,059,433 width=169) (actual time=2,017.488..6,265.061 rows=1,046,526 loops=1)

  • Hash Cond: (v.clinic_id = f.facility_id)
16. 1,309.322 5,935.572 ↑ 1.0 960,963 1

Hash Join (cost=141,202.97..264,040.99 rows=962,444 width=132) (actual time=2,017.348..5,935.572 rows=960,963 loops=1)

  • Hash Cond: (v.patient_id = p.patient_id)
17. 623.279 4,355.587 ↑ 1.0 960,963 1

Hash Join (cost=120,488.25..224,077.40 rows=962,444 width=83) (actual time=1,746.578..4,355.587 rows=960,963 loops=1)

  • Hash Cond: ((ed.diagnosis_code)::text = (dc.code)::text)
18. 1,603.323 3,726.590 ↑ 1.0 960,963 1

Hash Join (cost=119,800.41..201,734.57 rows=962,444 width=45) (actual time=1,740.831..3,726.590 rows=960,963 loops=1)

  • Hash Cond: (ed.patient_visit_id = v.visit_id)
19. 382.829 382.829 ↑ 1.0 960,963 1

Seq Scan on trx_cd_episode_diagnosis ed (cost=0.00..43,657.44 rows=962,444 width=13) (actual time=0.005..382.829 rows=960,963 loops=1)

20. 900.693 1,740.438 ↓ 1.1 1,858,264 1

Hash (cost=85,384.85..85,384.85 rows=1,694,285 width=40) (actual time=1,740.438..1,740.438 rows=1,858,264 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 69033kB
21. 839.745 839.745 ↓ 1.1 1,858,264 1

Seq Scan on trx_pm_patient_visit v (cost=0.00..85,384.85 rows=1,694,285 width=40) (actual time=0.004..839.745 rows=1,858,264 loops=1)

22. 2.897 5.718 ↑ 1.0 15,413 1

Hash (cost=495.15..495.15 rows=15,415 width=49) (actual time=5.718..5.718 rows=15,413 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1227kB
23. 2.821 2.821 ↑ 1.0 15,413 1

Seq Scan on ref_cd_diagnosis_code dc (cost=0.00..495.15 rows=15,415 width=49) (actual time=0.004..2.821 rows=15,413 loops=1)

24. 100.804 270.663 ↓ 1.0 364,107 1

Hash (cost=16,184.32..16,184.32 rows=362,432 width=57) (actual time=270.663..270.663 rows=364,107 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 31901kB
25. 169.859 169.859 ↓ 1.0 364,107 1

Seq Scan on mst_pm_patient p (cost=0.00..16,184.32 rows=362,432 width=57) (actual time=0.005..169.859 rows=364,107 loops=1)

26. 0.005 0.129 ↑ 1.0 29 1

Hash (cost=10.36..10.36 rows=29 width=45) (actual time=0.129..0.129 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
27. 0.019 0.124 ↑ 1.0 29 1

Hash Join (cost=7.87..10.36 rows=29 width=45) (actual time=0.092..0.124 rows=29 loops=1)

  • Hash Cond: (fa.district_id = rd.district_id)
28. 0.007 0.057 ↑ 1.0 29 1

Hash Join (cost=2.99..5.08 rows=29 width=40) (actual time=0.038..0.057 rows=29 loops=1)

  • Hash Cond: (fa.state_id = rs.state_id)
29. 0.020 0.041 ↑ 1.0 29 1

Hash Join (cost=1.63..3.32 rows=29 width=35) (actual time=0.026..0.041 rows=29 loops=1)

  • Hash Cond: (fa.facility_id = f.facility_id)
30. 0.004 0.004 ↑ 1.0 29 1

Seq Scan on mst_am_facility_address fa (cost=0.00..1.29 rows=29 width=12) (actual time=0.001..0.004 rows=29 loops=1)

31. 0.004 0.017 ↑ 1.0 28 1

Hash (cost=1.28..1.28 rows=28 width=23) (actual time=0.017..0.017 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
32. 0.013 0.013 ↑ 1.0 28 1

Seq Scan on mst_am_facility f (cost=0.00..1.28 rows=28 width=23) (actual time=0.003..0.013 rows=28 loops=1)

33. 0.004 0.009 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=13) (actual time=0.009..0.009 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
34. 0.005 0.005 ↑ 1.0 16 1

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

35. 0.025 0.048 ↑ 1.0 128 1

Hash (cost=3.28..3.28 rows=128 width=13) (actual time=0.048..0.048 rows=128 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
36. 0.023 0.023 ↑ 1.0 128 1

Seq Scan on ref_co_district rd (cost=0.00..3.28 rows=128 width=13) (actual time=0.002..0.023 rows=128 loops=1)

37. 0.000 0.002 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=14) (actual time=0.002..0.002 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
38. 0.002 0.002 ↑ 1.0 2 1

Seq Scan on ref_pm_visit_type vt (cost=0.00..1.02 rows=2 width=14) (actual time=0.001..0.002 rows=2 loops=1)

39. 0.003 0.012 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=16) (actual time=0.012..0.012 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 0.009 0.009 ↑ 1.0 13 1

Seq Scan on ref_co_ethnic_group eg (cost=0.00..1.13 rows=13 width=16) (actual time=0.005..0.009 rows=13 loops=1)

41. 0.004 0.007 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=12) (actual time=0.007..0.007 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
42. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on ref_co_gender g (cost=0.00..1.03 rows=3 width=12) (actual time=0.002..0.003 rows=3 loops=1)

43. 0.002 0.011 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=13) (actual time=0.011..0.011 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
44. 0.009 0.009 ↑ 1.0 12 1

Seq Scan on ref_co_religion rf (cost=0.00..1.12 rows=12 width=13) (actual time=0.005..0.009 rows=12 loops=1)

Planning time : 5.652 ms
Execution time : 16,938.378 ms