explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 49j1

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 6,494.750 ↓ 0.0 0 1

GroupAggregate (cost=342,179.83..342,179.88 rows=1 width=88) (actual time=6,494.750..6,494.750 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.038 6,494.749 ↓ 0.0 0 1

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

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

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

4. 0.000 6,494.710 ↓ 0.0 0 1

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

5. 0.831 6,494.710 ↓ 0.0 0 1

Nested Loop (cost=153,873.97..342,176.63 rows=1 width=78) (actual time=6,494.710..6,494.710 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. 632.363 1,887.367 ↓ 325.9 60,612 1

Hash Join (cost=153,873.54..341,520.95 rows=186 width=86) (actual time=564.396..1,887.367 rows=60,612 loops=1)

  • Hash Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
7. 720.293 799.831 ↓ 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=107.344..799.831 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. 79.538 79.538 ↑ 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=79.538..79.538 rows=1,407,154 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
9. 88.513 455.173 ↓ 1.0 328,620 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 20784kB
10. 336.385 366.660 ↓ 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=45.368..366.660 rows=328,622 loops=1)

  • Recheck Cond: ((sponsor_id)::text = 'TPAID0025'::text)
  • Heap Blocks: exact=100000
11. 30.275 30.275 ↓ 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=30.275..30.275 rows=328,622 loops=1)

  • Index Cond: ((sponsor_id)::text = 'TPAID0025'::text)
12. 4,606.512 4,606.512 ↓ 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.076..0.076 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))