explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mTdU

Settings
# exclusive inclusive rows x rows loops node
1. 0.347 9,713.603 ↓ 2.0 2 1

GroupAggregate (cost=452,694.74..452,694.79 rows=1 width=88) (actual time=9,713.412..9,713.603 rows=2 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: 589
2. 2.374 9,713.256 ↓ 594.0 594 1

Sort (cost=452,694.74..452,694.74 rows=1 width=81) (actual time=9,713.225..9,713.256 rows=594 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 108kB
3. 0.326 9,710.882 ↓ 594.0 594 1

Nested Loop (cost=448,382.47..452,694.73 rows=1 width=81) (actual time=7,728.664..9,710.882 rows=594 loops=1)

4. 0.000 9,700.871 ↓ 745.0 745 1

Nested Loop (cost=448,381.91..452,693.86 rows=1 width=78) (actual time=7,728.633..9,700.871 rows=745 loops=1)

5. 286.980 9,758.704 ↓ 903.0 903 1

Gather (cost=448,381.35..452,689.74 rows=1 width=63) (actual time=7,728.607..9,758.704 rows=903 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 14.200 9,471.724 ↓ 301.0 301 3 / 3

Nested Loop (cost=447,381.35..451,689.64 rows=1 width=63) (actual time=8,025.178..9,471.724 rows=301 loops=3)

  • 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. 498.548 9,074.319 ↓ 244.4 23,950 3 / 3

Merge Join (cost=447,380.79..451,310.49 rows=98 width=71) (actual time=8,021.939..9,074.319 rows=23,950 loops=3)

  • Merge Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
8. 4,346.043 5,215.947 ↓ 4.9 550,610 3 / 3

Sort (cost=265,212.65..265,493.47 rows=112,328 width=59) (actual time=4,715.237..5,215.947 rows=550,610 loops=3)

  • Sort Key: ic.patient_id, ic.plan_id
  • Sort Method: external merge Disk: 23,160kB
9. 869.904 869.904 ↓ 4.9 550,651 3 / 3

Parallel Seq Scan on insurance_claim ic (cost=0.00..255,789.81 rows=112,328 width=59) (actual time=0.044..869.904 rows=550,651 loops=3)

  • Filter: ((submission_batch_id IS NULL) AND (status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 2,269,485
10. 2,885.379 3,359.824 ↑ 1.0 402,357 3 / 3

Sort (cost=182,168.15..183,205.50 rows=414,940 width=20) (actual time=3,306.642..3,359.824 rows=402,357 loops=3)

  • Sort Key: pip.patient_id, pip.plan_id
  • Sort Method: external sort Disk: 13,392kB
11. 414.057 474.445 ↑ 1.0 402,060 3 / 3

Bitmap Heap Scan on patient_insurance_plans pip (cost=11,560.22..143,448.97 rows=414,940 width=20) (actual time=94.127..474.445 rows=402,060 loops=3)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0025'::text)
  • Heap Blocks: exact=120,071
12. 60.388 60.388 ↑ 1.0 402,127 3 / 3

Bitmap Index Scan on policy_sponsor_idx (cost=0.00..11,456.48 rows=414,940 width=0) (actual time=60.388..60.388 rows=402,127 loops=3)

  • Index Cond: ((sponsor_id)::text = 'TPAID0025'::text)
13. 383.205 383.205 ↓ 0.0 0 71,851 / 3

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..3.85 rows=1 width=24) (actual time=0.016..0.016 rows=0 loops=71,851)

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

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

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

Index Scan using bill_pkey on bill b (cost=0.56..0.87 rows=1 width=32) (actual time=0.013..0.013 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