explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tGvy

Settings
# exclusive inclusive rows x rows loops node
1. 1.752 50,002.238 ↑ 1.0 1 1

Aggregate (cost=16,394,076.43..16,394,076.53 rows=1 width=32) (actual time=50,002.238..50,002.238 rows=1 loops=1)

2. 0.374 50,000.486 ↑ 6.6 407 1

Subquery Scan on q (cost=16,392,473.09..16,394,069.72 rows=2,682 width=44) (actual time=49,992.187..50,000.486 rows=407 loops=1)

3. 7.949 50,000.112 ↑ 6.6 407 1

GroupAggregate (cost=16,392,473.09..16,393,801.52 rows=2,682 width=255) (actual time=49,992.171..50,000.112 rows=407 loops=1)

  • Group Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
4.          

CTE boundaries

5. 0.000 0.000 ↓ 0.0 0

Seq Scan on v_transit_boundaries (cost=0.00..1,218.40 rows=3,404 width=78,632) (never executed)

6. 3.870 49,992.163 ↑ 20.2 814 1

Sort (cost=16,391,254.69..16,391,295.87 rows=16,473 width=211) (actual time=49,992.061..49,992.163 rows=814 loops=1)

  • Sort Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
  • Sort Method: quicksort Memory: 249kB
7. 2.024 49,988.293 ↑ 20.2 814 1

Nested Loop Left Join (cost=19,191.85..16,390,100.94 rows=16,473 width=211) (actual time=11.283..49,988.293 rows=814 loops=1)

  • Join Filter: NULL::boolean
8. 1,557.555 49,986.269 ↑ 20.2 814 1

Hash Join (cost=19,191.85..16,388,453.64 rows=16,473 width=211) (actual time=11.278..49,986.269 rows=814 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 2,021.743 48,428.444 ↓ 4.1 7,519,551 1

Nested Loop (cost=81.67..16,360,840.69 rows=1,828,124 width=195) (actual time=1.581..48,428.444 rows=7,519,551 loops=1)

10. 91.600 92.854 ↓ 1.8 4,841 1

Bitmap Heap Scan on v_transit_roads (cost=81.10..9,962.66 rows=2,682 width=187) (actual time=1.532..92.854 rows=4,841 loops=1)

  • Recheck Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1,116
11. 1.254 1.254 ↑ 1.7 4,843 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..80.43 rows=8,047 width=0) (actual time=1.254..1.254 rows=4,843 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
12. 46,313.847 46,313.847 ↑ 3.4 1,553 4,841

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..5,571.42 rows=5,251 width=16) (actual time=0.016..9.567 rows=1,553 loops=4,841)

  • Index Cond: (link_id = v_transit_roads.link_id)
13. 0.010 0.270 ↑ 750.1 7 1

Hash (cost=18,571.96..18,571.96 rows=5,251 width=40) (actual time=0.269..0.270 rows=7 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 65kB
14. 0.016 0.260 ↑ 750.1 7 1

Nested Loop (cost=1.00..18,571.96 rows=5,251 width=40) (actual time=0.094..0.260 rows=7 loops=1)

15. 0.118 0.118 ↑ 750.1 7 1

Index Only Scan using idx_road_segments_key on road_segments road_segments_1 (cost=0.57..5,811.43 rows=5,251 width=8) (actual time=0.058..0.118 rows=7 loops=1)

  • Index Cond: (link_id = 1,201,710,493)
  • Heap Fetches: 7
16. 0.126 0.126 ↑ 1.0 1 7

Index Scan using road_movements_pkey on road_movements (cost=0.42..2.43 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=7)

  • Index Cond: (trip_id = road_segments_1.trip_id)
17. 0.000 0.000 ↓ 0.0 0 814

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=814)

  • One-Time Filter: false