explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HW2U

Settings
# exclusive inclusive rows x rows loops node
1. 15.351 482,116.821 ↑ 1.0 1 1

Aggregate (cost=18,030,079.91..18,030,079.92 rows=1 width=16) (actual time=482,116.820..482,116.821 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=67,544,993
2. 31.052 482,101.470 ↑ 12.0 21,281 1

Hash Left Join (cost=32,239.01..18,028,158.74 rows=256,156 width=32) (actual time=55.047..482,101.470 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=67,544,993
3. 35.351 482,070.381 ↑ 12.0 21,281 1

Nested Loop Left Join (cost=32,235.53..18,009,227.15 rows=256,156 width=36) (actual time=55.001..482,070.381 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=67,544,992
4. 18.011 914.182 ↑ 12.0 21,281 1

Hash Left Join (cost=32,165.84..149,198.32 rows=256,156 width=109) (actual time=52.231..914.182 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, up.usr_region_type_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=75,346
5. 863.755 896.137 ↑ 12.0 21,281 1

Seq Scan on "20191599".utilization_procedures up (cost=32,162.11..126,140.54 rows=256,156 width=101) (actual time=52.150..896.137 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, up.usr_region_type_id
  • 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=75,342
6.          

SubPlan (for Seq Scan)

7. 32.382 32.382 ↑ 1.0 81,368 1

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

  • Output: plan_provider_locations_1.provider_id
  • Index Cond: (plan_provider_locations_1.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. 0.000 481,120.848 ↑ 1.0 1 21,281

Subquery Scan on rp (cost=69.69..69.71 rows=1 width=12) (actual time=22.608..22.608 rows=1 loops=21,281)

  • Output: rp.id, rp.reallocated_cost
  • Filter: (up.id = rp.id)
  • Buffers: shared hit=67,469,646
11. 63.843 481,120.848 ↑ 1.0 1 21,281

Aggregate (cost=69.69..69.70 rows=1 width=12) (actual time=22.608..22.608 rows=1 loops=21,281)

  • Output: up.id, avg(urp.reprice_avg_cost)
  • Buffers: shared hit=67,469,646
12. 63.843 481,057.005 ↓ 2.0 2 21,281

Limit (cost=68.62..69.68 rows=1 width=8) (actual time=22.558..22.605 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=67,469,646
13. 29.937 480,993.162 ↓ 2.0 2 21,281

Nested Loop (cost=68.62..69.68 rows=1 width=8) (actual time=22.557..22.602 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=67,469,646
14. 936.364 474,374.771 ↓ 155.0 155 21,281

Unique (cost=68.20..68.21 rows=1 width=4) (actual time=22.227..22.291 rows=155 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=57,743,123
15. 3,617.770 473,438.407 ↓ 224.0 224 21,281

Sort (cost=68.20..68.21 rows=1 width=4) (actual time=22.225..22.247 rows=224 loops=21,281)

  • Output: p.provider_id
  • Sort Key: p.provider_id
  • Sort Method: quicksort Memory: 39kB
  • Buffers: shared hit=57,743,123
16. 2,426.034 469,820.637 ↓ 631.0 631 21,281

Subquery Scan on p (cost=68.18..68.19 rows=1 width=4) (actual time=21.908..22.077 rows=631 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=57,743,120
17. 5,596.903 467,394.603 ↓ 631.0 631 21,281

Sort (cost=68.18..68.18 rows=1 width=44) (actual time=21.905..21.963 rows=631 loops=21,281)

  • Output: plan_provider_locations.provider_id, plan_provider_locations.geography_point, ((up.provider_geography_point <-> plan_provider_locations.geography_point))
  • Sort Key: ((up.provider_geography_point <-> plan_provider_locations.geography_point))
  • Sort Method: quicksort Memory: 48kB
  • Buffers: shared hit=57,743,120
18. 21,855.587 461,797.700 ↓ 631.0 631 21,281

Group (cost=67.91..68.17 rows=1 width=44) (actual time=20.618..21.700 rows=631 loops=21,281)

  • Output: plan_provider_locations.provider_id, plan_provider_locations.geography_point, (up.provider_geography_point <-> plan_provider_locations.geography_point)
  • Group Key: plan_provider_locations.provider_id, plan_provider_locations.geography_point
  • Buffers: shared hit=57,743,117
19. 9,746.698 439,942.113 ↓ 631.0 631 21,281

Sort (cost=67.91..67.91 rows=1 width=36) (actual time=20.615..20.673 rows=631 loops=21,281)

  • Output: plan_provider_locations.provider_id, plan_provider_locations.geography_point
  • Sort Key: plan_provider_locations.provider_id, plan_provider_locations.geography_point
  • Sort Method: quicksort Memory: 48kB
  • Buffers: shared hit=57,743,114
20. 244,582.533 430,195.415 ↓ 631.0 631 21,281

Bitmap Heap Scan on "20191599".plan_provider_locations (cost=4.40..67.90 rows=1 width=36) (actual time=9.058..20.215 rows=631 loops=21,281)

  • Output: plan_provider_locations.provider_id, plan_provider_locations.geography_point
  • Recheck Cond: (((plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '16093.44'::double precision)) AND (plan_provider_locations.plan_id = 6,584)) OR ((plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '24140.16'::double precision)) AND (plan_provider_locations.plan_id = 6,584)) OR ((plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '32186.88'::double precision)) AND (plan_provider_locations.plan_id = 6,584)))
  • Filter: ((plan_provider_locations.specialty_ids && up.specialty_ids) AND (up.provider_id <> plan_provider_locations.provider_id) AND (((up.usr_region_type_id = 1) AND (up.provider_geography_point && _st_expand(plan_provider_locations.geography_point, '16093.44'::double precision)) AND (plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '16093.44'::double precision)) AND _st_dwithin(up.provider_geography_point, plan_provider_locations.geography_point, '16093.44'::double precision, true)) OR ((up.usr_region_type_id = 2) AND (up.provider_geography_point && _st_expand(plan_provider_locations.geography_point, '24140.16'::double precision)) AND (plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '24140.16'::double precision)) AND _st_dwithin(up.provider_geography_point, plan_provider_locations.geography_point, '24140.16'::double precision, true)) OR ((up.usr_region_type_id = 3) AND (up.provider_geography_point && _st_expand(plan_provider_locations.geography_point, '32186.88'::double precision)) AND (plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '32186.88'::double precision)) AND _st_dwithin(up.provider_geography_point, plan_provider_locations.geography_point, '32186.88'::double precision, true))))
  • Rows Removed by Filter: 33,124
  • Heap Blocks: exact=40,120,334
  • Buffers: shared hit=57,743,106
21. 127.686 185,612.882 ↓ 0.0 0 21,281

BitmapOr (cost=4.40..4.40 rows=25 width=0) (actual time=8.722..8.722 rows=0 loops=21,281)

  • Buffers: shared hit=17,622,707
22. 40,816.958 40,816.958 ↓ 1,835.6 14,685 21,281

Bitmap Index Scan on ix_ppl_shafjac_temp_geo (cost=0.00..1.47 rows=8 width=0) (actual time=1.918..1.918 rows=14,685 loops=21,281)

  • Index Cond: (plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '16093.44'::double precision))
  • Buffers: shared hit=3,842,990
23. 61,033.908 61,033.908 ↓ 2,975.2 23,802 21,281

Bitmap Index Scan on ix_ppl_shafjac_temp_geo (cost=0.00..1.47 rows=8 width=0) (actual time=2.868..2.868 rows=23,802 loops=21,281)

  • Index Cond: (plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '24140.16'::double precision))
  • Buffers: shared hit=5,837,469
24. 83,634.330 83,634.330 ↓ 4,219.4 33,755 21,281

Bitmap Index Scan on ix_ppl_shafjac_temp_geo (cost=0.00..1.47 rows=8 width=0) (actual time=3.930..3.930 rows=33,755 loops=21,281)

  • Index Cond: (plan_provider_locations.geography_point && _st_expand(up.provider_geography_point, '32186.88'::double precision))
  • Buffers: shared hit=7,942,248
25. 6,588.454 6,588.454 ↓ 0.0 0 3,294,227

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=3,294,227)

  • 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=9,726,523
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