explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mHXd

Settings
# exclusive inclusive rows x rows loops node
1. 1.458 41,972.650 ↓ 195.0 195 1

Hash Join (cost=1,357,211.04..1,531,745.30 rows=1 width=1,061) (actual time=41,964.507..41,972.650 rows=195 loops=1)

  • Hash Cond: (cr.latest_remittance_id = ir.remittance_id)
2. 802.272 41,843.821 ↓ 434.0 434 1

Hash Right Join (cost=1,344,146.99..1,518,680.69 rows=1 width=812) (actual time=41,832.327..41,843.821 rows=434 loops=1)

  • Hash Cond: ((icr.remittance_id = cr.latest_remittance_id) AND ((icr.claim_id)::text = (cr.claim_id)::text))
3. 704.867 704.867 ↑ 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.012..704.867 rows=6,778,286 loops=1)

4. 0.543 40,336.682 ↓ 434.0 434 1

Hash (cost=1,344,146.98..1,344,146.98 rows=1 width=812) (actual time=40,336.682..40,336.682 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 272kB
5. 0.257 40,336.139 ↓ 434.0 434 1

Hash Left Join (cost=1,227,974.42..1,344,146.98 rows=1 width=812) (actual time=39,505.815..40,336.139 rows=434 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
6. 0.362 40,335.873 ↓ 434.0 434 1

Hash Left Join (cost=1,227,973.28..1,344,145.84 rows=1 width=696) (actual time=39,505.791..40,335.873 rows=434 loops=1)

  • Hash Cond: (pip.plan_id = ipm.plan_id)
7. 0.221 40,307.132 ↓ 434.0 434 1

Hash Left Join (cost=1,225,640.79..1,341,813.34 rows=1 width=653) (actual time=39,477.318..40,307.132 rows=434 loops=1)

  • Hash Cond: ((pip.sponsor_id)::text = (tpa.tpa_id)::text)
8. 0.250 40,306.882 ↓ 434.0 434 1

Hash Left Join (cost=1,225,632.48..1,341,805.02 rows=1 width=629) (actual time=39,477.281..40,306.882 rows=434 loops=1)

  • Hash Cond: ((pip.insurance_co)::text = (icm.insurance_co_id)::text)
9. 263.035 40,305.712 ↓ 434.0 434 1

Hash Right Join (cost=1,225,604.24..1,341,776.78 rows=1 width=605) (actual time=39,476.343..40,305.712 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. 573.937 573.937 ↑ 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.014..573.937 rows=1,917,808 loops=1)

11. 0.771 39,468.740 ↓ 434.0 434 1

Hash (cost=1,225,604.22..1,225,604.22 rows=1 width=599) (actual time=39,468.740..39,468.740 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 206kB
12. 0.829 39,467.969 ↓ 434.0 434 1

Hash Left Join (cost=1,037,192.32..1,225,604.22 rows=1 width=599) (actual time=38,475.561..39,467.969 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.306 39,356.251 ↓ 434.0 434 1

Hash Left Join (cost=1,027,795.98..1,216,207.88 rows=1 width=480) (actual time=38,364.395..39,356.251 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (resub.claim_id)::text)
14. 0.461 39,301.386 ↓ 434.0 434 1

Hash Left Join (cost=1,019,706.89..1,208,118.79 rows=1 width=448) (actual time=38,309.829..39,301.386 rows=434 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
15. 0.439 39,300.217 ↓ 434.0 434 1

Hash Left Join (cost=1,019,613.69..1,208,025.59 rows=1 width=434) (actual time=38,309.107..39,300.217 rows=434 loops=1)

  • Hash Cond: (pip.plan_type_id = cat.category_id)
16. 0.400 39,295.821 ↓ 434.0 434 1

Hash Left Join (cost=1,019,187.12..1,207,599.02 rows=1 width=408) (actual time=38,305.123..39,295.821 rows=434 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sal.salutation_id)::text)
17. 285.718 39,295.405 ↓ 434.0 434 1

Hash Join (cost=1,019,185.65..1,207,597.54 rows=1 width=299) (actual time=38,305.090..39,295.405 rows=434 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
18. 707.922 707.922 ↑ 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.015..707.922 rows=2,769,745 loops=1)

19. 0.598 38,301.765 ↓ 434.0 434 1

Hash (cost=1,019,185.64..1,019,185.64 rows=1 width=246) (actual time=38,301.765..38,301.765 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 128kB
20. 295.114 38,301.167 ↓ 434.0 434 1

Hash Join (cost=653,663.92..1,019,185.64 rows=1 width=246) (actual time=34,130.576..38,301.167 rows=434 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (ic.patient_id)::text)
21. 8,270.182 8,442.869 ↓ 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=259.814..8,442.869 rows=1,771,789 loops=1)

  • Recheck Cond: (center_id = 9)
  • Heap Blocks: exact=328,002
22. 172.687 172.687 ↓ 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=172.687..172.687 rows=1,773,698 loops=1)

  • Index Cond: (center_id = 9)
23. 0.604 29,563.184 ↓ 434.0 434 1

Hash (cost=620,748.12..620,748.12 rows=1 width=215) (actual time=29,563.184..29,563.184 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 115kB
24. 499.121 29,562.580 ↓ 434.0 434 1

Hash Join (cost=471,087.17..620,748.12 rows=1 width=215) (actual time=22,641.689..29,562.580 rows=434 loops=1)

  • Hash Cond: (((pip.patient_id)::text = (ic.patient_id)::text) AND (pip.plan_id = ic.plan_id))
25. 9,994.697 10,160.548 ↓ 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=190.952..10,160.548 rows=2,186,594 loops=1)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0048'::text)
  • Heap Blocks: exact=106,110
26. 165.851 165.851 ↓ 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=165.851..165.851 rows=2,186,875 loops=1)

  • Index Cond: ((sponsor_id)::text = 'TPAID0048'::text)
27. 0.989 18,902.911 ↓ 434.0 434 1

Hash (cost=420,675.51..420,675.51 rows=1 width=177) (actual time=18,902.910..18,902.911 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 98kB
28. 199.955 18,901.922 ↓ 434.0 434 1

Hash Join (cost=248,744.25..420,675.51 rows=1 width=177) (actual time=7,560.428..18,901.922 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (cr.claim_id)::text)
29. 12,986.277 13,034.437 ↓ 1.0 491,833 1

Bitmap Heap Scan on insurance_claim ic (cost=8,930.09..179,586.03 rows=485,835 width=104) (actual time=75.217..13,034.437 rows=491,833 loops=1)

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

Bitmap Index Scan on insurance_claim_status_idx (cost=0.00..8,808.64 rows=485,835 width=0) (actual time=48.160..48.160 rows=491,907 loops=1)

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

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

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

Seq Scan on claim_reconciliation cr (cost=0.00..239,814.14 rows=2 width=73) (actual time=2,047.675..5,666.413 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.006 0.016 ↑ 1.0 21 1

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

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

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

35. 1.758 3.957 ↑ 1.0 10,603 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 731kB
36. 2.199 2.199 ↑ 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.008..2.199 rows=10,603 loops=1)

37. 0.347 0.708 ↓ 1.0 1,921 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 134kB
38. 0.361 0.361 ↓ 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.361 rows=1,921 loops=1)

39. 0.001 54.559 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.008 54.558 ↑ 1.0 1 1

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

  • Group Key: resub.claim_id
41. 0.012 54.550 ↑ 1.0 1 1

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

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

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

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

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

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
45. 0.045 0.045 ↓ 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=0.042..0.045 rows=6 loops=1)

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

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

  • Buckets: 262,144 Batches: 1 Memory Usage: 30,776kB
47. 51.968 51.968 ↑ 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..51.968 rows=174,815 loops=1)

48. 0.146 0.920 ↑ 1.0 722 1

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

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

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

50. 0.001 0.029 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 0.028 0.028 ↑ 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.022..0.028 rows=1 loops=1)

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

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 4,165kB
53. 18.792 18.792 ↑ 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.309..18.792 rows=46,076 loops=1)

54. 0.003 0.009 ↑ 1.0 6 1

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

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

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

56. 0.336 127.371 ↑ 1.3 1,232 1

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

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

Seq Scan on insurance_remittance ir (cost=0.00..13,043.88 rows=1,613 width=12) (actual time=8.806..127.035 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 : 31.991 ms
Execution time : 41,973.340 ms