explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XGcZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.806 66,282.249 ↑ 1.0 1 1

Aggregate (cost=127,358,523.38..127,358,523.39 rows=1 width=8) (actual time=66,282.248..66,282.249 rows=1 loops=1)

2.          

CTE oon_pl

3. 19.811 726.197 ↑ 323.5 794 1

Unique (cost=147,897.07..151,108.23 rows=256,893 width=72) (actual time=701.648..726.197 rows=794 loops=1)

4. 26.685 706.386 ↑ 12.1 21,281 1

Sort (cost=147,897.07..148,539.30 rows=256,893 width=72) (actual time=701.646..706.386 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. 647.219 679.701 ↑ 12.1 21,281 1

Seq Scan on utilization_procedures (cost=32,160.69..124,814.20 rows=256,893 width=72) (actual time=52.547..679.701 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. 32.482 32.482 ↑ 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.035..32.482 rows=81,368 loops=1)

  • Index Cond: (plan_id = 6,584)
8.          

CTE nearest_providers

9. 9.041 66,279.657 ↑ 112.7 2,280 1

Nested Loop (cost=494.86..127,201,635.06 rows=256,893 width=16) (actual time=753.373..66,279.657 rows=2,280 loops=1)

10. 726.710 726.710 ↑ 323.5 794 1

CTE Scan on oon_pl (cost=0.00..5,137.86 rows=256,893 width=68) (actual time=701.649..726.710 rows=794 loops=1)

11. 3.176 65,543.906 ↓ 3.0 3 794

Limit (cost=494.86..494.86 rows=1 width=44) (actual time=82.546..82.549 rows=3 loops=794)

12. 146.096 65,540.730 ↓ 3.0 3 794

Sort (cost=494.86..494.86 rows=1 width=44) (actual time=82.545..82.545 rows=3 loops=794)

  • Sort Key: ((oon_pl.provider_geography_point <-> plan_provider_locations_1.geography_point))
  • Sort Method: top-N heapsort Memory: 25kB
13. 8,522.002 65,394.634 ↓ 221.0 221 794

Bitmap Heap Scan on plan_provider_locations plan_provider_locations_1 (cost=485.46..494.85 rows=1 width=44) (actual time=76.013..82.361 rows=221 loops=794)

  • 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: 11,330
  • Heap Blocks: exact=5,049,832
14. 1,946.888 56,872.632 ↓ 0.0 0 794

BitmapAnd (cost=485.46..485.46 rows=6 width=0) (actual time=71.628..71.628 rows=0 loops=794)

15. 29,898.864 29,898.864 ↓ 452.9 270,817 794

Bitmap Index Scan on ix_plan_plan_provider_locations_geography (cost=0.00..12.52 rows=598 width=0) (actual time=37.656..37.656 rows=270,817 loops=794)

  • Index Cond: (geography_point && _st_expand(oon_pl.provider_geography_point, '8046.72'::double precision))
16. 25,026.880 25,026.880 ↓ 4.4 265,905 794

Bitmap Index Scan on ix_plan_provider_locations_specialties (cost=0.00..472.69 rows=59,826 width=0) (actual time=31.520..31.520 rows=265,905 loops=794)

  • Index Cond: (oon_pl.specialty_ids && specialty_ids)
17. 66,281.443 66,281.443 ↑ 112.7 2,280 1

CTE Scan on nearest_providers (cost=0.00..5,137.86 rows=256,893 width=0) (actual time=753.376..66,281.443 rows=2,280 loops=1)

Planning time : 4.218 ms
Execution time : 66,283.769 ms