explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EiK3

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 30,322.782 ↓ 0.0 0 1

GroupAggregate (cost=342,179.83..342,179.88 rows=1 width=88) (actual time=30,322.782..30,322.782 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)) <= '2020-01-31'::date))
2. 0.014 30,322.781 ↓ 0.0 0 1

Sort (cost=342,179.83..342,179.84 rows=1 width=96) (actual time=30,322.781..30,322.781 rows=0 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
3. 0.001 30,322.767 ↓ 0.0 0 1

Nested Loop (cost=153,874.83..342,179.82 rows=1 width=96) (actual time=30,322.767..30,322.767 rows=0 loops=1)

4. 0.000 30,322.766 ↓ 0.0 0 1

Nested Loop (cost=153,874.40..342,179.11 rows=1 width=92) (actual time=30,322.766..30,322.766 rows=0 loops=1)

5. 46.232 30,322.766 ↓ 0.0 0 1

Nested Loop (cost=153,873.97..342,176.63 rows=1 width=78) (actual time=30,322.766..30,322.766 rows=0 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)))
6. 916.859 19,002.702 ↓ 325.9 60,612 1

Hash Join (cost=153,873.54..341,520.95 rows=186 width=86) (actual time=16,872.924..19,002.702 rows=60,612 loops=1)

  • Hash Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
7. 1,241.587 1,419.770 ↓ 5.9 1,404,136 1

Bitmap Heap Scan on insurance_claim ic (cost=31,201.55..217,051.04 rows=239,475 width=74) (actual time=206.096..1,419.770 rows=1,404,136 loops=1)

  • Recheck Cond: (submission_batch_id IS NULL)
  • Filter: ((status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 389
  • Heap Blocks: exact=144278
8. 178.183 178.183 ↑ 1.0 1,407,154 1

Bitmap Index Scan on insurance_claim_submission_batch_id_index (cost=0.00..31,141.68 rows=1,412,966 width=0) (actual time=178.182..178.183 rows=1,407,154 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
9. 287.487 16,666.073 ↓ 1.0 328,620 1

Hash (cost=117,765.37..117,765.37 rows=327,108 width=20) (actual time=16,666.072..16,666.073 rows=328,620 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20784kB
10. 16,263.755 16,378.586 ↓ 1.0 328,622 1

Bitmap Heap Scan on patient_insurance_plans pip (cost=7,575.52..117,765.37 rows=327,108 width=20) (actual time=151.565..16,378.586 rows=328,622 loops=1)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0025'::text)
  • Heap Blocks: exact=100000
11. 114.831 114.831 ↓ 1.0 328,622 1

Bitmap Index Scan on policy_sponsor_idx (cost=0.00..7,493.74 rows=327,108 width=0) (actual time=114.831..114.831 rows=328,622 loops=1)

  • Index Cond: ((sponsor_id)::text = 'TPAID0025'::text)
12. 11,273.832 11,273.832 ↓ 0.0 0 60,612

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..3.51 rows=1 width=24) (actual time=0.186..0.186 rows=0 loops=60,612)

  • 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
13. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_claim_claim_idx on bill_claim bcl (cost=0.43..2.47 rows=1 width=28) (never executed)

  • Index Cond: ((claim_id)::text = (ic.claim_id)::text)
14. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_pkey on bill b (cost=0.43..0.71 rows=1 width=32) (never executed)

  • Index Cond: ((bill_no)::text = (bcl.bill_no)::text)
  • Filter: ((status = ANY ('{A,F}'::bpchar[])) AND (resubmission_status <> 'O'::bpchar))