explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gsom

Settings
# exclusive inclusive rows x rows loops node
1. 4.843 274,056.207 ↓ 637.0 637 1

Sort (cost=596,468.35..596,468.35 rows=1 width=252) (actual time=274,056.158..274,056.207 rows=637 loops=1)

  • Sort Key: icl.claim_id
  • Sort Method: quicksort Memory: 134kB
2. 7.196 274,051.364 ↓ 637.0 637 1

Nested Loop Left Join (cost=246,459.83..596,468.34 rows=1 width=252) (actual time=5,017.003..274,051.364 rows=637 loops=1)

3. 314.401 273,320.536 ↓ 637.0 637 1

Nested Loop (cost=246,459.40..596,467.76 rows=1 width=259) (actual time=5,016.951..273,320.536 rows=637 loops=1)

4. 264.886 259,525.079 ↓ 1,685,132.0 1,685,132 1

Nested Loop Left Join (cost=246,458.99..596,467.29 rows=1 width=240) (actual time=3,553.382..259,525.079 rows=1,685,132 loops=1)

5. 1,304.290 249,149.401 ↓ 1,685,132.0 1,685,132 1

Nested Loop Left Join (cost=246,458.71..596,466.99 rows=1 width=227) (actual time=3,553.365..249,149.401 rows=1,685,132 loops=1)

6. 1,997.839 242,789.715 ↓ 1,685,132.0 1,685,132 1

Nested Loop (cost=246,458.57..596,466.82 rows=1 width=232) (actual time=3,553.353..242,789.715 rows=1,685,132 loops=1)

7. 789.706 217,200.028 ↓ 1,685,132.0 1,685,132 1

Nested Loop (cost=246,458.15..596,466.13 rows=1 width=211) (actual time=3,553.317..217,200.028 rows=1,685,132 loops=1)

  • Join Filter: ((bcl.claim_id)::text = (bcc.claim_id)::text)
8. 1,333.538 78,137.822 ↓ 1,686,250.0 1,686,250 1

Nested Loop (cost=246,457.72..596,430.43 rows=1 width=125) (actual time=3,553.252..78,137.822 rows=1,686,250 loops=1)

9. 4,870.657 9,628.968 ↓ 137.0 1,722,444 1

Hash Join (cost=246,440.79..383,124.51 rows=12,577 width=59) (actual time=3,553.116..9,628.968 rows=1,722,444 loops=1)

  • Hash Cond: ((icl.plan_id = pip.plan_id) AND ((icl.patient_id)::text = (pr.patient_id)::text))
10. 1,205.740 1,205.740 ↑ 1.0 1,765,040 1

Seq Scan on insurance_claim icl (cost=0.00..57,986.65 rows=1,836,365 width=34) (actual time=0.015..1,205.740 rows=1,765,040 loops=1)

11. 472.097 3,552.571 ↓ 1.0 1,499,174 1

Hash (cost=210,849.61..210,849.61 rows=1,496,079 width=43) (actual time=3,552.571..3,552.571 rows=1,499,174 loops=1)

  • Buckets: 8192 Batches: 32 Memory Usage: 3554kB
12. 1,813.464 3,080.474 ↓ 1.0 1,499,174 1

Hash Join (cost=120,258.43..210,849.61 rows=1,496,079 width=43) (actual time=885.354..3,080.474 rows=1,499,174 loops=1)

  • Hash Cond: ((pip.patient_id)::text = (pr.patient_id)::text)
13. 382.317 382.317 ↓ 1.0 1,499,993 1

Seq Scan on patient_insurance_plans pip (cost=0.00..35,986.79 rows=1,496,079 width=23) (actual time=0.009..382.317 rows=1,499,993 loops=1)

14. 431.756 884.693 ↓ 1.0 1,874,329 1

Hash (cost=86,140.86..86,140.86 rows=1,858,286 width=20) (actual time=884.693..884.693 rows=1,874,329 loops=1)

  • Buckets: 16384 Batches: 32 Memory Usage: 3027kB
15. 452.937 452.937 ↓ 1.0 1,874,329 1

Seq Scan on patient_registration pr (cost=0.00..86,140.86 rows=1,858,286 width=20) (actual time=0.007..452.937 rows=1,874,329 loops=1)

16. 1,722.444 67,175.316 ↑ 1.0 1 1,722,444

Subquery Scan on bcl (cost=16.93..16.95 rows=1 width=78) (actual time=0.039..0.039 rows=1 loops=1,722,444)

  • Filter: (((icl.claim_id)::text = (bcl.claim_id)::text) AND (icl.plan_id = bcl.plan_id))
  • Rows Removed by Filter: 0
17. 3,444.888 65,452.872 ↑ 1.0 1 1,722,444

HashAggregate (cost=16.93..16.94 rows=1 width=35) (actual time=0.038..0.038 rows=1 loops=1,722,444)

18. 2,078.559 62,007.984 ↑ 1.0 1 1,722,444

Nested Loop (cost=0.86..16.91 rows=1 width=35) (actual time=0.033..0.036 rows=1 loops=1,722,444)

19. 24,114.216 24,114.216 ↑ 1.0 1 1,722,444

Index Scan using bill_claim_claim_idx on bill_claim bcl_1 (cost=0.43..8.45 rows=1 width=27) (actual time=0.013..0.014 rows=1 loops=1,722,444)

  • Index Cond: ((claim_id)::text = (icl.claim_id)::text)
20. 35,815.209 35,815.209 ↑ 1.0 1 1,885,011

Index Scan using bill_pkey on bill b (cost=0.43..8.45 rows=1 width=33) (actual time=0.019..0.019 rows=1 loops=1,885,011)

  • Index Cond: ((bill_no)::text = (bcl_1.bill_no)::text)
21. 1,686.250 138,272.500 ↑ 1.0 1 1,686,250

Subquery Scan on bcc (cost=0.43..35.68 rows=1 width=106) (actual time=0.082..0.082 rows=1 loops=1,686,250)

  • Filter: ((icl.claim_id)::text = (bcc.claim_id)::text)
22. 3,372.500 136,586.250 ↑ 2.0 1 1,686,250

GroupAggregate (cost=0.43..35.66 rows=2 width=23) (actual time=0.081..0.081 rows=1 loops=1,686,250)

23. 133,213.750 133,213.750 ↑ 4.0 2 1,686,250

Index Scan using bill_charge_claim_claim_idx on bill_charge_claim bcc_1 (cost=0.43..35.56 rows=8 width=23) (actual time=0.061..0.079 rows=2 loops=1,686,250)

  • Index Cond: ((claim_id)::text = (icl.claim_id)::text)
24. 23,591.848 23,591.848 ↑ 1.0 1 1,685,132

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.68 rows=1 width=43) (actual time=0.014..0.014 rows=1 loops=1,685,132)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
25. 5,055.396 5,055.396 ↑ 1.0 1 1,685,132

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.16 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=1,685,132)

  • Index Cond: ((pd.salutation)::text = (salutation_id)::text)
26. 10,110.792 10,110.792 ↑ 1.0 1 1,685,132

Index Scan using tpa_master_pkey on tpa_master tpa (cost=0.28..0.29 rows=1 width=33) (actual time=0.005..0.006 rows=1 loops=1,685,132)

  • Index Cond: ((pip.sponsor_id)::text = (tpa_id)::text)
27. 13,481.056 13,481.056 ↓ 0.0 0 1,685,132

Index Scan using insurance_submission_batch_id_index on insurance_submission_batch firstsub (cost=0.42..0.46 rows=1 width=19) (actual time=0.008..0.008 rows=0 loops=1,685,132)

  • Index Cond: ((submission_batch_id)::text = (icl.last_submission_batch_id)::text)
  • Filter: ((is_resubmission = 'N'::bpchar) AND ((created_date)::date >= '2019-01-09'::date) AND ((created_date)::date <= '2019-01-09'::date))
  • Rows Removed by Filter: 1
28. 723.632 723.632 ↑ 1.0 1 637

Index Scan using claim_submissions_claim_id_idx on claim_submissions scs (cost=0.43..0.50 rows=1 width=21) (actual time=0.720..1.136 rows=1 loops=637)

  • Index Cond: ((claim_id)::text = (icl.claim_id)::text)
  • Filter: ((icl.last_submission_batch_id)::text = (submission_batch_id)::text)