explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UEnV

Settings
# exclusive inclusive rows x rows loops node
1. 4.359 9,367.510 ↑ 1.0 1 1

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

  • Functions: 32
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.371 ms, Inlining 22.993 ms, Optimization 422.729 ms, Emission 272.784 ms, Total 723.877 ms"Execution Time: 9373.572 ms
2. 0.847 9,363.151 ↑ 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=9,264.264..9,363.151 rows=1,208 loops=1)

3. 95.515 9,362.304 ↑ 6.0 1,208 1

GroupAggregate (cost=42,009,495.70..42,012,873.22 rows=7,227 width=228) (actual time=9,264.253..9,362.304 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. 44.781 9,266.789 ↑ 1.9 22,386 1

Sort (cost=42,009,495.70..42,009,600.77 rows=42,028 width=196) (actual time=9,263.935..9,266.789 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. 7.868 9,222.008 ↑ 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,184.245..9,222.008 rows=22,386 loops=1)

  • Join Filter: NULL::boolean
6. 736.926 9,214.140 ↑ 1.9 22,386 1

Hash Join (cost=18,111.50..42,002,065.35 rows=42,028 width=196) (actual time=1,184.240..9,214.140 rows=22,386 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
7. 700.522 8,467.882 ↓ 1.5 7,519,551 1

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

8. 757.592 757.592 ↑ 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=719.683..757.592 rows=4,841 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Filter: st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 2
9. 7,009.768 7,009.768 ↑ 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.008..1.448 rows=1,553 loops=4,841)

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

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

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

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

12. 2.324 2.324 ↑ 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.016..2.324 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