explain.depesz.com

PostgreSQL's explain analyze made readable

Result: p83d : Optimization for: plan #x2d3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13,578.716 95,169.871 ↓ 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=59,342.945..95,169.871 rows=68,688,607 loops=1)

  • Hash Cond: (pp.orderingfacilityid = f.facility_id)
2. 22,161.123 81,591.096 ↓ 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=59,342.876..81,591.096 rows=68,688,607 loops=1)

  • Hash Cond: (pcn.patient_id = p.patient_id)
3. 88.588 88.588 ↓ 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.039..88.588 rows=306,274 loops=1)

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

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

  • Buckets: 262144 Batches: 64 (originally 4) Memory Usage: 102401kB
5. 13,405.885 44,482.722 ↓ 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=26,300.533..44,482.722 rows=68,688,607 loops=1)

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

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

  • Hash Cond: (pp.visitid = pv.visit_id)
7. 2,704.023 2,704.023 ↓ 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.017..2,704.023 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.338 798.712 ↑ 1.0 1,859,275 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 72628kB
9. 495.374 495.374 ↑ 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.012..495.374 rows=1,859,275 loops=1)

  • Heap Fetches: 471360
10. 727.646 25,500.886 ↓ 6.1 2,305,014 1

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

  • Buckets: 65536 Batches: 2 (originally 1) Memory Usage: 102401kB
11. 1,509.991 24,773.240 ↓ 6.1 2,305,014 1

Hash Join (cost=2,608,163.67..3,079,043.47 rows=376,707 width=24) (actual time=11,861.673..24,773.240 rows=2,305,014 loops=1)

  • Hash Cond: (ppo.patient_id = p.patient_id)
12. 1,274.208 20,292.033 ↓ 6.1 2,305,020 1

Hash Join (cost=2,474,063.19..2,937,408.72 rows=376,720 width=8) (actual time=8,890.214..20,292.033 rows=2,305,020 loops=1)

  • Hash Cond: (pv_1.patient_id = p_2.patient_id)
13. 1,584.365 18,874.695 ↓ 6.1 2,305,020 1

Hash Join (cost=2,453,304.73..2,909,115.86 rows=376,720 width=16) (actual time=8,746.956..18,874.695 rows=2,305,020 loops=1)

  • Hash Cond: (ve.visit_id = pv_1.visit_id)
14. 2,106.624 16,264.453 ↓ 6.1 2,305,020 1

Hash Join (cost=2,342,944.76..2,790,750.59 rows=376,720 width=16) (actual time=7,720.687..16,264.453 rows=2,305,020 loops=1)

  • Hash Cond: (c.visit_encounter_id = ve.visit_encounter_id)
15. 610.357 11,028.660 ↓ 6.1 2,305,020 1

Hash Join (cost=2,007,939.58..2,422,847.81 rows=377,072 width=16) (actual time=4,591.000..11,028.660 rows=2,305,020 loops=1)

  • Hash Cond: (po.route_of_administration_id = sl.simple_list_config_id)
16. 556.941 10,417.789 ↓ 6.1 2,305,020 1

Hash Join (cost=2,007,871.82..2,417,123.97 rows=377,072 width=24) (actual time=4,590.453..10,417.789 rows=2,305,020 loops=1)

  • Hash Cond: (po.form_id = sl1.simple_list_config_id)
17. 1,461.090 9,860.405 ↓ 6.1 2,305,412 1

Hash Join (cost=2,007,804.07..2,411,399.89 rows=377,122 width=32) (actual time=4,589.997..9,860.405 rows=2,305,412 loops=1)

  • Hash Cond: (ci.component_id = c.component_id)
18. 514.083 7,536.233 ↑ 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=3,726.710..7,536.233 rows=2,305,412 loops=1)

  • Hash Cond: (po.duration_uom = du.duration_unit_id)
19. 725.490 7,022.143 ↑ 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=3,726.680..7,022.143 rows=2,305,412 loops=1)

  • Hash Cond: (po.drug_id = dp.drug_id)
20. 2,103.052 6,294.632 ↑ 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=3,724.644..6,294.632 rows=2,305,412 loops=1)

  • Hash Cond: (po.patient_order_id = ppo.patient_order_id)
21. 467.355 467.355 ↑ 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.005..467.355 rows=2,312,037 loops=1)

22. 208.670 3,724.225 ↑ 1.0 805,365 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 44044kB
23. 993.763 3,515.555 ↑ 1.0 805,365 1

Nested Loop (cost=0.43..1,809,729.92 rows=807,701 width=24) (actual time=0.030..3,515.555 rows=805,365 loops=1)

24. 98.689 98.689 ↑ 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.004..98.689 rows=807,701 loops=1)

25. 2,423.103 2,423.103 ↑ 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.003..0.003 rows=1 loops=807,701)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 136kB
27. 1.490 1.490 ↑ 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.026..1.490 rows=3,481 loops=1)

  • Heap Fetches: 2431
28. 0.003 0.007 ↑ 1.0 3 1

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

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

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

30. 147.944 863.082 ↓ 1.1 826,085 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 38723kB
31. 635.530 715.138 ↓ 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=108.769..715.138 rows=826,085 loops=1)

  • Recheck Cond: (type = 64)
  • Heap Blocks: exact=125463
32. 79.608 79.608 ↓ 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=79.608..79.608 rows=857,121 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
34. 0.206 0.206 ↑ 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.002..0.206 rows=1,761 loops=1)

35. 0.227 0.514 ↑ 1.0 1,761 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
36. 0.287 0.287 ↑ 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.006..0.287 rows=1,761 loops=1)

37. 1,263.439 3,129.169 ↓ 1.1 4,536,028 1

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

  • Buckets: 262144 Batches: 4 (originally 2) Memory Usage: 102401kB
38. 1,865.730 1,865.730 ↓ 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.008..1,865.730 rows=4,536,028 loops=1)

39. 342.523 1,025.877 ↑ 1.0 1,859,275 1

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

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

41. 55.783 143.130 ↑ 1.0 364,284 1

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

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

43. 65.423 2,971.216 ↑ 1.0 364,282 1

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

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

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

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

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

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

47. 78.536 2,432.657 ↑ 1.0 364,284 1

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

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

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

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

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

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

CTE totalpatientaddress

51. 419.750 1,330.950 ↓ 1.0 367,317 1

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

52. 298.197 911.200 ↓ 1.0 367,317 1

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

53. 491.245 613.003 ↓ 1.0 367,317 1

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

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

55. 61.768 220.768 ↑ 1.0 364,284 1

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

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

57. 0.005 0.010 ↑ 1.0 3 1

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

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

59. 0.007 0.059 ↑ 1.0 28 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
60. 0.013 0.052 ↑ 1.0 28 1

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

  • Hash Cond: (f.facility_id = mst_am_facility_address.facility_id)
61. 0.002 0.002 ↑ 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.002 rows=28 loops=1)

62. 0.005 0.037 ↓ 1.0 27 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
63. 0.009 0.032 ↓ 1.0 27 1

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

64. 0.009 0.023 ↑ 1.0 29 1

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

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

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

Planning time : 8.630 ms
Execution time : 97,537.860 ms