explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4bNa

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 197,765.732 ↑ 1.0 1 1

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

2. 0.002 197,765.710 ↓ 0.0 0 1

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

3. 0.002 197,765.708 ↓ 0.0 0 1

GroupAggregate (cost=12,467,760.70..12,468,074.84 rows=901 width=255) (actual time=197,765.708..197,765.708 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.020 197,765.706 ↓ 0.0 0 1

Sort (cost=12,466,850.24..12,466,861.75 rows=4,606 width=211) (actual time=197,765.706..197,765.706 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.003 197,765.686 ↓ 0.0 0 1

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

  • Join Filter: NULL::boolean
8. 56.112 197,765.683 ↓ 0.0 0 1

Hash Join (cost=20,442.75..12,466,523.92 rows=4,606 width=211) (actual time=197,765.682..197,765.683 rows=0 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 84,173.328 197,700.052 ↑ 1.2 494,211 1

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

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
10. 113,515.023 113,515.023 ↑ 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.013..113,515.023 rows=593,771,497 loops=1)

11. 1.652 11.701 ↓ 2.5 2,264 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 509kB
12. 9.624 10.049 ↓ 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.049 rows=2,264 loops=1)

  • Recheck Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270413D0AB775869647C1E17ADC72B5327041CDCCCC56F6E247C13333FF6E433C7041CDCCCC56F6E247C13333FF6E433C70413D0AB775869647C1E17ADC72B53270413D0AB775869647C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270413D0AB775869647C1E17ADC72B5327041CDCCCC56F6E247C13333FF6E433C7041CDCCCC56F6E247C13333FF6E433C70413D0AB775869647C1E17ADC72B53270413D0AB775869647C1'::geometry)
  • Heap Blocks: exact=495
13. 0.425 0.425 ↑ 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.425..0.425 rows=2,264 loops=1)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 322kB
15. 7.675 8.432 ↑ 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.129..8.432 rows=4,113 loops=1)

  • Recheck Cond: (commod_id = ANY ('{2}'::integer[]))
  • Heap Blocks: exact=2378
16. 0.757 0.757 ↑ 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.757..0.757 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 : 1.925 ms