explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZXGl

Settings
# exclusive inclusive rows x rows loops node
1. 3.931 36,518.219 ↑ 1.0 1 1

Aggregate (cost=42,013,616.49..42,013,616.59 rows=1 width=32) (actual time=36,518.219..36,518.219 rows=1 loops=1)

  • Functions: 32
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 7.296 ms, Inlining 27.681 ms, Optimization 479.817 ms, Emission 280.718 ms, Total 795.512 ms"Execution Time: 36526.109 ms
2. 0.824 36,514.288 ↑ 6.0 1,208 1

Subquery Scan on q (cost=42,009,495.70..42,013,595.92 rows=7,227 width=56) (actual time=36,419.088..36,514.288 rows=1,208 loops=1)

3. 92.111 36,513.464 ↑ 6.0 1,208 1

GroupAggregate (cost=42,009,495.70..42,012,873.22 rows=7,227 width=228) (actual time=36,419.078..36,513.464 rows=1,208 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. 43.027 36,421.353 ↑ 1.9 22,386 1

Sort (cost=42,009,495.70..42,009,600.77 rows=42,028 width=196) (actual time=36,418.758..36,421.353 rows=22,386 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: 5722kB
5. 9.526 36,378.326 ↑ 1.9 22,386 1

Nested Loop Left Join (cost=18,111.50..42,006,268.15 rows=42,028 width=196) (actual time=1,293.878..36,378.326 rows=22,386 loops=1)

  • Join Filter: NULL::boolean
6. 853.048 36,368.800 ↑ 1.9 22,386 1

Hash Join (cost=18,111.50..42,002,065.35 rows=42,028 width=196) (actual time=1,293.873..36,368.800 rows=22,386 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
7. 757.163 35,504.076 ↓ 1.5 7,519,551 1

Nested Loop (cost=0.86..41,961,278.97 rows=4,926,119 width=180) (actual time=789.501..35,504.076 rows=7,519,551 loops=1)

8. 840.549 840.549 ↑ 1.5 4,841 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..188,353.67 rows=7,227 width=172) (actual time=789.452..840.549 rows=4,841 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Filter: st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 2
9. 33,906.364 33,906.364 ↑ 3.2 1,553 4,841

Index Only Scan using idx_road_segments_key on road_segments (cost=0.57..5,283.72 rows=4,964 width=16) (actual time=0.209..7.004 rows=1,553 loops=4,841)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 7519551
10. 0.875 11.676 ↑ 2.7 1,825 1

Hash (cost=17,601.83..17,601.83 rows=4,964 width=40) (actual time=11.676..11.676 rows=1,825 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 193kB
11. 2.132 10.801 ↑ 2.7 1,825 1

Nested Loop (cost=1.00..17,601.83 rows=4,964 width=40) (actual time=0.044..10.801 rows=1,825 loops=1)

12. 3.194 3.194 ↑ 2.7 1,825 1

Index Only Scan using idx_road_segments_key on road_segments road_segments_1 (cost=0.57..5,493.83 rows=4,964 width=8) (actual time=0.017..3.194 rows=1,825 loops=1)

  • Index Cond: (link_id = 1203650403)
  • Heap Fetches: 1825
13. 5.475 5.475 ↑ 1.0 1 1,825

Index Scan using road_movements_pkey on road_movements (cost=0.42..2.44 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1,825)

  • Index Cond: (trip_id = road_segments_1.trip_id)
14. 0.000 0.000 ↓ 0.0 0 22,386

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=22,386)

  • One-Time Filter: false