explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MhQb

Settings
# exclusive inclusive rows x rows loops node
1. 0.674 58,214.640 ↑ 1.0 1 1

Aggregate (cost=1,015,492.64..1,015,492.65 rows=1 width=16) (actual time=58,214.640..58,214.640 rows=1 loops=1)

2.          

CTE oon_pl

3. 14.175 694.911 ↑ 329.0 794 1

Unique (cost=148,321.87..151,587.57 rows=261,256 width=72) (actual time=678.459..694.911 rows=794 loops=1)

4. 23.084 680.736 ↑ 12.3 21,281 1

Sort (cost=148,321.87..148,975.01 rows=261,256 width=72) (actual time=678.458..680.736 rows=21,281 loops=1)

  • Sort Key: utilization_procedures.provider_id, utilization_procedures.provider_primary_location_id, utilization_procedures.specialty_ids, utilization_procedures.provider_geography_point
  • Sort Method: quicksort Memory: 3,764kB
5. 634.635 657.652 ↑ 12.3 21,281 1

Seq Scan on utilization_procedures (cost=32,160.69..124,815.22 rows=261,256 width=72) (actual time=42.125..657.652 rows=21,281 loops=1)

  • Filter: ((provider_id IS NOT NULL) AND (NOT is_passthrough) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 1,677,961
6.          

SubPlan (for Seq Scan)

7. 23.017 23.017 ↑ 1.0 81,368 1

Index Scan using ix_plan_provider_locations_plan on plan_provider_locations (cost=0.56..31,954.79 rows=82,360 width=4) (actual time=0.012..23.017 rows=81,368 loops=1)

  • Index Cond: (plan_id = 6,584)
8. 1.215 58,213.966 ↑ 2.1 747 1

Nested Loop Left Join (cost=69,750.26..863,893.11 rows=1,595 width=32) (actual time=849.828..58,213.966 rows=747 loops=1)

9. 0.956 802.066 ↑ 2.1 747 1

Hash Left Join (cost=69,254.37..72,898.08 rows=1,595 width=113) (actual time=796.394..802.066 rows=747 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.member_category_id = uf1.member_category_id) AND ((up.procedure_code)::text = (uf1.procedure_code)::text))
10. 1.425 801.084 ↑ 2.1 747 1

Hash Left Join (cost=69,250.64..72,750.79 rows=1,595 width=105) (actual time=796.333..801.084 rows=747 loops=1)

  • Hash Cond: ((up.claim_type_id = uf2.claim_type_id) AND (up.place_of_service_id = uf2.place_of_service_id) AND (up.member_category_id = uf2.member_category_id))
11. 2.647 799.613 ↑ 2.1 747 1

Merge Join (cost=69,247.16..72,629.54 rows=1,595 width=97) (actual time=796.264..799.613 rows=747 loops=1)

  • Merge Cond: (up.provider_primary_location_id = oon_pl.provider_primary_location_id)
  • Join Filter: (oon_pl.specialty_ids && up.specialty_ids)
12. 0.646 101.086 ↑ 22.0 747 1

Sort (cost=40,515.39..40,556.54 rows=16,458 width=65) (actual time=100.781..101.086 rows=747 loops=1)

  • Sort Key: up.provider_primary_location_id
  • Sort Method: quicksort Memory: 130kB
13. 68.272 100.440 ↑ 22.0 747 1

Index Scan using utilization_procedures_pkey on utilization_procedures up (cost=32,161.12..39,362.80 rows=16,458 width=65) (actual time=51.789..100.440 rows=747 loops=1)

  • Index Cond: ((id >= 1) AND (id <= 100,000))
  • Filter: ((NOT is_passthrough) AND (provider_id IS NOT NULL) AND (NOT (hashed SubPlan 3)))
  • Rows Removed by Filter: 99,253
14.          

SubPlan (for Index Scan)

15. 32.168 32.168 ↑ 1.0 81,368 1

Index Scan using ix_plan_provider_locations_plan on plan_provider_locations plan_provider_locations_1 (cost=0.56..31,954.79 rows=82,360 width=4) (actual time=0.032..32.168 rows=81,368 loops=1)

  • Index Cond: (plan_id = 6,584)
16. 0.680 695.880 ↑ 198.7 1,315 1

Sort (cost=28,731.77..29,384.91 rows=261,256 width=72) (actual time=695.470..695.880 rows=1,315 loops=1)

  • Sort Key: oon_pl.provider_primary_location_id
  • Sort Method: quicksort Memory: 136kB
17. 695.200 695.200 ↑ 329.0 794 1

CTE Scan on oon_pl (cost=0.00..5,225.12 rows=261,256 width=72) (actual time=678.462..695.200 rows=794 loops=1)

18. 0.017 0.046 ↑ 3.9 23 1

Hash (cost=1.91..1.91 rows=90 width=20) (actual time=0.046..0.046 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
19. 0.029 0.029 ↑ 1.0 90 1

Seq Scan on utilization_oon_factors uf2 (cost=0.00..1.91 rows=90 width=20) (actual time=0.010..0.029 rows=90 loops=1)

  • Filter: (procedure_code IS NULL)
  • Rows Removed by Filter: 1
20. 0.011 0.026 ↓ 0.0 0 1

Hash (cost=1.91..1.91 rows=91 width=26) (actual time=0.025..0.026 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
21. 0.015 0.015 ↑ 1.0 91 1

Seq Scan on utilization_oon_factors uf1 (cost=0.00..1.91 rows=91 width=26) (actual time=0.002..0.015 rows=91 loops=1)

22. 0.747 57,410.685 ↑ 1.0 1 747

Subquery Scan on rp (cost=495.89..495.91 rows=1 width=12) (actual time=76.855..76.855 rows=1 loops=747)

  • Filter: (up.id = rp.id)
23. 2.241 57,409.938 ↑ 1.0 1 747

Aggregate (cost=495.89..495.90 rows=1 width=12) (actual time=76.854..76.854 rows=1 loops=747)

24. 2.781 57,407.697 ↑ 1.0 1 747

Nested Loop (cost=493.83..495.88 rows=1 width=8) (actual time=76.847..76.851 rows=1 loops=747)

25. 0.747 57,392.757 ↓ 2.0 2 747

Unique (cost=493.41..493.42 rows=1 width=4) (actual time=76.830..76.831 rows=2 loops=747)

26. 2.988 57,392.010 ↓ 2.0 2 747

Sort (cost=493.41..493.41 rows=1 width=4) (actual time=76.829..76.830 rows=2 loops=747)

  • Sort Key: p.provider_id
  • Sort Method: quicksort Memory: 25kB
27. 1.494 57,389.022 ↓ 2.0 2 747

Subquery Scan on p (cost=493.38..493.40 rows=1 width=4) (actual time=76.823..76.826 rows=2 loops=747)

28. 1.494 57,387.528 ↓ 2.0 2 747

Limit (cost=493.38..493.39 rows=1 width=44) (actual time=76.822..76.824 rows=2 loops=747)

29. 26.145 57,386.034 ↓ 2.0 2 747

Sort (cost=493.38..493.39 rows=1 width=44) (actual time=76.822..76.822 rows=2 loops=747)

  • Sort Key: ((oon_pl.provider_geography_point <-> ppl.geography_point))
  • Sort Method: top-N heapsort Memory: 25kB
30. 195.714 57,359.889 ↓ 158.0 158 747

Group (cost=493.11..493.37 rows=1 width=44) (actual time=76.514..76.787 rows=158 loops=747)

  • Group Key: ppl.provider_id, ppl.geography_point
31. 162.099 57,164.175 ↓ 158.0 158 747

Sort (cost=493.11..493.12 rows=1 width=36) (actual time=76.511..76.525 rows=158 loops=747)

  • Sort Key: ppl.provider_id, ppl.geography_point
  • Sort Method: quicksort Memory: 80kB
32. 6,344.271 57,002.076 ↓ 158.0 158 747

Bitmap Heap Scan on plan_provider_locations ppl (cost=483.97..493.10 rows=1 width=36) (actual time=71.205..76.308 rows=158 loops=747)

  • Recheck Cond: ((geography_point && _st_expand(oon_pl.provider_geography_point, '8046.72'::double precision)) AND (oon_pl.specialty_ids && specialty_ids))
  • Filter: ((oon_pl.provider_id <> provider_id) AND (plan_id = 6,584) AND (oon_pl.provider_geography_point && _st_expand(geography_point, '8046.72'::double precision)) AND _st_dwithin(oon_pl.provider_geography_point, geography_point, '8046.72'::double precision, true))
  • Rows Removed by Filter: 10,792
  • Heap Blocks: exact=3,760,402
33. 1,529.109 50,657.805 ↓ 0.0 0 747

BitmapAnd (cost=483.97..483.97 rows=6 width=0) (actual time=67.815..67.815 rows=0 loops=747)

34. 20,403.558 20,403.558 ↓ 326.8 195,421 747

Bitmap Index Scan on ix_plan_plan_provider_locations_geography (cost=0.00..11.02 rows=598 width=0) (actual time=27.314..27.314 rows=195,421 loops=747)

  • Index Cond: (geography_point && _st_expand(oon_pl.provider_geography_point, '8046.72'::double precision))
35. 28,725.138 28,725.138 ↓ 4.6 275,969 747

Bitmap Index Scan on ix_plan_provider_locations_specialties (cost=0.00..472.69 rows=59,826 width=0) (actual time=38.454..38.454 rows=275,969 loops=747)

  • Index Cond: (oon_pl.specialty_ids && specialty_ids)
36. 12.159 12.159 ↓ 0.0 0 1,737

Index Scan using ix_tmp_reprice on utilization_reprice_procedures urp (cost=0.42..2.45 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=1,737)

  • Index Cond: ((provider_id = p.provider_id) AND ((procedure_code)::text = (up.procedure_code)::text) AND (place_of_service_id = up.place_of_service_id) AND (claim_type_id = up.claim_type_id) AND (member_category_id = up.member_category_id))
Planning time : 5.562 ms
Execution time : 58,216.508 ms