explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Dzc

Settings
# exclusive inclusive rows x rows loops node
1. 0.371 9,633.744 ↓ 2.0 2 1

GroupAggregate (cost=452,694.25..452,694.30 rows=1 width=88) (actual time=9,633.551..9,633.744 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.471 9,633.373 ↓ 594.0 594 1

Sort (cost=452,694.25..452,694.25 rows=1 width=81) (actual time=9,633.338..9,633.373 rows=594 loops=1)

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

Nested Loop (cost=448,382.47..452,694.24 rows=1 width=81) (actual time=8,088.594..9,630.902 rows=594 loops=1)

4. 0.000 9,620.582 ↓ 745.0 745 1

Nested Loop (cost=448,381.91..452,693.37 rows=1 width=78) (actual time=8,088.559..9,620.582 rows=745 loops=1)

5. 286.249 9,681.218 ↓ 903.0 903 1

Gather (cost=448,381.35..452,689.25 rows=1 width=63) (actual time=8,088.533..9,681.218 rows=903 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 15.843 9,394.969 ↓ 301.0 301 3 / 3

Nested Loop (cost=447,381.35..451,689.15 rows=1 width=63) (actual time=7,947.562..9,394.969 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.478 8,995.921 ↓ 244.4 23,950 3 / 3

Merge Join (cost=447,380.79..451,310.49 rows=98 width=71) (actual time=7,944.353..8,995.921 rows=23,950 loops=3)

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

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

  • Sort Key: ic.patient_id, ic.plan_id
  • Sort Method: external merge Disk: 25880kB
9. 875.187 875.187 ↓ 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.062..875.187 rows=550,651 loops=3)

  • Filter: ((submission_batch_id IS NULL) AND (status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 2269485
10. 2,881.044 3,335.623 ↑ 1.0 402,374 3 / 3

Sort (cost=182,168.15..183,205.50 rows=414,940 width=20) (actual time=3,282.183..3,335.623 rows=402,374 loops=3)

  • Sort Key: pip.patient_id, pip.plan_id
  • Sort Method: external sort Disk: 13392kB
11. 393.575 454.579 ↑ 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.255..454.579 rows=402,060 loops=3)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0025'::text)
  • Heap Blocks: exact=120071
12. 61.004 61.004 ↑ 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=61.004..61.004 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.84 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 ((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