explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DYxv

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=1,993,198.88..101,645,461.92 rows=50 width=68) (actual rows= loops=)

2.          

Initplan (forLimit)

3. 0.000 0.000 ↓ 0.0

Index Scan using mp_census_county_1 on mp_census_county (cost=0.28..8.30 rows=1 width=38,341) (actual rows= loops=)

  • Index Cond: (fips_id = '32031'::numeric)
4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,993,190.58..25,216,008,784.46 rows=12,651 width=68) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,483,580.25..18,768,928,060.38 rows=12,651 width=60) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=392,977.72..4,971,715,055.62 rows=12,651 width=52) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=27,921.30..353,385,976.02 rows=12,651 width=44) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Index Scan using all_subjects_1 on all_subjects a (cost=0.57..160,500.15 rows=12,651 width=40) (actual rows= loops=)

  • Index Cond: (fips_county_cd = '32031'::numeric)
  • Filter: st_contains($0, mp_geom)
9. 0.000 0.000 ↓ 0.0

Limit (cost=27,920.73..27,920.74 rows=1 width=167) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Sort (cost=27,920.73..27,920.74 rows=2 width=167) (actual rows= loops=)

  • Sort Key: ((st_transform(a.mp_geom, 3857) <-> "*SELECT* 1".way))
11. 0.000 0.000 ↓ 0.0

Result (cost=0.42..27,920.72 rows=2 width=167) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Append (cost=0.42..27,920.71 rows=2 width=167) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.42..9,040.11 rows=1 width=36) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..9,040.10 rows=1 width=32) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Index Scan using osm_point_index on osm_point b (cost=0.42..3,380,842.51 rows=374 width=32) (actual rows= loops=)

  • Order By: (way <-> st_transform(a.mp_geom, 3857))
  • Filter: ((name IS NOT NULL) AND (shop = 'supermarket'::text))
16. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=18,880.59..18,880.60 rows=1 width=298) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Limit (cost=18,880.59..18,880.59 rows=1 width=294) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Sort (cost=18,880.59..18,881.04 rows=181 width=294) (actual rows= loops=)

  • Sort Key: ((st_transform(a.mp_geom, 3857) <-> b_1.way))
19. 0.000 0.000 ↓ 0.0

Index Scan using osm_polygon_10 on osm_polygon b_1 (cost=0.56..18,879.68 rows=181 width=294) (actual rows= loops=)

  • Index Cond: (shop = 'supermarket'::text)
  • Filter: (name IS NOT NULL)
20. 0.000 0.000 ↓ 0.0

Aggregate (cost=365,056.41..365,056.42 rows=1 width=0) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Append (cost=1,864.91..365,049.11 rows=2,922 width=0) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1_1 (cost=1,864.91..165,556.29 rows=2,235 width=0) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on osm_point b_2 (cost=1,864.91..165,533.94 rows=2,235 width=0) (actual rows= loops=)

  • Recheck Cond: (amenity = ANY ('{restaurant,bar,pub,cafe}'::text[]))
  • Filter: ((name IS NOT NULL) AND ((st_transform(a.mp_geom, 3857) <-> way) < '5000'::double precision))
24. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on osm_point_1 (cost=0.00..1,864.35 rows=56,279 width=0) (actual rows= loops=)

  • Index Cond: (amenity = ANY ('{restaurant,bar,pub,cafe}'::text[]))
25. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_1 (cost=0.56..199,492.82 rows=687 width=0) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using osm_polygon_1 on osm_polygon b_3 (cost=0.56..199,485.95 rows=687 width=0) (actual rows= loops=)

  • Index Cond: (amenity = ANY ('{restaurant,bar,pub,cafe}'::text[]))
  • Filter: ((name IS NOT NULL) AND ((st_transform(a.mp_geom, 3857) <-> way) < '5000'::double precision))
27. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,090,602.53..1,090,602.54 rows=1 width=0) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Append (cost=5,234.81..1,090,568.58 rows=13,582 width=0) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1_2 (cost=5,234.81..464,724.79 rows=11,223 width=0) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on osm_point b_4 (cost=5,234.81..464,612.56 rows=11,223 width=0) (actual rows= loops=)

  • Recheck Cond: (shop IS NOT NULL)
  • Filter: ((name IS NOT NULL) AND ((st_transform(a.mp_geom, 3857) <-> way) < '5000'::double precision))
31. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on osm_point_10 (cost=0.00..5,232.01 rows=282,609 width=0) (actual rows= loops=)

  • Index Cond: (shop IS NOT NULL)
32. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_2 (cost=3,419.24..625,843.78 rows=2,359 width=0) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on osm_polygon b_5 (cost=3,419.24..625,820.19 rows=2,359 width=0) (actual rows= loops=)

  • Recheck Cond: (shop IS NOT NULL)
  • Filter: ((name IS NOT NULL) AND ((st_transform(a.mp_geom, 3857) <-> way) < '5000'::double precision))
34. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on osm_polygon_10 (cost=0.00..3,418.65 rows=184,811 width=0) (actual rows= loops=)

  • Index Cond: (shop IS NOT NULL)
35. 0.000 0.000 ↓ 0.0

Aggregate (cost=509,610.33..509,610.34 rows=1 width=0) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on osm_point b_6 (cost=8,386.70..509,317.44 rows=117,156 width=0) (actual rows= loops=)

  • Recheck Cond: ((highway = 'bus_stop'::text) OR (public_transport = 'platform'::text))
  • Filter: ((st_transform(a.mp_geom, 3857) <-> way) < '1000'::double precision)
37. 0.000 0.000 ↓ 0.0

BitmapOr (cost=8,386.70..8,386.70 rows=352,950 width=0) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on osm_point_9 (cost=0.00..4,505.19 rows=235,300 width=0) (actual rows= loops=)

  • Index Cond: (highway = 'bus_stop'::text)
39. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on osm_point_7 (cost=0.00..3,822.94 rows=117,650 width=0) (actual rows= loops=)