explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vhrZ : Optimization for: Optimization for: plan #pUZa; plan #7Aoc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.039 9,138.029 ↓ 28.6 3,086 1

Nested Loop Left Join (cost=10,829,874.10..11,357,967.51 rows=108 width=8) (actual time=8,863.473..9,138.029 rows=3,086 loops=1)

2. 1.016 9,129.818 ↓ 28.6 3,086 1

Hash Left Join (cost=10,829,873.67..11,357,912.97 rows=108 width=16) (actual time=8,863.453..9,129.818 rows=3,086 loops=1)

  • Hash Cond: (f.facility_id = mst_am_facility_address.facility_id)
3. 1.704 9,128.759 ↓ 28.6 3,086 1

Nested Loop (cost=10,829,870.95..11,357,908.84 rows=108 width=20) (actual time=8,863.377..9,128.759 rows=3,086 loops=1)

4. 101.780 9,117.797 ↓ 3.9 3,086 1

Merge Join (cost=10,829,870.52..11,357,533.71 rows=799 width=28) (actual time=8,863.314..9,117.797 rows=3,086 loops=1)

  • Merge Cond: (p_2.patient_id = pdd.patientid)
  • Join Filter: ((pdd.diagnosiscode)::text = (CASE WHEN ((ve.diagnosis_list IS NOT NULL) AND ((ve.diagnosis_list)::text <> '{}'::text)) THEN json_object_keys(ve.diagnosis_list) ELSE 'N/A'::text END))
  • Rows Removed by Join Filter: 15246
5. 3.639 70.961 ↑ 13,999.6 2,396 1

Sort (cost=10,234,424.08..10,318,281.58 rows=33,543,000 width=514) (actual time=70.157..70.961 rows=2,396 loops=1)

  • Sort Key: p_2.patient_id
  • Sort Method: quicksort Memory: 2534kB
6. 11.318 67.322 ↑ 13,999.6 2,396 1

Nested Loop (cost=623.99..472,969.03 rows=33,543,000 width=514) (actual time=1.742..67.322 rows=2,396 loops=1)

7. 0.862 41.256 ↑ 14.6 1,730 1

Hash Join (cost=623.55..154,751.15 rows=25,252 width=94) (actual time=1.691..41.256 rows=1,730 loops=1)

  • Hash Cond: (ve.user_id = au.user_id)
8. 0.776 39.295 ↑ 14.6 1,730 1

Nested Loop (cost=474.04..154,222.86 rows=25,252 width=72) (actual time=0.580..39.295 rows=1,730 loops=1)

9. 0.615 31.599 ↑ 14.6 1,730 1

Nested Loop (cost=473.61..142,529.31 rows=25,252 width=72) (actual time=0.567..31.599 rows=1,730 loops=1)

10. 1.396 18.874 ↑ 14.6 1,730 1

Nested Loop (cost=473.19..129,987.53 rows=25,252 width=64) (actual time=0.559..18.874 rows=1,730 loops=1)

11. 5.065 5.368 ↑ 14.6 1,730 1

Bitmap Heap Scan on trx_cd_component c (cost=472.75..38,411.38 rows=25,276 width=16) (actual time=0.543..5.368 rows=1,730 loops=1)

  • Recheck Cond: (type = ANY ('{97,98}'::integer[]))
  • Heap Blocks: exact=1649
12. 0.303 0.303 ↑ 13.9 1,815 1

Bitmap Index Scan on trx_cd_component_type_idx (cost=0.00..466.44 rows=25,276 width=0) (actual time=0.303..0.303 rows=1,815 loops=1)

  • Index Cond: (type = ANY ('{97,98}'::integer[]))
13. 12.110 12.110 ↑ 1.0 1 1,730

Index Scan using trx_pm_visit_encounter_pkey on trx_pm_visit_encounter ve (cost=0.43..3.61 rows=1 width=56) (actual time=0.007..0.007 rows=1 loops=1,730)

  • Index Cond: (visit_encounter_id = c.visit_encounter_id)
14. 12.110 12.110 ↑ 1.0 1 1,730

Index Scan using trx_pm_patient_visit_visit_id_idx on trx_pm_patient_visit pv_1 (cost=0.43..0.49 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=1,730)

  • Index Cond: (visit_id = ve.visit_id)
15. 6.920 6.920 ↑ 1.0 1 1,730

Index Only Scan using mst_pm_patient_patient_id_idx on mst_pm_patient p_2 (cost=0.42..0.45 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,730)

  • Index Cond: (patient_id = pv_1.patient_id)
  • Heap Fetches: 1475
16. 0.331 1.099 ↑ 1.0 1,623 1

Hash (cost=129.23..129.23 rows=1,623 width=30) (actual time=1.099..1.099 rows=1,623 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 99kB
17. 0.768 0.768 ↑ 1.0 1,623 1

Seq Scan on mst_am_user au (cost=0.00..129.23 rows=1,623 width=30) (actual time=0.005..0.768 rows=1,623 loops=1)

18. 10.380 10.380 ↑ 2.0 1 1,730

Index Scan using trx_cd_component_item_component_id_idx on trx_cd_component_item ci (cost=0.43..4.56 rows=2 width=436) (actual time=0.006..0.006 rows=1 loops=1,730)

  • Index Cond: (component_id = c.component_id)
19.          

SubPlan (forNested Loop)

20. 1.234 1.234 ↑ 50.0 2 1,234

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.001..0.001 rows=2 loops=1,234)

21. 3.134 3.134 ↑ 50.0 2 3,134

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.001..0.001 rows=2 loops=3,134)

22. 1,231.394 8,945.056 ↑ 1.1 1,332,139 1

Sort (cost=595,396.13..598,913.11 rows=1,406,789 width=33) (actual time=8,793.128..8,945.056 rows=1,332,139 loops=1)

  • Sort Key: pdd.patientid
  • Sort Method: external sort Disk: 64920kB
23. 306.976 7,713.662 ↑ 1.1 1,328,093 1

Hash Join (cost=315,163.77..451,735.02 rows=1,406,789 width=33) (actual time=5,012.504..7,713.662 rows=1,328,093 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
24. 303.483 7,406.681 ↑ 1.1 1,328,093 1

Hash Join (cost=315,162.70..432,390.60 rows=1,406,789 width=37) (actual time=5,012.488..7,406.681 rows=1,328,093 loops=1)

  • Hash Cond: (pv.clinic_id = f.facility_id)
25. 761.866 7,103.085 ↑ 1.1 1,328,093 1

Hash Join (cost=315,132.46..413,017.01 rows=1,406,789 width=37) (actual time=5,012.358..7,103.085 rows=1,328,093 loops=1)

  • Hash Cond: (pv.patient_id = p.patient_id)
26. 1,003.514 2,412.210 ↑ 1.0 961,709 1

Hash Join (cost=121,276.97..195,696.97 rows=961,709 width=33) (actual time=1,083.040..2,412.210 rows=961,709 loops=1)

  • Hash Cond: (pdd.patientvisitid = pv.visit_id)
27. 326.755 326.755 ↑ 1.0 961,709 1

Seq Scan on mat_patientdiagnosisdata pdd (cost=0.00..33,523.09 rows=961,709 width=29) (actual time=0.005..326.755 rows=961,709 loops=1)

28. 397.701 1,081.941 ↑ 1.0 1,859,275 1

Hash (cost=87,072.21..87,072.21 rows=1,863,021 width=20) (actual time=1,081.941..1,081.941 rows=1,859,275 loops=1)

  • Buckets: 262144 Batches: 2 Memory Usage: 47259kB
29. 684.240 684.240 ↑ 1.0 1,859,275 1

Seq Scan on trx_pm_patient_visit pv (cost=0.00..87,072.21 rows=1,863,021 width=20) (actual time=0.003..684.240 rows=1,859,275 loops=1)

30. 112.415 3,929.009 ↓ 1.0 479,121 1

Hash (cost=187,866.60..187,866.60 rows=479,111 width=36) (actual time=3,929.009..3,929.009 rows=479,121 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 33689kB
31. 106.129 3,816.594 ↓ 1.0 479,121 1

Hash Join (cost=157,136.49..187,866.60 rows=479,111 width=36) (actual time=3,280.264..3,816.594 rows=479,121 loops=1)

  • Hash Cond: (pi.identity_type_id = idt.identity_type_id)
32. 375.713 3,710.456 ↓ 1.0 479,121 1

Hash Join (cost=157,135.22..181,277.55 rows=479,111 width=40) (actual time=3,280.237..3,710.456 rows=479,121 loops=1)

  • Hash Cond: (pi.patient_id = p.patient_id)
33. 54.795 54.795 ↓ 1.0 479,121 1

Seq Scan on mst_pm_person_identification pi (cost=0.00..14,560.11 rows=479,111 width=12) (actual time=0.003..54.795 rows=479,121 loops=1)

34. 79.863 3,279.948 ↑ 1.0 364,284 1

Hash (cost=152,580.52..152,580.52 rows=364,376 width=28) (actual time=3,279.948..3,279.948 rows=364,284 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 22768kB
35. 300.903 3,200.085 ↑ 1.0 364,284 1

Hash Join (cost=121,801.72..152,580.52 rows=364,376 width=28) (actual time=2,518.609..3,200.085 rows=364,284 loops=1)

  • Hash Cond: (p.patient_id = p_1.patient_id)
36. 319.996 2,759.660 ↑ 1.0 364,284 1

Hash Join (cost=101,043.26..124,534.54 rows=364,376 width=20) (actual time=2,378.800..2,759.660 rows=364,284 loops=1)

  • Hash Cond: (mst_pm_patient.patient_id = p.patient_id)
37. 61.167 61.167 ↑ 1.0 364,284 1

Seq Scan on mst_pm_patient (cost=0.00..16,203.76 rows=364,376 width=8) (actual time=0.002..61.167 rows=364,284 loops=1)

38. 80.426 2,378.497 ↑ 1.0 364,284 1

Hash (cost=96,488.56..96,488.56 rows=364,376 width=12) (actual time=2,378.497..2,378.497 rows=364,284 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 15653kB
39. 337.977 2,298.071 ↑ 1.0 364,284 1

Hash Right Join (cost=84,550.78..96,488.56 rows=364,376 width=12) (actual time=745.476..2,298.071 rows=364,284 loops=1)

  • Hash Cond: (totalpatientaddress.patient_id = p.patient_id)
40. 1,749.004 1,749.004 ↓ 3.0 364,216 1

CTE Scan on totalpatientaddress (cost=63,792.32..72,056.93 rows=122,439 width=3,707) (actual time=534.104..1,749.004 rows=364,216 loops=1)

  • Filter: (totaladdress > 0)
  • Rows Removed by Filter: 3101
41.          

CTE totalpatientaddress

42. 418.721 1,341.816 ↓ 1.0 367,317 1

WindowAgg (cost=48,181.39..63,792.32 rows=367,316 width=204) (actual time=534.098..1,341.816 rows=367,317 loops=1)

43. 292.071 923.095 ↓ 1.0 367,317 1

WindowAgg (cost=48,181.39..56,446.00 rows=367,316 width=204) (actual time=534.076..923.095 rows=367,317 loops=1)

44. 510.900 631.024 ↓ 1.0 367,317 1

Sort (cost=48,181.39..49,099.68 rows=367,316 width=204) (actual time=534.059..631.024 rows=367,317 loops=1)

  • Sort Key: pa.patient_id, pa.address_type_address_type_id
  • Sort Method: external merge Disk: 60408kB
45. 120.124 120.124 ↓ 1.0 367,317 1

Seq Scan on mst_pm_address pa (cost=0.00..14,229.16 rows=367,316 width=204) (actual time=0.005..120.124 rows=367,317 loops=1)

46. 69.684 211.090 ↑ 1.0 364,284 1

Hash (cost=16,203.76..16,203.76 rows=364,376 width=16) (actual time=211.090..211.090 rows=364,284 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 17062kB
47. 141.406 141.406 ↑ 1.0 364,284 1

Seq Scan on mst_pm_patient p (cost=0.00..16,203.76 rows=364,376 width=16) (actual time=0.005..141.406 rows=364,284 loops=1)

48. 62.544 139.522 ↑ 1.0 364,284 1

Hash (cost=16,203.76..16,203.76 rows=364,376 width=8) (actual time=139.522..139.522 rows=364,284 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 14230kB
49. 76.978 76.978 ↑ 1.0 364,284 1

Seq Scan on mst_pm_patient p_1 (cost=0.00..16,203.76 rows=364,376 width=8) (actual time=0.005..76.978 rows=364,284 loops=1)

50. 0.004 0.009 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=4) (actual time=0.009..0.009 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.005 0.005 ↑ 1.0 12 1

Seq Scan on ref_co_identity_type idt (cost=0.00..1.12 rows=12 width=4) (actual time=0.005..0.005 rows=12 loops=1)

52. 0.005 0.113 ↑ 1.0 28 1

Hash (cost=29.89..29.89 rows=28 width=4) (actual time=0.113..0.113 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
53. 0.020 0.108 ↑ 1.0 28 1

Nested Loop (cost=0.28..29.89 rows=28 width=4) (actual time=0.046..0.108 rows=28 loops=1)

54. 0.004 0.004 ↑ 1.0 28 1

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

55. 0.084 0.084 ↑ 1.0 1 28

Index Only Scan using ref_am_simple_list_config_pkey on ref_am_simple_list_config sl (cost=0.28..1.01 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=28)

  • Index Cond: (simple_list_config_id = f.facility_type_id)
  • Heap Fetches: 19
56. 0.002 0.005 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.005..0.005 rows=3 loops=1)

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

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

58. 9.258 9.258 ↑ 1.0 1 3,086

Index Only Scan using mat_patientencounter_visit_encounter_id_idx on mat_patientencounter pe (cost=0.43..0.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3,086)

  • Index Cond: (visit_encounter_id = ve.visit_encounter_id)
  • Heap Fetches: 3086
59. 0.004 0.043 ↓ 1.0 27 1

Hash (cost=2.40..2.40 rows=26 width=12) (actual time=0.043..0.043 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
60. 0.007 0.039 ↓ 1.0 27 1

Unique (cost=1.99..2.14 rows=26 width=12) (actual time=0.031..0.039 rows=27 loops=1)

61. 0.017 0.032 ↑ 1.0 29 1

Sort (cost=1.99..2.07 rows=29 width=12) (actual time=0.030..0.032 rows=29 loops=1)

  • Sort Key: mst_am_facility_address.facility_id
  • Sort Method: quicksort Memory: 26kB
62. 0.015 0.015 ↑ 1.0 29 1

Seq Scan on mst_am_facility_address (cost=0.00..1.29 rows=29 width=12) (actual time=0.005..0.015 rows=29 loops=1)

63. 6.172 6.172 ↑ 1.0 1 3,086

Index Only Scan using mst_pm_contact_number_patient_id_default_contact_idx on mst_pm_contact_number pcn (cost=0.42..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3,086)

  • Index Cond: ((patient_id = p.patient_id) AND (default_contact_number = true))
  • Filter: (default_contact_number IS TRUE)
  • Heap Fetches: 422
Planning time : 15.680 ms
Execution time : 9,176.917 ms