explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x8Y4

Settings
# exclusive inclusive rows x rows loops node
1. 11.177 653,302.921 ↓ 13.5 755 1

Hash Join (cost=268,890,686.69..270,287,098.18 rows=56 width=305) (actual time=613,808.887..653,302.921 rows=755 loops=1)

  • Hash Cond: (up.place_of_service_id = pos.id)
2.          

CTE ranked_providers

3. 12,637.721 579,570.034 ↓ 14,840,302.0 29,680,604 1

WindowAgg (cost=268,434,874.56..268,434,875.64 rows=2 width=104) (actual time=545,353.338..579,570.034 rows=29,680,604 loops=1)

4. 16,701.405 566,932.313 ↓ 14,840,302.0 29,680,604 1

WindowAgg (cost=268,434,874.56..268,434,875.10 rows=2 width=96) (actual time=545,353.290..566,932.313 rows=29,680,604 loops=1)

5. 34,768.263 550,230.908 ↓ 14,840,302.0 29,680,604 1

Sort (cost=268,434,874.56..268,434,874.56 rows=2 width=88) (actual time=545,353.245..550,230.908 rows=29,680,604 loops=1)

  • Sort Key: up_1.id, ((up_1.provider_geography_point <-> urp.provider_geography_point)), urp.provider_id
  • Sort Method: external merge Disk: 2,846,264kB
6. 46,761.091 515,462.645 ↓ 14,840,302.0 29,680,604 1

GroupAggregate (cost=268,434,873.98..268,434,874.55 rows=2 width=88) (actual time=453,920.960..515,462.645 rows=29,680,604 loops=1)

  • Group Key: up_1.id, urp.provider_id, up_1.provider_geography_point, urp.provider_geography_point
7. 36,069.477 468,701.554 ↓ 15,551,971.5 31,103,943 1

Sort (cost=268,434,873.98..268,434,873.99 rows=2 width=84) (actual time=453,918.754..468,701.554 rows=31,103,943 loops=1)

  • Sort Key: up_1.id, urp.provider_id, up_1.provider_geography_point, urp.provider_geography_point
  • Sort Method: external merge Disk: 2,861,048kB
8. 358,106.438 432,632.077 ↓ 15,551,971.5 31,103,943 1

Merge Join (cost=300,413.17..268,434,873.97 rows=2 width=84) (actual time=9,650.593..432,632.077 rows=31,103,943 loops=1)

  • Merge Cond: ((up_1.place_of_service_id = urp.place_of_service_id) AND ((up_1.procedure_code)::text = (urp.procedure_code)::text) 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: 867,172,923
9. 1,514.514 1,636.868 ↑ 1.0 448,938 1

Sort (cost=59,871.03..60,993.38 rows=448,938 width=86) (actual time=1,287.007..1,636.868 rows=448,938 loops=1)

  • Sort Key: up_1.place_of_service_id, up_1.procedure_code, up_1.claim_type_id, up_1.member_category_id
  • Sort Method: quicksort Memory: 75,420kB
10. 122.354 122.354 ↑ 1.0 448,938 1

Seq Scan on utilization_procedures_non_par up_1 (cost=0.00..17,724.38 rows=448,938 width=86) (actual time=0.018..122.354 rows=448,938 loops=1)

11. 65,526.538 72,888.771 ↓ 1,711.8 898,354,287 1

Sort (cost=240,158.26..241,470.27 rows=524,805 width=86) (actual time=8,363.170..72,888.771 rows=898,354,287 loops=1)

  • Sort Key: urp.place_of_service_id, urp.procedure_code, urp.claim_type_id, urp.member_category_id
  • Sort Method: quicksort Memory: 102,650kB
12. 92.056 7,362.233 ↓ 1.1 573,917 1

Subquery Scan on urp (cost=121,588.05..190,298.05 rows=524,805 width=86) (actual time=7,067.626..7,362.233 rows=573,917 loops=1)

13. 264.770 7,270.177 ↓ 1.1 573,917 1

Gather Merge (cost=121,588.05..185,050.00 rows=524,805 width=86) (actual time=7,067.613..7,270.177 rows=573,917 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
14. 340.378 7,005.407 ↑ 1.1 95,653 6 / 6

Sort (cost=120,587.98..120,850.38 rows=104,961 width=86) (actual time=6,986.613..7,005.407 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: 19,268kB
  • Worker 0: Sort Method: quicksort Memory: 16,158kB
  • Worker 1: Sort Method: quicksort Memory: 15,946kB
  • Worker 2: Sort Method: quicksort Memory: 16,035kB
  • Worker 3: Sort Method: quicksort Memory: 15,731kB
  • Worker 4: Sort Method: quicksort Memory: 16,003kB
15. 6,665.029 6,665.029 ↑ 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.695..6,665.029 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. 132.541 653,291.732 ↓ 13.5 755 1

Hash Join (cost=455,809.62..1,852,217.71 rows=56 width=360) (actual time=613,808.022..653,291.732 rows=755 loops=1)

  • Hash Cond: (up.id = rp.id)
17. 10,002.059 61,315.525 ↑ 25.6 972,279 1

Hash Join (cost=455,809.56..1,758,742.11 rows=24,926,661 width=352) (actual time=21,953.188..61,315.525 rows=972,279 loops=1)

  • Hash Cond: (upd.utilization_procedure_id = up.id)
18. 29,462.015 29,462.015 ↑ 1.0 30,194,942 1

Seq Scan on utilization_procedure_details upd (cost=0.00..940,427.96 rows=30,196,796 width=130) (actual time=100.285..29,462.015 rows=30,194,942 loops=1)

19. 260.961 21,851.451 ↑ 1.0 448,938 1

Hash (cost=450,197.83..450,197.83 rows=448,938 width=222) (actual time=21,851.451..21,851.451 rows=448,938 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 106,218kB
20. 128.104 21,590.490 ↑ 1.0 448,938 1

Hash Join (cost=254,900.03..450,197.83 rows=448,938 width=222) (actual time=8,617.545..21,590.490 rows=448,938 loops=1)

  • Hash Cond: (up.member_type_id = mt.id)
21. 120.014 21,462.343 ↑ 1.0 448,938 1

Hash Join (cost=254,898.94..447,769.68 rows=448,938 width=219) (actual time=8,617.465..21,462.343 rows=448,938 loops=1)

  • Hash Cond: (up.member_category_id = mc.id)
22. 119.111 21,342.305 ↑ 1.0 448,938 1

Hash Join (cost=254,897.90..444,092.95 rows=448,938 width=215) (actual time=8,617.410..21,342.305 rows=448,938 loops=1)

  • Hash Cond: (up.cost_type_id = costs.id)
23. 123.941 21,223.165 ↑ 1.0 448,938 1

Hash Join (cost=254,896.79..441,914.49 rows=448,938 width=211) (actual time=8,617.353..21,223.165 rows=448,938 loops=1)

  • Hash Cond: (up.claim_type_id = claims.id)
24. 271.600 21,099.197 ↑ 1.0 448,938 1

Merge Join (cost=254,895.74..438,237.76 rows=448,938 width=202) (actual time=8,617.287..21,099.197 rows=448,938 loops=1)

  • Merge Cond: (pl.id = up.provider_primary_location_id)
25. 12,126.234 12,126.234 ↑ 2.5 953,801 1

Index Scan using provider_locations_pkey on provider_locations pl (cost=0.43..435,379.34 rows=2,388,727 width=67) (actual time=0.030..12,126.234 rows=953,801 loops=1)

26. 290.557 8,701.363 ↑ 1.0 448,938 1

Sort (cost=254,831.01..255,953.36 rows=448,938 width=143) (actual time=8,612.526..8,701.363 rows=448,938 loops=1)

  • Sort Key: up.provider_primary_location_id
  • Sort Method: quicksort Memory: 125,920kB
27. 305.535 8,410.806 ↑ 1.0 448,938 1

Merge Join (cost=59,888.82..212,684.36 rows=448,938 width=143) (actual time=4,343.816..8,410.806 rows=448,938 loops=1)

  • Merge Cond: (p.id = up.provider_id)
28. 7,726.145 7,726.145 ↓ 1.0 1,576,869 1

Index Scan using providers_pkey on providers p (cost=0.43..142,185.24 rows=1,564,854 width=82) (actual time=0.024..7,726.145 rows=1,576,869 loops=1)

29. 248.696 379.126 ↑ 1.0 448,938 1

Sort (cost=59,871.03..60,993.38 rows=448,938 width=65) (actual time=275.899..379.126 rows=448,938 loops=1)

  • Sort Key: up.provider_id
  • Sort Method: quicksort Memory: 75,420kB
30. 130.430 130.430 ↑ 1.0 448,938 1

Seq Scan on utilization_procedures_non_par up (cost=0.00..17,724.38 rows=448,938 width=65) (actual time=0.067..130.430 rows=448,938 loops=1)

31. 0.010 0.027 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=17) (actual time=0.027..0.027 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.017 0.017 ↑ 1.0 2 1

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

33. 0.012 0.029 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
34. 0.017 0.017 ↑ 1.0 5 1

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

35. 0.010 0.024 ↑ 1.0 2 1

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

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

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

37. 0.015 0.043 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=11) (actual time=0.043..0.043 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
38. 0.028 0.028 ↑ 1.0 4 1

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

39. 0.263 591,843.666 ↓ 404.0 404 1

Hash (cost=0.05..0.05 rows=1 width=20) (actual time=591,843.666..591,843.666 rows=404 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
40. 591,843.403 591,843.403 ↓ 404.0 404 1

CTE Scan on ranked_providers rp (cost=0.00..0.05 rows=1 width=20) (actual time=546,748.022..591,843.403 rows=404 loops=1)

  • Filter: (("row" <= 2) AND (provider_id = 700,366))
  • Rows Removed by Filter: 29,680,200
41. 0.004 0.012 ↑ 1.0 19 1

Hash (cost=1.19..1.19 rows=19 width=19) (actual time=0.012..0.012 rows=19 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 0.008 0.008 ↑ 1.0 19 1

Seq Scan on utilization_place_of_services pos (cost=0.00..1.19 rows=19 width=19) (actual time=0.005..0.008 rows=19 loops=1)

Planning time : 34.002 ms
Execution time : 654,727.959 ms