explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rMm7

Settings
# exclusive inclusive rows x rows loops node
1. 4.784 27,636.255 ↑ 1.0 1 1

Aggregate (cost=386,211.77..386,211.87 rows=1 width=32) (actual time=27,636.255..27,636.255 rows=1 loops=1)

  • Functions: 34
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 5.793 ms, Inlining 0.000 ms, Optimization 2.248 ms, Emission 39.713 ms, Total 47.753 ms"Execution Time: 27,642.671 ms
2. 0.924 27,631.471 ↓ 2.0 1,208 1

Subquery Scan on q (cost=386,021.09..386,207.73 rows=617 width=56) (actual time=27,525.526..27,631.471 rows=1,208 loops=1)

3. 102.703 27,630.547 ↓ 2.0 1,208 1

GroupAggregate (cost=386,021.09..386,146.03 rows=617 width=228) (actual time=27,525.511..27,630.547 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. 47.198 27,527.844 ↓ 36.3 22,386 1

Sort (cost=386,021.09..386,022.63 rows=617 width=196) (actual time=27,525.157..27,527.844 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: 5,722kB
5. 11.386 27,480.646 ↓ 36.3 22,386 1

Nested Loop Left Join (cost=6,003.78..385,992.49 rows=617 width=196) (actual time=1,809.498..27,480.646 rows=22,386 loops=1)

  • Join Filter: NULL::boolean
6. 1,383.921 27,469.260 ↓ 36.3 22,386 1

Hash Join (cost=6,003.78..385,930.79 rows=617 width=196) (actual time=1,809.494..27,469.260 rows=22,386 loops=1)

  • Hash Cond: (road_movements.trip_id = road_segments.trip_id)
7. 7,654.543 26,082.341 ↓ 104.0 7,519,551 1

Nested Loop (cost=1.14..379,738.44 rows=72,269 width=204) (actual time=43.038..26,082.341 rows=7,519,551 loops=1)

8. 6.632 123.977 ↑ 1.5 4,841 1

Nested Loop (cost=0.71..205,023.52 rows=7,227 width=358) (actual time=43.013..123.977 rows=4,841 loops=1)

9. 93.140 93.140 ↑ 1.5 4,841 1

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

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Filter: st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 2
10. 24.205 24.205 ↑ 1.0 1 4,841

Index Scan using idx_mv_link_trips_link_id on mv_link_trips (cost=0.42..2.34 rows=1 width=190) (actual time=0.005..0.005 rows=1 loops=4,841)

  • Index Cond: (link_id = v_transit_roads.link_id)
11. 18,303.821 18,303.821 ↓ 155.3 1,553 4,841

Index Scan using road_movements_pkey on road_movements (cost=0.42..23.18 rows=10 width=32) (actual time=0.003..3.781 rows=1,553 loops=4,841)

  • Index Cond: (trip_id = ANY (mv_link_trips.trip_ids))
12. 0.415 2.998 ↑ 2.7 1,825 1

Hash (cost=5,493.83..5,493.83 rows=4,964 width=8) (actual time=2.998..2.998 rows=1,825 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 136kB
13. 2.583 2.583 ↑ 2.7 1,825 1

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

  • Index Cond: (link_id = 1,203,650,403)
  • Heap Fetches: 1,825
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