explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JICB

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 32,649.222 ↓ 0.0 0 1

GroupAggregate (cost=1,563,683.26..1,563,683.31 rows=1 width=88) (actual time=32,649.222..32,649.222 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.025 32,649.219 ↓ 0.0 0 1

Sort (cost=1,563,683.26..1,563,683.26 rows=1 width=96) (actual time=32,649.219..32,649.219 rows=0 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
3. 0.038 32,649.194 ↓ 0.0 0 1

Hash Join (cost=1,042,370.46..1,563,683.25 rows=1 width=96) (actual time=32,649.194..32,649.194 rows=0 loops=1)

  • Hash Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
4. 0.453 0.453 ↑ 6,085,606.0 1 1

Seq Scan on bill b (cost=0.00..498,491.75 rows=6,085,606 width=32) (actual time=0.453..0.453 rows=1 loops=1)

  • Filter: ((status = ANY ('{A,F}'::bpchar[])) AND (resubmission_status <> 'O'::bpchar))
5. 0.001 32,648.703 ↓ 0.0 0 1

Hash (cost=1,042,370.45..1,042,370.45 rows=1 width=92) (actual time=32,648.703..32,648.703 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
6. 0.038 32,648.702 ↓ 0.0 0 1

Hash Join (cost=521,090.93..1,042,370.45 rows=1 width=92) (actual time=32,648.702..32,648.702 rows=0 loops=1)

  • Hash Cond: (((pr.patient_id)::text = (ic.patient_id)::text) AND ("substring"((pr.patient_id)::text, 5, 2) = "substring"((ic.patient_id)::text, 5, 2)))
7. 9,313.827 9,313.827 ↓ 22.0 22 1

Seq Scan on patient_registration pr (cost=0.00..521,279.50 rows=1 width=24) (actual time=9,300.868..9,313.827 rows=22 loops=1)

  • 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: 7371336
8. 12.581 23,334.837 ↓ 115.1 19,105 1

Hash (cost=521,088.44..521,088.44 rows=166 width=100) (actual time=23,334.837..23,334.837 rows=19,105 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2004kB
9. 200.512 23,322.256 ↓ 115.1 19,105 1

Hash Join (cost=342,716.46..521,088.44 rows=166 width=100) (actual time=17,556.818..23,322.256 rows=19,105 loops=1)

  • Hash Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
10. 3,103.888 7,107.128 ↓ 2.6 556,577 1

Hash Join (cost=220,044.47..396,818.79 rows=212,800 width=88) (actual time=1,539.728..7,107.128 rows=556,577 loops=1)

  • Hash Cond: ((bcl.claim_id)::text = (ic.claim_id)::text)
11. 2,472.989 2,472.989 ↑ 1.0 6,363,699 1

Seq Scan on bill_claim bcl (cost=0.00..160,066.56 rows=6,364,856 width=28) (actual time=0.336..2,472.989 rows=6,363,699 loops=1)

12. 446.146 1,530.251 ↓ 5.9 1,404,136 1

Hash (cost=217,051.04..217,051.04 rows=239,475 width=74) (actual time=1,530.251..1,530.251 rows=1,404,136 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 2 (originally 1) Memory Usage: 54818kB
13. 933.415 1,084.105 ↓ 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=177.601..1,084.105 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
14. 150.690 150.690 ↑ 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=150.690..150.690 rows=1,407,154 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
15. 281.351 16,014.616 ↓ 1.0 328,620 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 20784kB
16. 15,655.069 15,733.265 ↓ 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=95.318..15,733.265 rows=328,622 loops=1)

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

  • Index Cond: ((sponsor_id)::text = 'TPAID0025'::text)