explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i7H6 : Optimization for: plan #jEZj

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.132 5,146.379 ↓ 96.1 769 1

Nested Loop Left Join (cost=10,580,133.48..11,085,085.75 rows=8 width=5) (actual time=5,129.116..5,146.379 rows=769 loops=1)

2. 0.253 5,143.940 ↓ 96.1 769 1

Hash Left Join (cost=10,580,133.06..11,085,081.71 rows=8 width=13) (actual time=5,129.093..5,143.940 rows=769 loops=1)

  • Hash Cond: (f.facility_id = mst_am_facility_address.facility_id)
3. 0.038 5,143.645 ↓ 96.1 769 1

Nested Loop (cost=10,580,130.33..11,085,078.89 rows=8 width=17) (actual time=5,129.024..5,143.645 rows=769 loops=1)

4. 6.948 5,140.531 ↓ 12.6 769 1

Merge Join (cost=10,580,129.90..11,085,050.25 rows=61 width=25) (actual time=5,128.996..5,140.531 rows=769 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: 2170
5. 3.620 59.519 ↑ 13,999.6 2,396 1

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

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

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

7. 0.840 33.018 ↑ 14.6 1,730 1

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

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

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

9. 1.137 23.271 ↑ 14.6 1,730 1

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

10. 1.332 13.484 ↑ 14.6 1,730 1

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

11. 3.199 3.502 ↑ 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.544..3.502 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. 8.650 8.650 ↑ 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.005..0.005 rows=1 loops=1,730)

  • Index Cond: (visit_encounter_id = c.visit_encounter_id)
14. 8.650 8.650 ↑ 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.005..0.005 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.346 1.001 ↑ 1.0 1,623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 99kB
17. 0.655 0.655 ↑ 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.655 rows=1,623 loops=1)

18. 8.650 8.650 ↑ 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.005..0.005 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. 25.406 5,074.064 ↑ 1.5 73,085 1

Sort (cost=345,655.52..345,923.51 rows=107,198 width=25) (actual time=5,070.202..5,074.064 rows=73,085 loops=1)

  • Sort Key: pdd.patientid
  • Sort Method: quicksort Memory: 8695kB
23. 23.308 5,048.658 ↑ 1.5 71,973 1

Hash Join (cost=318,630.08..336,699.17 rows=107,198 width=25) (actual time=3,264.340..5,048.658 rows=71,973 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
24. 22.534 5,025.344 ↑ 1.5 71,973 1

Hash Join (cost=318,629.01..335,224.13 rows=107,198 width=29) (actual time=3,264.317..5,025.344 rows=71,973 loops=1)

  • Hash Cond: (pv.clinic_id = f.facility_id)
25. 22.628 5,002.709 ↑ 1.5 71,973 1

Hash Join (cost=318,598.95..333,705.21 rows=111,168 width=29) (actual time=3,264.206..5,002.709 rows=71,973 loops=1)

  • Hash Cond: (pi.identity_type_id = idt.identity_type_id)
26. 69.397 4,980.074 ↑ 1.5 71,973 1

Hash Join (cost=318,597.68..332,175.38 rows=111,168 width=33) (actual time=3,264.178..4,980.074 rows=71,973 loops=1)

  • Hash Cond: (pv.patient_id = mst_pm_patient.patient_id)
27. 57.199 4,120.571 ↑ 1.7 43,578 1

Hash Join (cost=267,708.01..279,431.46 rows=75,997 width=45) (actual time=2,473.787..4,120.571 rows=43,578 loops=1)

  • Hash Cond: (pv.patient_id = p_1.patient_id)
28. 220.454 3,921.550 ↑ 1.7 43,578 1

Hash Right Join (cost=246,949.55..257,153.06 rows=75,997 width=37) (actual time=2,331.673..3,921.550 rows=43,578 loops=1)

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

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

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

CTE totalpatientaddress

31. 477.309 1,544.402 ↓ 1.0 367,317 1

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

32. 329.017 1,067.093 ↓ 1.0 367,317 1

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

33. 614.361 738.076 ↓ 1.0 367,317 1

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

  • Sort Key: pa.patient_id, pa.address_type_address_type_id
  • Sort Method: external merge Disk: 60408kB
34. 123.715 123.715 ↓ 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.008..123.715 rows=367,317 loops=1)

35. 11.746 1,701.935 ↑ 1.7 43,578 1

Hash (cost=182,207.26..182,207.26 rows=75,997 width=37) (actual time=1,701.935..1,701.935 rows=43,578 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 3235kB
36. 45.859 1,690.189 ↑ 1.7 43,578 1

Hash Join (cost=142,692.41..182,207.26 rows=75,997 width=37) (actual time=1,453.828..1,690.189 rows=43,578 loops=1)

  • Hash Cond: (pv.patient_id = p.patient_id)
37. 142.815 1,429.100 ↑ 1.7 43,578 1

Hash Join (cost=121,933.95..159,928.86 rows=75,997 width=25) (actual time=1,238.312..1,429.100 rows=43,578 loops=1)

  • Hash Cond: (pdd.patientvisitid = pv.visit_id)
38. 52.618 64.344 ↑ 1.7 43,578 1

Bitmap Heap Scan on mat_patientdiagnosisdata pdd (cost=656.98..25,512.94 rows=75,997 width=21) (actual time=15.265..64.344 rows=43,578 loops=1)

  • Recheck Cond: ((diagnosiscode)::text ~~ 'I10%'::text)
  • Heap Blocks: exact=18167
39. 11.726 11.726 ↑ 1.7 43,578 1

Bitmap Index Scan on mat_patientdiagnosisdata_diagnosiscode_idx (cost=0.00..637.98 rows=75,997 width=0) (actual time=11.726..11.726 rows=43,578 loops=1)

  • Index Cond: ((diagnosiscode)::text ~~ 'I10%'::text)
40. 494.843 1,221.941 ↑ 1.0 1,859,275 1

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

  • Buckets: 262144 Batches: 2 Memory Usage: 47259kB
41. 727.098 727.098 ↑ 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.004..727.098 rows=1,859,275 loops=1)

42. 70.788 215.230 ↑ 1.0 364,284 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 17062kB
43. 144.442 144.442 ↑ 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.004..144.442 rows=364,284 loops=1)

44. 62.583 141.822 ↑ 1.0 364,284 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 14230kB
45. 79.239 79.239 ↑ 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.004..79.239 rows=364,284 loops=1)

46. 118.960 790.106 ↓ 1.0 479,121 1

Hash (cost=44,900.79..44,900.79 rows=479,111 width=20) (actual time=790.106..790.106 rows=479,121 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 24331kB
47. 469.930 671.146 ↓ 1.0 479,121 1

Hash Join (cost=20,758.46..44,900.79 rows=479,111 width=20) (actual time=141.811..671.146 rows=479,121 loops=1)

  • Hash Cond: (pi.patient_id = mst_pm_patient.patient_id)
48. 59.689 59.689 ↓ 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..59.689 rows=479,121 loops=1)

49. 61.031 141.527 ↑ 1.0 364,284 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 14230kB
50. 80.496 80.496 ↑ 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.003..80.496 rows=364,284 loops=1)

51. 0.002 0.007 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
52. 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.004..0.005 rows=12 loops=1)

53. 0.008 0.101 ↑ 1.0 27 1

Hash (cost=29.72..29.72 rows=27 width=4) (actual time=0.101..0.101 rows=27 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
54. 0.001 0.093 ↑ 1.0 27 1

Nested Loop (cost=0.28..29.72 rows=27 width=4) (actual time=0.029..0.093 rows=27 loops=1)

55. 0.038 0.038 ↑ 1.0 27 1

Seq Scan on mst_am_facility f (cost=0.00..1.42 rows=27 width=12) (actual time=0.018..0.038 rows=27 loops=1)

  • Filter: (upper((facility_name)::text) <> 'DC'::text)
  • Rows Removed by Filter: 1
56. 0.054 0.054 ↑ 1.0 1 27

Index Only Scan using ref_am_simple_list_config_pkey on ref_am_simple_list_config sl (cost=0.28..1.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=27)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
58. 0.004 0.004 ↑ 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.004 rows=3 loops=1)

59. 3.076 3.076 ↑ 1.0 1 769

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.004..0.004 rows=1 loops=769)

  • Index Cond: (visit_encounter_id = ve.visit_encounter_id)
  • Heap Fetches: 769
60. 0.006 0.042 ↓ 1.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
61. 0.006 0.036 ↓ 1.0 27 1

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

62. 0.017 0.030 ↑ 1.0 29 1

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

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

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

64. 2.307 2.307 ↑ 1.0 1 769

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.003..0.003 rows=1 loops=769)

  • Index Cond: ((patient_id = p.patient_id) AND (default_contact_number = true))
  • Filter: (default_contact_number IS TRUE)
  • Heap Fetches: 105
Planning time : 12.536 ms
Execution time : 5,176.013 ms