explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BRfA

Settings
# exclusive inclusive rows x rows loops node
1. 11.506 129,423.206 ↑ 1.0 1 1

Aggregate (cost=424,511,283.27..424,511,283.28 rows=1 width=16) (actual time=129,423.206..129,423.206 rows=1 loops=1)

  • Output: sum(rp.reallocated_cost), sum(CASE WHEN (rp.reallocated_cost IS NULL) THEN '0'::double precision ELSE (up.total_cost * COALESCE(uf1.oon_factor, uf2.oon_factor, '0'::double precision)) END)
  • Buffers: shared hit=27,052,324
2. 24.370 129,411.700 ↑ 12.3 21,281 1

Hash Left Join (cost=33,793.53..424,509,323.85 rows=261,256 width=32) (actual time=53.741..129,411.700 rows=21,281 loops=1)

  • Output: rp.reallocated_cost, up.total_cost, uf1.oon_factor, uf2.oon_factor
  • Hash Cond: ((up.claim_type_id = uf2.claim_type_id) AND (up.place_of_service_id = uf2.place_of_service_id) AND (up.member_category_id = uf2.member_category_id))
  • Buffers: shared hit=27,052,324
3. 15.054 129,387.294 ↑ 12.3 21,281 1

Nested Loop Left Join (cost=33,790.04..424,490,030.51 rows=261,256 width=36) (actual time=53.697..129,387.294 rows=21,281 loops=1)

  • Output: up.total_cost, up.place_of_service_id, up.claim_type_id, up.member_category_id, uf1.oon_factor, rp.reallocated_cost
  • Inner Unique: true
  • Buffers: shared hit=27,052,323
4. 15.814 813.719 ↑ 12.3 21,281 1

Hash Left Join (cost=32,165.84..148,333.41 rows=261,256 width=105) (actual time=52.837..813.719 rows=21,281 loops=1)

  • Output: up.total_cost, up.id, up.procedure_code, up.place_of_service_id, up.claim_type_id, up.member_category_id, up.provider_geography_point, up.specialty_ids, up.provider_id, uf1.oon_factor
  • Hash Cond: ((up.claim_type_id = uf1.claim_type_id) AND (up.place_of_service_id = uf1.place_of_service_id) AND (up.member_category_id = uf1.member_category_id) AND ((up.procedure_code)::text = (uf1.procedure_code)::text))
  • Buffers: shared hit=74,022
5. 765.524 797.865 ↑ 12.3 21,281 1

Seq Scan on "20191599".utilization_procedures up (cost=32,162.11..124,816.63 rows=261,256 width=97) (actual time=52.757..797.865 rows=21,281 loops=1)

  • Output: up.id, up.cost_type_id, up.procedure_code, up.total_cost, up.provider_id, up.client_provider_id, up.provider_primary_location_id, up.specialty_ids, up.place_of_service_id, up.claim_type_id, up.member_zip_code, up.member_category_id, up.client_claim_id, up.client_episode_id, up.member_geography_point, up.is_passthrough, up.member_type_id, up.combined_column, up.provider_geography_point
  • Filter: ((NOT up.is_passthrough) AND (up.provider_id IS NOT NULL) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 1,677,961
  • Buffers: shared hit=74,018
6.          

SubPlan (for Seq Scan)

7. 32.341 32.341 ↑ 1.0 81,368 1

Index Scan using ix_plan_provider_locations_plan on "20191599".plan_provider_locations (cost=0.56..31,956.19 rows=82,365 width=4) (actual time=0.032..32.341 rows=81,368 loops=1)

  • Output: plan_provider_locations.provider_id
  • Index Cond: (plan_provider_locations.plan_id = 6,584)
  • Buffers: shared hit=2,604
8. 0.011 0.040 ↓ 0.0 0 1

Hash (cost=1.91..1.91 rows=91 width=26) (actual time=0.040..0.040 rows=0 loops=1)

  • Output: uf1.oon_factor, uf1.claim_type_id, uf1.place_of_service_id, uf1.member_category_id, uf1.procedure_code
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
9. 0.029 0.029 ↑ 1.0 91 1

Seq Scan on "20191599".utilization_oon_factors uf1 (cost=0.00..1.91 rows=91 width=26) (actual time=0.014..0.029 rows=91 loops=1)

  • Output: uf1.oon_factor, uf1.claim_type_id, uf1.place_of_service_id, uf1.member_category_id, uf1.procedure_code
  • Buffers: shared hit=1
10. 21.281 128,558.521 ↑ 1.0 1 21,281

Subquery Scan on rp (cost=1,624.20..1,624.23 rows=1 width=12) (actual time=6.041..6.041 rows=1 loops=21,281)

  • Output: rp.id, rp.reallocated_cost
  • Filter: (up.id = rp.id)
  • Buffers: shared hit=26,978,301
11. 42.562 128,537.240 ↑ 1.0 1 21,281

Aggregate (cost=1,624.20..1,624.21 rows=1 width=12) (actual time=6.040..6.040 rows=1 loops=21,281)

  • Output: up.id, avg(urp.reprice_avg_cost)
  • Buffers: shared hit=26,978,301
12. 42.562 128,494.678 ↓ 2.0 2 21,281

Limit (cost=1,154.73..1,624.19 rows=1 width=8) (actual time=6.013..6.038 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=26,978,301
13. 103.102 128,452.116 ↓ 2.0 2 21,281

Nested Loop (cost=1,154.73..1,624.19 rows=1 width=8) (actual time=6.012..6.036 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=26,978,301
14. 383.058 125,600.462 ↑ 3.1 65 21,281

Unique (cost=1,154.31..1,155.69 rows=200 width=4) (actual time=5.877..5.902 rows=65 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=22,888,014
15. 1,170.455 125,217.404 ↑ 3.2 87 21,281

Sort (cost=1,154.31..1,155.00 rows=275 width=4) (actual time=5.876..5.884 rows=87 loops=21,281)

  • Output: p.provider_id
  • Sort Key: p.provider_id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=22,888,014
16. 829.959 124,046.949 ↑ 1.3 215 21,281

Subquery Scan on p (cost=1,139.74..1,143.17 rows=275 width=4) (actual time=5.772..5.829 rows=215 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=22,888,011
17. 1,723.761 123,216.990 ↑ 1.3 215 21,281

Sort (cost=1,139.74..1,140.42 rows=275 width=44) (actual time=5.772..5.790 rows=215 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point, ((up.provider_geography_point <-> ppl.geography_point))
  • Sort Key: ((up.provider_geography_point <-> ppl.geography_point))
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=22,888,011
18. 7,512.193 121,493.229 ↑ 1.3 215 21,281

Group (cost=1,057.78..1,128.59 rows=275 width=44) (actual time=5.339..5.709 rows=215 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point, (up.provider_geography_point <-> ppl.geography_point)
  • Group Key: ppl.provider_id, ppl.geography_point
  • Buffers: shared hit=22,888,008
19. 2,702.687 113,981.036 ↑ 1.3 215 21,281

Sort (cost=1,057.78..1,058.47 rows=275 width=36) (actual time=5.337..5.356 rows=215 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point
  • Sort Key: ppl.provider_id, ppl.geography_point
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=22,888,005
20. 100,425.039 111,278.349 ↑ 1.3 215 21,281

Bitmap Heap Scan on "20191599".plan_provider_locations ppl (cost=2.16..1,046.64 rows=275 width=36) (actual time=0.729..5.229 rows=215 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point
  • Recheck Cond: ((ppl.specialty_ids && up.specialty_ids) AND (ppl.plan_id = 6,584))
  • Filter: ((up.provider_id <> ppl.provider_id) AND (_st_distance(up.provider_geography_point, ppl.geography_point, '0'::double precision, false) <= '8046.72'::double precision))
  • Rows Removed by Filter: 3,761
  • Heap Blocks: exact=22,750,795
  • Buffers: shared hit=22,887,997
21. 10,853.310 10,853.310 ↓ 4.8 3,976 21,281

Bitmap Index Scan on ix_ppl_shafjac_temp_specialty_ids (cost=0.00..2.09 rows=824 width=0) (actual time=0.510..0.510 rows=3,976 loops=21,281)

  • Index Cond: (ppl.specialty_ids && up.specialty_ids)
  • Buffers: shared hit=137,143
22. 2,748.552 2,748.552 ↓ 0.0 0 1,374,276

Index Scan using ix_tmp_reprice on "20191599".utilization_reprice_procedures urp (cost=0.42..2.32 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=1,374,276)

  • Output: urp.id, urp.procedure_code, urp.reprice_avg_cost, urp.provider_id, urp.client_provider_id, urp.place_of_service_id, urp.member_category_id, urp.claim_type_id, urp.cost_type_id
  • Index Cond: ((urp.provider_id = p.provider_id) AND ((urp.procedure_code)::text = (up.procedure_code)::text) AND (urp.place_of_service_id = up.place_of_service_id) AND (urp.claim_type_id = up.claim_type_id) AND (urp.member_category_id = up.member_category_id))
  • Buffers: shared hit=4,090,287
23. 0.015 0.036 ↑ 3.9 23 1

Hash (cost=1.91..1.91 rows=90 width=20) (actual time=0.036..0.036 rows=23 loops=1)

  • Output: uf2.oon_factor, uf2.claim_type_id, uf2.place_of_service_id, uf2.member_category_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
24. 0.021 0.021 ↑ 1.0 90 1

Seq Scan on "20191599".utilization_oon_factors uf2 (cost=0.00..1.91 rows=90 width=20) (actual time=0.004..0.021 rows=90 loops=1)

  • Output: uf2.oon_factor, uf2.claim_type_id, uf2.place_of_service_id, uf2.member_category_id
  • Filter: (uf2.procedure_code IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1
Planning time : 2.336 ms
Execution time : 129,426.563 ms