explain.depesz.com

PostgreSQL's explain analyze made readable

Result: klOH

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,531.091 14,133.365 ↓ 1.0 1,057,994 1

Sort (cost=633,178.10..635,778.70 rows=1,040,241 width=210) (actual time=13,543.114..14,133.365 rows=1,057,994 loops=1)

  • Sort Key: v.visit_date_time
  • Sort Method: external merge Disk: 250920kB
2. 1,263.300 11,602.274 ↓ 1.0 1,057,994 1

Hash Left Join (cost=314,425.36..453,023.89 rows=1,040,241 width=210) (actual time=7,944.308..11,602.274 rows=1,057,994 loops=1)

  • Hash Cond: (p.religion_religion_id = rf.religion_id)
3. 288.970 10,338.963 ↓ 1.0 1,057,994 1

Hash Join (cost=314,424.09..424,184.02 rows=1,040,241 width=205) (actual time=7,944.262..10,338.963 rows=1,057,994 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
4. 289.415 10,049.987 ↓ 1.0 1,057,994 1

Hash Left Join (cost=314,423.02..409,879.63 rows=1,040,241 width=201) (actual time=7,944.241..10,049.987 rows=1,057,994 loops=1)

  • Hash Cond: (p.ethnic_group_ethnic_group_id = eg.ethnic_group_id)
5. 292.665 9,760.559 ↓ 1.0 1,057,994 1

Hash Join (cost=314,421.73..395,672.12 rows=1,040,241 width=193) (actual time=7,944.212..9,760.559 rows=1,057,994 loops=1)

  • Hash Cond: (v.visit_type_id = vt.visit_type_id)
6. 1,229.052 9,467.892 ↓ 1.0 1,057,994 1

Hash Right Join (cost=314,420.68..381,367.76 rows=1,040,241 width=187) (actual time=7,944.197..9,467.892 rows=1,057,994 loops=1)

  • Hash Cond: (pa.patient_id = p.patient_id)
7. 86.751 295.039 ↓ 1.0 367,130 1

Hash Left Join (cost=6.24..23,534.00 rows=361,337 width=26) (actual time=0.129..295.039 rows=367,130 loops=1)

  • Hash Cond: (pa.district_district_id = rd2.district_id)
8. 154.064 208.235 ↓ 1.0 367,130 1

Hash Left Join (cost=1.36..18,850.04 rows=361,337 width=21) (actual time=0.063..208.235 rows=367,130 loops=1)

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

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

10. 0.006 0.018 ↑ 1.0 16 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
11. 0.012 0.012 ↑ 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.012 rows=16 loops=1)

12. 0.024 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.029 0.029 ↑ 1.0 128 1

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

14. 1,981.853 7,943.801 ↓ 1.0 1,046,488 1

Hash (cost=276,014.43..276,014.43 rows=1,040,241 width=169) (actual time=7,943.801..7,943.801 rows=1,046,488 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 49814kB
15. 325.010 5,961.948 ↓ 1.0 1,046,488 1

Hash Join (cost=141,213.69..276,014.43 rows=1,040,241 width=169) (actual time=1,834.425..5,961.948 rows=1,046,488 loops=1)

  • Hash Cond: (v.clinic_id = f.facility_id)
16. 1,258.513 5,636.800 ↓ 1.0 960,928 1

Hash Join (cost=141,202.97..262,672.34 rows=945,009 width=132) (actual time=1,834.272..5,636.800 rows=960,928 loops=1)

  • Hash Cond: (v.patient_id = p.patient_id)
17. 587.558 4,105.460 ↓ 1.0 960,928 1

Hash Join (cost=120,488.25..223,057.44 rows=945,009 width=83) (actual time=1,561.255..4,105.460 rows=960,928 loops=1)

  • Hash Cond: ((ed.diagnosis_code)::text = (dc.code)::text)
18. 1,575.016 3,511.991 ↓ 1.0 960,928 1

Hash Join (cost=119,800.41..201,106.90 rows=945,009 width=45) (actual time=1,555.254..3,511.991 rows=960,928 loops=1)

  • Hash Cond: (ed.patient_visit_id = v.visit_id)
19. 382.703 382.703 ↓ 1.0 960,928 1

Seq Scan on trx_cd_episode_diagnosis ed (cost=0.00..43,483.09 rows=945,009 width=13) (actual time=0.007..382.703 rows=960,928 loops=1)

20. 815.775 1,554.272 ↓ 1.1 1,858,211 1

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

  • Buckets: 262144 Batches: 2 Memory Usage: 69031kB
21. 738.497 738.497 ↓ 1.1 1,858,211 1

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

22. 3.221 5.911 ↑ 1.0 15,413 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 1227kB
23. 2.690 2.690 ↑ 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.005..2.690 rows=15,413 loops=1)

24. 102.207 272.827 ↓ 1.0 364,097 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 31900kB
25. 170.620 170.620 ↓ 1.0 364,097 1

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

26. 0.006 0.138 ↑ 1.0 29 1

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

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

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

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

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

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

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

  • Hash Cond: (fa.facility_id = f.facility_id)
30. 0.003 0.003 ↑ 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.003 rows=29 loops=1)

31. 0.007 0.016 ↑ 1.0 28 1

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

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

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

33. 0.006 0.010 ↑ 1.0 16 1

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

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

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

35. 0.026 0.051 ↑ 1.0 128 1

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

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

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

37. 0.002 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.000 0.000 ↑ 1.0 2 1

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

39. 0.006 0.013 ↑ 1.0 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 0.007 0.007 ↑ 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.007 rows=13 loops=1)

41. 0.003 0.006 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=12) (actual time=0.006..0.006 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.003 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.008 0.008 ↑ 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.008 rows=12 loops=1)