explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qFlq

Settings
# exclusive inclusive rows x rows loops node
1. 0.334 15,064.595 ↓ 2.0 2 1

GroupAggregate (cost=452,694.49..452,694.54 rows=1 width=88) (actual time=15,064.413..15,064.595 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: 583
2. 2.418 15,064.261 ↓ 588.0 588 1

Sort (cost=452,694.49..452,694.50 rows=1 width=81) (actual time=15,064.231..15,064.261 rows=588 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 107kB
3. 0.642 15,061.843 ↓ 588.0 588 1

Nested Loop (cost=448,382.47..452,694.48 rows=1 width=81) (actual time=13,375.363..15,061.843 rows=588 loops=1)

4. 0.000 15,050.855 ↓ 739.0 739 1

Nested Loop (cost=448,381.91..452,693.61 rows=1 width=78) (actual time=13,372.222..15,050.855 rows=739 loops=1)

5. 244.240 15,112.172 ↓ 896.0 896 1

Gather (cost=448,381.35..452,689.49 rows=1 width=63) (actual time=13,372.195..15,112.172 rows=896 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 11.373 14,867.932 ↓ 299.0 299 3 / 3

Nested Loop (cost=447,381.35..451,689.39 rows=1 width=63) (actual time=13,610.005..14,867.932 rows=299 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. 504.499 14,449.403 ↓ 244.4 23,950 3 / 3

Merge Join (cost=447,380.79..451,310.49 rows=98 width=71) (actual time=13,382.806..14,449.403 rows=23,950 loops=3)

  • Merge Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
8. 4,257.745 10,478.890 ↓ 4.9 550,610 3 / 3

Sort (cost=265,212.65..265,493.47 rows=112,328 width=59) (actual time=9,971.616..10,478.890 rows=550,610 loops=3)

  • Sort Key: ic.patient_id, ic.plan_id
  • Sort Method: external merge Disk: 24328kB
9. 6,221.145 6,221.145 ↓ 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=162.534..6,221.145 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,964.378 3,466.014 ↑ 1.0 402,370 3 / 3

Sort (cost=182,168.15..183,205.50 rows=414,940 width=20) (actual time=3,411.122..3,466.014 rows=402,370 loops=3)

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

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0025'::text)
  • Heap Blocks: exact=120071
12. 65.197 65.197 ↑ 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=65.197..65.197 rows=402,127 loops=3)

  • Index Cond: ((sponsor_id)::text = 'TPAID0025'::text)
13. 407.156 407.156 ↓ 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.017..0.017 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) AND ((center_id)::text = '19'::text))
  • Rows Removed by Filter: 1
14. 11.648 11.648 ↑ 1.0 1 896

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

  • Index Cond: ((claim_id)::text = (ic.claim_id)::text)
15. 10.346 10.346 ↑ 1.0 1 739

Index Scan using bill_pkey on bill b (cost=0.56..0.87 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=739)

  • 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
Planning time : 3.210 ms
Execution time : 15,149.381 ms