explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fNjJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.738 626,327.500 ↓ 52.5 525 1

Sort (cost=3,338,843.13..3,338,843.16 rows=10 width=298) (actual time=626,327.453..626,327.500 rows=525 loops=1)

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

CTE geo

3. 0.106 15.039 ↓ 52.5 105 1

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

4. 0.246 14.933 ↓ 373.5 747 1

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

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

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

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

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

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

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

8. 0.004 0.063 ↑ 1.0 1 1

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

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

Nested Loop (cost=0.29..4.71 rows=1 width=3) (actual time=0.050..0.059 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.029 0.029 ↑ 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.029..0.029 rows=1 loops=1)

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

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

12. 0.266 1.346 ↓ 1.0 1,506 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 97kB
13. 1.080 1.080 ↓ 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=0.040..1.080 rows=1,506 loops=1)

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

CTE valid_provider_types

15. 0.010 0.546 ↑ 1.0 5 1

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

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

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

17. 0.475 0.475 ↑ 1.0 4 1

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

  • Filter: use_for_comparisons
  • Rows Removed by Filter: 4
18. 0.006 0.060 ↑ 1.0 1 1

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

19. 0.054 0.054 ↑ 1.0 2 1

Seq Scan on provider_types provider_types_1 (cost=0.00..1.13 rows=2 width=4) (actual time=0.052..0.054 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.084 15.778 ↓ 52.5 525 1

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

22. 15.064 15.064 ↓ 52.5 105 1

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

23. 0.630 0.630 ↑ 1.0 5 105

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

24.          

CTE matching_results

25. 226.525 611,002.688 ↓ 40.3 403 1

GroupAggregate (cost=3,153,139.64..3,176,135.82 rows=10 width=274) (actual time=610,675.918..611,002.688 rows=403 loops=1)

  • Group Key: valid_combos_1.location, valid_combos_1.provider_type
26. 812.276 610,776.163 ↑ 1.3 914,426 1

Sort (cost=3,153,139.64..3,156,014.15 rows=1,149,804 width=258) (actual time=610,675.832..610,776.163 rows=914,426 loops=1)

  • Sort Key: valid_combos_1.location, valid_combos_1.provider_type
  • Sort Method: quicksort Memory: 96,016kB
27. 559,213.067 609,963.887 ↑ 1.3 914,426 1

Hash Join (cost=629,337.54..2,956,548.87 rows=1,149,804 width=258) (actual time=20,595.778..609,963.887 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,941.684 50,716.219 ↓ 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,105.755..50,716.219 rows=48,060,034 loops=1)

  • Hash Cond: (plsp.provider_location_id = pl.id)
29. 10,675.596 10,675.596 ↓ 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.007..10,675.596 rows=48,060,034 loops=1)

30. 832.798 20,098.939 ↓ 1.0 3,220,972 1

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

  • Buckets: 2,097,152 Batches: 4 Memory Usage: 94,250kB
31. 19,266.141 19,266.141 ↓ 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,649.527..19,266.141 rows=3,220,972 loops=1)

32. 4.418 34.601 ↓ 169.7 23,415 1

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

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

Nested Loop (cost=0.28..86.45 rows=138 width=258) (actual time=0.109..30.183 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.087 0.087 ↓ 52.5 525 1

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

35. 10.396 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.104 0.104 ↑ 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.023..0.104 rows=282 loops=1)

  • Index Cond: (specialty_mapping_id = 73)
37.          

CTE matches_in_base_network

38. 2.278 3,524.634 ↓ 34.5 8,775 1

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

39. 17.504 3,522.356 ↓ 46.9 11,907 1

Sort (cost=23,051.21..23,051.85 rows=254 width=254) (actual time=3,521.268..3,522.356 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. 687.114 3,504.852 ↓ 46.9 11,907 1

Nested Loop (cost=89.17..23,041.07 rows=254 width=254) (actual time=925.088..3,504.852 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. 193.050 1,567.503 ↓ 240.2 1,250,235 1

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

  • Hash Cond: (plsp_1.specialty_id = pts_1.specialty_id)
42. 1,339.109 1,339.109 ↓ 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.321..1,339.109 rows=15,444 loops=1)

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

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

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

Nested Loop (cost=0.28..86.45 rows=138 width=258) (actual time=1.443..31.158 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.064 0.064 ↓ 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.064 rows=525 loops=1)

46. 10.112 11.550 ↑ 1.0 282 525

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

47. 1.438 1.438 ↑ 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.767..1.438 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.424 11,763.371 ↓ 3.2 5,718 1

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

51. 10.815 11,761.947 ↓ 4.3 7,519 1

Sort (cost=138,391.39..138,395.80 rows=1,763 width=254) (actual time=11,761.316..11,761.947 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,429.210 11,751.132 ↓ 4.3 7,519 1

Nested Loop (cost=89.17..138,296.33 rows=1,763 width=254) (actual time=866.125..11,751.132 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. 898.953 2,559.417 ↓ 159.8 5,762,505 1

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

  • Hash Cond: (plsp_2.specialty_id = pts_2.specialty_id)
54. 1,625.992 1,625.992 ↑ 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.086..1,625.992 rows=62,512 loops=1)

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

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

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

Nested Loop (cost=0.28..86.45 rows=138 width=258) (actual time=0.107..30.093 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.088 0.088 ↓ 52.5 525 1

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

58. 10.401 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.099 0.099 ↑ 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.018..0.099 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.467 15,306.917 ↑ 4.5 389 1

GroupAggregate (cost=260.78..304.86 rows=1,763 width=274) (actual time=15,302.296..15,306.917 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.389 15,303.450 ↓ 7.8 13,720 1

Sort (cost=260.78..265.19 rows=1,763 width=258) (actual time=15,302.268..15,303.450 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.422 15,299.061 ↓ 7.8 13,720 1

Merge Full Join (cost=145.54..165.72 rows=1,763 width=258) (actual time=15,294.503..15,299.061 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.781 3,529.291 ↓ 34.5 8,775 1

Sort (cost=15.23..15.86 rows=254 width=254) (actual time=3,528.545..3,529.291 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,526.510 3,526.510 ↓ 34.5 8,775 1

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

67. 1.736 11,766.348 ↓ 3.2 5,718 1

Sort (cost=130.32..134.73 rows=1,763 width=254) (actual time=11,765.939..11,766.348 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,764.612 11,764.612 ↓ 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,761.325..11,764.612 rows=5,718 loops=1)

69. 0.234 626,326.762 ↓ 52.5 525 1

Hash Left Join (cost=0.70..49.28 rows=10 width=298) (actual time=626,321.561..626,326.762 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.240 15,323.399 ↓ 52.5 525 1

Hash Right Join (cost=0.35..48.84 rows=10 width=274) (actual time=15,318.402..15,323.399 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,307.078 15,307.078 ↑ 4.5 389 1

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

72. 0.117 16.081 ↓ 52.5 525 1

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

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

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

74. 0.176 611,003.129 ↓ 40.3 403 1

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

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

CTE Scan on matching_results (cost=0.00..0.20 rows=10 width=274) (actual time=610,675.928..611,002.953 rows=403 loops=1)

Planning time : 20.689 ms
Execution time : 626,336.778 ms