explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6QIO

Settings
# exclusive inclusive rows x rows loops node
1. 2.922 31,532.021 ↑ 170.0 1 1

Nested Loop Left Join (cost=1,274,102.22..1,370,925.07 rows=170 width=1,004) (actual time=31,532.013..31,532.021 rows=1 loops=1)

2. 42.397 31,527.311 ↑ 170.0 1 1

Merge Left Join (cost=1,274,101.93..1,370,857.54 rows=170 width=1,056) (actual time=31,527.304..31,527.311 rows=1 loops=1)

  • Merge Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
3. 0.000 2,916.218 ↑ 170.0 1 1

Sort (cost=100,031.97..100,032.39 rows=170 width=1,028) (actual time=2,916.218..2,916.218 rows=1 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
4. 530.175 2,918.438 ↑ 170.0 1 1

Gather (cost=97,787.34..100,025.67 rows=170 width=1,028) (actual time=2,916.200..2,918.438 rows=1 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.660 2,388.263 ↓ 0.0 0 3 / 3

Merge Left Join (cost=96,787.34..99,008.67 rows=71 width=1,028) (actual time=2,388.262..2,388.263 rows=0 loops=3)

  • Merge Cond: ((icl.claim_id)::text = (insurance_claim_resubmission.claim_id)::text)
6. 1.031 2,193.898 ↓ 0.0 0 3 / 3

Sort (cost=79,705.23..79,705.41 rows=71 width=1,010) (actual time=2,193.898..2,193.898 rows=0 loops=3)

  • Sort Key: icl.claim_id
  • Sort Method: quicksort Memory: 25kB
7. 0.007 2,192.867 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,733.18..79,703.05 rows=71 width=1,010) (actual time=2,192.858..2,192.867 rows=0 loops=3)

8. 0.006 2,192.846 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,732.90..79,680.87 rows=71 width=1,011) (actual time=2,192.837..2,192.846 rows=0 loops=3)

9. 0.010 2,192.586 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,732.47..79,643.43 rows=71 width=671) (actual time=2,192.577..2,192.586 rows=0 loops=3)

10. 0.008 2,192.390 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,732.05..79,605.00 rows=71 width=662) (actual time=2,192.382..2,192.390 rows=0 loops=3)

11. 0.024 2,192.144 ↓ 0.0 0 3 / 3

Hash Left Join (cost=77,731.62..79,151.08 rows=71 width=648) (actual time=2,192.138..2,192.144 rows=0 loops=3)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
12. 0.004 2,191.974 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,730.22..79,149.44 rows=71 width=539) (actual time=2,191.968..2,191.974 rows=0 loops=3)

13. 0.004 2,191.958 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,729.79..79,116.42 rows=71 width=530) (actual time=2,191.953..2,191.958 rows=0 loops=3)

14. 0.011 2,191.949 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,729.65..79,104.71 rows=71 width=508) (actual time=2,191.944..2,191.949 rows=0 loops=3)

15. 0.011 2,191.918 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,729.50..79,092.99 rows=71 width=496) (actual time=2,191.914..2,191.918 rows=0 loops=3)

16. 0.062 2,191.372 ↓ 0.0 0 3 / 3

Hash Left Join (cost=77,729.22..79,072.22 rows=71 width=482) (actual time=2,191.367..2,191.372 rows=0 loops=3)

  • Hash Cond: (pr.visit_type = vn.visit_type)
17. 0.004 2,191.023 ↓ 0.0 0 3 / 3

Nested Loop Left Join (cost=77,728.13..79,070.74 rows=71 width=452) (actual time=2,191.019..2,191.023 rows=0 loops=3)

18. 0.008 2,189.277 ↓ 0.0 0 3 / 3

Nested Loop (cost=77,727.14..78,983.73 rows=71 width=464) (actual time=2,189.275..2,189.277 rows=0 loops=3)

19. 0.016 2,188.721 ↑ 1,845.0 1 3 / 3

Nested Loop Left Join (cost=77,727.00..78,692.40 rows=1,845 width=250) (actual time=2,188.313..2,188.721 rows=1 loops=3)

20. 0.007 2,187.605 ↑ 1,845.0 1 3 / 3

Merge Left Join (cost=77,726.57..77,737.21 rows=1,845 width=218) (actual time=2,187.600..2,187.605 rows=1 loops=3)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
21. 0.031 2,187.569 ↑ 1,845.0 1 3 / 3

Sort (cost=77,702.39..77,707.01 rows=1,845 width=170) (actual time=2,187.567..2,187.569 rows=1 loops=3)

  • Sort Key: b.visit_id
  • Sort Method: quicksort Memory: 25kB
22. 0.163 2,187.538 ↑ 1,845.0 1 3 / 3

Hash Left Join (cost=725.04..77,602.31 rows=1,845 width=170) (actual time=2,186.992..2,187.538 rows=1 loops=3)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
23. 1.272 2,176.941 ↑ 1,845.0 1 3 / 3

Nested Loop (cost=0.42..76,872.85 rows=1,845 width=161) (actual time=2,176.398..2,176.941 rows=1 loops=3)

  • -> Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..6.80 rows=1 width=69) (actual time= (...)
24. 2,175.669 2,175.669 ↑ 1,845.0 1 3 / 3

Parallel Seq Scan on bill b (cost=0.00..64,325.75 rows=1,845 width=92) (actual time=2,175.661..2,175.669 rows=1 loops=3)

  • Filter: (is_tpa AND (status <> 'X'::bpchar) AND (date(open_date) >= '2020-10-01'::date) AND (date(open_date) <= '202 (...)
  • Rows Removed by Filter: 375,738
  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
25. 10.434 10.434 ↑ 1.0 17,094 1 / 3

Hash (cost=510.94..510.94 rows=17,094 width=9) (actual time=31.303..31.303 rows=17,094 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 941kB
  • -> Seq Scan on incoming_sample_registration isr (cost=0.00..510.94 rows=17,094 width=9) (actual time=0.021..17.605 rows=1 (...)
26. 0.026 0.029 ↓ 0.0 0 1 / 3

Sort (cost=24.18..24.88 rows=280 width=48) (actual time=0.087..0.087 rows=0 loops=1)

  • Sort Key: prc.customer_id
  • Sort Method: quicksort Memory: 25kB
27. 0.003 0.003 ↓ 0.0 0 1 / 3

Seq Scan on store_retail_customers prc (cost=0.00..12.80 rows=280 width=48) (actual time=0.010..0.010 rows=0 loops=1)

28. 1.100 1.100 ↑ 1.0 1 3 / 3

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..0.52 rows=1 width=47) (actual time=1.100..1.100 rows=1 loops=3)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
29. 0.548 0.548 ↓ 0.0 0 3 / 3

Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..0.16 rows=1 width=222) (actual time=0.548..0.548 rows=0 loops=3)

  • Index Cond: (center_id = pr.center_id)
  • Filter: ((center_name)::text = 'NMC AlNahda'::text)
  • Rows Removed by Filter: 1
30. 0.011 1.742 ↑ 1.0 1 1 / 3

Nested Loop Left Join (cost=1.00..1.22 rows=1 width=20) (actual time=5.221..5.226 rows=1 loops=1)

31. 0.915 1.699 ↑ 1.0 1 1 / 3

Nested Loop (cost=0.57..0.68 rows=1 width=20) (actual time=5.092..5.096 rows=1 loops=1)

  • -> Index Scan using policy_patient_idx on patient_insurance_plans pri_plan (cost=0.42..0.52 rows=1 width=42) (actual time=2.691..2.694 rows=1 lo (...)
  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
32. 0.783 0.783 ↑ 1.0 1 1 / 3

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.15..0.17 rows=1 width=10) (actual time=2.350..2.350 rows=1 loops=1)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 1
33. 0.032 0.032 ↓ 0.0 0 1 / 3

Index Scan using policy_patient_idx on patient_insurance_plans sec_plan (cost=0.42..0.52 rows=1 width=38) (actual time=0.096..0.096 rows=0 loops=1)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
34. 0.011 0.287 ↑ 1.0 4 1 / 3

Hash (cost=1.04..1.04 rows=4 width=40) (actual time=0.862..0.862 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.276 0.276 ↑ 1.0 4 1 / 3

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=40) (actual time=0.823..0.828 rows=4 loops=1)

36. 0.535 0.535 ↑ 1.0 1 1 / 3

Index Scan using organization_details_pkey on organization_details od (cost=0.28..0.29 rows=1 width=30) (actual time=1.606..1.606 rows=1 loops=1)

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
37. 0.020 0.020 ↑ 1.0 1 1 / 3

Index Scan using tpa_master_pkey on tpa_master ptpa (cost=0.15..0.17 rows=1 width=32) (actual time=0.060..0.060 rows=1 loops=1)

  • Index Cond: ((tpa_id)::text = (pr.primary_sponsor_id)::text)
38. 0.005 0.005 ↓ 0.0 0 1 / 3

Index Scan using tpa_master_pkey on tpa_master stpa (cost=0.15..0.17 rows=1 width=32) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((tpa_id)::text = (pr.secondary_sponsor_id)::text)
39. 0.012 0.012 ↑ 1.0 1 1 / 3

Index Scan using patient_policy_details_patient_policy_id_key on patient_policy_details pppd (cost=0.42..0.47 rows=1 width=17) (actual time=0.036..0.036 rows=1 loops=1)

  • Index Cond: (patient_policy_id = pri_plan.patient_policy_id)
40. 0.012 0.146 ↑ 1.0 18 1 / 3

Hash (cost=1.18..1.18 rows=18 width=156) (actual time=0.439..0.439 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.134 0.134 ↑ 1.0 18 1 / 3

Seq Scan on salutation_master sm (cost=0.00..1.18 rows=18 width=156) (actual time=0.391..0.402 rows=18 loops=1)

42. 0.238 0.238 ↑ 1.0 1 1 / 3

Index Scan using bill_claim_bill_no_idx on bill_claim bclm (cost=0.42..6.38 rows=1 width=29) (actual time=0.709..0.713 rows=1 loops=1)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
43. 0.186 0.186 ↑ 1.0 1 1 / 3

Index Scan using insurance_claim_id_index on insurance_claim icl (cost=0.42..0.54 rows=1 width=23) (actual time=0.559..0.559 rows=1 loops=1)

  • Index Cond: ((claim_id)::text = (bclm.claim_id)::text)
44. 0.254 0.254 ↑ 1.0 1 1 / 3

Index Scan using claim_reconciliation_pkey on claim_reconciliation rv (cost=0.42..0.53 rows=1 width=354) (actual time=0.762..0.762 rows=1 loops=1)

  • Index Cond: ((claim_id)::text = (icl.claim_id)::text)
45. 0.014 0.014 ↑ 1.0 1 1 / 3

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch firstsub (cost=0.29..0.31 rows=1 width=17) (actual time=0.042..0.042 rows=1 loops=1)

  • Index Cond: ((submission_batch_id)::text = (icl.submission_batch_id)::text)
  • Filter: ((is_resubmission = 'N'::bpchar) AND (is_reconciliation = 'N'::bpchar))
46. 2.426 193.705 ↑ 3.4 17,904 1 / 3

GroupAggregate (cost=17,082.11..18,536.44 rows=61,328 width=46) (actual time=569.149..581.114 rows=17,904 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
47. 171.132 191.279 ↑ 4.2 26,864 1 / 3

Sort (cost=17,082.11..17,362.46 rows=112,140 width=23) (actual time=568.553..573.836 rows=26,864 loops=1)

  • Sort Key: insurance_claim_resubmission.claim_id
  • Sort Method: external merge Disk: 3,712kB
48. 20.147 20.147 ↓ 1.0 112,159 1 / 3

Seq Scan on insurance_claim_resubmission (cost=0.00..5,373.40 rows=112,140 width=23) (actual time=0.350..60.440 rows=112,159 loops=1)

49. 74.664 28,568.696 ↑ 4.2 223,872 1

Materialize (cost=1,174,069.96..1,268,463.46 rows=943,935 width=43) (actual time=28,279.831..28,568.696 rows=223,872 loops=1)

50. 176.478 28,494.032 ↑ 4.2 223,872 1

Group (cost=1,174,069.96..1,256,664.28 rows=943,935 width=927) (actual time=28,279.823..28,494.032 rows=223,872 loops=1)

  • Group Key: bcl.bill_no, bcl_1.claim_id, sic.submission_batch_id, sic.main_visit_id, sic.status, sic.resubmission_count, sic.closure_type, sic.action_remarks, sic.attachment, sic.attachment_content_type, sic.resubmission_type, sic.commen (...)
51. 7,625.707 28,317.554 ↑ 4.1 230,502 1

Sort (cost=1,174,069.96..1,176,429.80 rows=943,935 width=927) (actual time=28,279.322..28,317.554 rows=230,502 loops=1)

  • Sort Key: bcl.bill_no, bcl_1.claim_id, sic.submission_batch_id, sic.main_visit_id, sic.status, sic.resubmission_count, sic.closure_type, sic.action_remarks, sic.attachment, sic.attachment_content_type, sic.resubmission_type, sic.c (...)
  • Sort Method: external sort Disk: 146,784kB
52. 1,132.329 20,691.847 ↑ 1.0 943,797 1

Merge Left Join (cost=254,313.82..312,520.31 rows=943,935 width=927) (actual time=15,966.712..20,691.847 rows=943,797 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_1.bill_no)::text)
53. 455.700 5,338.157 ↑ 1.0 943,773 1

Merge Left Join (cost=59,427.55..115,014.91 rows=943,935 width=463) (actual time=3,551.421..5,338.157 rows=943,773 loops=1)

  • Merge Cond: ((bcl.bill_no)::text = (bcl_2.bill_no)::text)
54. 1,312.502 1,312.502 ↑ 1.0 943,773 1

Index Only Scan using bill_claim_bill_no_idx on bill_claim bcl (cost=0.42..53,220.68 rows=943,935 width=15) (actual time=0.015..1,312.502 rows=943,773 loops=1)

  • Heap Fetches: 943,773
55. 351.832 3,569.955 ↓ 138.4 59,083 1

Sort (cost=59,427.12..59,428.19 rows=427 width=463) (actual time=3,551.384..3,569.955 rows=59,083 loops=1)

  • Sort Key: bcl_2.bill_no
  • Sort Method: external sort Disk: 3,120kB
56. 0.000 3,218.123 ↓ 69.2 29,543 1

Gather (cost=1,000.85..59,408.47 rows=427 width=463) (actual time=7.249..3,218.123 rows=29,543 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
57. 21.308 3,358.769 ↓ 55.3 9,848 3 / 3

Nested Loop (cost=0.85..58,365.77 rows=178 width=463) (actual time=4.159..3,358.769 rows=9,848 loops=3)

58. 18.987 2,632.100 ↓ 54.3 9,935 3 / 3

Nested Loop (cost=0.42..58,269.65 rows=183 width=448) (actual time=3.220..2,632.100 rows=9,935 loops=3)

59. 297.522 297.522 ↑ 1.2 9,689 3 / 3

Parallel Seq Scan on patient_insurance_plans sip_1 (cost=0.00..18,983.80 rows=11,972 width=20) (actual time=0.607..297.522 rows=9,689 loops=3)

  • Filter: (priority = 2)
  • Rows Removed by Filter: 297,627
60. 2,315.591 2,315.591 ↑ 1.0 1 29,066 / 3

Index Scan using insurance_claim_patient_id_idx on insurance_claim sic_1 (cost=0.42..3.27 rows=1 width=468) (actual time=0.174..0.239 rows=1 loops=29,066)

  • Index Cond: ((patient_id)::text = (sip_1.patient_id)::text)
  • Filter: (sip_1.plan_id = plan_id)
  • Rows Removed by Filter: 1
61. 705.361 705.361 ↑ 1.0 1 29,804 / 3

Index Scan using bill_claim_claim_idx on bill_claim bcl_2 (cost=0.42..0.52 rows=1 width=29) (actual time=0.070..0.071 rows=1 loops=29,804)

  • Index Cond: ((claim_id)::text = (sic_1.claim_id)::text)
62. 119.165 14,221.361 ↓ 71.0 943,520 1

Materialize (cost=194,886.27..194,952.68 rows=13,283 width=479) (actual time=12,415.274..14,221.361 rows=943,520 loops=1)

63. 6,438.906 14,102.196 ↓ 68.8 913,933 1

Sort (cost=194,886.27..194,919.48 rows=13,283 width=479) (actual time=12,414.472..14,102.196 rows=913,933 loops=1)

  • Sort Key: bcl_1.bill_no
  • Sort Method: external merge Disk: 136,856kB
64. 0.000 7,663.290 ↓ 68.8 913,933 1

Gather (cost=45,338.93..191,113.56 rows=13,283 width=479) (actual time=705.869..7,663.290 rows=913,933 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
65. 157.983 8,002.470 ↓ 55.0 304,644 3 / 3

Nested Loop (cost=44,338.93..188,785.26 rows=5,535 width=479) (actual time=699.577..8,002.470 rows=304,644 loops=3)

66. 891.855 2,117.741 ↓ 53.0 301,408 3 / 3

Hash Join (cost=44,338.51..185,798.78 rows=5,686 width=464) (actual time=697.911..2,117.741 rows=301,408 loops=3)

  • Hash Cond: (((sic.patient_id)::text = (sip.patient_id)::text) AND (sic.plan_id = sip.plan_id))
67. 538.323 538.323 ↑ 1.2 323,284 3 / 3

Parallel Seq Scan on insurance_claim sic (cost=0.00..23,590.92 rows=404,092 width=468) (actual time=0.359..538.323 rows=323,284 loops=3)

68. 259.313 687.563 ↑ 1.0 892,708 3 / 3

Hash (cost=25,706.33..25,706.33 rows=893,212 width=20) (actual time=687.563..687.563 rows=892,708 loops=3)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,332kB
69. 428.250 428.250 ↑ 1.0 892,881 3 / 3

Seq Scan on patient_insurance_plans sip (cost=0.00..25,706.33 rows=893,212 width=20) (actual time=0.066..428.250 rows=892,881 loops=3)

  • Filter: (priority = 1)
  • Rows Removed by Filter: 29,066
70. 5,726.746 5,726.746 ↑ 1.0 1 904,223 / 3

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.42..0.52 rows=1 width=29) (actual time=0.019..0.019 rows=1 loops=904,223)

  • Index Cond: ((claim_id)::text = (sic.claim_id)::text)
71. 1.788 1.788 ↑ 1.0 1 1

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.29..0.34 rows=1 width=17) (actual time=1.788..1.788 rows=1 loops=1)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))
Planning time : 48.278 ms
Execution time : 31,560.160 ms