explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oqFh

Settings
# exclusive inclusive rows x rows loops node
1. 3.804 58,754.613 ↓ 238.2 953 1

Sort (cost=50,462.74..50,462.75 rows=4 width=842) (actual time=58,754.489..58,754.613 rows=953 loops=1)

  • Sort Key: (date(b.open_date))
  • Sort Method: quicksort Memory: 483kB
2. 0.324 58,750.809 ↓ 238.2 953 1

Append (cost=14,309.75..50,462.70 rows=4 width=842) (actual time=492.586..58,750.809 rows=953 loops=1)

3. 2.635 516.977 ↓ 216.7 650 1

Nested Loop Left Join (cost=14,309.75..26,093.55 rows=3 width=836) (actual time=492.585..516.977 rows=650 loops=1)

4. 0.421 514.342 ↓ 216.7 650 1

Nested Loop Left Join (cost=14,309.60..26,089.31 rows=3 width=321) (actual time=492.541..514.342 rows=650 loops=1)

5. 0.401 511.321 ↓ 216.7 650 1

Nested Loop Left Join (cost=14,309.47..26,088.83 rows=3 width=326) (actual time=492.529..511.321 rows=650 loops=1)

6. 5.340 507.020 ↓ 216.7 650 1

Merge Right Join (cost=14,309.33..26,088.32 rows=3 width=315) (actual time=492.507..507.020 rows=650 loops=1)

  • Merge Cond: ((mrd_diagnosis.visit_id)::text = (pr.patient_id)::text)
7. 167.559 186.402 ↑ 1.1 6,195 1

GroupAggregate (cost=0.29..11,690.89 rows=7,069 width=77) (actual time=0.144..186.402 rows=6,195 loops=1)

8. 18.843 18.843 ↑ 1.0 12,704 1

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis (cost=0.29..1,639.04 rows=13,264 width=77) (actual time=0.021..18.843 rows=12,704 loops=1)

  • Filter: (diag_type = 'S'::bpchar)
  • Rows Removed by Filter: 14138
9. 4.570 315.278 ↓ 216.7 650 1

Sort (cost=14,309.04..14,309.05 rows=3 width=260) (actual time=315.123..315.278 rows=650 loops=1)

  • Sort Key: pr.patient_id
  • Sort Method: quicksort Memory: 324kB
10. 0.561 310.708 ↓ 216.7 650 1

Nested Loop Left Join (cost=6,984.49..14,309.02 rows=3 width=260) (actual time=143.665..310.708 rows=650 loops=1)

11. 0.557 303.657 ↓ 216.3 649 1

Nested Loop Left Join (cost=6,984.20..14,300.00 rows=3 width=198) (actual time=143.646..303.657 rows=649 loops=1)

12. 0.949 282.332 ↓ 216.3 649 1

Hash Join (cost=6,983.21..14,277.23 rows=3 width=189) (actual time=143.579..282.332 rows=649 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
13. 5.000 281.364 ↓ 11.6 2,467 1

Nested Loop Left Join (cost=6,981.71..14,274.90 rows=213 width=181) (actual time=143.542..281.364 rows=2,467 loops=1)

  • Join Filter: ((pbcc.charge_id)::text = (bcc.charge_id)::text)
  • Rows Removed by Join Filter: 11081
14. 1.695 239.359 ↓ 11.6 2,467 1

Hash Join (cost=6,981.29..13,325.19 rows=213 width=177) (actual time=143.507..239.359 rows=2,467 loops=1)

  • Hash Cond: ((bcc.charge_head)::text = (chc.chargehead_id)::text)
15. 4.249 237.617 ↓ 11.6 2,467 1

Nested Loop (cost=6,977.01..13,317.98 rows=213 width=167) (actual time=143.445..237.617 rows=2,467 loops=1)

16. 2.766 202.968 ↓ 8.7 1,600 1

Nested Loop Left Join (cost=6,976.59..10,491.84 rows=184 width=104) (actual time=143.399..202.968 rows=1,600 loops=1)

17. 2.216 179.402 ↓ 8.7 1,600 1

Nested Loop (cost=6,976.18..9,897.31 rows=184 width=67) (actual time=143.368..179.402 rows=1,600 loops=1)

18. 7.854 159.586 ↓ 7.2 1,600 1

Hash Right Join (cost=6,975.76..8,262.52 rows=222 width=43) (actual time=143.280..159.586 rows=1,600 loops=1)

  • Hash Cond: ((pbcl.bill_no)::text = (b.bill_no)::text)
19. 10.545 10.545 ↓ 1.0 44,033 1

Seq Scan on bill_claim pbcl (cost=0.00..1,121.39 rows=43,868 width=23) (actual time=0.009..10.545 rows=44,033 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2
20. 1.192 141.187 ↓ 7.2 1,600 1

Hash (cost=6,972.99..6,972.99 rows=222 width=32) (actual time=141.187..141.187 rows=1,600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 100kB
21. 139.995 139.995 ↓ 7.2 1,600 1

Seq Scan on bill b (cost=0.00..6,972.99 rows=222 width=32) (actual time=0.185..139.995 rows=1,600 loops=1)

  • Filter: (is_tpa AND (status <> 'X'::bpchar) AND (date(open_date) >= '2017-09-01'::date) AND (date(open_date) <= '2017-09-30'::date))
  • Rows Removed by Filter: 110674
22. 17.600 17.600 ↑ 1.0 1 1,600

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.41..7.35 rows=1 width=33) (actual time=0.011..0.011 rows=1 loops=1,600)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
23. 20.800 20.800 ↑ 1.0 1 1,600

Index Scan using patient_details_pkey on patient_details pd (cost=0.41..3.22 rows=1 width=47) (actual time=0.013..0.013 rows=1 loops=1,600)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
24. 30.400 30.400 ↑ 1.0 2 1,600

Index Scan using bill_charge_bill_no_index on bill_charge bcc (cost=0.42..15.34 rows=2 width=87) (actual time=0.017..0.019 rows=2 loops=1,600)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{PHMED,PHCMED,PHRET,PHCRET}'::text[])))
  • Rows Removed by Filter: 0
25. 0.027 0.047 ↑ 1.0 57 1

Hash (cost=3.57..3.57 rows=57 width=22) (actual time=0.047..0.047 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
26. 0.020 0.020 ↑ 1.0 57 1

Seq Scan on chargehead_constants chc (cost=0.00..3.57 rows=57 width=22) (actual time=0.005..0.020 rows=57 loops=1)

27. 37.005 37.005 ↓ 1.7 5 2,467

Index Scan using bill_charge_claim_claim_idx on bill_charge_claim pbcc (cost=0.42..4.42 rows=3 width=33) (actual time=0.014..0.015 rows=5 loops=2,467)

  • Index Cond: ((pbcl.claim_id)::text = (claim_id)::text)
28. 0.003 0.019 ↑ 1.0 1 1

Hash (cost=1.49..1.49 rows=1 width=28) (actual time=0.019..0.019 rows=1 loops=1)

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

Seq Scan on tpa_master ptpa (cost=0.00..1.49 rows=1 width=28) (actual time=0.007..0.016 rows=1 loops=1)

  • Filter: ((tpa_name)::text = 'GLOBEMED BAHRAIN W.L.L'::text)
  • Rows Removed by Filter: 38
30. 0.649 20.768 ↑ 1.0 1 649

Nested Loop Left Join (cost=0.99..7.58 rows=1 width=18) (actual time=0.031..0.032 rows=1 loops=649)

31. 1.298 14.278 ↑ 1.0 1 649

Nested Loop Left Join (cost=0.70..4.67 rows=1 width=18) (actual time=0.021..0.022 rows=1 loops=649)

32. 6.490 6.490 ↑ 1.0 1 649

Index Only Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.41..1.76 rows=1 width=9) (actual time=0.010..0.010 rows=1 loops=649)

  • Index Cond: (patient_id = (pr.patient_id)::text)
  • Heap Fetches: 145
33. 6.490 6.490 ↑ 1.0 1 649

Index Scan using policy_patient_idx on patient_insurance_plans ppip (cost=0.29..2.90 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=649)

  • Index Cond: ((patient_id)::text = (pr_1.patient_id)::text)
  • Filter: (priority = 1)
34. 5.841 5.841 ↓ 0.0 0 649

Index Scan using policy_patient_idx on patient_insurance_plans spip (cost=0.29..2.90 rows=1 width=23) (actual time=0.009..0.009 rows=0 loops=649)

  • Index Cond: ((patient_id)::text = (pr_1.patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
35. 6.490 6.490 ↑ 1.0 1 649

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis pdiag (cost=0.29..3.00 rows=1 width=71) (actual time=0.010..0.010 rows=1 loops=649)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 0
36. 3.900 3.900 ↑ 1.0 1 650

Index Scan using insurance_company_master_pkey on insurance_company_master picm (cost=0.14..0.16 rows=1 width=29) (actual time=0.005..0.006 rows=1 loops=650)

  • Index Cond: ((insurance_co_id)::text = (ppip.insurance_co)::text)
37. 2.600 2.600 ↑ 1.0 1 650

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.13..0.15 rows=1 width=13) (actual time=0.003..0.004 rows=1 loops=650)

  • Index Cond: ((pd.salutation)::text = (salutation_id)::text)
38. 0.000 0.000 ↓ 0.0 0 650

Index Scan using insurance_denial_codes_pkey on insurance_denial_codes pidc (cost=0.14..1.36 rows=1 width=564) (actual time=0.000..0.000 rows=0 loops=650)

  • Index Cond: ((denial_code)::text = (pbcc.denial_code)::text)
39. 0.598 58,233.508 ↓ 303.0 303 1

Subquery Scan on *SELECT* 2 (cost=9,936.73..24,369.12 rows=1 width=860) (actual time=367.687..58,233.508 rows=303 loops=1)

40. 485.925 58,232.910 ↓ 303.0 303 1

Nested Loop Left Join (cost=9,936.73..24,369.11 rows=1 width=860) (actual time=367.684..58,232.910 rows=303 loops=1)

  • Join Filter: ((mrd_diagnosis_1.visit_id)::text = (pr_2.patient_id)::text)
  • Rows Removed by Join Filter: 1956712
41. 0.893 301.518 ↓ 303.0 303 1

Nested Loop Left Join (cost=9,936.44..12,519.11 rows=1 width=805) (actual time=169.960..301.518 rows=303 loops=1)

42. 1.088 296.686 ↓ 303.0 303 1

Nested Loop Left Join (cost=9,936.15..12,515.18 rows=1 width=743) (actual time=169.935..296.686 rows=303 loops=1)

43. 1.007 295.598 ↓ 303.0 303 1

Nested Loop Left Join (cost=9,936.01..12,513.41 rows=1 width=228) (actual time=169.932..295.598 rows=303 loops=1)

44. 1.117 292.167 ↓ 303.0 303 1

Nested Loop Left Join (cost=9,935.88..12,513.25 rows=1 width=233) (actual time=169.918..292.167 rows=303 loops=1)

45. 1.312 288.323 ↓ 303.0 303 1

Nested Loop Left Join (cost=9,935.73..12,513.08 rows=1 width=222) (actual time=169.901..288.323 rows=303 loops=1)

46. 1.606 272.467 ↓ 303.0 303 1

Hash Join (cost=9,934.74..12,503.20 rows=1 width=213) (actual time=169.833..272.467 rows=303 loops=1)

  • Hash Cond: ((pr_2.primary_sponsor_id)::text = (ptpa_1.tpa_id)::text)
47. 3.410 270.844 ↓ 28.4 1,476 1

Nested Loop Left Join (cost=9,933.24..12,501.49 rows=52 width=205) (actual time=167.581..270.844 rows=1,476 loops=1)

48. 2.772 243.818 ↓ 28.4 1,476 1

Nested Loop (cost=9,932.83..12,288.93 rows=52 width=168) (actual time=167.551..243.818 rows=1,476 loops=1)

49. 2.237 220.382 ↓ 23.4 1,476 1

Nested Loop Left Join (cost=9,932.41..11,784.89 rows=63 width=144) (actual time=167.527..220.382 rows=1,476 loops=1)

  • Join Filter: ((pbcl_1.claim_id)::text = (pscl.claim_id)::text)
50. 2.842 210.765 ↓ 23.4 1,476 1

Nested Loop (cost=9,932.12..11,542.73 rows=63 width=138) (actual time=167.513..210.765 rows=1,476 loops=1)

51. 2.568 200.543 ↓ 23.4 1,476 1

Nested Loop (cost=9,931.84..11,419.25 rows=63 width=114) (actual time=167.481..200.543 rows=1,476 loops=1)

52. 0.657 181.370 ↓ 18.1 615 1

Hash Join (cost=9,931.42..11,218.12 rows=34 width=81) (actual time=167.404..181.370 rows=615 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc_1.chargehead_id)::text)
53. 6.990 180.678 ↓ 18.1 615 1

Hash Right Join (cost=9,927.14..11,213.37 rows=34 width=71) (actual time=167.355..180.678 rows=615 loops=1)

  • Hash Cond: ((pbcl_1.bill_no)::text = (ssm.bill_no)::text)
54. 13.347 13.347 ↓ 1.0 44,033 1

Seq Scan on bill_claim pbcl_1 (cost=0.00..1,121.39 rows=43,868 width=23) (actual time=0.009..13.347 rows=44,033 loops=1)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 2
55. 0.384 160.341 ↓ 18.1 615 1

Hash (cost=9,926.72..9,926.72 rows=34 width=72) (actual time=160.341..160.341 rows=615 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
56. 18.801 159.957 ↓ 18.1 615 1

Hash Join (cost=8,392.99..9,926.72 rows=34 width=72) (actual time=132.999..159.957 rows=615 loops=1)

  • Hash Cond: ((ssm.charge_id)::text = (bc.charge_id)::text)
57. 8.396 8.396 ↓ 1.0 43,900 1

Seq Scan on store_sales_main ssm (cost=0.00..1,369.37 rows=43,737 width=32) (actual time=0.003..8.396 rows=43,900 loops=1)

58. 2.326 132.760 ↓ 22.3 3,082 1

Hash (cost=8,391.27..8,391.27 rows=138 width=58) (actual time=132.760..132.760 rows=3,082 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 287kB
59. 2.492 130.434 ↓ 22.3 3,082 1

Nested Loop (cost=0.42..8,391.27 rows=138 width=58) (actual time=0.153..130.434 rows=3,082 loops=1)

60. 102.342 102.342 ↓ 17.8 1,600 1

Seq Scan on bill b_1 (cost=0.00..6,972.99 rows=90 width=32) (actual time=0.119..102.342 rows=1,600 loops=1)

  • Filter: (is_tpa AND is_tpa AND (status <> 'X'::bpchar) AND (date(open_date) >= '2017-09-01'::date) AND (date(open_date) <= '2017-09-30'::date))
  • Rows Removed by Filter: 110674
61. 25.600 25.600 ↑ 1.5 2 1,600

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.42..15.73 rows=3 width=50) (actual time=0.015..0.016 rows=2 loops=1,600)

  • Index Cond: ((bill_no)::text = (b_1.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
62. 0.017 0.035 ↑ 1.0 57 1

Hash (cost=3.57..3.57 rows=57 width=22) (actual time=0.035..0.035 rows=57 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
63. 0.018 0.018 ↑ 1.0 57 1

Seq Scan on chargehead_constants chc_1 (cost=0.00..3.57 rows=57 width=22) (actual time=0.005..0.018 rows=57 loops=1)

64. 16.605 16.605 ↑ 1.0 2 615

Index Scan using pharmacy_medicine_sales_sale_id_idx on store_sales_details s (cost=0.42..5.90 rows=2 width=55) (actual time=0.024..0.027 rows=2 loops=615)

  • Index Cond: ((sale_id)::text = (ssm.sale_id)::text)
65. 7.380 7.380 ↑ 1.0 1 1,476

Index Only Scan using store_item_mas_indx on store_item_details m (cost=0.28..1.95 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1,476)

  • Index Cond: (medicine_id = s.medicine_id)
  • Heap Fetches: 47
66. 7.380 7.380 ↑ 1.0 1 1,476

Index Scan using sales_claim_sale_item_idx on sales_claim_details pscl (cost=0.29..3.83 rows=1 width=25) (actual time=0.004..0.005 rows=1 loops=1,476)

  • Index Cond: (s.sale_item_id = sale_item_id)
67. 20.664 20.664 ↑ 1.0 1 1,476

Index Scan using patient_registration_pkey on patient_registration pr_2 (cost=0.41..7.99 rows=1 width=33) (actual time=0.014..0.014 rows=1 loops=1,476)

  • Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
68. 23.616 23.616 ↑ 1.0 1 1,476

Index Scan using patient_details_pkey on patient_details pd_1 (cost=0.41..4.08 rows=1 width=47) (actual time=0.015..0.016 rows=1 loops=1,476)

  • Index Cond: ((mr_no)::text = (pr_2.mr_no)::text)
69. 0.003 0.017 ↑ 1.0 1 1

Hash (cost=1.49..1.49 rows=1 width=28) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
70. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on tpa_master ptpa_1 (cost=0.00..1.49 rows=1 width=28) (actual time=0.007..0.014 rows=1 loops=1)

  • Filter: ((tpa_name)::text = 'GLOBEMED BAHRAIN W.L.L'::text)
  • Rows Removed by Filter: 38
71. 1.212 14.544 ↑ 1.0 1 303

Nested Loop Left Join (cost=0.99..9.87 rows=1 width=18) (actual time=0.043..0.048 rows=1 loops=303)

72. 1.212 10.302 ↑ 1.0 1 303

Nested Loop Left Join (cost=0.70..6.04 rows=1 width=18) (actual time=0.030..0.034 rows=1 loops=303)

73. 3.636 3.636 ↑ 1.0 1 303

Index Only Scan using patient_registration_pkey on patient_registration pr_3 (cost=0.41..2.21 rows=1 width=9) (actual time=0.010..0.012 rows=1 loops=303)

  • Index Cond: (patient_id = (pr_2.patient_id)::text)
  • Heap Fetches: 120
74. 5.454 5.454 ↑ 1.0 1 303

Index Scan using policy_patient_idx on patient_insurance_plans ppip_1 (cost=0.29..3.82 rows=1 width=32) (actual time=0.016..0.018 rows=1 loops=303)

  • Index Cond: ((patient_id)::text = (pr_3.patient_id)::text)
  • Filter: (priority = 1)
75. 3.030 3.030 ↓ 0.0 0 303

Index Scan using policy_patient_idx on patient_insurance_plans spip_1 (cost=0.29..3.82 rows=1 width=23) (actual time=0.010..0.010 rows=0 loops=303)

  • Index Cond: ((patient_id)::text = (pr_3.patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
76. 2.727 2.727 ↑ 1.0 1 303

Index Scan using insurance_company_master_pkey on insurance_company_master picm_1 (cost=0.14..0.16 rows=1 width=29) (actual time=0.008..0.009 rows=1 loops=303)

  • Index Cond: ((insurance_co_id)::text = (ppip_1.insurance_co)::text)
77. 2.424 2.424 ↑ 1.0 1 303

Index Scan using salutation_master_pkey on salutation_master sm_1 (cost=0.13..0.15 rows=1 width=13) (actual time=0.006..0.008 rows=1 loops=303)

  • Index Cond: ((pd_1.salutation)::text = (salutation_id)::text)
78. 0.000 0.000 ↓ 0.0 0 303

Index Scan using insurance_denial_codes_pkey on insurance_denial_codes pidc_1 (cost=0.14..1.76 rows=1 width=564) (actual time=0.000..0.000 rows=0 loops=303)

  • Index Cond: ((denial_code)::text = (pscl.denial_code)::text)
79. 3.939 3.939 ↑ 1.0 1 303

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis pdiag_1 (cost=0.29..3.92 rows=1 width=71) (actual time=0.012..0.013 rows=1 loops=303)

  • Index Cond: ((visit_id)::text = (pr_2.patient_id)::text)
  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 1
80. 51,581.508 57,445.467 ↑ 1.1 6,458 303

GroupAggregate (cost=0.29..11,690.89 rows=7,069 width=77) (actual time=0.048..189.589 rows=6,458 loops=303)

81. 5,863.959 5,863.959 ↓ 1.0 13,349 303

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis mrd_diagnosis_1 (cost=0.29..1,639.04 rows=13,264 width=77) (actual time=0.009..19.353 rows=13,349 loops=303)

  • Filter: (diag_type = 'S'::bpchar)
  • Rows Removed by Filter: 15267