explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w3Mg

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 9,557.922 ↓ 28.6 3,086 1

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

2. 1.130 9,548.893 ↓ 28.6 3,086 1

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

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

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

4. 102.295 9,525.576 ↓ 3.9 3,086 1

Merge Join (cost=10,829,870.52..11,357,533.71 rows=799 width=28) (actual time=9,270.157..9,525.576 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. 2.125 83.217 ↑ 13,999.6 2,396 1

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

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

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

7. 0.929 43.622 ↑ 14.6 1,730 1

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

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

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

9. 0.527 33.610 ↑ 14.6 1,730 1

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

10. 1.601 19.243 ↑ 14.6 1,730 1

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

11. 5.208 5.532 ↑ 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.556..5.532 rows=1,730 loops=1)

  • Recheck Cond: (type = ANY ('{97,98}'::integer[]))
  • Heap Blocks: exact=1649
12. 0.324 0.324 ↑ 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.324..0.324 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. 13.840 13.840 ↑ 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.007..0.008 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.263 1.054 ↑ 1.0 1,623 1

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

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

18. 22.490 22.490 ↑ 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.012..0.013 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,256.565 9,340.064 ↑ 1.1 1,332,139 1

Sort (cost=595,396.13..598,913.11 rows=1,406,789 width=33) (actual time=9,187.291..9,340.064 rows=1,332,139 loops=1)

  • Sort Key: pdd.patientid
  • Sort Method: external sort Disk: 64920kB
23. 308.790 8,083.499 ↑ 1.1 1,328,093 1

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

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

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

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

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

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

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

  • Hash Cond: (pdd.patientvisitid = pv.visit_id)
27. 370.781 370.781 ↑ 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.009..370.781 rows=961,709 loops=1)

28. 397.991 1,098.893 ↑ 1.0 1,859,275 1

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

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

30. 115.603 4,136.690 ↓ 1.0 479,121 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 33689kB
31. 105.562 4,021.087 ↓ 1.0 479,121 1

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

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

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

  • Hash Cond: (pi.patient_id = p.patient_id)
33. 97.284 97.284 ↓ 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.031..97.284 rows=479,121 loops=1)

34. 80.826 3,420.053 ↑ 1.0 364,284 1

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

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

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

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

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

  • Hash Cond: (mst_pm_patient.patient_id = p.patient_id)
37. 63.367 63.367 ↑ 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..63.367 rows=364,284 loops=1)

38. 81.090 2,465.982 ↑ 1.0 364,284 1

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

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

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

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

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

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

CTE totalpatientaddress

42. 416.705 1,386.189 ↓ 1.0 367,317 1

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

43. 292.686 969.484 ↓ 1.0 367,317 1

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

44. 552.557 676.798 ↓ 1.0 367,317 1

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

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

46. 70.448 213.816 ↑ 1.0 364,284 1

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

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

48. 62.597 140.764 ↑ 1.0 364,284 1

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

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

50. 0.002 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.007 0.007 ↑ 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.007 rows=12 loops=1)

52. 0.004 0.120 ↑ 1.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
53. 0.025 0.116 ↑ 1.0 28 1

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

54. 0.007 0.007 ↑ 1.0 28 1

Seq Scan on mst_am_facility f (cost=0.00..1.28 rows=28 width=12) (actual time=0.004..0.007 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.001 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
57. 0.005 0.005 ↑ 1.0 3 1

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

58. 21.602 21.602 ↑ 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.007..0.007 rows=1 loops=3,086)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
60. 0.008 0.040 ↓ 1.0 27 1

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

61. 0.016 0.032 ↑ 1.0 29 1

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

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

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

63. 9.258 9.258 ↑ 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.003 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.360 ms
Execution time : 9,597.052 ms