explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 91ge

Settings
# exclusive inclusive rows x rows loops node
1. 0.273 53,370.951 ↓ 0.0 0 1

GroupAggregate (cost=427,060.57..427,060.64 rows=1 width=88) (actual time=53,370.951..53,370.951 rows=0 loops=1)

  • Group Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Filter: (bool_and((b.status = 'F'::bpchar)) AND (date(max(b.finalized_date)) <= '2020-01-31'::date))
  • Rows Removed by Filter: 591
2. 1.852 53,370.678 ↓ 594.0 594 1

Sort (cost=427,060.57..427,060.57 rows=1 width=81) (actual time=53,370.655..53,370.678 rows=594 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 108kB
3. 1.419 53,368.826 ↓ 594.0 594 1

Nested Loop (cost=422,992.61..427,060.56 rows=1 width=81) (actual time=46,508.874..53,368.826 rows=594 loops=1)

4. 1.359 53,096.972 ↓ 745.0 745 1

Nested Loop (cost=422,992.05..427,059.81 rows=1 width=78) (actual time=46,508.145..53,096.972 rows=745 loops=1)

5. 1.867 52,506.857 ↓ 903.0 903 1

Gather (cost=422,991.49..427,057.38 rows=1 width=63) (actual time=46,507.334..52,506.857 rows=903 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
6. 0.106 52,504.990 ↓ 903.0 903 1

Nested Loop (cost=421,991.49..426,057.28 rows=1 width=63) (actual time=46,505.964..52,504.990 rows=903 loops=1)

  • Join Filter: (((ic.patient_id)::text = (pr.patient_id)::text) AND ("substring"((ic.patient_id)::text, 5, 2) = "substring"((pr.patient_id)::text, 5, 2)))
7. 872.122 48,402.594 ↓ 757.6 71,970 1

Merge Join (cost=421,990.93..425,839.23 rows=95 width=71) (actual time=46,500.098..48,402.594 rows=71,970 loops=1)

  • Merge Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
8. 8,812.081 44,166.712 ↓ 15.1 1,651,695 1

Sort (cost=236,864.05..237,136.80 rows=109,099 width=59) (actual time=43,255.432..44,166.712 rows=1,651,695 loops=1)

  • Sort Key: ic.patient_id, ic.plan_id
  • Sort Method: external merge Disk: 74,344kB
9. 35,354.631 35,354.631 ↓ 15.1 1,651,826 1

Parallel Index Scan using insurance_claim_submission_batch_id_index on insurance_claim ic (cost=0.56..227,735.04 rows=109,099 width=59) (actual time=0.119..35,354.631 rows=1,651,826 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
  • Filter: ((status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 394
10. 1,725.721 3,363.760 ↑ 1.0 404,555 1

Sort (cost=185,126.88..186,144.38 rows=407,001 width=20) (actual time=3,244.606..3,363.760 rows=404,555 loops=1)

  • Sort Key: pip.patient_id, pip.plan_id
  • Sort Method: quicksort Memory: 43,720kB
11. 580.601 1,638.039 ↑ 1.0 402,330 1

Bitmap Heap Scan on patient_insurance_plans pip (cost=7,250.69..147,205.22 rows=407,001 width=20) (actual time=1,085.020..1,638.039 rows=402,330 loops=1)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0025'::text)
  • Heap Blocks: exact=120,127
12. 1,057.438 1,057.438 ↑ 1.0 402,348 1

Bitmap Index Scan on policy_sponsor_idx (cost=0.00..7,148.94 rows=407,001 width=0) (actual time=1,057.438..1,057.438 rows=402,348 loops=1)

  • Index Cond: ((sponsor_id)::text = 'TPAID0025'::text)
13. 4,102.290 4,102.290 ↓ 0.0 0 71,970

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..2.26 rows=1 width=24) (actual time=0.057..0.057 rows=0 loops=71,970)

  • Index Cond: ((patient_id)::text = (pip.patient_id)::text)
  • Filter: ((visit_type = ANY ('{o,i}'::bpchar[])) AND (reg_date >= '2020-01-01'::date) AND (reg_date <= '2020-01-31'::date) AND ((center_id)::text = '19'::text))
  • Rows Removed by Filter: 1
14. 588.756 588.756 ↑ 1.0 1 903

Index Scan using bill_claim_claim_idx on bill_claim bcl (cost=0.56..2.40 rows=1 width=29) (actual time=0.619..0.652 rows=1 loops=903)

  • Index Cond: ((claim_id)::text = (ic.claim_id)::text)
15. 270.435 270.435 ↑ 1.0 1 745

Index Scan using bill_pkey on bill b (cost=0.56..0.75 rows=1 width=32) (actual time=0.363..0.363 rows=1 loops=745)

  • Index Cond: ((bill_no)::text = (bcl.bill_no)::text)
  • Filter: ((status = ANY ('{A,F}'::bpchar[])) AND (resubmission_status <> 'O'::bpchar))
  • Rows Removed by Filter: 0