explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jEZj

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.635 4,861.929 ↓ 96.1 769 1

Nested Loop Left Join (cost=10,590,547.91..11,095,500.17 rows=8 width=5) (actual time=4,837.477..4,861.929 rows=769 loops=1)

2. 0.300 4,857.449 ↓ 96.1 769 1

Hash Left Join (cost=10,590,547.48..11,095,496.13 rows=8 width=13) (actual time=4,837.455..4,857.449 rows=769 loops=1)

  • Hash Cond: (f.facility_id = mst_am_facility_address.facility_id)
3. 0.583 4,857.104 ↓ 96.1 769 1

Nested Loop (cost=10,590,544.76..11,095,493.31 rows=8 width=17) (actual time=4,837.383..4,857.104 rows=769 loops=1)

4. 7.444 4,849.600 ↓ 12.6 769 1

Merge Join (cost=10,590,544.32..11,095,464.67 rows=61 width=25) (actual time=4,837.346..4,849.600 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.629 59.996 ↑ 13,999.6 2,396 1

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

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

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

7. 0.840 33.471 ↑ 14.6 1,730 1

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

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

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

9. 1.282 23.493 ↑ 14.6 1,730 1

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

10. 1.385 13.561 ↑ 14.6 1,730 1

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

11. 3.224 3.526 ↑ 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.540..3.526 rows=1,730 loops=1)

  • Recheck Cond: (type = ANY ('{97,98}'::integer[]))
  • Heap Blocks: exact=1649
12. 0.302 0.302 ↑ 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.302..0.302 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.335 1.009 ↑ 1.0 1,623 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 99kB
17. 0.674 0.674 ↑ 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.674 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.004..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. 22.130 4,782.160 ↑ 1.5 73,085 1

Sort (cost=356,069.94..356,337.93 rows=107,198 width=25) (actual time=4,778.078..4,782.160 rows=73,085 loops=1)

  • Sort Key: pdd.patientid
  • Sort Method: quicksort Memory: 8695kB
23. 19.128 4,760.030 ↑ 1.5 71,973 1

Hash Join (cost=329,044.50..347,113.59 rows=107,198 width=25) (actual time=3,136.876..4,760.030 rows=71,973 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
24. 19.122 4,740.896 ↑ 1.5 71,973 1

Hash Join (cost=329,043.44..345,638.55 rows=107,198 width=29) (actual time=3,136.858..4,740.896 rows=71,973 loops=1)

  • Hash Cond: (pv.clinic_id = f.facility_id)
25. 18.766 4,721.653 ↑ 1.5 71,973 1

Hash Join (cost=329,013.38..344,119.63 rows=111,168 width=29) (actual time=3,136.726..4,721.653 rows=71,973 loops=1)

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

Hash Join (cost=329,012.11..342,589.80 rows=111,168 width=33) (actual time=3,136.701..4,702.880 rows=71,973 loops=1)

  • Hash Cond: (pv.patient_id = mst_pm_patient.patient_id)
27. 54.101 3,888.575 ↑ 1.7 43,578 1

Hash Join (cost=278,122.43..289,845.89 rows=75,997 width=45) (actual time=2,387.127..3,888.575 rows=43,578 loops=1)

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

Hash Right Join (cost=257,363.97..267,567.49 rows=75,997 width=37) (actual time=2,244.830..3,692.465 rows=43,578 loops=1)

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

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

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

CTE totalpatientaddress

31. 420.090 1,351.634 ↓ 1.0 367,317 1

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

32. 290.173 931.544 ↓ 1.0 367,317 1

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

33. 518.527 641.371 ↓ 1.0 367,317 1

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

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

35. 11.575 1,698.979 ↑ 1.7 43,578 1

Hash (cost=192,621.68..192,621.68 rows=75,997 width=37) (actual time=1,698.979..1,698.979 rows=43,578 loops=1)

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

Hash Join (cost=142,035.43..192,621.68 rows=75,997 width=37) (actual time=1,289.180..1,687.404 rows=43,578 loops=1)

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

Hash Join (cost=121,276.97..170,343.28 rows=75,997 width=25) (actual time=1,073.914..1,420.549 rows=43,578 loops=1)

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

Seq Scan on mat_patientdiagnosisdata pdd (cost=0.00..35,927.36 rows=75,997 width=21) (actual time=0.032..200.006 rows=43,578 loops=1)

  • Filter: ((diagnosiscode)::text ~~ 'I10%'::text)
  • Rows Removed by Filter: 918131
39. 428.298 1,072.742 ↑ 1.0 1,859,275 1

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

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

41. 71.045 214.971 ↑ 1.0 364,284 1

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

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

43. 62.335 142.009 ↑ 1.0 364,284 1

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

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

45. 101.919 749.285 ↓ 1.0 479,121 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 24331kB
46. 451.575 647.366 ↓ 1.0 479,121 1

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

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

48. 61.336 142.574 ↑ 1.0 364,284 1

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

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

50. 0.003 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
51. 0.004 0.004 ↑ 1.0 12 1

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

52. 0.003 0.121 ↑ 1.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
53. 0.008 0.118 ↑ 1.0 27 1

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

54. 0.056 0.056 ↑ 1.0 27 1

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

  • Filter: (upper((facility_name)::text) <> 'DC'::text)
  • Rows Removed by Filter: 1
55. 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
56. 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
57. 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.003..0.004 rows=3 loops=1)

58. 6.921 6.921 ↑ 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.009..0.009 rows=1 loops=769)

  • Index Cond: (visit_encounter_id = ve.visit_encounter_id)
  • Heap Fetches: 769
59. 0.005 0.045 ↓ 1.0 27 1

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

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

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

61. 0.018 0.033 ↑ 1.0 29 1

Sort (cost=1.99..2.07 rows=29 width=12) (actual time=0.032..0.033 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.007..0.015 rows=29 loops=1)

63. 3.845 3.845 ↑ 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.005..0.005 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.680 ms
Execution time : 4,897.813 ms