explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hBLc

Settings
# exclusive inclusive rows x rows loops node
1. 12.528 114,865.788 ↑ 1.0 1 1

Aggregate (cost=4,222,318.46..4,222,318.47 rows=1 width=16) (actual time=114,865.788..114,865.788 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=118,781,087
2. 23.980 114,853.260 ↑ 12.3 21,281 1

Hash Left Join (cost=32,183.39..4,220,359.04 rows=261,256 width=32) (actual time=59.271..114,853.260 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=118,781,087
3. 14.646 114,829.242 ↑ 12.3 21,281 1

Nested Loop Left Join (cost=32,179.90..4,201,065.70 rows=261,256 width=36) (actual time=59.229..114,829.242 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=118,781,086
4. 16.445 812.279 ↑ 12.3 21,281 1

Hash Left Join (cost=32,164.42..148,332.00 rows=261,256 width=105) (actual time=52.711..812.279 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. 763.531 795.793 ↑ 12.3 21,281 1

Seq Scan on "20191599".utilization_procedures up (cost=32,160.69..124,815.22 rows=261,256 width=97) (actual time=52.631..795.793 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.262 32.262 ↑ 1.0 81,368 1

Index Scan using ix_plan_provider_locations_plan on "20191599".plan_provider_locations (cost=0.56..31,954.79 rows=82,360 width=4) (actual time=0.033..32.262 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.012 0.041 ↓ 0.0 0 1

Hash (cost=1.91..1.91 rows=91 width=26) (actual time=0.041..0.041 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.013..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 114,002.317 ↑ 1.0 1 21,281

Subquery Scan on rp (cost=15.48..15.50 rows=1 width=12) (actual time=5.357..5.357 rows=1 loops=21,281)

  • Output: rp.id, rp.reallocated_cost
  • Filter: (up.id = rp.id)
  • Buffers: shared hit=118,707,064
11. 42.562 113,981.036 ↑ 1.0 1 21,281

Aggregate (cost=15.48..15.49 rows=1 width=12) (actual time=5.356..5.356 rows=1 loops=21,281)

  • Output: up.id, avg(urp.reprice_avg_cost)
  • Buffers: shared hit=118,707,064
12. 42.562 113,938.474 ↓ 2.0 2 21,281

Limit (cost=13.41..15.47 rows=1 width=8) (actual time=5.328..5.354 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=118,707,064
13. 173.451 113,895.912 ↓ 2.0 2 21,281

Nested Loop (cost=13.41..15.47 rows=1 width=8) (actual time=5.327..5.352 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=118,707,064
14. 383.058 110,980.415 ↓ 64.0 64 21,281

Unique (cost=12.99..13.00 rows=1 width=4) (actual time=5.190..5.215 rows=64 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=114,626,529
15. 1,170.455 110,597.357 ↓ 87.0 87 21,281

Sort (cost=12.99..13.00 rows=1 width=4) (actual time=5.189..5.197 rows=87 loops=21,281)

  • Output: p.provider_id
  • Sort Key: p.provider_id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=114,626,529
16. 829.959 109,426.902 ↓ 215.0 215 21,281

Subquery Scan on p (cost=12.97..12.98 rows=1 width=4) (actual time=5.086..5.142 rows=215 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=114,626,526
17. 1,745.042 108,596.943 ↓ 215.0 215 21,281

Sort (cost=12.97..12.97 rows=1 width=44) (actual time=5.085..5.103 rows=215 loops=21,281)

  • Output: provider_location_specialty_plans.provider_id, provider_location_specialty_plans.geography_point, ((up.provider_geography_point <-> provider_location_specialty_plans.geography_point))
  • Sort Key: ((up.provider_geography_point <-> provider_location_specialty_plans.geography_point))
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=114,626,526
18. 7,576.036 106,851.901 ↓ 215.0 215 21,281

Group (cost=12.70..12.96 rows=1 width=44) (actual time=4.648..5.021 rows=215 loops=21,281)

  • Output: provider_location_specialty_plans.provider_id, provider_location_specialty_plans.geography_point, (up.provider_geography_point <-> provider_location_specialty_plans.geography_point)
  • Group Key: provider_location_specialty_plans.provider_id, provider_location_specialty_plans.geography_point
  • Buffers: shared hit=114,626,523
19. 2,723.968 99,275.865 ↓ 217.0 217 21,281

Sort (cost=12.70..12.70 rows=1 width=36) (actual time=4.645..4.665 rows=217 loops=21,281)

  • Output: provider_location_specialty_plans.provider_id, provider_location_specialty_plans.geography_point
  • Sort Key: provider_location_specialty_plans.provider_id, provider_location_specialty_plans.geography_point
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=114,626,520
20. 96,551.897 96,551.897 ↓ 217.0 217 21,281

Index Scan using ix_plsp_shafjac_temp_geo on "20191599".provider_location_specialty_plans (cost=0.41..12.69 rows=1 width=36) (actual time=0.250..4.537 rows=217 loops=21,281)

  • Output: provider_location_specialty_plans.provider_id, provider_location_specialty_plans.geography_point
  • Index Cond: (provider_location_specialty_plans.geography_point && _st_expand(up.provider_geography_point, '8046.72'::double precision))
  • Filter: ((up.provider_id <> provider_location_specialty_plans.provider_id) AND (provider_location_specialty_plans.specialty_id = ANY (up.specialty_ids)) AND (up.provider_geography_point && _st_expand(provider_location_specialty_plans.geography_point, '8046.72'::double precision)) AND _st_dwithin(up.provider_geography_point, provider_location_specialty_plans.geography_point, '8046.72'::double precision, true))
  • Rows Removed by Filter: 5,670
  • Buffers: shared hit=114,626,512
21. 2,742.046 2,742.046 ↓ 0.0 0 1,371,023

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

  • 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,080,535
22. 0.016 0.038 ↑ 3.9 23 1

Hash (cost=1.91..1.91 rows=90 width=20) (actual time=0.038..0.038 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
23. 0.022 0.022 ↑ 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.022 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 : 5.383 ms
Execution time : 114,866.396 ms