explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tOoD

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 15,067.564 ↓ 0.0 0 1

GroupAggregate (cost=1,563,656.76..1,563,656.81 rows=1 width=88) (actual time=15,067.564..15,067.564 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.011 15,067.561 ↓ 0.0 0 1

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

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
3. 0.009 15,067.550 ↓ 0.0 0 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
6. 0.026 15,067.519 ↓ 0.0 0 1

Hash Join (cost=521,090.93..1,042,343.95 rows=1 width=92) (actual time=15,067.519..15,067.519 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,537.664 3,537.664 ↓ 12.0 12 1

Seq Scan on patient_registration pr (cost=0.00..521,253.00 rows=1 width=24) (actual time=0.185..3,537.664 rows=12 loops=1)

  • Filter: ((reg_date >= '2020-01-01'::date) AND (reg_date <= '2020-01-31'::date) AND (visit_type = 'o'::bpchar) AND ((center_id)::text = '19'::text))
  • Rows Removed by Filter: 7371348
8. 11.048 11,529.829 ↓ 115.1 19,105 1

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

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2004kB
9. 188.589 11,518.781 ↓ 115.1 19,105 1

Hash Join (cost=342,716.46..521,088.44 rows=166 width=100) (actual time=7,015.764..11,518.781 rows=19,105 loops=1)

  • Hash Cond: (((ic.patient_id)::text = (pip.patient_id)::text) AND (ic.plan_id = pip.plan_id))
10. 2,891.803 5,986.823 ↓ 2.6 556,577 1

Hash Join (cost=220,044.47..396,818.79 rows=212,800 width=88) (actual time=1,669.527..5,986.823 rows=556,577 loops=1)

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

12. 449.344 1,666.640 ↓ 5.9 1,404,136 1

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

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

  • Index Cond: (submission_batch_id IS NULL)
15. 163.378 5,343.369 ↓ 1.0 328,620 1

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

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

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

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