explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rOuk

Settings
# exclusive inclusive rows x rows loops node
1. 6.173 27,893.269 ↓ 14.9 832 1

Nested Loop (cost=3,254,253.68..3,472,691.86 rows=56 width=307) (actual time=10,023.701..27,893.269 rows=832 loops=1)

  • Join Filter: (up.place_of_service_id = pos.id)
  • Rows Removed by Join Filter: 8,284
2.          

CTE ranked_providers

3. 84.946 10,230.842 ↓ 192,621.0 192,621 1

WindowAgg (cost=3,254,249.68..3,254,250.22 rows=1 width=104) (actual time=9,991.899..10,230.842 rows=192,621 loops=1)

4. 126.455 10,145.896 ↓ 192,621.0 192,621 1

WindowAgg (cost=3,254,249.68..3,254,249.96 rows=1 width=96) (actual time=9,991.871..10,145.896 rows=192,621 loops=1)

5. 536.191 10,019.441 ↓ 192,621.0 192,621 1

Sort (cost=3,254,249.68..3,254,249.69 rows=1 width=88) (actual time=9,991.841..10,019.441 rows=192,621 loops=1)

  • Sort Key: up_1.id, ((up_1.provider_geography_point <-> urp.provider_geography_point)), urp.provider_id
  • Sort Method: quicksort Memory: 33,232kB
6. 303.542 9,483.250 ↓ 192,621.0 192,621 1

GroupAggregate (cost=3,254,249.39..3,254,249.67 rows=1 width=88) (actual time=9,158.195..9,483.250 rows=192,621 loops=1)

  • Group Key: up_1.id, urp.provider_id, up_1.provider_geography_point, urp.provider_geography_point
7. 112.700 9,179.708 ↓ 193,959.0 193,959 1

Sort (cost=3,254,249.39..3,254,249.39 rows=1 width=84) (actual time=9,158.007..9,179.708 rows=193,959 loops=1)

  • Sort Key: up_1.id, urp.provider_id, up_1.provider_geography_point, urp.provider_geography_point
  • Sort Method: quicksort Memory: 33,420kB
8. 3,052.887 9,067.008 ↓ 193,959.0 193,959 1

Merge Join (cost=240,724.47..3,254,249.38 rows=1 width=84) (actual time=5,334.663..9,067.008 rows=193,959 loops=1)

  • Merge Cond: (((up_1.procedure_code)::text = (urp.procedure_code)::text) AND (up_1.place_of_service_id = urp.place_of_service_id) AND (up_1.claim_type_id = urp.claim_type_id) AND (up_1.member_category_id = urp.member_category_id))
  • Join Filter: ((urp.provider_id <> up_1.provider_id) AND (urp.specialty_ids && up_1.specialty_ids) AND (up_1.provider_geography_point && _st_expand(urp.provider_geography_point, '24140.16'::double precision)) AND (urp.provider_geography_point && _st_expand(up_1.provider_geography_point, '24140.16'::double precision)) AND _st_dwithin(up_1.provider_geography_point, urp.provider_geography_point, '24140.16'::double precision, true))
  • Rows Removed by Join Filter: 8,512,143
9. 3.473 4.500 ↑ 1.0 3,843 1

Sort (cost=399.24..408.85 rows=3,843 width=90) (actual time=3.782..4.500 rows=3,843 loops=1)

  • Sort Key: up_1.procedure_code, up_1.place_of_service_id, up_1.claim_type_id, up_1.member_category_id
  • Sort Method: quicksort Memory: 637kB
10. 1.027 1.027 ↑ 1.0 3,843 1

Seq Scan on utilization_procedures_non_par up_1 (cost=0.00..170.43 rows=3,843 width=90) (actual time=0.007..1.027 rows=3,843 loops=1)

11. 1,960.984 6,009.621 ↓ 17.5 9,157,887 1

Sort (cost=240,158.26..241,470.27 rows=524,805 width=86) (actual time=5,326.817..6,009.621 rows=9,157,887 loops=1)

  • Sort Key: urp.procedure_code, urp.place_of_service_id, urp.claim_type_id, urp.member_category_id
  • Sort Method: quicksort Memory: 102,650kB
12. 92.399 4,048.637 ↓ 1.1 573,917 1

Subquery Scan on urp (cost=121,588.05..190,298.05 rows=524,805 width=86) (actual time=3,746.111..4,048.637 rows=573,917 loops=1)

13. 213.583 3,956.238 ↓ 1.1 573,917 1

Gather Merge (cost=121,588.05..185,050.00 rows=524,805 width=86) (actual time=3,746.108..3,956.238 rows=573,917 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
14. 422.295 3,742.655 ↑ 1.1 95,653 6 / 6

Sort (cost=120,587.98..120,850.38 rows=104,961 width=86) (actual time=3,723.065..3,742.655 rows=95,653 loops=6)

  • Sort Key: urp_1.member_category_id, urp_1.claim_type_id, urp_1.procedure_code
  • Sort Method: quicksort Memory: 17,802kB
  • Worker 0: Sort Method: quicksort Memory: 16,394kB
  • Worker 1: Sort Method: quicksort Memory: 16,191kB
  • Worker 2: Sort Method: quicksort Memory: 16,442kB
  • Worker 3: Sort Method: quicksort Memory: 16,078kB
  • Worker 4: Sort Method: quicksort Memory: 16,235kB
15. 3,320.360 3,320.360 ↑ 1.1 95,653 6 / 6

Parallel Seq Scan on utilization_reprice_procedures urp_1 (cost=0.00..111,834.49 rows=104,961 width=86) (actual time=0.621..3,320.360 rows=95,653 loops=6)

  • Filter: ((provider_id IS NOT NULL) AND (cost_type_id = 2) AND (68870 = ANY (base_network_ids)))
  • Rows Removed by Filter: 609,796
16. 289.658 27,886.264 ↓ 14.9 832 1

Nested Loop (cost=3.45..218,421.99 rows=56 width=363) (actual time=10,023.592..27,886.264 rows=832 loops=1)

  • Join Filter: (up.id = rp.id)
  • Rows Removed by Join Filter: 2,811,648
17. 10,289.206 10,289.206 ↓ 440.0 440 1

CTE Scan on ranked_providers rp (cost=0.00..0.03 rows=1 width=20) (actual time=9,991.983..10,289.206 rows=440 loops=1)

  • Filter: (("row" <= 2) AND (provider_id = 700,366))
  • Rows Removed by Filter: 192,181
18. 869.408 17,307.400 ↑ 33.4 6,392 440

Hash Join (cost=3.45..215,754.75 rows=213,377 width=355) (actual time=0.027..39.335 rows=6,392 loops=440)

  • Hash Cond: (up.member_category_id = mc.id)
19. 1,122.440 16,437.960 ↑ 33.4 6,392 440

Nested Loop (cost=2.41..214,006.69 rows=213,377 width=351) (actual time=0.026..37.359 rows=6,392 loops=440)

20. 923.560 8,551.840 ↑ 1.0 3,843 440

Nested Loop (cost=1.97..18,491.19 rows=3,843 width=221) (actual time=0.018..19.436 rows=3,843 loops=440)

  • Join Filter: (up.member_type_id = mt.id)
  • Rows Removed by Join Filter: 2,212
21. 556.143 7,628.280 ↑ 1.0 3,843 440

Hash Join (cost=1.97..18,294.16 rows=3,843 width=218) (actual time=0.017..17.337 rows=3,843 loops=440)

  • Hash Cond: (up.cost_type_id = costs.id)
22. 1,028.720 7,072.120 ↑ 1.0 3,843 440

Nested Loop (cost=0.86..18,274.40 rows=3,843 width=214) (actual time=0.017..16.073 rows=3,843 loops=440)

  • Join Filter: (up.claim_type_id = claims.id)
  • Rows Removed by Join Filter: 3,843
23. 1,188.440 6,043.400 ↑ 1.0 3,843 440

Nested Loop (cost=0.86..18,177.31 rows=3,843 width=205) (actual time=0.016..13.735 rows=3,843 loops=440)

24. 1,257.960 3,164.040 ↑ 1.0 3,843 440

Nested Loop (cost=0.43..9,003.57 rows=3,843 width=146) (actual time=0.009..7.191 rows=3,843 loops=440)

25. 215.160 215.160 ↑ 1.0 3,843 440

Seq Scan on utilization_procedures_non_par up (cost=0.00..170.43 rows=3,843 width=68) (actual time=0.001..0.489 rows=3,843 loops=440)

26. 1,690.920 1,690.920 ↑ 1.0 1 1,690,920

Index Scan using providers_pkey on providers p (cost=0.43..2.30 rows=1 width=82) (actual time=0.001..0.001 rows=1 loops=1,690,920)

  • Index Cond: (id = up.provider_id)
27. 1,690.920 1,690.920 ↑ 1.0 1 1,690,920

Index Scan using provider_locations_pkey on provider_locations pl (cost=0.43..2.39 rows=1 width=67) (actual time=0.001..0.001 rows=1 loops=1,690,920)

  • Index Cond: (id = up.provider_primary_location_id)
28. 0.000 0.000 ↑ 1.0 2 1,690,920

Materialize (cost=0.00..1.03 rows=2 width=17) (actual time=0.000..0.000 rows=2 loops=1,690,920)

29. 0.009 0.009 ↑ 1.0 2 1

Seq Scan on utilization_claim_types claims (cost=0.00..1.02 rows=2 width=17) (actual time=0.009..0.009 rows=2 loops=1)

30. 0.005 0.017 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.017..0.017 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.012 0.012 ↑ 1.0 5 1

Seq Scan on utilization_cost_types costs (cost=0.00..1.05 rows=5 width=12) (actual time=0.011..0.012 rows=5 loops=1)

32. 0.000 0.000 ↑ 2.0 2 1,690,920

Materialize (cost=0.00..1.06 rows=4 width=11) (actual time=0.000..0.000 rows=2 loops=1,690,920)

33. 0.012 0.012 ↑ 1.3 3 1

Seq Scan on utilization_member_types mt (cost=0.00..1.04 rows=4 width=11) (actual time=0.011..0.012 rows=3 loops=1)

34. 6,763.680 6,763.680 ↑ 28.0 2 1,690,920

Index Scan using ix_utilization_procedure_details_proc_id on utilization_procedure_details upd (cost=0.44..50.32 rows=56 width=130) (actual time=0.003..0.004 rows=2 loops=1,690,920)

  • Index Cond: (utilization_procedure_id = up.id)
35. 0.006 0.032 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=12) (actual time=0.032..0.032 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.026 0.026 ↑ 1.0 2 1

Seq Scan on utilization_member_categories mc (cost=0.00..1.02 rows=2 width=12) (actual time=0.025..0.026 rows=2 loops=1)

37. 0.815 0.832 ↑ 1.7 11 832

Materialize (cost=0.00..1.28 rows=19 width=19) (actual time=0.000..0.001 rows=11 loops=832)

38. 0.017 0.017 ↑ 1.7 11 1

Seq Scan on utilization_place_of_services pos (cost=0.00..1.19 rows=19 width=19) (actual time=0.015..0.017 rows=11 loops=1)

Planning time : 31.409 ms
Execution time : 27,919.109 ms