explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d51K

Settings
# exclusive inclusive rows x rows loops node
1. 14.784 573,488.274 ↑ 1.0 1 1

Aggregate (cost=3,940,163.40..3,940,163.41 rows=1 width=16) (actual time=573,488.274..573,488.274 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=592,621,263 read=1
2. 28.021 573,473.490 ↑ 12.3 21,281 1

Hash Left Join (cost=32,183.72..3,938,203.98 rows=261,256 width=32) (actual time=51.554..573,473.490 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=592,621,263 read=1
3. 21.504 573,445.434 ↑ 12.3 21,281 1

Nested Loop Left Join (cost=32,180.24..3,918,910.63 rows=261,256 width=36) (actual time=51.512..573,445.434 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=592,621,262 read=1
4. 18.777 858.625 ↑ 12.3 21,281 1

Hash Left Join (cost=32,165.84..148,333.41 rows=261,256 width=105) (actual time=49.304..858.625 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. 809.042 839.814 ↑ 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=49.229..839.814 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. 30.772 30.772 ↑ 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.031..30.772 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.034 ↓ 0.0 0 1

Hash (cost=1.91..1.91 rows=91 width=26) (actual time=0.034..0.034 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.023 0.023 ↑ 1.0 91 1

Seq Scan on "20191599".utilization_oon_factors uf1 (cost=0.00..1.91 rows=91 width=26) (actual time=0.006..0.023 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 572,565.305 ↑ 1.0 1 21,281

Subquery Scan on rp (cost=14.40..14.42 rows=1 width=12) (actual time=26.905..26.905 rows=1 loops=21,281)

  • Output: rp.id, rp.reallocated_cost
  • Filter: (up.id = rp.id)
  • Buffers: shared hit=592,547,240 read=1
11. 63.843 572,544.024 ↑ 1.0 1 21,281

Aggregate (cost=14.40..14.41 rows=1 width=12) (actual time=26.904..26.904 rows=1 loops=21,281)

  • Output: up.id, avg(urp.reprice_avg_cost)
  • Buffers: shared hit=592,547,240 read=1
12. 42.562 572,480.181 ↓ 4.0 4 21,281

Limit (cost=13.33..14.39 rows=1 width=8) (actual time=26.786..26.901 rows=4 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=592,547,240 read=1
13. 0.000 572,437.619 ↓ 4.0 4 21,281

Nested Loop (cost=13.33..14.39 rows=1 width=8) (actual time=26.786..26.899 rows=4 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=592,547,240 read=1
14. 2,213.224 558,690.093 ↓ 351.0 351 21,281

Unique (cost=12.91..12.92 rows=1 width=4) (actual time=26.095..26.253 rows=351 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=570,361,604
15. 8,193.185 556,476.869 ↓ 544.0 544 21,281

Sort (cost=12.91..12.92 rows=1 width=4) (actual time=26.094..26.149 rows=544 loops=21,281)

  • Output: p.provider_id
  • Sort Key: p.provider_id
  • Sort Method: quicksort Memory: 116kB
  • Buffers: shared hit=570,361,604
16. 5,171.283 548,283.684 ↓ 1,362.0 1,362 21,281

Subquery Scan on p (cost=12.89..12.90 rows=1 width=4) (actual time=25.395..25.764 rows=1,362 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=570,361,601
17. 12,726.038 543,112.401 ↓ 1,362.0 1,362 21,281

Sort (cost=12.89..12.89 rows=1 width=44) (actual time=25.392..25.521 rows=1,362 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: 161kB
  • Buffers: shared hit=570,361,601
18. 47,286.382 530,386.363 ↓ 1,362.0 1,362 21,281

Group (cost=12.62..12.88 rows=1 width=44) (actual time=22.580..24.923 rows=1,362 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=570,361,598
19. 24,813.646 483,099.981 ↓ 1,362.0 1,362 21,281

Sort (cost=12.62..12.62 rows=1 width=36) (actual time=22.572..22.701 rows=1,362 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point
  • Sort Key: ppl.provider_id, ppl.geography_point
  • Sort Method: quicksort Memory: 161kB
  • Buffers: shared hit=570,361,595
20. 458,286.335 458,286.335 ↓ 1,362.0 1,362 21,281

Index Scan using ix_ppl_shafjac_temp_geo on "20191599".plan_provider_locations ppl (cost=0.41..12.61 rows=1 width=36) (actual time=0.366..21.535 rows=1,362 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point
  • Index Cond: (ppl.geography_point && _st_expand(up.provider_geography_point, '32186.88'::double precision))
  • Filter: ((ppl.specialty_ids && up.specialty_ids) AND (up.provider_id <> ppl.provider_id) AND (up.provider_geography_point && _st_expand(ppl.geography_point, '32186.88'::double precision)) AND _st_dwithin(up.provider_geography_point, ppl.geography_point, '32186.88'::double precision, true))
  • Rows Removed by Filter: 32,392
  • Buffers: shared hit=570,361,587
21. 14,955.882 14,955.882 ↓ 0.0 0 7,477,941

Index Only Scan using ix_tmp_reprice on "20191599".utilization_reprice_procedures urp (cost=0.42..1.45 rows=1 width=12) (actual time=0.002..0.002 rows=0 loops=7,477,941)

  • Output: urp.provider_id, urp.procedure_code, urp.place_of_service_id, urp.claim_type_id, urp.member_category_id, urp.reprice_avg_cost
  • Index Cond: ((urp.provider_id = p.provider_id) AND (urp.procedure_code = (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))
  • Heap Fetches: 0
  • Buffers: shared hit=22,185,636 read=1
22. 0.015 0.035 ↑ 3.9 23 1

Hash (cost=1.91..1.91 rows=90 width=20) (actual time=0.035..0.035 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.020 0.020 ↑ 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.020 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 : 4.669 ms
Execution time : 573,488.742 ms