explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Kc75

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 31,017.599 ↓ 0.0 0 1

GroupAggregate (cost=397,039.62..397,039.69 rows=1 width=88) (actual time=31,017.599..31,017.599 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.043 31,017.593 ↓ 0.0 0 1

Sort (cost=397,039.62..397,039.62 rows=1 width=81) (actual time=31,017.593..31,017.593 rows=0 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
3. 0.019 31,017.550 ↓ 0.0 0 1

Nested Loop (cost=1,002.54..397,039.61 rows=1 width=81) (actual time=31,017.549..31,017.550 rows=0 loops=1)

4. 0.000 31,015.087 ↓ 2.0 2 1

Nested Loop (cost=1,001.99..397,038.86 rows=1 width=78) (actual time=24,892.500..31,015.087 rows=2 loops=1)

5. 390.116 31,395.199 ↓ 8.0 8 1

Gather (cost=1,001.43..397,036.50 rows=1 width=63) (actual time=15,823.015..31,395.199 rows=8 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
6. 24.512 31,005.083 ↓ 4.0 4 2 / 2

Nested Loop (cost=1.43..396,036.40 rows=1 width=63) (actual time=16,278.400..31,005.083 rows=4 loops=2)

  • 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. 472.966 22,522.051 ↓ 319.7 60,418 2 / 2

Nested Loop (cost=0.87..395,691.72 rows=189 width=71) (actual time=2.220..22,522.051 rows=60,418 loops=2)

8. 1,220.672 1,220.672 ↓ 7.2 833,136 2 / 2

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=1.458..1,220.672 rows=833,136 loops=2)

  • Index Cond: (submission_batch_id IS NULL)
  • Filter: ((status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 197
9. 20,828.413 20,828.413 ↓ 0.0 0 1,666,273 / 2

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.025..0.025 rows=0 loops=1,666,273)

  • 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. 8,458.520 8,458.520 ↓ 0.0 0 120,836 / 2

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

  • 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. 7.976 7.976 ↓ 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=0.997..0.997 rows=0 loops=8)

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

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