explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AcTC

Settings
# exclusive inclusive rows x rows loops node
1. 0.380 54,718.183 ↓ 2.0 2 1

GroupAggregate (cost=452,694.49..452,694.54 rows=1 width=88) (actual time=54,717.982..54,718.183 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. 3.361 54,717.803 ↓ 594.0 594 1

Sort (cost=452,694.49..452,694.50 rows=1 width=81) (actual time=54,717.768..54,717.803 rows=594 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 108kB
3. 2.641 54,714.442 ↓ 594.0 594 1

Nested Loop (cost=448,382.47..452,694.48 rows=1 width=81) (actual time=41,625.634..54,714.442 rows=594 loops=1)

4. 0.000 54,111.331 ↓ 745.0 745 1

Nested Loop (cost=448,381.91..452,693.61 rows=1 width=78) (actual time=41,605.273..54,111.331 rows=745 loops=1)

5. 0.000 51,957.982 ↓ 903.0 903 1

Gather (cost=448,381.35..452,689.49 rows=1 width=63) (actual time=41,568.796..51,957.982 rows=903 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 32.917 53,740.142 ↓ 301.0 301 3 / 3

Nested Loop (cost=447,381.35..451,689.39 rows=1 width=63) (actual time=40,226.581..53,740.142 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. 598.204 40,726.144 ↓ 244.4 23,950 3 / 3

Merge Join (cost=447,380.79..451,310.49 rows=98 width=71) (actual time=39,493.197..40,726.144 rows=23,950 loops=3)

  • Merge Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
8. 4,428.572 7,554.611 ↓ 4.9 550,610 3 / 3

Sort (cost=265,212.65..265,493.47 rows=112,328 width=59) (actual time=6,995.880..7,554.611 rows=550,610 loops=3)

  • Sort Key: ic.patient_id, ic.plan_id
  • Sort Method: external merge Disk: 22952kB
9. 3,126.039 3,126.039 ↓ 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=6.646..3,126.039 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. 3,317.042 32,573.329 ↑ 1.0 402,392 3 / 3

Sort (cost=182,168.15..183,205.50 rows=414,940 width=20) (actual time=32,497.229..32,573.329 rows=402,392 loops=3)

  • Sort Key: pip.patient_id, pip.plan_id
  • Sort Method: external sort Disk: 13392kB
11. 23,591.825 29,256.287 ↑ 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=5,721.501..29,256.287 rows=402,060 loops=3)

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

  • Index Cond: ((sponsor_id)::text = 'TPAID0025'::text)
13. 12,981.081 12,981.081 ↓ 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.542..0.542 rows=0 loops=71,851)

  • 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. 2,225.895 2,225.895 ↑ 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=2.459..2.465 rows=1 loops=903)

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

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