explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ciyp

Settings
# exclusive inclusive rows x rows loops node
1. 4.566 4,643.651 ↑ 1.0 1 1

Aggregate (cost=586,642.95..586,643.05 rows=1 width=32) (actual time=4,643.651..4,643.651 rows=1 loops=1)

  • Functions: 30
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 5.216 ms, Inlining 20.114 ms, Optimization 375.076 ms, Emission 232.207 ms, Total 632.613 ms"Execution Time: 4649.492 ms
2. 0.825 4,639.085 ↓ 2.9 1,208 1

Subquery Scan on q (cost=586,514.79..586,639.42 rows=412 width=56) (actual time=4,541.147..4,639.085 rows=1,208 loops=1)

3. 94.905 4,638.260 ↓ 2.9 1,208 1

GroupAggregate (cost=586,514.79..586,598.22 rows=412 width=228) (actual time=4,541.136..4,638.260 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. 39.796 4,543.355 ↓ 54.3 22,386 1

Sort (cost=586,514.79..586,515.82 rows=412 width=196) (actual time=4,540.800..4,543.355 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. 8.153 4,503.559 ↓ 54.3 22,386 1

Nested Loop Left Join (cost=89.69..586,496.90 rows=412 width=196) (actual time=910.227..4,503.559 rows=22,386 loops=1)

  • Join Filter: NULL::boolean
6. 12.263 4,495.406 ↓ 54.3 22,386 1

Nested Loop (cost=89.69..586,455.70 rows=412 width=196) (actual time=910.224..4,495.406 rows=22,386 loops=1)

7. 12.363 4,415.985 ↓ 54.3 22,386 1

Nested Loop (cost=89.27..585,499.27 rows=412 width=184) (actual time=910.204..4,415.985 rows=22,386 loops=1)

8. 631.356 651.422 ↑ 2.7 1,825 1

Nested Loop (cost=1.00..17,226.83 rows=4,964 width=444) (actual time=628.432..651.422 rows=1,825 loops=1)

9. 9.116 9.116 ↑ 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.025..9.116 rows=1,825 loops=1)

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

Index Scan using idx_mv_trip_links_trip_id on mv_trip_links (cost=0.42..2.36 rows=1 width=436) (actual time=0.006..0.006 rows=1 loops=1,825)

  • Index Cond: (trip_id = road_segments.trip_id)
11. 102.200 3,752.200 ↓ 12.0 12 1,825

Bitmap Heap Scan on v_transit_roads (cost=88.27..114.38 rows=1 width=172) (actual time=2.004..2.056 rows=12 loops=1,825)

  • Recheck Cond: (link_id = ANY (mv_trip_links.link_ids))
  • Filter: st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Heap Blocks: exact=8718
12. 89.425 3,650.000 ↓ 0.0 0 1,825

BitmapAnd (cost=88.27..88.27 rows=1 width=0) (actual time=2.000..2.000 rows=0 loops=1,825)

13. 2,359.725 2,359.725 ↓ 101.2 1,012 1,825

Bitmap Index Scan on idx_v_transit_roads_link_id (cost=0.00..13.24 rows=10 width=0) (actual time=1.293..1.293 rows=1,012 loops=1,825)

  • Index Cond: (link_id = ANY (mv_trip_links.link_ids))
14. 1,200.850 1,200.850 ↑ 1.5 4,843 1,825

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..72.97 rows=7,227 width=0) (actual time=0.658..0.658 rows=4,843 loops=1,825)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
15. 67.158 67.158 ↑ 1.0 1 22,386

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

  • Index Cond: (trip_id = mv_trip_links.trip_id)
16. 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