explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QFWm

Settings
# exclusive inclusive rows x rows loops node
1. 2.273 30.214 ↑ 1.0 1 1

Aggregate (cost=3,534,764.38..3,534,764.39 rows=1 width=32) (actual time=30.214..30.214 rows=1 loops=1)

2. 0.193 27.941 ↑ 4.6 407 1

Subquery Scan on q (cost=3,534,129.14..3,534,759.68 rows=1,876 width=44) (actual time=20.386..27.941 rows=407 loops=1)

3. 7.379 27.748 ↑ 4.6 407 1

GroupAggregate (cost=3,534,129.14..3,534,740.92 rows=1,876 width=253) (actual time=20.377..27.748 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..909.04 rows=3,404 width=78,632) (never executed)

6. 0.669 20.369 ↑ 10.8 814 1

Sort (cost=3,533,220.10..3,533,242.15 rows=8,821 width=209) (actual time=20.324..20.369 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. 0.121 19.700 ↑ 10.8 814 1

Nested Loop Left Join (cost=6,690.07..3,532,642.03 rows=8,821 width=209) (actual time=18.863..19.700 rows=814 loops=1)

  • Join Filter: NULL::boolean
8. 0.336 19.579 ↑ 10.8 814 1

Hash Join (cost=6,690.07..3,532,553.82 rows=8,821 width=209) (actual time=18.862..19.579 rows=814 loops=1)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
9. 0.278 0.852 ↑ 1,293.4 3,167 1

Nested Loop (cost=1.58..3,515,112.74 rows=4,096,200 width=32) (actual time=0.043..0.852 rows=3,167 loops=1)

10. 0.009 0.077 ↑ 574.3 7 1

Nested Loop (cost=1.00..11,200.04 rows=4,020 width=40) (actual time=0.030..0.077 rows=7 loops=1)

11. 0.026 0.026 ↑ 574.3 7 1

Index Only Scan using idx_road_segments_key on road_segments road_segments_1 (cost=0.57..4,104.19 rows=4,020 width=8) (actual time=0.018..0.026 rows=7 loops=1)

  • Index Cond: (link_id = 1201710493)
  • Heap Fetches: 7
12. 0.042 0.042 ↑ 1.0 1 7

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

  • Index Cond: (trip_id = road_segments_1.trip_id)
13. 0.497 0.497 ↑ 11.4 452 7

Index Scan using idx_road_segments_trip_id on road_segments (cost=0.57..819.90 rows=5,172 width=16) (actual time=0.011..0.071 rows=452 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
14. 1.455 18.391 ↓ 2.6 4,841 1

Hash (cost=6,665.04..6,665.04 rows=1,876 width=185) (actual time=18.391..18.391 rows=4,841 loops=1)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 828kB
15. 16.936 16.936 ↓ 2.6 4,841 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..6,665.04 rows=1,876 width=185) (actual time=0.103..16.936 rows=4,841 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 2
16. 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
Planning time : 2.339 ms