explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RvE8

Settings
# exclusive inclusive rows x rows loops node
1. 3.609 1,154,528.876 ↓ 26.0 832 1

Hash Join (cost=486,046,855.46..487,818,371.38 rows=32 width=305) (actual time=1,121,380.395..1,154,528.876 rows=832 loops=1)

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

CTE ranked_providers

3. 25,085.089 1,087,176.392 ↓ 19,703,435.0 59,110,305 1

WindowAgg (cost=485,337,829.51..485,337,831.13 rows=3 width=104) (actual time=1,019,584.415..1,087,176.392 rows=59,110,305 loops=1)

4. 33,106.822 1,062,091.303 ↓ 19,703,435.0 59,110,305 1

WindowAgg (cost=485,337,829.51..485,337,830.33 rows=3 width=96) (actual time=1,019,584.377..1,062,091.303 rows=59,110,305 loops=1)

5. 69,130.911 1,028,984.481 ↓ 19,703,435.0 59,110,305 1

Sort (cost=485,337,829.51..485,337,829.52 rows=3 width=88) (actual time=1,019,584.349..1,028,984.481 rows=59,110,305 loops=1)

  • Sort Key: up_1.id, ((up_1.provider_geography_point <-> urp.provider_geography_point)), urp.provider_id
  • Sort Method: external merge Disk: 5,668,472kB
6. 98,040.436 959,853.570 ↓ 19,703,435.0 59,110,305 1

GroupAggregate (cost=485,337,828.64..485,337,829.49 rows=3 width=88) (actual time=848,055.433..959,853.570 rows=59,110,305 loops=1)

  • Group Key: up_1.id, urp.provider_id, up_1.provider_geography_point, urp.provider_geography_point
7. 72,038.542 861,813.134 ↓ 20,593,329.3 61,779,988 1

Sort (cost=485,337,828.64..485,337,828.65 rows=3 width=84) (actual time=848,053.001..861,813.134 rows=61,779,988 loops=1)

  • Sort Key: up_1.id, urp.provider_id, up_1.provider_geography_point, urp.provider_geography_point
  • Sort Method: external merge Disk: 5,682,736kB
8. 663,378.557 789,774.592 ↓ 20,593,329.3 61,779,988 1

Merge Join (cost=375,219.18..485,337,828.62 rows=3 width=84) (actual time=3,156.752..789,774.592 rows=61,779,988 loops=1)

  • Merge Cond: ((urp.place_of_service_id = up_1.place_of_service_id) AND ((urp.procedure_code)::text = (up_1.procedure_code)::text) AND (urp.claim_type_id = up_1.claim_type_id) AND (urp.member_category_id = up_1.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: 1,621,494,872
9. 1,066.657 1,809.361 ↓ 1.1 573,917 1

Sort (cost=240,158.26..241,470.27 rows=524,805 width=86) (actual time=1,686.758..1,809.361 rows=573,917 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
10. 94.255 742.704 ↓ 1.1 573,917 1

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

11. 228.920 648.449 ↓ 1.1 573,917 1

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

  • Workers Planned: 5
  • Workers Launched: 5
12. 238.604 419.529 ↑ 1.1 95,653 6 / 6

Sort (cost=120,587.98..120,850.38 rows=104,961 width=86) (actual time=403.691..419.529 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: 18,913kB
  • Worker 0: Sort Method: quicksort Memory: 16,024kB
  • Worker 1: Sort Method: quicksort Memory: 16,162kB
  • Worker 2: Sort Method: quicksort Memory: 15,950kB
  • Worker 3: Sort Method: quicksort Memory: 16,197kB
  • Worker 4: Sort Method: quicksort Memory: 15,895kB
13. 180.925 180.925 ↑ 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.053..180.925 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
14. 124,303.004 124,586.674 ↓ 1,799.4 1,683,284,622 1

Sort (cost=135,060.42..137,399.12 rows=935,478 width=86) (actual time=1,469.483..124,586.674 rows=1,683,284,622 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: 153,491kB
15. 283.670 283.670 ↑ 1.0 935,448 1

Seq Scan on utilization_procedures_non_par up_1 (cost=0.00..42,282.78 rows=935,478 width=86) (actual time=0.043..283.670 rows=935,448 loops=1)

16. 179.352 1,154,525.255 ↓ 26.0 832 1

Merge Join (cost=709,022.90..2,480,536.87 rows=32 width=360) (actual time=1,121,379.591..1,154,525.255 rows=832 loops=1)

  • Merge Cond: (up.id = rp.id)
17. 3,516.100 43,371.203 ↑ 14.7 2,050,831 1

Merge Join (cost=709,022.81..2,405,044.31 rows=30,196,796 width=352) (actual time=7,114.526..43,371.203 rows=2,050,831 loops=1)

  • Merge Cond: (upd.utilization_procedure_id = up.id)
18. 32,294.541 32,294.541 ↑ 1.1 26,591,659 1

Index Scan using ix_utilization_procedure_details_proc_id on utilization_procedure_details upd (cost=0.44..1,166,048.38 rows=30,196,796 width=130) (actual time=0.009..32,294.541 rows=26,591,659 loops=1)

19. 224.109 7,560.562 ↓ 2.2 2,050,831 1

Materialize (cost=708,214.02..712,891.41 rows=935,478 width=222) (actual time=7,072.427..7,560.562 rows=2,050,831 loops=1)

20. 1,193.624 7,336.453 ↑ 1.1 824,940 1

Sort (cost=708,214.02..710,552.71 rows=935,478 width=222) (actual time=7,072.424..7,336.453 rows=824,940 loops=1)

  • Sort Key: up.id
  • Sort Method: external merge Disk: 193,792kB
21. 253.277 6,142.829 ↑ 1.0 935,448 1

Hash Join (cost=343,241.30..558,794.37 rows=935,478 width=222) (actual time=3,520.543..6,142.829 rows=935,448 loops=1)

  • Hash Cond: (up.member_type_id = mt.id)
22. 245.519 5,889.547 ↑ 1.0 935,448 1

Hash Join (cost=343,240.21..553,735.85 rows=935,478 width=219) (actual time=3,520.533..5,889.547 rows=935,448 loops=1)

  • Hash Cond: (up.member_category_id = mc.id)
23. 247.620 5,644.024 ↑ 1.0 935,448 1

Hash Join (cost=343,239.16..546,075.58 rows=935,478 width=215) (actual time=3,520.525..5,644.024 rows=935,448 loops=1)

  • Hash Cond: (up.cost_type_id = costs.id)
24. 246.311 5,396.399 ↑ 1.0 935,448 1

Hash Join (cost=343,238.05..541,537.40 rows=935,478 width=211) (actual time=3,520.512..5,396.399 rows=935,448 loops=1)

  • Hash Cond: (up.claim_type_id = claims.id)
25. 410.722 5,150.085 ↑ 1.0 935,448 1

Merge Join (cost=343,237.00..533,877.12 rows=935,478 width=202) (actual time=3,520.503..5,150.085 rows=935,448 loops=1)

  • Merge Cond: (pl.id = up.provider_primary_location_id)
26. 1,040.698 1,040.698 ↑ 2.5 956,713 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.008..1,040.698 rows=956,713 loops=1)

27. 1,156.259 3,698.665 ↑ 1.0 935,448 1

Sort (cost=343,172.28..345,510.97 rows=935,478 width=143) (actual time=3,519.461..3,698.665 rows=935,448 loops=1)

  • Sort Key: up.provider_primary_location_id
  • Sort Method: external sort Disk: 126,904kB
28. 536.644 2,542.406 ↑ 1.0 935,448 1

Hash Join (cost=162,335.21..250,394.64 rows=935,478 width=143) (actual time=1,697.200..2,542.406 rows=935,448 loops=1)

  • Hash Cond: (up.provider_id = p.id)
29. 311.310 311.310 ↑ 1.0 935,448 1

Seq Scan on utilization_procedures_non_par up (cost=0.00..42,282.78 rows=935,478 width=65) (actual time=0.020..311.310 rows=935,448 loops=1)

30. 475.154 1,694.452 ↓ 1.0 1,577,736 1

Hash (cost=121,379.54..121,379.54 rows=1,564,854 width=82) (actual time=1,694.452..1,694.452 rows=1,577,736 loops=1)

  • Buckets: 2,097,152 Batches: 2 Memory Usage: 80,338kB
31. 1,219.298 1,219.298 ↓ 1.0 1,577,736 1

Seq Scan on providers p (cost=0.00..121,379.54 rows=1,564,854 width=82) (actual time=0.039..1,219.298 rows=1,577,736 loops=1)

32. 0.000 0.003 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.003 0.003 ↑ 1.0 2 1

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

34. 0.001 0.005 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
35. 0.004 0.004 ↑ 1.0 5 1

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

36. 0.001 0.004 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
37. 0.003 0.003 ↑ 1.0 2 1

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

38. 0.001 0.005 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.004 0.004 ↑ 1.0 4 1

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

40. 0.145 1,110,974.700 ↓ 832.0 832 1

Materialize (cost=0.09..0.09 rows=1 width=20) (actual time=1,110,974.454..1,110,974.700 rows=832 loops=1)

41. 0.276 1,110,974.555 ↓ 440.0 440 1

Sort (cost=0.09..0.09 rows=1 width=20) (actual time=1,110,974.447..1,110,974.555 rows=440 loops=1)

  • Sort Key: rp.id
  • Sort Method: quicksort Memory: 59kB
42. 1,110,974.279 1,110,974.279 ↓ 440.0 440 1

CTE Scan on ranked_providers rp (cost=0.00..0.08 rows=1 width=20) (actual time=1,022,830.296..1,110,974.279 rows=440 loops=1)

  • Filter: (("row" <= 2) AND (provider_id = 700,366))
  • Rows Removed by Filter: 59,109,865
43. 0.005 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
44. 0.007 0.007 ↑ 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.007 rows=19 loops=1)

Planning time : 4.147 ms
Execution time : 1,157,171.406 ms