explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RJP7

Settings
# exclusive inclusive rows x rows loops node
1. 11.053 26,395.544 ↓ 195.0 195 1

Hash Join (cost=1,357,216.70..1,531,750.97 rows=1 width=1,061) (actual time=26,389.875..26,395.544 rows=195 loops=1)

  • Hash Cond: (cr.latest_remittance_id = ir.remittance_id)
2. 818.008 26,319.039 ↓ 434.0 434 1

Hash Right Join (cost=1,344,152.66..1,518,686.36 rows=1 width=812) (actual time=26,310.340..26,319.039 rows=434 loops=1)

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

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

4. 0.687 24,682.127 ↓ 434.0 434 1

Hash (cost=1,344,152.65..1,344,152.65 rows=1 width=812) (actual time=24,682.127..24,682.127 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 272kB
5. 0.301 24,681.440 ↓ 434.0 434 1

Hash Left Join (cost=1,227,980.08..1,344,152.65 rows=1 width=812) (actual time=23,435.187..24,681.440 rows=434 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
6. 0.513 24,680.846 ↓ 434.0 434 1

Hash Left Join (cost=1,227,978.95..1,344,151.51 rows=1 width=696) (actual time=23,434.844..24,680.846 rows=434 loops=1)

  • Hash Cond: (pip.plan_id = ipm.plan_id)
7. 0.236 24,653.594 ↓ 434.0 434 1

Hash Left Join (cost=1,225,646.45..1,341,819.01 rows=1 width=653) (actual time=23,407.845..24,653.594 rows=434 loops=1)

  • Hash Cond: ((pip.sponsor_id)::text = (tpa.tpa_id)::text)
8. 0.248 24,650.486 ↓ 434.0 434 1

Hash Left Join (cost=1,225,638.15..1,341,810.69 rows=1 width=629) (actual time=23,404.959..24,650.486 rows=434 loops=1)

  • Hash Cond: ((pip.insurance_co)::text = (icm.insurance_co_id)::text)
9. 291.020 24,648.618 ↓ 434.0 434 1

Hash Right Join (cost=1,225,609.90..1,341,782.44 rows=1 width=605) (actual time=23,403.310..24,648.618 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. 963.540 963.540 ↑ 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.318..963.540 rows=1,917,808 loops=1)

11. 1.338 23,394.058 ↓ 434.0 434 1

Hash (cost=1,225,609.89..1,225,609.89 rows=1 width=599) (actual time=23,394.058..23,394.058 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 206kB
12. 1.003 23,392.720 ↓ 434.0 434 1

Hash Left Join (cost=1,037,197.98..1,225,609.89 rows=1 width=599) (actual time=20,813.084..23,392.720 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.373 23,282.364 ↓ 434.0 434 1

Hash Left Join (cost=1,027,801.64..1,216,213.54 rows=1 width=480) (actual time=20,703.450..23,282.364 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (resub.claim_id)::text)
14. 0.541 23,191.584 ↓ 434.0 434 1

Hash Left Join (cost=1,019,712.56..1,208,124.46 rows=1 width=448) (actual time=20,613.032..23,191.584 rows=434 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
15. 0.524 23,187.580 ↓ 434.0 434 1

Hash Left Join (cost=1,019,619.36..1,208,031.26 rows=1 width=434) (actual time=20,609.550..23,187.580 rows=434 loops=1)

  • Hash Cond: (pip.plan_type_id = cat.category_id)
16. 0.493 23,172.807 ↓ 434.0 434 1

Hash Left Join (cost=1,019,192.79..1,207,604.68 rows=1 width=408) (actual time=20,595.265..23,172.807 rows=434 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sal.salutation_id)::text)
17. 313.767 23,171.011 ↓ 434.0 434 1

Hash Join (cost=1,019,191.32..1,207,603.21 rows=1 width=299) (actual time=20,593.936..23,171.011 rows=434 loops=1)

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

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

19. 1.219 20,576.009 ↓ 434.0 434 1

Hash (cost=1,019,191.30..1,019,191.30 rows=1 width=246) (actual time=20,576.009..20,576.009 rows=434 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 128kB
20. 322.799 20,574.790 ↓ 434.0 434 1

Hash Join (cost=653,669.58..1,019,191.30 rows=1 width=246) (actual time=7,961.834..20,574.790 rows=434 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (ic.patient_id)::text)
21. 12,733.194 12,889.858 ↓ 1.0 1,771,787 1

Bitmap Heap Scan on patient_registration pr (cost=32,915.79..391,847.49 rows=1,757,336 width=47) (actual time=242.417..12,889.858 rows=1,771,787 loops=1)

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

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

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

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

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

Hash Join (cost=471,092.84..620,753.79 rows=1 width=215) (actual time=6,642.644..7,361.745 rows=434 loops=1)

  • Hash Cond: (((pip.patient_id)::text = (ic.patient_id)::text) AND (pip.plan_id = ic.plan_id))
25. 888.814 1,054.971 ↓ 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=189.659..1,054.971 rows=2,186,594 loops=1)

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

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

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

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

Hash Join (cost=248,746.15..420,681.17 rows=1 width=177) (actual time=5,227.871..5,944.330 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (cr.claim_id)::text)
29. 783.514 841.345 ↓ 1.0 491,832 1

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

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

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

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

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

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

Seq Scan on claim_reconciliation cr (cost=0.00..239,814.11 rows=2 width=73) (actual time=1,785.978..5,028.591 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,737
33. 0.014 1.303 ↑ 1.0 21 1

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

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

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

35. 3.284 14.249 ↑ 1.0 10,603 1

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

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

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

37. 0.432 3.463 ↓ 1.0 1,921 1

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

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

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

39. 0.002 90.407 ↑ 1.0 1 1

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

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

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

  • Group Key: resub.claim_id
41. 1.755 90.397 ↑ 1.0 1 1

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

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

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

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

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

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

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

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

  • Index Cond: ((category_type)::text = 'RECON'::text)
46. 59.746 109.353 ↑ 1.0 174,814 1

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

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

Seq Scan on insurance_submission_batch isb (cost=0.00..7,210.04 rows=174,904 width=128) (actual time=0.009..49.607 rows=174,814 loops=1)

48. 0.174 1.620 ↑ 1.0 722 1

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

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

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

50. 0.004 2.872 ↑ 1.0 1 1

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

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

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

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

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

54. 0.005 0.293 ↑ 1.0 6 1

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

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

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

56. 0.239 65.452 ↑ 1.3 1,232 1

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

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

Seq Scan on insurance_remittance ir (cost=0.00..13,043.88 rows=1,613 width=12) (actual time=4.428..65.213 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,186
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 : 20.448 ms
Execution time : 26,396.158 ms