explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HIMP

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 30,620.983 ↓ 0.0 0 1

GroupAggregate (cost=397,063.13..397,063.20 rows=1 width=88) (actual time=30,620.983..30,620.983 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)) >= '2019-02-07'::date) AND (date(max(b.finalized_date)) <= '2019-02-08'::date))
  • Buffers: shared hit=7407613 read=52710 dirtied=3 written=8
2. 0.010 30,620.980 ↓ 0.0 0 1

Sort (cost=397,063.13..397,063.14 rows=1 width=81) (actual time=30,620.980..30,620.980 rows=0 loops=1)

  • Sort Key: ic.claim_id, pr.reg_date, pr.discharge_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=7407613 read=52710 dirtied=3 written=8
3. 0.041 30,620.970 ↓ 0.0 0 1

Nested Loop (cost=1,002.54..397,063.12 rows=1 width=81) (actual time=30,620.970..30,620.970 rows=0 loops=1)

  • Buffers: shared hit=7407613 read=52710 dirtied=3 written=8
4. 0.000 30,618.977 ↓ 2.0 2 1

Nested Loop (cost=1,001.99..397,062.37 rows=1 width=78) (actual time=19,673.686..30,618.977 rows=2 loops=1)

  • Buffers: shared hit=7407606 read=52707 dirtied=3 written=8
5. 1.195 30,610.752 ↓ 8.0 8 1

Gather (cost=1,001.43..397,060.01 rows=1 width=63) (actual time=8,927.565..30,610.752 rows=8 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
  • Buffers: shared hit=7407583 read=52696 dirtied=3 written=8
6. 46.850 30,609.557 ↓ 8.0 8 1

Nested Loop (cost=1.43..396,059.91 rows=1 width=63) (actual time=8,926.507..30,609.557 rows=8 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)))
  • Buffers: shared hit=7407583 read=52696 dirtied=3 written=8
7. 0.000 25,987.887 ↓ 637.0 120,390 1

Nested Loop (cost=0.87..395,715.22 rows=189 width=71) (actual time=1.132..25,987.887 rows=120,390 loops=1)

  • Buffers: shared hit=6807868 read=47699 dirtied=3 written=8
8. 4,485.890 4,485.890 ↓ 14.4 1,663,107 1

Parallel Index Scan using insurance_claim_submission_batch_id_index on insurance_claim ic (cost=0.43..204,482.25 rows=115,842 width=59) (actual time=0.572..4,485.890 rows=1,663,107 loops=1)

  • Index Cond: (submission_batch_id IS NULL)
  • Filter: ((status = 'O'::bpchar) AND (resubmission_count = 0))
  • Rows Removed by Filter: 394
  • Buffers: shared hit=150359 read=43565 dirtied=3 written=8
9. 21,620.391 21,620.391 ↓ 0.0 0 1,663,107

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.43..1.62 rows=1 width=20) (actual time=0.013..0.013 rows=0 loops=1,663,107)

  • Index Cond: ((patient_id)::text = (ic.patient_id)::text)
  • Filter: (((sponsor_id)::text = 'TPAID0024'::text) AND (ic.plan_id = plan_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=6657509 read=4134
10. 4,574.820 4,574.820 ↓ 0.0 0 120,390

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..1.78 rows=1 width=24) (actual time=0.038..0.038 rows=0 loops=120,390)

  • Index Cond: ((patient_id)::text = (pip.patient_id)::text)
  • Filter: ((reg_date >= '2019-02-07'::date) AND (reg_date <= '2019-02-08'::date) AND (visit_type = 'o'::bpchar) AND ((center_id)::text = '9'::text))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=599715 read=4997
11. 8.232 8.232 ↓ 0.0 0 8

Index Scan using bill_claim_claim_idx on bill_claim bcl (cost=0.56..2.33 rows=1 width=29) (actual time=1.029..1.029 rows=0 loops=8)

  • Index Cond: ((claim_id)::text = (ic.claim_id)::text)
  • Buffers: shared hit=23 read=11
12. 1.952 1.952 ↓ 0.0 0 2

Index Scan using bill_pkey on bill b (cost=0.56..0.75 rows=1 width=32) (actual time=0.976..0.976 rows=0 loops=2)

  • Index Cond: ((bill_no)::text = (bcl.bill_no)::text)
  • Filter: ((status = ANY ('{A,F}'::bpchar[])) AND (resubmission_status <> 'O'::bpchar))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=7 read=3
Planning time : 4.834 ms
Execution time : 30,621.261 ms