explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VAEt

Settings
# exclusive inclusive rows x rows loops node
1. 2.377 53.843 ↑ 1.0 1 1

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

2. 0.206 51.466 ↑ 4.6 407 1

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

3. 7.563 51.260 ↑ 4.6 407 1

GroupAggregate (cost=3,534,129.14..3,534,740.92 rows=1,876 width=253) (actual time=43.736..51.260 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.850 43.697 ↑ 10.8 814 1

Sort (cost=3,533,220.10..3,533,242.15 rows=8,821 width=209) (actual time=43.666..43.697 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.115 42.847 ↑ 10.8 814 1

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

  • Join Filter: NULL::boolean
8. 0.395 42.732 ↑ 10.8 814 1

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

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

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

10. 0.013 0.918 ↑ 574.3 7 1

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

11. 0.296 0.296 ↑ 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.210..0.296 rows=7 loops=1)

  • Index Cond: (link_id = 1,201,710,493)
  • Heap Fetches: 7
12. 0.609 0.609 ↑ 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.087..0.087 rows=1 loops=7)

  • Index Cond: (trip_id = road_segments_1.trip_id)
13. 0.833 0.833 ↑ 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.039..0.119 rows=452 loops=7)

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

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

  • Buckets: 8,192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 828kB
15. 38.434 38.434 ↓ 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.807..38.434 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