explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NhmC

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 51,898.899 ↓ 0.0 0 1

GroupAggregate (cost=397,041.29..397,041.36 rows=1 width=88) (actual time=51,898.899..51,898.899 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.010 51,898.896 ↓ 0.0 0 1

Sort (cost=397,041.29..397,041.29 rows=1 width=81) (actual time=51,898.896..51,898.896 rows=0 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
3. 0.022 51,898.886 ↓ 0.0 0 1

Nested Loop (cost=1,002.54..397,041.28 rows=1 width=81) (actual time=51,898.886..51,898.886 rows=0 loops=1)

4. 0.078 51,896.616 ↓ 2.0 2 1

Nested Loop (cost=1,001.99..397,040.53 rows=1 width=78) (actual time=35,603.721..51,896.616 rows=2 loops=1)

5. 1.705 51,888.042 ↓ 8.0 8 1

Gather (cost=1,001.43..397,038.17 rows=1 width=63) (actual time=15,817.075..51,888.042 rows=8 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
6. 108.994 51,886.337 ↓ 8.0 8 1

Nested Loop (cost=1.43..396,038.07 rows=1 width=63) (actual time=15,815.487..51,886.337 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. 624.624 38,131.237 ↓ 639.0 120,762 1

Nested Loop (cost=0.87..395,693.39 rows=189 width=71) (actual time=0.200..38,131.237 rows=120,762 loops=1)

8. 7,513.033 7,513.033 ↓ 14.4 1,666,310 1

Parallel Index Scan using insurance_claim_submission_batch_id_index on insurance_claim ic (cost=0.43..204,466.25 rows=115,842 width=59) (actual time=0.058..7,513.033 rows=1,666,310 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
  • Filter: ((status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 394
9. 29,993.580 29,993.580 ↓ 0.0 0 1,666,310

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.43..1.62 rows=1 width=20) (actual time=0.018..0.018 rows=0 loops=1,666,310)

  • 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. 13,646.106 13,646.106 ↓ 0.0 0 120,762

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..1.78 rows=1 width=24) (actual time=0.113..0.113 rows=0 loops=120,762)

  • 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. 8.496 8.496 ↓ 0.0 0 8

Index Scan using bill_claim_claim_idx on bill_claim bcl (cost=0.56..2.33 rows=1 width=29) (actual time=1.062..1.062 rows=0 loops=8)

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

Index Scan using bill_pkey on bill b (cost=0.56..0.75 rows=1 width=32) (actual time=1.124..1.124 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 : 5.553 ms
Execution time : 51,899.166 ms