explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cKTm

Settings
# exclusive inclusive rows x rows loops node
1. 1.307 35,482.084 ↓ 195.0 195 1

Hash Join (cost=1,357,216.70..1,531,750.97 rows=1 width=1,061) (actual time=35,476.891..35,482.084 rows=195 loops=1)

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

Hash Right Join (cost=1,344,152.66..1,518,686.36 rows=1 width=812) (actual time=35,361.796..35,369.770 rows=434 loops=1)

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

4. 0.533 33,896.703 ↓ 434.0 434 1

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

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

Hash Left Join (cost=1,227,980.08..1,344,152.65 rows=1 width=812) (actual time=33,189.140..33,896.170 rows=434 loops=1)

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

Hash Left Join (cost=1,227,978.95..1,344,151.51 rows=1 width=696) (actual time=33,189.109..33,895.901 rows=434 loops=1)

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

Hash Left Join (cost=1,225,646.45..1,341,819.01 rows=1 width=653) (actual time=33,168.620..33,875.174 rows=434 loops=1)

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

Hash Left Join (cost=1,225,638.15..1,341,810.69 rows=1 width=629) (actual time=33,168.580..33,874.925 rows=434 loops=1)

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

Hash Right Join (cost=1,225,609.90..1,341,782.44 rows=1 width=605) (actual time=33,168.264..33,874.370 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. 450.537 450.537 ↑ 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.010..450.537 rows=1,917,808 loops=1)

11. 1.061 33,161.053 ↓ 434.0 434 1

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

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

Hash Left Join (cost=1,037,197.98..1,225,609.89 rows=1 width=599) (actual time=31,130.499..33,159.992 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.399 33,044.560 ↓ 434.0 434 1

Hash Left Join (cost=1,027,801.64..1,216,213.54 rows=1 width=480) (actual time=31,015.776..33,044.560 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (resub.claim_id)::text)
14. 0.581 32,953.345 ↓ 434.0 434 1

Hash Left Join (cost=1,019,712.56..1,208,124.46 rows=1 width=448) (actual time=30,924.948..32,953.345 rows=434 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
15. 0.529 32,952.037 ↓ 434.0 434 1

Hash Left Join (cost=1,019,619.36..1,208,031.26 rows=1 width=434) (actual time=30,924.200..32,952.037 rows=434 loops=1)

  • Hash Cond: (pip.plan_type_id = cat.category_id)
16. 0.488 32,943.248 ↓ 434.0 434 1

Hash Left Join (cost=1,019,192.79..1,207,604.68 rows=1 width=408) (actual time=30,915.901..32,943.248 rows=434 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sal.salutation_id)::text)
17. 308.856 32,942.738 ↓ 434.0 434 1

Hash Join (cost=1,019,191.32..1,207,603.21 rows=1 width=299) (actual time=30,915.855..32,942.738 rows=434 loops=1)

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

19. 1.536 30,909.952 ↓ 434.0 434 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 128kB
20. 402.162 30,908.416 ↓ 434.0 434 1

Hash Join (cost=653,669.58..1,019,191.30 rows=1 width=246) (actual time=14,242.887..30,908.416 rows=434 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (ic.patient_id)::text)
21. 17,089.241 17,230.737 ↓ 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=238.134..17,230.737 rows=1,771,787 loops=1)

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

  • Index Cond: (center_id = 9)
23. 0.387 13,275.517 ↓ 434.0 434 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 115kB
24. 398.269 13,275.130 ↓ 434.0 434 1

Hash Join (cost=471,092.84..620,753.79 rows=1 width=215) (actual time=12,611.954..13,275.130 rows=434 loops=1)

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

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

  • Index Cond: ((sponsor_id)::text = 'TPAID0048'::text)
27. 0.511 9,529.001 ↓ 434.0 434 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 98kB
28. 121.685 9,528.490 ↓ 434.0 434 1

Hash Join (cost=248,746.15..420,681.17 rows=1 width=177) (actual time=7,771.037..9,528.490 rows=434 loops=1)

  • Hash Cond: ((ic.claim_id)::text = (cr.claim_id)::text)
29. 4,322.676 4,368.187 ↓ 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=72.080..4,368.187 rows=491,832 loops=1)

  • Recheck Cond: (status = ANY ('{D,R}'::bpchar[]))
  • Heap Blocks: exact=116,579
30. 45.511 45.511 ↓ 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=45.511..45.511 rows=491,905 loops=1)

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

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

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

Seq Scan on claim_reconciliation cr (cost=0.00..239,814.11 rows=2 width=73) (actual time=1,727.986..5,037.593 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.009 0.022 ↑ 1.0 21 1

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

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

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

35. 2.137 8.260 ↑ 1.0 10,603 1

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

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

37. 0.354 0.727 ↓ 1.0 1,921 1

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

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

39. 0.002 90.816 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
40. 0.007 90.814 ↑ 1.0 1 1

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

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

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

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

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

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

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

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

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

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

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

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

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

48. 0.140 0.297 ↑ 1.0 722 1

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

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

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

50. 0.001 0.035 ↑ 1.0 1 1

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

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

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

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

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

54. 0.003 0.019 ↑ 1.0 6 1

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

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

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

56. 0.308 111.007 ↑ 1.3 1,232 1

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

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

Seq Scan on insurance_remittance ir (cost=0.00..13,043.88 rows=1,613 width=12) (actual time=9.087..110.699 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 : 21.932 ms
Execution time : 35,482.711 ms