explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tJbS

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 205,551.304 ↑ 1.0 1 1

Aggregate (cost=12,468,086.11..12,468,086.12 rows=1 width=32) (actual time=205,551.304..205,551.304 rows=1 loops=1)

2. 0.015 205,551.279 ↓ 0.0 0 1

Subquery Scan on q (cost=12,467,760.70..12,468,083.85 rows=901 width=44) (actual time=205,551.279..205,551.279 rows=0 loops=1)

3. 0.003 205,551.264 ↓ 0.0 0 1

GroupAggregate (cost=12,467,760.70..12,468,074.84 rows=901 width=255) (actual time=205,551.264..205,551.264 rows=0 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..910.46 rows=3,346 width=75,373) (never executed)

6. 0.024 205,551.261 ↓ 0.0 0 1

Sort (cost=12,466,850.24..12,466,861.75 rows=4,606 width=211) (actual time=205,551.261..205,551.261 rows=0 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: 25kB
7. 0.002 205,551.237 ↓ 0.0 0 1

Nested Loop Left Join (cost=20,442.75..12,466,569.98 rows=4,606 width=211) (actual time=205,551.237..205,551.237 rows=0 loops=1)

  • Join Filter: NULL::boolean
8. 53.471 205,551.235 ↓ 0.0 0 1

Hash Join (cost=20,442.75..12,466,523.92 rows=4,606 width=211) (actual time=205,551.234..205,551.235 rows=0 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 84,161.304 205,487.559 ↑ 1.2 494,211 1

Hash Join (cost=8,799.47..12,453,268.50 rows=614,146 width=195) (actual time=290.293..205,487.559 rows=494,211 loops=1)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
10. 121,314.096 121,314.096 ↑ 1.0 593,771,497 1

Seq Scan on road_segments (cost=0.00..10,885,811.20 rows=593,771,520 width=16) (actual time=0.014..121,314.096 rows=593,771,497 loops=1)

11. 1.728 12.159 ↓ 2.5 2,264 1

Hash (cost=8,788.21..8,788.21 rows=901 width=187) (actual time=12.158..12.159 rows=2,264 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 509kB
12. 10.001 10.431 ↓ 2.5 2,264 1

Bitmap Heap Scan on v_transit_roads (cost=96.78..8,788.21 rows=901 width=187) (actual time=0.582..10.431 rows=2,264 loops=1)

  • Recheck Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270413D0AB775869647C1E17ADC72B5327041CDCCCC56F6E247C13333FF6E433C7041CDCCCC56F6E247C13333FF6E433C70413D0AB775869647C1E17ADC72B53270413D0AB775869647C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270413D0AB775869647C1E17ADC72B5327041CDCCCC56F6E247C13333FF6E433C7041CDCCCC56F6E247C13333FF6E433C70413D0AB775869647C1E17ADC72B53270413D0AB775869647C1'::geometry)
  • Heap Blocks: exact=495
13. 0.430 0.430 ↑ 1.2 2,264 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..96.56 rows=2,703 width=0) (actual time=0.430..0.430 rows=2,264 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270413D0AB775869647C1E17ADC72B5327041CDCCCC56F6E247C13333FF6E433C7041CDCCCC56F6E247C13333FF6E433C70413D0AB775869647C1E17ADC72B53270413D0AB775869647C1'::geometry)
14. 1.118 10.205 ↑ 1.1 4,113 1

Hash (cost=11,588.65..11,588.65 rows=4,370 width=32) (actual time=10.205..10.205 rows=4,113 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 322kB
15. 8.296 9.087 ↑ 1.1 4,113 1

Bitmap Heap Scan on road_movements (cost=86.29..11,588.65 rows=4,370 width=32) (actual time=1.163..9.087 rows=4,113 loops=1)

  • Recheck Cond: (commod_id = ANY ('{2}'::integer[]))
  • Heap Blocks: exact=2378
16. 0.791 0.791 ↑ 1.1 4,113 1

Bitmap Index Scan on idx_road_movements_commod_id (cost=0.00..85.20 rows=4,370 width=0) (actual time=0.791..0.791 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
17. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
Planning time : 7.405 ms