explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NlPO

Settings
# exclusive inclusive rows x rows loops node
1. 1.899 35,688.784 ↓ 195.0 195 1

Hash Join (cost=1,357,216.73..1,531,750.99 rows=1 width=1,061) (actual time=35,680.313..35,688.784 rows=195 loops=1)

  • Hash Cond: (cr.latest_remittance_id = ir.remittance_id)
2. 901.457 35,606.169 ↓ 434.0 434 1

Hash Right Join (cost=1,344,152.68..1,518,686.38 rows=1 width=812) (actual time=35,586.186..35,606.169 rows=434 loops=1)

  • Hash Cond: ((icr.remittance_id = cr.latest_remittance_id) AND ((icr.claim_id)::text = (cr.claim_id)::text))
3. 1,429.125 1,429.125 ↑ 1.0 6,778,286 1

Seq Scan on insurance_claim_remittance icr (cost=0.00..123,687.68 rows=6,779,468 width=28) (actual time=0.020..1,429.125 rows=6,778,286 loops=1)

4. 0.606 33,275.587 ↓ 434.0 434 1

Hash (cost=1,344,152.67..1,344,152.67 rows=1 width=812) (actual time=33,275.586..33,275.587 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 272kB
5. 0.295 33,274.981 ↓ 434.0 434 1

Hash Left Join (cost=1,227,980.11..1,344,152.67 rows=1 width=812) (actual time=31,841.133..33,274.981 rows=434 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
6. 0.395 33,274.676 ↓ 434.0 434 1

Hash Left Join (cost=1,227,978.97..1,344,151.53 rows=1 width=696) (actual time=31,841.094..33,274.676 rows=434 loops=1)

  • Hash Cond: (pip.plan_id = ipm.plan_id)
7. 0.225 33,250.869 ↓ 434.0 434 1

Hash Left Join (cost=1,225,646.47..1,341,819.03 rows=1 width=653) (actual time=31,817.545..33,250.869 rows=434 loops=1)

  • Hash Cond: ((pip.sponsor_id)::text = (tpa.tpa_id)::text)
8. 0.261 33,250.331 ↓ 434.0 434 1

Hash Left Join (cost=1,225,638.17..1,341,810.71 rows=1 width=629) (actual time=31,817.224..33,250.331 rows=434 loops=1)

  • Hash Cond: ((pip.insurance_co)::text = (icm.insurance_co_id)::text)
9. 329.711 33,249.759 ↓ 434.0 434 1

Hash Right Join (cost=1,225,609.92..1,341,782.46 rows=1 width=605) (actual time=31,816.893..33,249.759 rows=434 loops=1)

  • Hash Cond: (((icrsub.claim_id)::text = (cr.claim_id)::text) AND ((icrsub.resubmission_batch_id)::text = (isb.submission_batch_id)::text))
10. 1,113.022 1,113.022 ↑ 1.0 1,917,808 1

Seq Scan on insurance_claim_resubmission icrsub (cost=0.00..101,788.16 rows=1,917,916 width=29) (actual time=0.302..1,113.022 rows=1,917,808 loops=1)

11. 1.229 31,807.026 ↓ 434.0 434 1

Hash (cost=1,225,609.91..1,225,609.91 rows=1 width=599) (actual time=31,807.026..31,807.026 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 206kB
12. 1.189 31,805.797 ↓ 434.0 434 1

Hash Left Join (cost=1,037,198.00..1,225,609.91 rows=1 width=599) (actual time=30,133.035..31,805.797 rows=434 loops=1)

  • Hash Cond: (COALESCE((max((isb_1.submission_batch_id)::text)), (cr.latest_resubmission_id)::text) = (isb.submission_batch_id)::text)
13. 0.476 31,684.718 ↓ 434.0 434 1

Hash Left Join (cost=1,027,801.66..1,216,213.57 rows=1 width=480) (actual time=30,012.841..31,684.718 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (resub.claim_id)::text)
14. 0.693 31,623.900 ↓ 434.0 434 1

Hash Left Join (cost=1,019,712.58..1,208,124.48 rows=1 width=448) (actual time=29,952.490..31,623.900 rows=434 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
15. 0.768 31,622.471 ↓ 434.0 434 1

Hash Left Join (cost=1,019,619.38..1,208,031.28 rows=1 width=434) (actual time=29,951.736..31,622.471 rows=434 loops=1)

  • Hash Cond: (pip.plan_type_id = cat.category_id)
16. 0.626 31,615.080 ↓ 434.0 434 1

Hash Left Join (cost=1,019,192.81..1,207,604.71 rows=1 width=408) (actual time=29,945.084..31,615.080 rows=434 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sal.salutation_id)::text)
17. 331.619 31,614.430 ↓ 434.0 434 1

Hash Join (cost=1,019,191.34..1,207,603.23 rows=1 width=299) (actual time=29,945.038..31,614.430 rows=434 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
18. 1,348.370 1,348.370 ↑ 1.0 2,769,745 1

Seq Scan on patient_details pd (cost=0.00..177,945.46 rows=2,791,046 width=68) (actual time=0.457..1,348.370 rows=2,769,745 loops=1)

19. 1.046 29,934.441 ↓ 434.0 434 1

Hash (cost=1,019,191.33..1,019,191.33 rows=1 width=246) (actual time=29,934.441..29,934.441 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 128kB
20. 446.976 29,933.395 ↓ 434.0 434 1

Hash Join (cost=653,669.61..1,019,191.33 rows=1 width=246) (actual time=9,404.877..29,933.395 rows=434 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (ic.patient_id)::text)
21. 21,843.052 21,996.626 ↓ 1.0 1,771,789 1

Bitmap Heap Scan on patient_registration pr (cost=32,915.79..391,847.49 rows=1,757,336 width=47) (actual time=239.220..21,996.626 rows=1,771,789 loops=1)

  • Recheck Cond: (center_id = 9)
  • Heap Blocks: exact=328,002
22. 153.574 153.574 ↓ 1.0 1,773,698 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..32,476.45 rows=1,757,336 width=0) (actual time=153.574..153.574 rows=1,773,698 loops=1)

  • Index Cond: (center_id = 9)
23. 0.518 7,489.793 ↓ 434.0 434 1

Hash (cost=620,753.81..620,753.81 rows=1 width=215) (actual time=7,489.793..7,489.793 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 115kB
24. 353.631 7,489.275 ↓ 434.0 434 1

Hash Join (cost=471,092.86..620,753.81 rows=1 width=215) (actual time=6,744.516..7,489.275 rows=434 loops=1)

  • Hash Cond: (((pip.patient_id)::text = (ic.patient_id)::text) AND (pip.plan_id = ic.plan_id))
25. 1,136.254 1,323.676 ↓ 1.0 2,186,594 1

Bitmap Heap Scan on patient_insurance_plans pip (cost=50,411.65..183,741.73 rows=2,177,447 width=42) (actual time=212.504..1,323.676 rows=2,186,594 loops=1)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0048'::text)
  • Heap Blocks: exact=106,110
26. 187.422 187.422 ↓ 1.0 2,186,875 1

Bitmap Index Scan on policy_sponsor_idx (cost=0.00..49,867.29 rows=2,177,447 width=0) (actual time=187.422..187.422 rows=2,186,875 loops=1)

  • Index Cond: ((sponsor_id)::text = 'TPAID0048'::text)
27. 0.539 5,811.968 ↓ 434.0 434 1

Hash (cost=420,681.20..420,681.20 rows=1 width=177) (actual time=5,811.968..5,811.968 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 98kB
28. 82.560 5,811.429 ↓ 434.0 434 1

Hash Join (cost=248,746.18..420,681.20 rows=1 width=177) (actual time=5,085.673..5,811.429 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (cr.claim_id)::text)
29. 810.569 866.600 ↓ 1.0 491,833 1

Bitmap Heap Scan on insurance_claim ic (cost=8,932.02..179,591.07 rows=486,084 width=104) (actual time=83.210..866.600 rows=491,833 loops=1)

  • Recheck Cond: (status = ANY ('{D,R}'::bpchar[]))
  • Heap Blocks: exact=116,580
30. 56.031 56.031 ↓ 1.0 491,907 1

Bitmap Index Scan on insurance_claim_status_idx (cost=0.00..8,810.49 rows=486,084 width=0) (actual time=56.031..56.031 rows=491,907 loops=1)

  • Index Cond: (status = ANY ('{D,R}'::bpchar[]))
31. 0.968 4,862.269 ↓ 934.0 1,868 1

Hash (cost=239,814.14..239,814.14 rows=2 width=73) (actual time=4,862.269..4,862.269 rows=1,868 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 222kB
32. 4,861.301 4,861.301 ↓ 934.0 1,868 1

Seq Scan on claim_reconciliation cr (cost=0.00..239,814.14 rows=2 width=73) (actual time=1,655.029..4,861.301 rows=1,868 loops=1)

  • Filter: ((payment_ref_all ~~* '%50491720%'::text) AND ((last_bill_open_date)::date >= '2016-01-01'::date) AND ((last_bill_open_date)::date <= '2020-08-27'::date))
  • Rows Removed by Filter: 6,263,738
33. 0.013 0.024 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=156) (actual time=0.024..0.024 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 0.011 0.011 ↑ 1.0 21 1

Seq Scan on salutation_master sal (cost=0.00..1.21 rows=21 width=156) (actual time=0.008..0.011 rows=21 loops=1)

35. 1.850 6.623 ↑ 1.0 10,603 1

Hash (cost=294.03..294.03 rows=10,603 width=26) (actual time=6.623..6.623 rows=10,603 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 731kB
36. 4.773 4.773 ↑ 1.0 10,603 1

Seq Scan on insurance_category_master cat (cost=0.00..294.03 rows=10,603 width=26) (actual time=0.317..4.773 rows=10,603 loops=1)

37. 0.368 0.736 ↓ 1.0 1,921 1

Hash (cost=69.20..69.20 rows=1,920 width=30) (actual time=0.736..0.736 rows=1,921 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 134kB
38. 0.368 0.368 ↓ 1.0 1,921 1

Seq Scan on doctors doc (cost=0.00..69.20 rows=1,920 width=30) (actual time=0.006..0.368 rows=1,921 loops=1)

39. 0.002 60.342 ↑ 1.0 1 1

Hash (cost=8,089.07..8,089.07 rows=1 width=46) (actual time=60.342..60.342 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.006 60.340 ↑ 1.0 1 1

GroupAggregate (cost=8,089.04..8,089.06 rows=1 width=46) (actual time=60.340..60.340 rows=1 loops=1)

  • Group Key: resub.claim_id
41. 0.013 60.334 ↑ 1.0 1 1

Sort (cost=8,089.04..8,089.05 rows=1 width=23) (actual time=60.333..60.334 rows=1 loops=1)

  • Sort Key: resub.claim_id
  • Sort Method: quicksort Memory: 25kB
42. 0.022 60.321 ↑ 1.0 1 1

Hash Join (cost=4.46..8,089.03 rows=1 width=23) (actual time=60.291..60.321 rows=1 loops=1)

  • Hash Cond: ((isb_1.submission_batch_id)::text = (resub.resubmission_batch_id)::text)
43. 59.179 59.179 ↑ 1.0 1 1

Seq Scan on insurance_submission_batch isb_1 (cost=0.00..8,084.56 rows=1 width=9) (actual time=59.150..59.179 rows=1 loops=1)

  • Filter: ((is_reconciliation = 'Y'::bpchar) AND (status = 'O'::bpchar))
  • Rows Removed by Filter: 174,814
44. 0.008 1.120 ↓ 6.0 6 1

Hash (cost=4.45..4.45 rows=1 width=23) (actual time=1.120..1.120 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
45. 1.112 1.112 ↓ 6.0 6 1

Index Scan using idx_insurance_claim_resubmission_category_type on insurance_claim_resubmission resub (cost=0.43..4.45 rows=1 width=23) (actual time=1.109..1.112 rows=6 loops=1)

  • Index Cond: ((category_type)::text = 'RECON'::text)
46. 63.353 119.890 ↑ 1.0 174,815 1

Hash (cost=7,210.04..7,210.04 rows=174,904 width=128) (actual time=119.890..119.890 rows=174,815 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 30,776kB
47. 56.537 56.537 ↑ 1.0 174,815 1

Seq Scan on insurance_submission_batch isb (cost=0.00..7,210.04 rows=174,904 width=128) (actual time=0.008..56.537 rows=174,815 loops=1)

48. 0.143 0.311 ↑ 1.0 722 1

Hash (cost=19.22..19.22 rows=722 width=32) (actual time=0.311..0.311 rows=722 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 55kB
49. 0.168 0.168 ↑ 1.0 722 1

Seq Scan on insurance_company_master icm (cost=0.00..19.22 rows=722 width=32) (actual time=0.012..0.168 rows=722 loops=1)

50. 0.002 0.313 ↑ 1.0 1 1

Hash (cost=8.29..8.29 rows=1 width=34) (actual time=0.313..0.313 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.311 0.311 ↑ 1.0 1 1

Index Scan using tpa_master_pkey on tpa_master tpa (cost=0.28..8.29 rows=1 width=34) (actual time=0.304..0.311 rows=1 loops=1)

  • Index Cond: ((tpa_id)::text = 'TPAID0048'::text)
52. 9.966 23.412 ↑ 1.0 46,076 1

Hash (cost=1,756.11..1,756.11 rows=46,111 width=47) (actual time=23.411..23.412 rows=46,076 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,165kB
53. 13.446 13.446 ↑ 1.0 46,076 1

Seq Scan on insurance_plan_main ipm (cost=0.00..1,756.11 rows=46,111 width=47) (actual time=0.009..13.446 rows=46,076 loops=1)

54. 0.003 0.010 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=126) (actual time=0.010..0.010 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
55. 0.007 0.007 ↑ 1.0 6 1

Seq Scan on op_type_names otn (cost=0.00..1.06 rows=6 width=126) (actual time=0.006..0.007 rows=6 loops=1)

56. 0.269 80.716 ↑ 1.3 1,232 1

Hash (cost=13,043.88..13,043.88 rows=1,613 width=12) (actual time=80.716..80.716 rows=1,232 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 69kB
57. 80.447 80.447 ↑ 1.3 1,232 1

Seq Scan on insurance_remittance ir (cost=0.00..13,043.88 rows=1,613 width=12) (actual time=5.265..80.447 rows=1,232 loops=1)

  • Filter: (((transaction_date)::date >= '2019-09-30'::date) AND ((transaction_date)::date <= '2020-07-23'::date))
  • Rows Removed by Filter: 319,187
58.          

SubPlan (for Hash Join)

59. 0.000 0.000 ↑ 1.0 1 195

Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=195)

Planning time : 21.883 ms
Execution time : 35,689.535 ms