explain.depesz.com

PostgreSQL's explain analyze made readable

Result: srPa

Settings
# exclusive inclusive rows x rows loops node
1. 0.883 65,726.785 ↑ 1.0 1 1

Aggregate (cost=393,172.32..393,172.33 rows=1 width=8) (actual time=65,726.785..65,726.785 rows=1 loops=1)

2.          

CTE nearest_providers

3. 9.702 65,724.095 ↓ 2.9 2,280 1

Nested Loop (cost=494.86..393,154.46 rows=794 width=16) (actual time=53.207..65,724.095 rows=2,280 loops=1)

4. 0.571 0.571 ↑ 1.0 794 1

Seq Scan on oon_pl (cost=0.00..17.94 rows=794 width=63) (actual time=0.003..0.571 rows=794 loops=1)

5. 2.382 65,713.822 ↓ 3.0 3 794

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

6. 174.680 65,711.440 ↓ 3.0 3 794

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

  • Sort Key: ((oon_pl.provider_geography_point <-> plan_provider_locations.geography_point))
  • Sort Method: top-N heapsort Memory: 25kB
7. 8,581.552 65,536.760 ↓ 221.0 221 794

Bitmap Heap Scan on plan_provider_locations (cost=485.46..494.85 rows=1 width=44) (actual time=76.148..82.540 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
8. 1,951.652 56,955.208 ↓ 0.0 0 794

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

9. 30,036.226 30,036.226 ↓ 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.829..37.829 rows=270,817 loops=794)

  • Index Cond: (geography_point && _st_expand(oon_pl.provider_geography_point, '8046.72'::double precision))
10. 24,967.330 24,967.330 ↓ 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.445..31.445 rows=265,905 loops=794)

  • Index Cond: (oon_pl.specialty_ids && specialty_ids)
11. 65,725.902 65,725.902 ↓ 2.9 2,280 1

CTE Scan on nearest_providers (cost=0.00..15.88 rows=794 width=0) (actual time=53.209..65,725.902 rows=2,280 loops=1)

Planning time : 0.429 ms
Execution time : 65,727.575 ms