explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kUWp : joining on ids

Settings
# exclusive inclusive rows x rows loops node
1. 0.741 624,909.505 ↓ 52.5 525 1

Sort (cost=3,338,843.13..3,338,843.16 rows=10 width=298) (actual time=624,909.468..624,909.505 rows=525 loops=1)

  • Sort Key: valid_combos.location, valid_combos.provider_type
  • Sort Method: quicksort Memory: 98kB
2.          

CTE geo

3. 0.099 24.459 ↓ 52.5 105 1

Unique (cost=887.55..887.57 rows=2 width=14) (actual time=24.306..24.459 rows=105 loops=1)

4. 0.258 24.360 ↓ 373.5 747 1

Sort (cost=887.55..887.56 rows=2 width=14) (actual time=24.306..24.360 rows=747 loops=1)

  • Sort Key: geo_county.id, geo_county.name
  • Sort Method: quicksort Memory: 83kB
5. 1.434 24.102 ↓ 373.5 747 1

Hash Join (cost=246.79..887.54 rows=2 width=14) (actual time=19.188..24.102 rows=747 loops=1)

  • Hash Cond: (format_county((uz.county)::text, (uz.state_abbreviation)::text, true) = (geo_county.name)::text)
6. 5.069 12.438 ↓ 20.2 747 1

Hash Join (cost=4.72..640.69 rows=37 width=230) (actual time=8.655..12.438 rows=747 loops=1)

  • Hash Cond: ((uz.state_abbreviation)::text = (st.abbreviation)::text)
7. 6.799 6.799 ↓ 5.5 41,069 1

Seq Scan on us_zips uz (cost=0.00..607.62 rows=7,462 width=230) (actual time=0.015..6.799 rows=41,069 loops=1)

8. 0.004 0.570 ↑ 1.0 1 1

Hash (cost=4.71..4.71 rows=1 width=3) (actual time=0.570..0.570 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.009 0.566 ↑ 1.0 1 1

Nested Loop (cost=0.29..4.71 rows=1 width=3) (actual time=0.557..0.566 rows=1 loops=1)

  • Join Filter: (((st.name)::text = (geo_state.name)::text) OR ((st.abbreviation)::text = (geo_state.name)::text))
  • Rows Removed by Join Filter: 55
10. 0.537 0.537 ↑ 1.0 1 1

Index Scan using geolocations_pkey on geolocations geo_state (cost=0.29..2.31 rows=1 width=10) (actual time=0.536..0.537 rows=1 loops=1)

  • Index Cond: (id = 62,918)
  • Filter: ((location_type)::text = ANY ('{state_name,state_abbreviation}'::text[]))
11. 0.020 0.020 ↑ 1.0 56 1

Seq Scan on states st (cost=0.00..1.56 rows=56 width=13) (actual time=0.016..0.020 rows=56 loops=1)

12. 0.283 10.230 ↓ 1.0 1,506 1

Hash (cost=223.33..223.33 rows=1,499 width=14) (actual time=10.230..10.230 rows=1,506 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 97kB
13. 9.947 9.947 ↓ 1.0 1,506 1

Index Scan using ix_geolocations_location_type_lower_name_id on geolocations geo_county (cost=0.41..223.33 rows=1,499 width=14) (actual time=3.599..9.947 rows=1,506 loops=1)

  • Index Cond: ((location_type)::text = 'county'::text)
14.          

CTE valid_provider_types

15. 0.012 0.093 ↑ 1.0 5 1

HashAggregate (cost=2.33..2.38 rows=5 width=64) (actual time=0.092..0.093 rows=5 loops=1)

  • Group Key: (ARRAY[provider_types.id]), provider_types.description
16. 0.002 0.081 ↑ 1.0 5 1

Append (cost=0.00..2.30 rows=5 width=64) (actual time=0.013..0.081 rows=5 loops=1)

17. 0.014 0.014 ↑ 1.0 4 1

Seq Scan on provider_types (cost=0.00..1.08 rows=4 width=45) (actual time=0.012..0.014 rows=4 loops=1)

  • Filter: use_for_comparisons
  • Rows Removed by Filter: 4
18. 0.008 0.065 ↑ 1.0 1 1

Aggregate (cost=1.14..1.15 rows=1 width=64) (actual time=0.065..0.065 rows=1 loops=1)

19. 0.057 0.057 ↑ 1.0 2 1

Seq Scan on provider_types provider_types_1 (cost=0.00..1.13 rows=2 width=4) (actual time=0.055..0.057 rows=2 loops=1)

  • Filter: (use_for_comparisons AND (lower((description)::text) = ANY ('{"primary care","general dentist",specialist}'::text[])))
  • Rows Removed by Filter: 6
20.          

CTE valid_combos

21. 0.053 24.742 ↓ 52.5 525 1

Nested Loop (cost=0.00..0.29 rows=10 width=286) (actual time=24.402..24.742 rows=525 loops=1)

22. 24.479 24.479 ↓ 52.5 105 1

CTE Scan on geo (cost=0.00..0.04 rows=2 width=222) (actual time=24.308..24.479 rows=105 loops=1)

23. 0.210 0.210 ↑ 1.0 5 105

CTE Scan on valid_provider_types (cost=0.00..0.10 rows=5 width=64) (actual time=0.001..0.002 rows=5 loops=105)

24.          

CTE matching_results

25. 222.550 609,383.347 ↓ 40.3 403 1

GroupAggregate (cost=3,153,139.64..3,176,135.82 rows=10 width=274) (actual time=609,081.142..609,383.347 rows=403 loops=1)

  • Group Key: valid_combos_1.location, valid_combos_1.provider_type
26. 560.434 609,160.797 ↑ 1.3 914,426 1

Sort (cost=3,153,139.64..3,156,014.15 rows=1,149,804 width=258) (actual time=609,081.018..609,160.797 rows=914,426 loops=1)

  • Sort Key: valid_combos_1.location, valid_combos_1.provider_type
  • Sort Method: quicksort Memory: 96,016kB
27. 557,839.788 608,600.363 ↑ 1.3 914,426 1

Hash Join (cost=629,337.54..2,956,548.87 rows=1,149,804 width=258) (actual time=20,903.276..608,600.363 rows=914,426 loops=1)

  • Hash Cond: (plsp.specialty_id = pts.specialty_id)
  • Join Filter: (valid_combos_1.geo_id = ANY (pl.geolocation_ids))
  • Rows Removed by Join Filter: 4,132,618,789
28. 19,719.893 50,725.906 ↓ 1.0 48,060,034 1

Hash Join (cost=629,249.36..2,187,004.86 rows=48,059,036 width=72) (actual time=20,399.727..50,725.906 rows=48,060,034 loops=1)

  • Hash Cond: (plsp.provider_location_id = pl.id)
29. 10,613.148 10,613.148 ↓ 1.0 48,060,034 1

Seq Scan on provider_location_specialty_plans plsp (cost=0.00..928,221.36 rows=48,059,036 width=16) (actual time=0.010..10,613.148 rows=48,060,034 loops=1)

30. 833.299 20,392.865 ↓ 1.0 3,220,972 1

Hash (cost=555,575.94..555,575.94 rows=3,169,794 width=64) (actual time=20,392.865..20,392.865 rows=3,220,972 loops=1)

  • Buckets: 2,097,152 Batches: 4 Memory Usage: 94,250kB
31. 19,559.566 19,559.566 ↓ 1.0 3,220,972 1

Seq Scan on provider_locations pl (cost=0.00..555,575.94 rows=3,169,794 width=64) (actual time=1,611.010..19,559.566 rows=3,220,972 loops=1)

32. 4.380 34.669 ↓ 169.7 23,415 1

Hash (cost=86.45..86.45 rows=138 width=258) (actual time=34.669..34.669 rows=23,415 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,881kB
33. 19.650 30.289 ↓ 169.7 23,415 1

Nested Loop (cost=0.28..86.45 rows=138 width=258) (actual time=0.133..30.289 rows=23,415 loops=1)

  • Join Filter: (pts.provider_type_id = ANY (valid_combos_1.provider_type_ids))
  • Rows Removed by Join Filter: 124,635
34. 0.139 0.139 ↓ 52.5 525 1

CTE Scan on valid_combos valid_combos_1 (cost=0.00..0.20 rows=10 width=286) (actual time=0.011..0.139 rows=525 loops=1)

35. 10.382 10.500 ↑ 1.0 282 525

Materialize (cost=0.28..16.46 rows=282 width=8) (actual time=0.000..0.020 rows=282 loops=525)

36. 0.118 0.118 ↑ 1.0 282 1

Index Scan using ix_pts_spec_mapping_provider_type on provider_type_specialties pts (cost=0.28..15.05 rows=282 width=8) (actual time=0.028..0.118 rows=282 loops=1)

  • Index Cond: (specialty_mapping_id = 73)
37.          

CTE matches_in_base_network

38. 2.243 3,776.734 ↓ 34.5 8,775 1

Unique (cost=23,051.21..23,053.75 rows=254 width=254) (actual time=3,773.475..3,776.734 rows=8,775 loops=1)

39. 17.746 3,774.491 ↓ 46.9 11,907 1

Sort (cost=23,051.21..23,051.85 rows=254 width=254) (actual time=3,773.473..3,774.491 rows=11,907 loops=1)

  • Sort Key: valid_combos_2.location, valid_combos_2.provider_type, plsp_1.provider_id
  • Sort Method: quicksort Memory: 1,315kB
40. 822.759 3,756.745 ↓ 46.9 11,907 1

Nested Loop (cost=89.17..23,041.07 rows=254 width=254) (actual time=1,012.971..3,756.745 rows=11,907 loops=1)

  • Join Filter: (valid_combos_2.geo_id = ANY (pl_1.geolocation_ids))
  • Rows Removed by Join Filter: 1,238,328
41. 199.074 1,683.751 ↓ 240.2 1,250,235 1

Hash Join (cost=88.74..10,917.31 rows=5,205 width=262) (actual time=1,011.658..1,683.751 rows=1,250,235 loops=1)

  • Hash Cond: (plsp_1.specialty_id = pts_1.specialty_id)
42. 1,448.264 1,448.264 ↓ 1.5 15,444 1

Index Scan using ix_provider_location_specialty_plans_planlocation on provider_location_specialty_plans plsp_1 (cost=0.56..10,723.91 rows=10,636 width=12) (actual time=2.391..1,448.264 rows=15,444 loops=1)

  • Index Cond: (plan_id = 51,917)
43. 4.585 36.413 ↓ 169.7 23,415 1

Hash (cost=86.45..86.45 rows=138 width=258) (actual time=36.412..36.413 rows=23,415 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,881kB
44. 19.667 31.828 ↓ 169.7 23,415 1

Nested Loop (cost=0.28..86.45 rows=138 width=258) (actual time=1.980..31.828 rows=23,415 loops=1)

  • Join Filter: (pts_1.provider_type_id = ANY (valid_combos_2.provider_type_ids))
  • Rows Removed by Join Filter: 124,635
45. 0.086 0.086 ↓ 52.5 525 1

CTE Scan on valid_combos valid_combos_2 (cost=0.00..0.20 rows=10 width=286) (actual time=0.000..0.086 rows=525 loops=1)

46. 10.104 12.075 ↑ 1.0 282 525

Materialize (cost=0.28..16.46 rows=282 width=8) (actual time=0.002..0.023 rows=282 loops=525)

47. 1.971 1.971 ↑ 1.0 282 1

Index Scan using ix_pts_spec_mapping_provider_type on provider_type_specialties pts_1 (cost=0.28..15.05 rows=282 width=8) (actual time=0.904..1.971 rows=282 loops=1)

  • Index Cond: (specialty_mapping_id = 73)
48. 1,250.235 1,250.235 ↑ 1.0 1 1,250,235

Index Scan using provider_locations_pkey on provider_locations pl_1 (cost=0.43..2.31 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1,250,235)

  • Index Cond: (id = plsp_1.provider_location_id)
49.          

CTE matches_in_comparison_networks

50. 1.443 11,703.328 ↓ 3.2 5,718 1

Unique (cost=138,391.39..138,409.02 rows=1,763 width=254) (actual time=11,701.259..11,703.328 rows=5,718 loops=1)

51. 10.715 11,701.885 ↓ 4.3 7,519 1

Sort (cost=138,391.39..138,395.80 rows=1,763 width=254) (actual time=11,701.254..11,701.885 rows=7,519 loops=1)

  • Sort Key: valid_combos_3.location, valid_combos_3.provider_type, plsp_2.provider_id
  • Sort Method: quicksort Memory: 780kB
52. 3,370.604 11,691.170 ↓ 4.3 7,519 1

Nested Loop (cost=89.17..138,296.33 rows=1,763 width=254) (actual time=888.266..11,691.170 rows=7,519 loops=1)

  • Join Filter: (valid_combos_3.geo_id = ANY (pl_2.geolocation_ids))
  • Rows Removed by Join Filter: 5,754,986
53. 876.613 2,558.061 ↓ 159.8 5,762,505 1

Hash Join (cost=88.74..70,365.84 rows=36,062 width=262) (actual time=888.076..2,558.061 rows=5,762,505 loops=1)

  • Hash Cond: (plsp_2.specialty_id = pts_2.specialty_id)
54. 1,646.890 1,646.890 ↑ 1.2 62,512 1

Index Scan using ix_provider_location_specialty_plans_planlocation on provider_location_specialty_plans plsp_2 (cost=0.56..69,548.59 rows=73,691 width=12) (actual time=1.504..1,646.890 rows=62,512 loops=1)

  • Index Cond: (plan_id = 3,888)
55. 4.460 34.558 ↓ 169.7 23,415 1

Hash (cost=86.45..86.45 rows=138 width=258) (actual time=34.558..34.558 rows=23,415 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,881kB
56. 19.512 30.098 ↓ 169.7 23,415 1

Nested Loop (cost=0.28..86.45 rows=138 width=258) (actual time=0.122..30.098 rows=23,415 loops=1)

  • Join Filter: (pts_2.provider_type_id = ANY (valid_combos_3.provider_type_ids))
  • Rows Removed by Join Filter: 124,635
57. 0.086 0.086 ↓ 52.5 525 1

CTE Scan on valid_combos valid_combos_3 (cost=0.00..0.20 rows=10 width=286) (actual time=0.006..0.086 rows=525 loops=1)

58. 10.390 10.500 ↑ 1.0 282 525

Materialize (cost=0.28..16.46 rows=282 width=8) (actual time=0.000..0.020 rows=282 loops=525)

59. 0.110 0.110 ↑ 1.0 282 1

Index Scan using ix_pts_spec_mapping_provider_type on provider_type_specialties pts_2 (cost=0.28..15.05 rows=282 width=8) (actual time=0.023..0.110 rows=282 loops=1)

  • Index Cond: (specialty_mapping_id = 73)
60. 5,762.505 5,762.505 ↑ 1.0 1 5,762,505

Index Scan using provider_locations_pkey on provider_locations pl_2 (cost=0.43..1.86 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=5,762,505)

  • Index Cond: (id = plsp_2.provider_location_id)
61.          

CTE matching_overlaps

62. 3.511 15,499.184 ↑ 4.5 389 1

GroupAggregate (cost=260.78..304.86 rows=1,763 width=274) (actual time=15,494.577..15,499.184 rows=389 loops=1)

  • Group Key: (COALESCE(matches_in_base_network.location, matches_in_comparison_networks.location)), (COALESCE(matches_in_base_network.provider_type, matches_in_comparison_networks.provider_type))
63. 4.424 15,495.673 ↓ 7.8 13,720 1

Sort (cost=260.78..265.19 rows=1,763 width=258) (actual time=15,494.517..15,495.673 rows=13,720 loops=1)

  • Sort Key: (COALESCE(matches_in_base_network.location, matches_in_comparison_networks.location)), (COALESCE(matches_in_base_network.provider_type, matches_in_comparison_networks.provider_type))
  • Sort Method: quicksort Memory: 1,456kB
64. 3.476 15,491.249 ↓ 7.8 13,720 1

Merge Full Join (cost=145.54..165.72 rows=1,763 width=258) (actual time=15,486.651..15,491.249 rows=13,720 loops=1)

  • Merge Cond: (((matches_in_base_network.location)::text = (matches_in_comparison_networks.location)::text) AND ((matches_in_base_network.provider_type)::text = (matches_in_comparison_networks.provider_type)::text) AND (matches_in_base_network.id = matches_in_comparison_networks.id))
65. 2.794 3,781.430 ↓ 34.5 8,775 1

Sort (cost=15.23..15.86 rows=254 width=254) (actual time=3,780.683..3,781.430 rows=8,775 loops=1)

  • Sort Key: matches_in_base_network.location, matches_in_base_network.provider_type, matches_in_base_network.id
  • Sort Method: quicksort Memory: 1,070kB
66. 3,778.636 3,778.636 ↓ 34.5 8,775 1

CTE Scan on matches_in_base_network (cost=0.00..5.08 rows=254 width=254) (actual time=3,773.478..3,778.636 rows=8,775 loops=1)

67. 1.771 11,706.343 ↓ 3.2 5,718 1

Sort (cost=130.32..134.73 rows=1,763 width=254) (actual time=11,705.930..11,706.343 rows=5,718 loops=1)

  • Sort Key: matches_in_comparison_networks.location, matches_in_comparison_networks.provider_type, matches_in_comparison_networks.id
  • Sort Method: quicksort Memory: 639kB
68. 11,704.572 11,704.572 ↓ 3.2 5,718 1

CTE Scan on matches_in_comparison_networks (cost=0.00..35.26 rows=1,763 width=254) (actual time=11,701.263..11,704.572 rows=5,718 loops=1)

69. 0.271 624,908.764 ↓ 52.5 525 1

Hash Left Join (cost=0.70..49.28 rows=10 width=298) (actual time=624,903.553..624,908.764 rows=525 loops=1)

  • Hash Cond: (((valid_combos.location)::text = (matching_results.location)::text) AND ((valid_combos.provider_type)::text = (matching_results.provider_type)::text))
70. 0.285 15,524.699 ↓ 52.5 525 1

Hash Right Join (cost=0.35..48.84 rows=10 width=274) (actual time=15,519.692..15,524.699 rows=525 loops=1)

  • Hash Cond: (((matching_overlaps.location)::text = (valid_combos.location)::text) AND ((matching_overlaps.provider_type)::text = (valid_combos.provider_type)::text))
71. 15,499.355 15,499.355 ↑ 4.5 389 1

CTE Scan on matching_overlaps (cost=0.00..35.26 rows=1,763 width=274) (actual time=15,494.586..15,499.355 rows=389 loops=1)

72. 0.121 25.059 ↓ 52.5 525 1

Hash (cost=0.20..0.20 rows=10 width=250) (actual time=25.059..25.059 rows=525 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 41kB
73. 24.938 24.938 ↓ 52.5 525 1

CTE Scan on valid_combos (cost=0.00..0.20 rows=10 width=250) (actual time=24.406..24.938 rows=525 loops=1)

74. 0.172 609,383.794 ↓ 40.3 403 1

Hash (cost=0.20..0.20 rows=10 width=274) (actual time=609,383.794..609,383.794 rows=403 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 42kB
75. 609,383.622 609,383.622 ↓ 40.3 403 1

CTE Scan on matching_results (cost=0.00..0.20 rows=10 width=274) (actual time=609,081.171..609,383.622 rows=403 loops=1)

Planning time : 22.182 ms
Execution time : 624,918.725 ms