explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vlXp : baseline

Settings
# exclusive inclusive rows x rows loops node
1. 78.485 322,987.017 ↑ 1.0 1 1

Aggregate (cost=73,128,185.60..73,128,185.61 rows=1 width=24) (actual time=322,987.016..322,987.017 rows=1 loops=1)

2. 157.283 322,908.532 ↑ 1.0 506,810 1

Hash Left Join (cost=73,080,551.57..73,123,117.50 rows=506,810 width=32) (actual time=322,328.216..322,908.532 rows=506,810 loops=1)

  • Hash Cond: (up.id = rp.id)
3. 152.404 423.146 ↑ 1.0 506,810 1

Hash Left Join (cost=2.81..41,238.36 rows=506,810 width=28) (actual time=0.087..423.146 rows=506,810 loops=1)

  • Hash Cond: ((up.claim_type_id = uf2.claim_type_id) AND (up.place_of_service_id = uf2.place_of_service_id))
  • Join Filter: ((uf2.member_category_id = up.member_category_id) OR (uf2.member_category_id IS NULL))
4. 168.070 270.734 ↑ 1.0 506,810 1

Hash Left Join (cost=1.44..31,701.85 rows=506,810 width=32) (actual time=0.060..270.734 rows=506,810 loops=1)

  • Hash Cond: ((up.claim_type_id = uf1.claim_type_id) AND (up.place_of_service_id = uf1.place_of_service_id) AND ((up.procedure_code)::text = (uf1.procedure_code)::text))
  • Join Filter: ((uf1.member_category_id = up.member_category_id) OR (uf1.member_category_id IS NULL))
5. 102.646 102.646 ↑ 1.0 506,810 1

Seq Scan on utilization_procedures_non_par up (cost=0.00..22,197.10 rows=506,810 width=30) (actual time=0.021..102.646 rows=506,810 loops=1)

6. 0.005 0.018 ↑ 8.0 2 1

Hash (cost=1.16..1.16 rows=16 width=26) (actual time=0.018..0.018 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.013 0.013 ↑ 1.0 16 1

Seq Scan on utilization_oon_factors uf1 (cost=0.00..1.16 rows=16 width=26) (actual time=0.009..0.013 rows=16 loops=1)

8. 0.003 0.008 ↑ 1.0 14 1

Hash (cost=1.16..1.16 rows=14 width=20) (actual time=0.008..0.008 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
9. 0.005 0.005 ↑ 1.0 14 1

Seq Scan on utilization_oon_factors uf2 (cost=0.00..1.16 rows=14 width=20) (actual time=0.002..0.005 rows=14 loops=1)

  • Filter: (procedure_code IS NULL)
  • Rows Removed by Filter: 2
10. 64.494 322,328.103 ↓ 326,589.0 326,589 1

Hash (cost=73,080,548.74..73,080,548.74 rows=1 width=12) (actual time=322,328.103..322,328.103 rows=326,589 loops=1)

  • Buckets: 524,288 (originally 1024) Batches: 1 (originally 1) Memory Usage: 18,130kB
11. 62.342 322,263.609 ↓ 326,589.0 326,589 1

Subquery Scan on rp (cost=73,080,548.42..73,080,548.74 rows=1 width=12) (actual time=316,424.897..322,263.609 rows=326,589 loops=1)

12. 148.449 322,201.267 ↓ 326,589.0 326,589 1

GroupAggregate (cost=73,080,548.42..73,080,548.73 rows=1 width=12) (actual time=316,424.896..322,201.267 rows=326,589 loops=1)

  • Group Key: t.id
13. 607.323 322,052.818 ↓ 332,066.0 332,066 1

Subquery Scan on t (cost=73,080,548.42..73,080,548.72 rows=1 width=12) (actual time=316,424.859..322,052.818 rows=332,066 loops=1)

  • Filter: (t."row" <= 1)
  • Rows Removed by Filter: 6,235,565
14. 3,988.411 321,445.495 ↓ 6,567,631.0 6,567,631 1

WindowAgg (cost=73,080,548.42..73,080,548.70 rows=1 width=32) (actual time=316,424.854..321,445.495 rows=6,567,631 loops=1)

15. 5,424.664 317,457.084 ↓ 6,567,631.0 6,567,631 1

Sort (cost=73,080,548.42..73,080,548.43 rows=1 width=28) (actual time=316,424.814..317,457.084 rows=6,567,631 loops=1)

  • Sort Key: up_1.id, ((up_1.provider_geography_point <-> urp.provider_geography_point)), urp.provider_id
  • Sort Method: external merge Disk: 295,632kB
16. 263,607.774 312,032.420 ↓ 6,567,631.0 6,567,631 1

Merge Join (cost=306,040.74..73,080,548.41 rows=1 width=28) (actual time=2,820.468..312,032.420 rows=6,567,631 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, '8046.72'::double precision)) AND (urp.provider_geography_point && _st_expand(up_1.provider_geography_point, '8046.72'::double precision)) AND _st_dwithin(up_1.provider_geography_point, urp.provider_geography_point, '8046.72'::double precision, true))
  • Rows Removed by Join Filter: 537,908,262
17. 1,078.662 2,211.931 ↓ 1.1 548,327 1

Sort (cost=235,820.63..237,064.41 rows=497,512 width=86) (actual time=2,145.997..2,211.931 rows=548,327 loops=1)

  • Sort Key: urp.place_of_service_id, urp.procedure_code, urp.claim_type_id, urp.member_category_id
  • Sort Method: quicksort Memory: 99,080kB
18. 101.190 1,133.269 ↓ 1.1 548,529 1

Subquery Scan on urp (cost=124,200.58..188,745.12 rows=497,512 width=86) (actual time=810.427..1,133.269 rows=548,529 loops=1)

19. 261.901 1,032.079 ↓ 1.1 548,529 1

Gather Merge (cost=124,200.58..183,770.00 rows=497,512 width=86) (actual time=810.424..1,032.079 rows=548,529 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
20. 249.282 770.178 ↑ 1.1 109,706 5 / 5

Sort (cost=123,200.52..123,511.47 rows=124,378 width=86) (actual time=758.191..770.178 rows=109,706 loops=5)

  • Sort Key: urp_1.member_category_id, urp_1.claim_type_id, urp_1.procedure_code
  • Sort Method: quicksort Memory: 24,668kB
  • Worker 0: Sort Method: quicksort Memory: 18,902kB
  • Worker 1: Sort Method: quicksort Memory: 15,865kB
  • Worker 2: Sort Method: quicksort Memory: 19,748kB
  • Worker 3: Sort Method: quicksort Memory: 16,400kB
21. 520.896 520.896 ↑ 1.1 109,706 5 / 5

Parallel Index Scan using ix_utilization_reprice_procedures_match on utilization_reprice_procedures urp_1 (cost=0.56..112,675.42 rows=124,378 width=86) (actual time=0.050..520.896 rows=109,706 loops=5)

  • Index Cond: (cost_type_id = 3)
  • Filter: ((provider_id IS NOT NULL) AND (68870 = ANY (base_network_ids)))
  • Rows Removed by Filter: 172,474
22. 46,035.901 46,212.715 ↓ 1,074.4 544,494,146 1

Sort (cost=70,220.10..71,487.12 rows=506,810 width=88) (actual time=672.387..46,212.715 rows=544,494,146 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: 84,161kB
23. 176.814 176.814 ↑ 1.0 506,810 1

Seq Scan on utilization_procedures_non_par up_1 (cost=0.00..22,197.10 rows=506,810 width=88) (actual time=0.019..176.814 rows=506,810 loops=1)

Planning time : 1.498 ms
Execution time : 323,018.598 ms