explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x2d3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 13,843.797 99,669.351 ↓ 10.0 68,688,607 1

Hash Join (cost=3,580,547.13..3,807,163.46 rows=6,889,909 width=12) (actual time=62,816.535..99,669.351 rows=68,688,607 loops=1)

  • Hash Cond: (pp.orderingfacilityid = f.facility_id)
2. 22,902.691 85,825.497 ↓ 10.0 68,688,607 1

Hash Right Join (cost=3,580,542.41..3,712,406.12 rows=6,894,275 width=16) (actual time=62,816.469..85,825.497 rows=68,688,607 loops=1)

  • Hash Cond: (pcn.patient_id = p.patient_id)
3. 117.369 117.369 ↓ 1.0 306,274 1

Index Only Scan using mst_pm_contact_number_patient_id_default_contact_idx on mst_pm_contact_number pcn (cost=0.42..23,544.31 rows=304,212 width=8) (actual time=0.047..117.369 rows=306,274 loops=1)

  • Index Cond: (default_contact_number = true)
  • Filter: (default_contact_number IS TRUE)
  • Heap Fetches: 46557
4. 14,865.405 62,805.437 ↓ 10.0 68,688,607 1

Hash (cost=3,453,966.55..3,453,966.55 rows=6,894,275 width=24) (actual time=62,805.437..62,805.437 rows=68,688,607 loops=1)

  • Buckets: 262144 Batches: 64 (originally 4) Memory Usage: 102401kB
5. 13,423.707 47,940.032 ↓ 10.0 68,688,607 1

Hash Join (cost=3,186,365.47..3,453,966.55 rows=6,894,275 width=24) (actual time=29,753.865..47,940.032 rows=68,688,607 loops=1)

  • Hash Cond: (pp.patientid = p.patient_id)
6. 2,066.318 5,594.066 ↓ 1.0 2,303,680 1

Hash Join (cost=102,613.16..323,806.43 rows=2,281,781 width=24) (actual time=831.350..5,594.066 rows=2,303,680 loops=1)

  • Hash Cond: (pp.visitid = pv.visit_id)
7. 2,696.795 2,696.795 ↓ 1.0 2,303,680 1

Seq Scan on mat_patientdiagnosisprescriptiondata pp (cost=0.00..172,705.42 rows=2,281,781 width=32) (actual time=0.015..2,696.795 rows=2,303,680 loops=1)

  • Filter: ((upper((orderingfacility)::text) <> 'DC'::text) AND (upper((dispensingfacility)::text) <> 'DC'::text))
  • Rows Removed by Filter: 1091
8. 303.185 830.953 ↑ 1.0 1,859,275 1

Hash (cost=79,325.40..79,325.40 rows=1,863,021 width=8) (actual time=830.953..830.953 rows=1,859,275 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 72628kB
9. 527.768 527.768 ↑ 1.0 1,859,275 1

Index Only Scan using trx_pm_patient_visit_visit_id_idx on trx_pm_patient_visit pv (cost=0.43..79,325.40 rows=1,863,021 width=8) (actual time=0.053..527.768 rows=1,859,275 loops=1)

  • Heap Fetches: 471360
10. 734.567 28,922.259 ↓ 6.1 2,305,014 1

Hash (cost=3,079,043.47..3,079,043.47 rows=376,707 width=24) (actual time=28,922.259..28,922.259 rows=2,305,014 loops=1)

  • Buckets: 65536 Batches: 2 (originally 1) Memory Usage: 102401kB
11. 1,555.103 28,187.692 ↓ 6.1 2,305,014 1

Hash Join (cost=2,608,163.67..3,079,043.47 rows=376,707 width=24) (actual time=14,250.334..28,187.692 rows=2,305,014 loops=1)

  • Hash Cond: (ppo.patient_id = p.patient_id)
12. 1,298.208 23,648.291 ↓ 6.1 2,305,020 1

Hash Join (cost=2,474,063.19..2,937,408.72 rows=376,720 width=8) (actual time=11,265.787..23,648.291 rows=2,305,020 loops=1)

  • Hash Cond: (pv_1.patient_id = p_2.patient_id)
13. 1,636.283 22,208.206 ↓ 6.1 2,305,020 1

Hash Join (cost=2,453,304.73..2,909,115.86 rows=376,720 width=16) (actual time=11,123.586..22,208.206 rows=2,305,020 loops=1)

  • Hash Cond: (ve.visit_id = pv_1.visit_id)
14. 2,164.045 19,568.182 ↓ 6.1 2,305,020 1

Hash Join (cost=2,342,944.76..2,790,750.59 rows=376,720 width=16) (actual time=10,118.744..19,568.182 rows=2,305,020 loops=1)

  • Hash Cond: (c.visit_encounter_id = ve.visit_encounter_id)
15. 647.911 13,947.538 ↓ 6.1 2,305,020 1

Hash Join (cost=2,007,939.58..2,422,847.81 rows=377,072 width=16) (actual time=6,660.993..13,947.538 rows=2,305,020 loops=1)

  • Hash Cond: (po.route_of_administration_id = sl.simple_list_config_id)
16. 594.550 13,298.937 ↓ 6.1 2,305,020 1

Hash Join (cost=2,007,871.82..2,417,123.97 rows=377,072 width=24) (actual time=6,660.270..13,298.937 rows=2,305,020 loops=1)

  • Hash Cond: (po.form_id = sl1.simple_list_config_id)
17. 1,536.084 12,703.883 ↓ 6.1 2,305,412 1

Hash Join (cost=2,007,804.07..2,411,399.89 rows=377,122 width=32) (actual time=6,659.745..12,703.883 rows=2,305,412 loops=1)

  • Hash Cond: (ci.component_id = c.component_id)
18. 528.998 9,910.648 ↑ 1.5 2,305,412 1

Hash Join (cost=1,820,047.18..2,185,671.77 rows=3,420,001 width=32) (actual time=5,400.317..9,910.648 rows=2,305,412 loops=1)

  • Hash Cond: (po.duration_uom = du.duration_unit_id)
19. 751.361 9,381.640 ↑ 1.5 2,305,412 1

Hash Join (cost=1,820,046.11..2,138,645.69 rows=3,420,001 width=40) (actual time=5,400.263..9,381.640 rows=2,305,412 loops=1)

  • Hash Cond: (po.drug_id = dp.drug_id)
20. 1,983.729 8,627.963 ↑ 1.5 2,305,412 1

Hash Join (cost=1,819,826.19..2,078,575.75 rows=3,420,001 width=48) (actual time=5,397.896..8,627.963 rows=2,305,412 loops=1)

  • Hash Cond: (po.patient_order_id = ppo.patient_order_id)
21. 1,246.975 1,246.975 ↑ 1.5 2,312,037 1

Seq Scan on mst_om_prescription_order po (cost=0.00..190,339.27 rows=3,421,027 width=48) (actual time=0.018..1,246.975 rows=2,312,037 loops=1)

22. 270.026 5,397.259 ↑ 1.0 805,365 1

Hash (cost=1,809,729.92..1,809,729.92 rows=807,701 width=24) (actual time=5,397.259..5,397.259 rows=805,365 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 44044kB
23. 866.054 5,127.233 ↑ 1.0 805,365 1

Nested Loop (cost=0.43..1,809,729.92 rows=807,701 width=24) (actual time=0.046..5,127.233 rows=805,365 loops=1)

24. 222.674 222.674 ↑ 1.0 807,701 1

Seq Scan on mst_om_prescription_patient_order ppo (cost=0.00..32,282.43 rows=807,943 width=73) (actual time=0.011..222.674 rows=807,701 loops=1)

25. 4,038.505 4,038.505 ↑ 1.0 1 807,701

Index Scan using trx_cd_component_item_pkey on trx_cd_component_item ci (cost=0.43..2.19 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=807,701)

  • Index Cond: (item_id = ppo.item_id)
26. 0.612 2.316 ↑ 1.3 3,481 1

Hash (cost=165.24..165.24 rows=4,375 width=8) (actual time=2.316..2.316 rows=3,481 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 136kB
27. 1.704 1.704 ↑ 1.3 3,481 1

Index Only Scan using mst_md_drug_prescription_pkey on mst_md_drug_prescription dp (cost=0.28..165.24 rows=4,375 width=8) (actual time=0.063..1.704 rows=3,481 loops=1)

  • Heap Fetches: 2431
28. 0.004 0.010 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
29. 0.006 0.006 ↑ 1.0 3 1

Seq Scan on ref_cd_duration_unit du (cost=0.00..1.03 rows=3 width=8) (actual time=0.005..0.006 rows=3 loops=1)

30. 207.972 1,257.151 ↓ 1.1 826,085 1

Hash (cost=178,072.36..178,072.36 rows=774,762 width=16) (actual time=1,257.151..1,257.151 rows=826,085 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 38723kB
31. 946.521 1,049.179 ↓ 1.1 826,085 1

Bitmap Heap Scan on trx_cd_component c (cost=14,362.84..178,072.36 rows=774,762 width=16) (actual time=134.171..1,049.179 rows=826,085 loops=1)

  • Recheck Cond: (type = 64)
  • Heap Blocks: exact=125463
32. 102.658 102.658 ↓ 1.1 857,121 1

Bitmap Index Scan on trx_cd_component_type_idx (cost=0.00..14,169.15 rows=774,762 width=0) (actual time=102.658..102.658 rows=857,121 loops=1)

  • Index Cond: (type = 64)
33. 0.305 0.504 ↑ 1.0 1,761 1

Hash (cost=45.67..45.67 rows=1,767 width=8) (actual time=0.504..0.504 rows=1,761 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
34. 0.199 0.199 ↑ 1.0 1,761 1

Seq Scan on ref_am_simple_list_config sl1 (cost=0.00..45.67 rows=1,767 width=8) (actual time=0.003..0.199 rows=1,761 loops=1)

35. 0.308 0.690 ↑ 1.0 1,761 1

Hash (cost=45.67..45.67 rows=1,767 width=8) (actual time=0.690..0.690 rows=1,761 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
36. 0.382 0.382 ↑ 1.0 1,761 1

Seq Scan on ref_am_simple_list_config sl (cost=0.00..45.67 rows=1,767 width=8) (actual time=0.032..0.382 rows=1,761 loops=1)

37. 1,331.248 3,456.599 ↓ 1.1 4,536,028 1

Hash (cost=261,198.19..261,198.19 rows=4,245,919 width=16) (actual time=3,456.599..3,456.599 rows=4,536,028 loops=1)

  • Buckets: 262144 Batches: 4 (originally 2) Memory Usage: 102401kB
38. 2,125.351 2,125.351 ↓ 1.1 4,536,028 1

Seq Scan on trx_pm_visit_encounter ve (cost=0.00..261,198.19 rows=4,245,919 width=16) (actual time=0.023..2,125.351 rows=4,536,028 loops=1)

39. 369.961 1,003.741 ↑ 1.0 1,859,275 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 87154kB
40. 633.780 633.780 ↑ 1.0 1,859,275 1

Seq Scan on trx_pm_patient_visit pv_1 (cost=0.00..87,072.21 rows=1,863,021 width=16) (actual time=0.005..633.780 rows=1,859,275 loops=1)

41. 62.402 141.877 ↑ 1.0 364,284 1

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

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

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

43. 70.121 2,984.298 ↑ 1.0 364,282 1

Hash (cost=129,545.78..129,545.78 rows=364,376 width=16) (actual time=2,984.298..2,984.298 rows=364,282 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 17076kB
44. 82.477 2,914.177 ↑ 1.0 364,282 1

Hash Join (cost=101,044.33..129,545.78 rows=364,376 width=16) (actual time=2,456.419..2,914.177 rows=364,282 loops=1)

  • Hash Cond: (p.gender_gender_id = g.gender_id)
45. 316.524 2,831.689 ↑ 1.0 364,284 1

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

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

47. 86.721 2,456.137 ↑ 1.0 364,284 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 15653kB
48. 380.857 2,369.416 ↑ 1.0 364,284 1

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

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

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

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

CTE totalpatientaddress

51. 423.620 1,355.513 ↓ 1.0 367,317 1

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

52. 299.880 931.893 ↓ 1.0 367,317 1

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

53. 509.961 632.013 ↓ 1.0 367,317 1

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

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

55. 63.309 219.950 ↑ 1.0 364,284 1

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

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

57. 0.004 0.011 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
58. 0.007 0.007 ↑ 1.0 3 1

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

59. 0.002 0.057 ↑ 1.0 28 1

Hash (cost=4.37..4.37 rows=28 width=4) (actual time=0.057..0.057 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
60. 0.017 0.055 ↑ 1.0 28 1

Hash Left Join (cost=2.72..4.37 rows=28 width=4) (actual time=0.041..0.055 rows=28 loops=1)

  • Hash Cond: (f.facility_id = mst_am_facility_address.facility_id)
61. 0.003 0.003 ↑ 1.0 28 1

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

62. 0.004 0.035 ↓ 1.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
63. 0.006 0.031 ↓ 1.0 27 1

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

64. 0.013 0.025 ↑ 1.0 29 1

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

  • Sort Key: mst_am_facility_address.facility_id
  • Sort Method: quicksort Memory: 26kB
65. 0.012 0.012 ↑ 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.012 rows=29 loops=1)

Planning time : 9.089 ms
Execution time : 102,177.202 ms