explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z57aO

Settings
# exclusive inclusive rows x rows loops node
1. 9.318 78,416.942 ↑ 1.0 1 1

Aggregate (cost=32,955,729.13..32,955,729.14 rows=1 width=16) (actual time=78,416.941..78,416.942 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=42,100,393
2. 17.349 78,407.624 ↑ 13.9 21,808 1

Hash Left Join (cost=115.33..32,953,455.04 rows=303,212 width=32) (actual time=304.314..78,407.624 rows=21,808 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=42,100,393
3. 32.253 78,390.240 ↑ 13.9 21,808 1

Nested Loop Left Join (cost=111.84..32,931,064.86 rows=303,212 width=36) (actual time=304.272..78,390.240 rows=21,808 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=42,100,392
4. 11.059 612.467 ↑ 13.9 21,808 1

Hash Left Join (cost=3.73..140,203.12 rows=303,212 width=73) (actual time=300.880..612.467 rows=21,808 loops=1)

  • Output: up.total_cost, up.id, up.provider_geography_point, up.provider_id, up.procedure_code, up.place_of_service_id, up.claim_type_id, up.member_category_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,733
5. 601.371 601.371 ↑ 13.9 21,808 1

Seq Scan on "20191599".utilization_procedures up (cost=0.00..112,910.30 rows=303,212 width=65) (actual time=300.802..601.371 rows=21,808 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, up.usr_region_type_id, up.base_network_ids
  • Filter: ((NOT up.is_passthrough) AND (up.provider_id IS NOT NULL) AND (6584 <> ALL (up.base_network_ids)))
  • Rows Removed by Filter: 1,677,434
  • Buffers: shared hit=74,726
6. 0.011 0.037 ↓ 0.0 0 1

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

Seq Scan on "20191599".utilization_oon_factors uf1 (cost=0.00..1.91 rows=91 width=26) (actual time=0.009..0.026 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
8. 0.000 77,745.520 ↑ 1.0 1 21,808

Subquery Scan on rp (cost=108.11..108.14 rows=1 width=12) (actual time=3.565..3.565 rows=1 loops=21,808)

  • Output: rp.id, rp.reallocated_cost
  • Filter: (up.id = rp.id)
  • Buffers: shared hit=42,025,659
9. 43.616 77,745.520 ↑ 1.0 1 21,808

Aggregate (cost=108.11..108.12 rows=1 width=12) (actual time=3.565..3.565 rows=1 loops=21,808)

  • Output: up.id, avg(urp.reprice_avg_cost)
  • Buffers: shared hit=42,025,659
10. 43.616 77,701.904 ↓ 2.0 2 21,808

Limit (cost=0.41..108.10 rows=1 width=8) (actual time=2.944..3.563 rows=2 loops=21,808)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=42,025,659
11. 77,658.288 77,658.288 ↓ 2.0 2 21,808

Index Scan using ix_reprice_geo_new on "20191599".utilization_reprice_procedures urp (cost=0.41..108.10 rows=1 width=8) (actual time=2.942..3.561 rows=2 loops=21,808)

  • Output: urp.reprice_avg_cost
  • Index Cond: (urp.provider_geography_point && _st_expand(up.provider_geography_point, '8046.72'::double precision))
  • Filter: ((urp.provider_id <> up.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) AND (urp.cost_type_id = 1) AND (6584 = ANY (urp.base_network_ids)) AND (up.provider_geography_point && _st_expand(urp.provider_geography_point, '8046.72'::double precision)) AND _st_dwithin(up.provider_geography_point, urp.provider_geography_point, '8046.72'::double precision, true))
  • Rows Removed by Filter: 10,429
  • Buffers: shared hit=42,025,659
12. 0.014 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
13. 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 : 3.972 ms
Execution time : 78,417.155 ms