explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jm3X

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 8,463.619 ↓ 0.0 0 1

GroupAggregate (cost=1,563,656.76..1,563,656.81 rows=1 width=88) (actual time=8,463.619..8,463.619 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.013 8,463.617 ↓ 0.0 0 1

Sort (cost=1,563,656.76..1,563,656.76 rows=1 width=96) (actual time=8,463.617..8,463.617 rows=0 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
3. 0.013 8,463.604 ↓ 0.0 0 1

Hash Join (cost=1,042,343.96..1,563,656.75 rows=1 width=96) (actual time=8,463.604..8,463.604 rows=0 loops=1)

  • Hash Cond: ((b.bill_no)::text = (bcl.bill_no)::text)
4. 0.015 0.015 ↑ 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.015..0.015 rows=1 loops=1)

  • Filter: ((status = ANY ('{A,F}'::bpchar[])) AND (resubmission_status <> 'O'::bpchar))
  • Rows Removed by Filter: 2
5. 0.001 8,463.576 ↓ 0.0 0 1

Hash (cost=1,042,343.95..1,042,343.95 rows=1 width=92) (actual time=8,463.576..8,463.576 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
6. 0.030 8,463.575 ↓ 0.0 0 1

Hash Join (cost=521,090.93..1,042,343.95 rows=1 width=92) (actual time=8,463.575..8,463.575 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. 3,202.825 3,202.825 ↓ 22.0 22 1

Seq Scan on patient_registration pr (cost=0.00..521,253.00 rows=1 width=24) (actual time=1.842..3,202.825 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: 7371338
8. 10.701 5,260.720 ↓ 115.1 19,105 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2004kB
9. 189.185 5,250.019 ↓ 115.1 19,105 1

Hash Join (cost=342,716.46..521,088.44 rows=166 width=100) (actual time=1,598.683..5,250.019 rows=19,105 loops=1)

  • Hash Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
10. 2,867.862 4,669.350 ↓ 2.6 556,577 1

Hash Join (cost=220,044.47..396,818.79 rows=212,800 width=88) (actual time=1,204.324..4,669.350 rows=556,577 loops=1)

  • Hash Cond: ((bcl.claim_id)::text = (ic.claim_id)::text)
11. 598.546 598.546 ↑ 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.011..598.546 rows=6,363,699 loops=1)

12. 449.248 1,202.942 ↓ 5.9 1,404,136 1

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

  • Buckets: 1048576 (originally 262144) Batches: 2 (originally 1) Memory Usage: 54818kB
13. 687.788 753.694 ↓ 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=96.066..753.694 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. 65.906 65.906 ↑ 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=65.906..65.906 rows=1,407,154 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
15. 98.757 391.484 ↓ 1.0 328,620 1

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

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

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

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