explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cJMk : create temp table

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 35,046.952 ↓ 3,843.0 3,843 1

GroupAggregate (cost=14,026.86..14,026.89 rows=1 width=267) (actual time=35,042.144..35,046.952 rows=3,843 loops=1)

  • Group Key: up.id
2.          

CTE specialty_substitutions

3. 0.022 4.530 ↑ 10.1 46 1

HashAggregate (cost=548.86..553.52 rows=466 width=17) (actual time=4.521..4.530 rows=46 loops=1)

  • Group Key: usd.procedure_code, usd.member_category_id, fs.id, ts.id
4. 2.776 4.508 ↑ 10.1 46 1

Nested Loop (cost=12.45..544.20 rows=466 width=17) (actual time=0.578..4.508 rows=46 loops=1)

  • Join Filter: ((ts.description)::text = ANY ((usd.to_value)::text[]))
  • Rows Removed by Join Filter: 19,274
5. 0.052 0.052 ↑ 1.0 420 1

Seq Scan on specialties ts (cost=0.00..7.20 rows=420 width=25) (actual time=0.015..0.052 rows=420 loops=1)

6. 1.219 1.680 ↑ 1.0 46 420

Materialize (cost=12.45..43.62 rows=47 width=63) (actual time=0.000..0.004 rows=46 loops=420)

7. 0.030 0.461 ↑ 1.0 46 1

Hash Join (cost=12.45..43.38 rows=47 width=63) (actual time=0.163..0.461 rows=46 loops=1)

  • Hash Cond: ((usd.from_value)::text = (fs.description)::text)
8. 0.302 0.302 ↑ 1.0 46 1

Seq Scan on utilization_substitution_details usd (cost=0.00..30.23 rows=47 width=78) (actual time=0.018..0.302 rows=46 loops=1)

  • Filter: (((substitution_type)::text = 'specialty'::text) AND (utilization_substitution_id = 9))
  • Rows Removed by Filter: 818
9. 0.075 0.129 ↑ 1.0 420 1

Hash (cost=7.20..7.20 rows=420 width=25) (actual time=0.128..0.129 rows=420 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
10. 0.054 0.054 ↑ 1.0 420 1

Seq Scan on specialties fs (cost=0.00..7.20 rows=420 width=25) (actual time=0.002..0.054 rows=420 loops=1)

11.          

CTE pos_substitutions

12. 0.187 2.598 ↓ 1.1 358 1

HashAggregate (cost=209.53..212.85 rows=332 width=13) (actual time=2.544..2.598 rows=358 loops=1)

  • Group Key: usd_1.procedure_code, fs_1.id, ts_1.id
13. 0.155 2.411 ↓ 1.1 358 1

Hash Join (cost=1.43..207.04 rows=332 width=13) (actual time=0.746..2.411 rows=358 loops=1)

  • Hash Cond: ((usd_1.from_value)::text = (fs_1.description)::text)
14. 1.005 2.235 ↓ 1.1 358 1

Nested Loop (cost=0.00..201.04 rows=332 width=28) (actual time=0.657..2.235 rows=358 loops=1)

  • Join Filter: ((ts_1.description)::text = ANY ((usd_1.to_value)::text[]))
  • Rows Removed by Join Filter: 6,444
15. 0.156 0.156 ↓ 1.0 358 1

Seq Scan on utilization_substitution_details usd_1 (cost=0.00..30.23 rows=357 width=74) (actual time=0.031..0.156 rows=358 loops=1)

  • Filter: (((substitution_type)::text = 'place_of_service'::text) AND (utilization_substitution_id = 9))
  • Rows Removed by Filter: 506
16. 0.487 1.074 ↑ 1.0 19 358

Materialize (cost=0.00..1.28 rows=19 width=19) (actual time=0.002..0.003 rows=19 loops=358)

17. 0.587 0.587 ↑ 1.0 19 1

Seq Scan on utilization_place_of_services ts_1 (cost=0.00..1.19 rows=19 width=19) (actual time=0.585..0.587 rows=19 loops=1)

18. 0.015 0.021 ↑ 1.0 19 1

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

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

Seq Scan on utilization_place_of_services fs_1 (cost=0.00..1.19 rows=19 width=19) (actual time=0.004..0.006 rows=19 loops=1)

20.          

Initplan (for GroupAggregate)

21. 0.187 16.953 ↑ 1.0 1 1

Aggregate (cost=13,227.58..13,227.59 rows=1 width=32) (actual time=16.952..16.953 rows=1 loops=1)

22.          

CTE expanded_provider_specialties

23. 0.458 16.268 ↓ 1.1 688 1

GroupAggregate (cost=12,057.58..12,072.21 rows=650 width=36) (actual time=15.765..16.268 rows=688 loops=1)

  • Group Key: upi.id
24. 0.247 15.810 ↓ 1.1 688 1

Sort (cost=12,057.58..12,059.21 rows=650 width=33) (actual time=15.750..15.810 rows=688 loops=1)

  • Sort Key: upi.id
  • Sort Method: quicksort Memory: 78kB
25. 4.725 15.563 ↓ 1.1 688 1

Nested Loop Left Join (cost=0.43..12,027.21 rows=650 width=33) (actual time=6.052..15.563 rows=688 loops=1)

  • Join Filter: (((upi.member_category_id = ss_1.member_category_id) OR (ss_1.member_category_id IS NULL)) AND (((upi.procedure_code)::text = (ss_1.procedure_code)::text) OR (ss_1.procedure_code IS NULL)) AND (ss_1.from_value = ANY (upi.specialty_ids)))
  • Rows Removed by Join Filter: 31,648
26. 3.270 3.270 ↓ 1.1 688 1

Index Scan using ix_utilization_procedures_provider on utilization_procedures upi (cost=0.43..663.80 rows=650 width=38) (actual time=1.500..3.270 rows=688 loops=1)

  • Index Cond: (provider_id = 700,366)
27. 7.568 7.568 ↑ 10.1 46 688

CTE Scan on specialty_substitutions ss_1 (cost=0.00..9.32 rows=466 width=44) (actual time=0.007..0.011 rows=46 loops=688)

28. 0.302 16.766 ↑ 47.2 1,376 1

ProjectSet (cost=0.00..342.88 rows=65,000 width=4) (actual time=15.770..16.766 rows=1,376 loops=1)

29. 16.464 16.464 ↓ 1.1 688 1

CTE Scan on expanded_provider_specialties (cost=0.00..13.00 rows=650 width=32) (actual time=15.767..16.464 rows=688 loops=1)

30. 2.289 35,042.480 ↓ 3,843.0 3,843 1

Sort (cost=32.90..32.91 rows=1 width=211) (actual time=35,042.091..35,042.480 rows=3,843 loops=1)

  • Sort Key: up.id
  • Sort Method: quicksort Memory: 1,117kB
31. 27.142 35,040.191 ↓ 3,843.0 3,843 1

Nested Loop Left Join (cost=2.85..32.89 rows=1 width=211) (actual time=34,993.045..35,040.191 rows=3,843 loops=1)

  • Join Filter: (((up.member_category_id = ss.member_category_id) OR (ss.member_category_id IS NULL)) AND (((up.procedure_code)::text = (ss.procedure_code)::text) OR (ss.procedure_code IS NULL)) AND (ss.from_value = ANY (up.specialty_ids)))
  • Rows Removed by Join Filter: 176,778
32. 7.314 34,997.677 ↓ 3,843.0 3,843 1

Hash Right Join (cost=2.85..10.76 rows=1 width=207) (actual time=34,992.991..34,997.677 rows=3,843 loops=1)

  • Hash Cond: (ps.from_value = up.place_of_service_id)
  • Join Filter: (((up.procedure_code)::text = (ps.procedure_code)::text) OR (ps.procedure_code IS NULL))
  • Rows Removed by Join Filter: 63,540
33. 2.721 2.721 ↓ 1.1 358 1

CTE Scan on pos_substitutions ps (cost=0.00..6.64 rows=332 width=40) (actual time=2.559..2.721 rows=358 loops=1)

34. 1.863 34,987.642 ↓ 3,843.0 3,843 1

Hash (cost=2.83..2.83 rows=1 width=203) (actual time=34,987.642..34,987.642 rows=3,843 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 938kB
35. 34,985.779 34,985.779 ↓ 3,843.0 3,843 1

Index Scan using ix_utilization_procedures_provider_geo on utilization_procedures up (cost=0.42..2.83 rows=1 width=203) (actual time=4,322.780..34,985.779 rows=3,843 loops=1)

  • Index Cond: (provider_geography_point && '0101000020E61000003F1D8F19A84853C04209336DFF824340'::geography)
  • Filter: ((provider_id <> 700366) AND (specialty_ids && $3) AND (cost_type_id = 3) AND (68870 <> ALL (base_network_ids)) AND ('0101000020E61000003F1D8F19A84853C04209336DFF824340'::geography && _st_expand(provider_geography_point, '24140.16'::double precision)) AND _st_dwithin(provider_geography_point, '0101000020E61000003F1D8F19A84853C04209336DFF824340'::geography, '24140.16'::double precision, true))
  • Rows Removed by Filter: 4,274,729
36. 15.372 15.372 ↑ 10.1 46 3,843

CTE Scan on specialty_substitutions ss (cost=0.00..9.32 rows=466 width=44) (actual time=0.000..0.004 rows=46 loops=3,843)

Planning time : 12.873 ms
Execution time : 35,053.624 ms