explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uk2r

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 18,959.178 ↓ 0.0 0 1

GroupAggregate (cost=463,804.17..463,804.24 rows=1 width=88) (actual time=18,959.178..18,959.178 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)) >= '2019-02-07'::date) AND (date(max(b.finalized_date)) <= '2019-02-08'::date))
2. 0.051 18,959.175 ↓ 0.0 0 1

Sort (cost=463,804.17..463,804.17 rows=1 width=81) (actual time=18,959.175..18,959.175 rows=0 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
3. 0.015 18,959.124 ↓ 0.0 0 1

Nested Loop (cost=1,002.79..463,804.16 rows=1 width=81) (actual time=18,959.123..18,959.124 rows=0 loops=1)

4. 0.000 18,958.341 ↓ 2.0 2 1

Nested Loop (cost=1,002.24..463,803.41 rows=1 width=77) (actual time=8,076.614..18,958.341 rows=2 loops=1)

5. 1.492 18,957.266 ↓ 8.0 8 1

Gather (cost=1,001.68..463,801.03 rows=1 width=63) (actual time=6,763.782..18,957.266 rows=8 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
6. 53.736 18,955.774 ↓ 8.0 8 1

Nested Loop (cost=1.68..462,800.93 rows=1 width=63) (actual time=6,762.425..18,955.774 rows=8 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. 414.171 17,093.458 ↓ 689.0 120,572 1

Nested Loop (cost=1.12..462,478.17 rows=175 width=71) (actual time=0.454..17,093.458 rows=120,572 loops=1)

8. 3,365.375 3,365.375 ↓ 14.6 1,664,239 1

Parallel Index Scan using insurance_claim_submission_batch_id_index on insurance_claim ic (cost=0.56..234,746.38 rows=113,801 width=59) (actual time=0.144..3,365.375 rows=1,664,239 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
  • Filter: ((status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 394
9. 13,313.912 13,313.912 ↓ 0.0 0 1,664,239

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.56..1.97 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=1,664,239)

  • Index Cond: ((patient_id)::text = (ic.patient_id)::text)
  • Filter: (((sponsor_id)::text = 'TPAID0024'::text) AND (ic.plan_id = plan_id))
  • Rows Removed by Filter: 1
10. 1,808.580 1,808.580 ↓ 0.0 0 120,572

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..1.80 rows=1 width=24) (actual time=0.015..0.015 rows=0 loops=120,572)

  • Index Cond: ((patient_id)::text = (pip.patient_id)::text)
  • Filter: ((reg_date >= '2019-02-07'::date) AND (reg_date <= '2019-02-08'::date) AND (visit_type = 'o'::bpchar) AND ((center_id)::text = '9'::text))
  • Rows Removed by Filter: 1
11. 1.160 1.160 ↓ 0.0 0 8

Index Scan using bill_claim_claim_idx on bill_claim bcl (cost=0.56..2.35 rows=1 width=28) (actual time=0.145..0.145 rows=0 loops=8)

  • Index Cond: ((claim_id)::text = (ic.claim_id)::text)
12. 0.768 0.768 ↓ 0.0 0 2

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

  • Index Cond: ((bill_no)::text = (bcl.bill_no)::text)
  • Filter: ((status = ANY ('{A,F}'::bpchar[])) AND (resubmission_status <> 'O'::bpchar))
  • Rows Removed by Filter: 1
Planning time : 58.157 ms
Execution time : 18,959.660 ms