explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mWk9

Settings
# exclusive inclusive rows x rows loops node
1. 13.389 124,478.846 ↑ 1.0 1 1

Aggregate (cost=4,656,014.96..4,656,014.97 rows=1 width=16) (actual time=124,478.846..124,478.846 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=122,280,260 read=3
2. 24.675 124,465.457 ↑ 12.3 21,281 1

Hash Left Join (cost=32,186.46..4,654,055.54 rows=261,256 width=32) (actual time=54.824..124,465.457 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=122,280,260 read=3
3. 27.125 124,440.745 ↑ 12.3 21,281 1

Nested Loop Left Join (cost=32,182.98..4,634,762.20 rows=261,256 width=36) (actual time=54.776..124,440.745 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=122,280,259 read=3
4. 16.705 813.572 ↑ 12.3 21,281 1

Hash Left Join (cost=32,165.84..148,333.41 rows=261,256 width=105) (actual time=52.199..813.572 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. 764.331 796.833 ↑ 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.124..796.833 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.502 32.502 ↑ 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.039..32.502 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.033..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.007..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 123,600.048 ↑ 1.0 1 21,281

Subquery Scan on rp (cost=17.14..17.16 rows=1 width=12) (actual time=5.808..5.808 rows=1 loops=21,281)

  • Output: rp.id, rp.reallocated_cost
  • Filter: (up.id = rp.id)
  • Buffers: shared hit=122,206,237 read=3
11. 21.281 123,578.767 ↑ 1.0 1 21,281

Aggregate (cost=17.14..17.15 rows=1 width=12) (actual time=5.807..5.807 rows=1 loops=21,281)

  • Output: up.id, avg(urp.reprice_avg_cost)
  • Buffers: shared hit=122,206,237 read=3
12. 63.843 123,557.486 ↓ 2.0 2 21,281

Limit (cost=16.07..17.13 rows=1 width=8) (actual time=5.780..5.806 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=122,206,237 read=3
13. 109.608 123,493.643 ↓ 2.0 2 21,281

Nested Loop (cost=16.07..17.13 rows=1 width=8) (actual time=5.780..5.803 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=122,206,237 read=3
14. 383.058 120,641.989 ↓ 64.0 64 21,281

Unique (cost=15.65..15.66 rows=1 width=4) (actual time=5.644..5.669 rows=64 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=118,158,351 read=3
15. 1,170.455 120,258.931 ↓ 87.0 87 21,281

Sort (cost=15.65..15.66 rows=1 width=4) (actual time=5.643..5.651 rows=87 loops=21,281)

  • Output: p.provider_id
  • Sort Key: p.provider_id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=118,158,351 read=3
16. 808.678 119,088.476 ↓ 215.0 215 21,281

Subquery Scan on p (cost=15.63..15.64 rows=1 width=4) (actual time=5.539..5.596 rows=215 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=118,158,348 read=3
17. 1,723.761 118,279.798 ↓ 215.0 215 21,281

Sort (cost=15.63..15.63 rows=1 width=44) (actual time=5.539..5.558 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=118,158,348 read=3
18. 7,427.069 116,556.037 ↓ 215.0 215 21,281

Group (cost=15.36..15.62 rows=1 width=44) (actual time=5.111..5.477 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=118,158,345 read=3
19. 2,766.530 109,128.968 ↓ 215.0 215 21,281

Sort (cost=15.36..15.36 rows=1 width=36) (actual time=5.108..5.128 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=118,158,342 read=3
20. 1,114.602 106,362.438 ↓ 215.0 215 21,281

Nested Loop (cost=1.11..15.35 rows=1 width=36) (actual time=0.261..4.998 rows=215 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point
  • Inner Unique: true
  • Buffers: shared hit=118,158,334 read=3
21. 2,339.143 91,550.862 ↓ 215.0 215 21,281

Nested Loop (cost=0.83..15.05 rows=1 width=42) (actual time=0.257..4.302 rows=215 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point, pl.zip
  • Inner Unique: true
  • Buffers: shared hit=109,027,020
22. 80,080.403 80,080.403 ↓ 215.0 215 21,281

Index Scan using ix_ppl_shafjac_temp_geo on "20191599".plan_provider_locations ppl (cost=0.41..12.61 rows=1 width=40) (actual time=0.252..3.763 rows=215 loops=21,281)

  • Output: ppl.id, ppl.plan_id, ppl.plan_name, ppl.is_client_plan, ppl.provider_id, ppl.provider_location_id, ppl.geolocation_ids, ppl.specialty_ids, ppl.geography_point, ppl.address_id, ppl.group_ids, ppl.is_confirmed
  • Index Cond: (ppl.geography_point && _st_expand(up.provider_geography_point, '8046.72'::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, '8046.72'::double precision)) AND _st_dwithin(up.provider_geography_point, ppl.geography_point, '8046.72'::double precision, true))
  • Rows Removed by Filter: 5,279
  • Buffers: shared hit=90,764,388
23. 9,131.316 9,131.316 ↑ 1.0 1 4,565,658

Index Scan using provider_locations_pkey on "20191599".provider_locations pl (cost=0.42..2.44 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=4,565,658)

  • Output: pl.id, pl.provider_id, pl.address, pl.address_2, pl.city, pl.state, pl.zip, pl.county, pl.msa, pl.client_product_count, pl.non_client_product_count, pl.phone_number, pl.latitude, pl.longitude, pl.address_quality_score, pl.strenuus_location_id, pl.geolocation_ids, pl.specialty_ids, pl.address_id, pl.providers_at_address_count, pl.geography_point, pl.non_market_providers_at_address_count, pl.address_confidence, pl.group_ids, pl.is_confirmed, pl.group_tax_ids
  • Index Cond: (pl.id = ppl.provider_location_id)
  • Buffers: shared hit=18,262,632
24. 13,696.974 13,696.974 ↑ 1.0 1 4,565,658

Index Only Scan using ix_zip_region_types_zip on "20191599".zip_region_types zrt (cost=0.28..0.30 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=4,565,658)

  • Output: zrt.zip
  • Index Cond: (zrt.zip = (pl.zip)::text)
  • Heap Fetches: 0
  • Buffers: shared hit=9,131,314 read=3
25. 2,742.046 2,742.046 ↓ 0.0 0 1,371,023

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=1,371,023)

  • 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=4,047,886
26. 0.015 0.037 ↑ 3.9 23 1

Hash (cost=1.91..1.91 rows=90 width=20) (actual time=0.037..0.037 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
27. 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 : 6.326 ms
Execution time : 124,479.479 ms