explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DE2K

Settings
# exclusive inclusive rows x rows loops node
1. 16.218 808,021.570 ↑ 1.0 1 1

Aggregate (cost=19,454,406.19..19,454,406.20 rows=1 width=16) (actual time=808,021.570..808,021.570 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=271,326,887
2. 31.821 808,005.352 ↑ 12.3 21,281 1

Hash Left Join (cost=32,243.11..19,452,446.77 rows=261,256 width=32) (actual time=54.646..808,005.352 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=271,326,887
3. 34.499 807,973.492 ↑ 12.3 21,281 1

Nested Loop Left Join (cost=32,239.62..19,433,153.43 rows=261,256 width=36) (actual time=54.601..807,973.492 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=271,326,886
4. 19.724 878.349 ↑ 12.3 21,281 1

Hash Left Join (cost=32,165.84..148,333.41 rows=261,256 width=105) (actual time=51.699..878.349 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. 826.384 858.591 ↑ 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=51.621..858.591 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.207 32.207 ↑ 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.036..32.207 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.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.022 0.022 ↑ 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.022 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 807,060.644 ↑ 1.0 1 21,281

Subquery Scan on rp (cost=73.78..73.81 rows=1 width=12) (actual time=37.924..37.924 rows=1 loops=21,281)

  • Output: rp.id, rp.reallocated_cost
  • Filter: (up.id = rp.id)
  • Buffers: shared hit=271,252,864
11. 63.843 807,060.644 ↑ 1.0 1 21,281

Aggregate (cost=73.78..73.79 rows=1 width=12) (actual time=37.924..37.924 rows=1 loops=21,281)

  • Output: up.id, avg(urp.reprice_avg_cost)
  • Buffers: shared hit=271,252,864
12. 63.843 806,996.801 ↓ 2.0 2 21,281

Limit (cost=72.71..73.77 rows=1 width=8) (actual time=37.869..37.921 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=271,252,864
13. 223.537 806,932.958 ↓ 2.0 2 21,281

Nested Loop (cost=72.71..73.77 rows=1 width=8) (actual time=37.868..37.918 rows=2 loops=21,281)

  • Output: urp.reprice_avg_cost
  • Buffers: shared hit=271,252,864
14. 1,042.769 799,548.451 ↓ 168.0 168 21,281

Unique (cost=72.29..72.30 rows=1 width=4) (actual time=37.497..37.571 rows=168 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=260,689,942
15. 4,000.828 798,505.682 ↓ 260.0 260 21,281

Sort (cost=72.29..72.30 rows=1 width=4) (actual time=37.496..37.522 rows=260 loops=21,281)

  • Output: p.provider_id
  • Sort Key: p.provider_id
  • Sort Method: quicksort Memory: 46kB
  • Buffers: shared hit=260,689,942
16. 2,617.563 794,504.854 ↓ 685.0 685 21,281

Subquery Scan on p (cost=72.27..72.28 rows=1 width=4) (actual time=37.151..37.334 rows=685 loops=21,281)

  • Output: p.provider_id
  • Buffers: shared hit=260,689,939
17. 6,128.928 791,887.291 ↓ 685.0 685 21,281

Sort (cost=72.27..72.27 rows=1 width=44) (actual time=37.148..37.211 rows=685 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: 60kB
  • Buffers: shared hit=260,689,939
18. 23,877.282 785,758.363 ↓ 685.0 685 21,281

Group (cost=72.00..72.26 rows=1 width=44) (actual time=35.742..36.923 rows=685 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=260,689,936
19. 11,768.393 761,881.081 ↓ 685.0 685 21,281

Sort (cost=72.00..72.00 rows=1 width=36) (actual time=35.739..35.801 rows=685 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point
  • Sort Key: ppl.provider_id, ppl.geography_point
  • Sort Method: quicksort Memory: 60kB
  • Buffers: shared hit=260,689,933
20. 71,098.062 750,112.688 ↓ 685.0 685 21,281

Nested Loop (cost=5.10..71.99 rows=1 width=36) (actual time=9.097..35.248 rows=685 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point
  • Inner Unique: true
  • Join Filter: (((zrt.usr_region_type_id = 1) AND (up.provider_geography_point && _st_expand(ppl.geography_point, '16093.44'::double precision)) AND (ppl.geography_point && _st_expand(up.provider_geography_point, '16093.44'::double precision)) AND _st_dwithin(up.provider_geography_point, ppl.geography_point, '16093.44'::double precision, true)) OR ((zrt.usr_region_type_id = 2) AND (up.provider_geography_point && _st_expand(ppl.geography_point, '24140.16'::double precision)) AND (ppl.geography_point && _st_expand(up.provider_geography_point, '24140.16'::double precision)) AND _st_dwithin(up.provider_geography_point, ppl.geography_point, '24140.16'::double precision, true)) OR ((zrt.usr_region_type_id = 3) AND (up.provider_geography_point && _st_expand(ppl.geography_point, '32186.88'::double precision)) AND (ppl.geography_point && _st_expand(up.provider_geography_point, '32186.88'::double precision)) AND _st_dwithin(up.provider_geography_point, ppl.geography_point, '32186.88'::double precision, true)))
  • Rows Removed by Join Filter: 677
  • Buffers: shared hit=260,689,925
21. 10,135.677 592,037.420 ↓ 1,362.0 1,362 21,281

Nested Loop (cost=4.82..70.15 rows=1 width=42) (actual time=9.049..27.820 rows=1,362 loops=21,281)

  • Output: ppl.provider_id, ppl.geography_point, pl.zip
  • Inner Unique: true
  • Buffers: shared hit=173,712,714
22. 338,282.776 523,916.939 ↓ 1,362.0 1,362 21,281

Bitmap Heap Scan on "20191599".plan_provider_locations ppl (cost=4.40..67.71 rows=1 width=40) (actual time=9.035..24.619 rows=1,362 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
  • Recheck Cond: (((ppl.geography_point && _st_expand(up.provider_geography_point, '16093.44'::double precision)) AND (ppl.plan_id = 6,584)) OR ((ppl.geography_point && _st_expand(up.provider_geography_point, '24140.16'::double precision)) AND (ppl.plan_id = 6,584)) OR ((ppl.geography_point && _st_expand(up.provider_geography_point, '32186.88'::double precision)) AND (ppl.plan_id = 6,584)))
  • Filter: ((ppl.specialty_ids && up.specialty_ids) AND (up.provider_id <> ppl.provider_id) AND (((up.provider_geography_point && _st_expand(ppl.geography_point, '16093.44'::double precision)) AND (ppl.geography_point && _st_expand(up.provider_geography_point, '16093.44'::double precision)) AND _st_dwithin(up.provider_geography_point, ppl.geography_point, '16093.44'::double precision, true)) OR ((up.provider_geography_point && _st_expand(ppl.geography_point, '24140.16'::double precision)) AND (ppl.geography_point && _st_expand(up.provider_geography_point, '24140.16'::double precision)) AND _st_dwithin(up.provider_geography_point, ppl.geography_point, '24140.16'::double precision, true)) OR ((up.provider_geography_point && _st_expand(ppl.geography_point, '32186.88'::double precision)) AND (ppl.geography_point && _st_expand(up.provider_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
  • Heap Blocks: exact=40,120,334
  • Buffers: shared hit=57,743,106
23. 127.686 185,634.163 ↓ 0.0 0 21,281

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

  • Buffers: shared hit=17,622,707
24. 40,838.239 40,838.239 ↓ 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.919 rows=14,685 loops=21,281)

  • Index Cond: (ppl.geography_point && _st_expand(up.provider_geography_point, '16093.44'::double precision))
  • Buffers: shared hit=3,842,990
25. 61,140.313 61,140.313 ↓ 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.873..2.873 rows=23,802 loops=21,281)

  • Index Cond: (ppl.geography_point && _st_expand(up.provider_geography_point, '24140.16'::double precision))
  • Buffers: shared hit=5,837,469
26. 83,527.925 83,527.925 ↓ 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.925..3.925 rows=33,755 loops=21,281)

  • Index Cond: (ppl.geography_point && _st_expand(up.provider_geography_point, '32186.88'::double precision))
  • Buffers: shared hit=7,942,248
27. 57,984.804 57,984.804 ↑ 1.0 1 28,992,402

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=28,992,402)

  • 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=115,969,608
28. 86,977.206 86,977.206 ↑ 1.0 1 28,992,402

Index Scan using ix_zip_region_types_zip on "20191599".zip_region_types zrt (cost=0.28..0.31 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=28,992,402)

  • Output: zrt.id, zrt.zip, zrt.usr_region_type_id, zrt.cbsa_region_type_id
  • Index Cond: ((zrt.zip)::text = (pl.zip)::text)
  • Filter: ((zrt.usr_region_type_id = 1) OR (zrt.usr_region_type_id = 2) OR (zrt.usr_region_type_id = 3))
  • Buffers: shared hit=86,977,202
29. 7,160.970 7,160.970 ↓ 0.0 0 3,580,485

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,580,485)

  • 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=10,562,922
30. 0.018 0.039 ↑ 3.9 23 1

Hash (cost=1.91..1.91 rows=90 width=20) (actual time=0.038..0.039 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
31. 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 : 6.725 ms
Execution time : 808,022.460 ms