explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lp5B : Claim Recon Nested loop off

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 16,365.131 ↓ 18.0 18 1

Limit (cost=2,180,004.98..2,665,304.48 rows=1 width=844) (actual time=16,297.300..16,365.131 rows=18 loops=1)

2. 0.327 16,365.127 ↓ 18.0 18 1

Hash Join (cost=2,180,004.98..2,665,304.45 rows=1 width=844) (actual time=16,297.299..16,365.127 rows=18 loops=1)

  • Hash Cond: (cr.latest_remittance_id = ir.remittance_id)
3. 896.369 16,230.473 ↓ 18.0 18 1

Hash Right Join (cost=2,139,229.34..2,624,528.22 rows=1 width=595) (actual time=16,162.779..16,230.473 rows=18 loops=1)

  • Hash Cond: ((icr.remittance_id = cr.latest_remittance_id) AND ((icr.claim_id)::text = (cr.claim_id)::text))
4. 678.694 678.694 ↓ 1.0 10,562,540 1

Seq Scan on insurance_claim_remittance icr (cost=0.00..406,115.68 rows=10,557,756 width=28) (actual time=0.011..678.694 rows=10,562,540 loops=1)

5. 0.049 14,655.410 ↓ 18.0 18 1

Hash (cost=2,139,229.31..2,139,229.31 rows=1 width=595) (actual time=14,655.410..14,655.410 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
6. 0.050 14,655.361 ↓ 18.0 18 1

Hash Left Join (cost=1,768,294.61..2,139,229.31 rows=1 width=595) (actual time=14,387.714..14,655.361 rows=18 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
7. 0.354 14,655.301 ↓ 18.0 18 1

Hash Left Join (cost=1,768,293.30..2,139,227.99 rows=1 width=579) (actual time=14,387.676..14,655.301 rows=18 loops=1)

  • Hash Cond: (pip.plan_id = ipm.plan_id)
8. 0.044 14,632.924 ↓ 18.0 18 1

Hash Left Join (cost=1,762,823.80..2,133,758.49 rows=1 width=535) (actual time=14,365.318..14,632.924 rows=18 loops=1)

  • Hash Cond: ((pip.sponsor_id)::text = (tpa.tpa_id)::text)
9. 0.058 14,632.848 ↓ 18.0 18 1

Hash Left Join (cost=1,762,819.45..2,133,754.11 rows=1 width=510) (actual time=14,365.266..14,632.848 rows=18 loops=1)

  • Hash Cond: ((pip.insurance_co)::text = (icm.insurance_co_id)::text)
10. 407.988 14,632.490 ↓ 18.0 18 1

Hash Right Join (cost=1,762,752.70..2,133,687.36 rows=1 width=485) (actual time=14,364.928..14,632.490 rows=18 loops=1)

  • Hash Cond: (((icrsub.claim_id)::text = (cr.claim_id)::text) AND ((icrsub.resubmission_batch_id)::text = (isb.submission_batch_id)::text))
11. 583.122 583.122 ↓ 1.0 3,134,575 1

Seq Scan on insurance_claim_resubmission icrsub (cost=0.00..347,449.50 rows=3,131,350 width=29) (actual time=0.012..583.122 rows=3,134,575 loops=1)

12. 0.087 13,641.380 ↓ 18.0 18 1

Hash (cost=1,762,752.67..1,762,752.67 rows=1 width=479) (actual time=13,641.380..13,641.380 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
13. 2.051 13,641.293 ↓ 18.0 18 1

Hash Left Join (cost=1,240,284.09..1,762,752.67 rows=1 width=479) (actual time=10,340.120..13,641.293 rows=18 loops=1)

  • Hash Cond: (COALESCE((max((isb_1.submission_batch_id)::text)), (cr.latest_resubmission_id)::text) = (isb.submission_batch_id)::text)
14. 0.060 13,496.572 ↓ 18.0 18 1

Hash Left Join (cost=1,213,374.09..1,735,842.66 rows=1 width=367) (actual time=10,195.471..13,496.572 rows=18 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (resub.claim_id)::text)
15. 0.102 13,435.110 ↓ 18.0 18 1

Hash Left Join (cost=1,194,070.53..1,716,539.10 rows=1 width=335) (actual time=10,134.051..13,435.110 rows=18 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
16. 0.106 13,434.113 ↓ 18.0 18 1

Hash Left Join (cost=1,193,846.47..1,716,315.04 rows=1 width=321) (actual time=10,133.127..13,434.113 rows=18 loops=1)

  • Hash Cond: (pip.plan_type_id = cat.category_id)
17. 0.093 13,427.206 ↓ 18.0 18 1

Hash Left Join (cost=1,192,804.28..1,715,272.85 rows=1 width=295) (actual time=10,126.267..13,427.206 rows=18 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sal.salutation_id)::text)
18. 2.763 13,427.091 ↓ 18.0 18 1

Hash Join (cost=1,192,801.97..1,715,270.53 rows=1 width=299) (actual time=10,126.206..13,427.091 rows=18 loops=1)

  • Hash Cond: ((cr.claim_id)::text = (ic.claim_id)::text)
19. 5,298.559 5,298.559 ↓ 429.6 14,177 1

Seq Scan on claim_reconciliation cr (cost=0.00..522,468.41 rows=33 width=73) (actual time=2,000.393..5,298.559 rows=14,177 loops=1)

  • Filter: ((payment_ref_all ~~* '%290440%'::text) AND ((last_bill_open_date)::date >= '2019-01-01'::date) AND ((last_bill_open_date)::date <= '2020-08-27'::date))
  • Rows Removed by Filter: 8,818,799
20. 1.120 8,125.769 ↓ 10.7 1,053 1

Hash (cost=1,192,798.78..1,192,798.78 rows=98 width=226) (actual time=8,125.769..8,125.769 rows=1,053 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 287kB
21. 240.441 8,124.649 ↓ 10.7 1,053 1

Hash Join (cost=984,721.22..1,192,798.78 rows=98 width=226) (actual time=6,846.491..8,124.649 rows=1,053 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
22. 1,040.125 1,040.125 ↓ 1.0 3,246,035 1

Seq Scan on patient_details pd (cost=0.00..195,910.33 rows=3,243,811 width=68) (actual time=0.016..1,040.125 rows=3,246,035 loops=1)

23. 0.838 6,844.083 ↓ 10.7 1,053 1

Hash (cost=984,718.04..984,718.04 rows=98 width=173) (actual time=6,844.083..6,844.083 rows=1,053 loops=1)

  • Buckets: 2,048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 238kB
24. 148.179 6,843.245 ↓ 10.7 1,053 1

Hash Join (cost=708,122.90..984,718.04 rows=98 width=173) (actual time=5,835.951..6,843.245 rows=1,053 loops=1)

  • Hash Cond: (((ic.patient_id)::text = (pr.patient_id)::text) AND (ic.plan_id = pip.plan_id))
25. 934.053 989.425 ↓ 1.0 589,543 1

Bitmap Heap Scan on insurance_claim ic (cost=10,491.38..282,687.60 rows=586,130 width=104) (actual time=78.252..989.425 rows=589,543 loops=1)

  • Recheck Cond: (status = ANY ('{D,R}'::bpchar[]))
  • Heap Blocks: exact=145,238
26. 55.372 55.372 ↓ 1.0 589,547 1

Bitmap Index Scan on insurance_claim_status_idx (cost=0.00..10,344.84 rows=586,130 width=0) (actual time=55.372..55.372 rows=589,547 loops=1)

  • Index Cond: (status = ANY ('{D,R}'::bpchar[]))
27. 103.497 5,705.641 ↓ 1.4 160,883 1

Hash (cost=693,735.56..693,735.56 rows=111,313 width=89) (actual time=5,705.641..5,705.641 rows=160,883 loops=1)

  • Buckets: 262,144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 21,530kB
28. 934.230 5,602.144 ↓ 1.4 160,883 1

Hash Join (cost=432,448.45..693,735.56 rows=111,313 width=89) (actual time=2,754.262..5,602.144 rows=160,883 loops=1)

  • Hash Cond: ((pip.patient_id)::text = (pr.patient_id)::text)
29. 1,938.152 2,126.619 ↑ 1.0 3,053,729 1

Bitmap Heap Scan on patient_insurance_plans pip (cost=55,162.85..308,345.90 rows=3,087,263 width=42) (actual time=212.618..2,126.619 rows=3,053,729 loops=1)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0048'::text)
  • Heap Blocks: exact=152,794
30. 188.467 188.467 ↑ 1.0 3,053,742 1

Bitmap Index Scan on policy_sponsor_idx (cost=0.00..54,391.03 rows=3,087,263 width=0) (actual time=188.466..188.467 rows=3,053,742 loops=1)

  • Index Cond: ((sponsor_id)::text = 'TPAID0048'::text)
31. 142.783 2,541.295 ↑ 1.0 366,340 1

Hash (cost=364,948.47..364,948.47 rows=379,604 width=47) (actual time=2,541.294..2,541.295 rows=366,340 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 32,712kB
32. 2,340.784 2,398.512 ↑ 1.0 366,340 1

Bitmap Heap Scan on patient_registration pr (cost=9,632.37..364,948.47 rows=379,604 width=47) (actual time=99.443..2,398.512 rows=366,340 loops=1)

  • Recheck Cond: (center_id = 2)
  • Heap Blocks: exact=238,538
33. 57.728 57.728 ↑ 1.0 366,625 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..9,537.46 rows=379,604 width=0) (actual time=57.728..57.728 rows=366,625 loops=1)

  • Index Cond: (center_id = 2)
34. 0.007 0.022 ↑ 1.0 21 1

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.022..0.022 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.015 0.015 ↑ 1.0 21 1

Seq Scan on salutation_master sal (cost=0.00..1.63 rows=21 width=14) (actual time=0.013..0.015 rows=21 loops=1)

36. 2.101 6.801 ↑ 1.0 13,203 1

Hash (cost=613.09..613.09 rows=13,203 width=26) (actual time=6.801..6.801 rows=13,203 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 884kB
37. 4.700 4.700 ↑ 1.0 13,203 1

Seq Scan on insurance_category_master cat (cost=0.00..613.09 rows=13,203 width=26) (actual time=0.010..4.700 rows=13,203 loops=1)

38. 0.393 0.895 ↑ 1.0 2,225 1

Hash (cost=151.75..151.75 rows=2,225 width=30) (actual time=0.894..0.895 rows=2,225 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 169kB
39. 0.502 0.502 ↑ 1.0 2,225 1

Seq Scan on doctors doc (cost=0.00..151.75 rows=2,225 width=30) (actual time=0.009..0.502 rows=2,225 loops=1)

40. 0.001 61.402 ↓ 0.0 0 1

Hash (cost=19,303.53..19,303.53 rows=1 width=46) (actual time=61.402..61.402 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
41. 0.003 61.401 ↓ 0.0 0 1

GroupAggregate (cost=19,303.46..19,303.50 rows=1 width=46) (actual time=61.401..61.401 rows=0 loops=1)

  • Group Key: resub.claim_id
42. 0.018 61.398 ↓ 0.0 0 1

Sort (cost=19,303.46..19,303.46 rows=1 width=23) (actual time=61.398..61.398 rows=0 loops=1)

  • Sort Key: resub.claim_id
  • Sort Method: quicksort Memory: 25kB
43. 0.002 61.380 ↓ 0.0 0 1

Hash Join (cost=212.78..19,303.45 rows=1 width=23) (actual time=61.380..61.380 rows=0 loops=1)

  • Hash Cond: ((isb_1.submission_batch_id)::text = (resub.resubmission_batch_id)::text)
44. 61.378 61.378 ↓ 0.0 0 1

Seq Scan on insurance_submission_batch isb_1 (cost=0.00..19,089.00 rows=1 width=9) (actual time=61.378..61.378 rows=0 loops=1)

  • Filter: ((is_reconciliation = 'Y'::bpchar) AND (status = 'O'::bpchar))
  • Rows Removed by Filter: 284,400
45. 0.000 0.000 ↓ 0.0 0

Hash (cost=170.37..170.37 rows=1,305 width=23) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_insurance_claim_resubmission_category_type on insurance_claim_resubmission resub (cost=0.43..170.37 rows=1,305 width=23) (never executed)

  • Index Cond: ((category_type)::text = 'RECON'::text)
47. 76.926 142.670 ↑ 1.0 284,400 1

Hash (cost=17,667.00..17,667.00 rows=284,400 width=121) (actual time=142.670..142.670 rows=284,400 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 48,719kB
48. 65.744 65.744 ↑ 1.0 284,400 1

Seq Scan on insurance_submission_batch isb (cost=0.00..17,667.00 rows=284,400 width=121) (actual time=0.007..65.744 rows=284,400 loops=1)

49. 0.157 0.300 ↑ 1.0 844 1

Hash (cost=39.32..39.32 rows=844 width=33) (actual time=0.300..0.300 rows=844 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
50. 0.143 0.143 ↑ 1.0 844 1

Seq Scan on insurance_company_master icm (cost=0.00..39.32 rows=844 width=33) (actual time=0.017..0.143 rows=844 loops=1)

51. 0.003 0.032 ↑ 1.0 1 1

Hash (cost=4.31..4.31 rows=1 width=35) (actual time=0.032..0.032 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.029 0.029 ↑ 1.0 1 1

Index Scan using tpa_master_pkey on tpa_master tpa (cost=0.28..4.31 rows=1 width=35) (actual time=0.028..0.029 rows=1 loops=1)

  • Index Cond: ((tpa_id)::text = 'TPAID0048'::text)
53. 11.158 22.023 ↑ 1.0 56,248 1

Hash (cost=3,641.44..3,641.44 rows=56,248 width=48) (actual time=22.023..22.023 rows=56,248 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 5,034kB
54. 10.865 10.865 ↑ 1.0 56,248 1

Seq Scan on insurance_plan_main ipm (cost=0.00..3,641.44 rows=56,248 width=48) (actual time=0.009..10.865 rows=56,248 loops=1)

55. 0.004 0.010 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=20) (actual time=0.010..0.010 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
56. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on op_type_names otn (cost=0.00..1.15 rows=5 width=20) (actual time=0.006..0.006 rows=5 loops=1)

57. 0.872 134.327 ↓ 2.1 6,876 1

Hash (cost=40,666.96..40,666.96 rows=3,344 width=12) (actual time=134.327..134.327 rows=6,876 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 360kB
58. 133.455 133.455 ↓ 2.1 6,876 1

Seq Scan on insurance_remittance ir (cost=0.00..40,666.96 rows=3,344 width=12) (actual time=85.799..133.455 rows=6,876 loops=1)

  • Filter: (((transaction_date)::date >= '2020-07-23'::date) AND ((transaction_date)::date <= '2020-07-23'::date))
  • Rows Removed by Filter: 661,898
59.          

SubPlan (for Hash Join)

60. 0.000 0.000 ↑ 1.0 1 18

Result (cost=0.00..0.04 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=18)

Planning time : 26.125 ms
Execution time : 16,368.864 ms