explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sNJ88

Settings
# exclusive inclusive rows x rows loops node
1. 0.085 28.721 ↑ 1.0 1 1

Aggregate (cost=16,601.78..16,601.79 rows=1 width=32) (actual time=28.721..28.721 rows=1 loops=1)

2. 0.124 28.636 ↓ 6.5 130 1

Subquery Scan on q (cost=16,599.03..16,601.73 rows=20 width=52) (actual time=26.687..28.636 rows=130 loops=1)

3. 1.862 28.512 ↓ 6.5 130 1

GroupAggregate (cost=16,599.03..16,601.53 rows=20 width=263) (actual time=26.678..28.512 rows=130 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, road_movements.trip_id
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.164 26.650 ↓ 6.5 130 1

Sort (cost=15,688.57..15,688.62 rows=20 width=219) (actual time=26.629..26.650 rows=130 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: 64kB
7. 0.090 26.486 ↓ 6.5 130 1

Nested Loop Left Join (cost=92.63..15,688.13 rows=20 width=219) (actual time=2.632..26.486 rows=130 loops=1)

  • Join Filter: NULL::boolean
8. 0.046 26.396 ↓ 6.5 130 1

Nested Loop (cost=92.63..15,687.93 rows=20 width=219) (actual time=2.629..26.396 rows=130 loops=1)

9. 0.019 0.019 ↑ 1.0 1 1

Index Scan using trip on road_movements (cost=0.42..8.44 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: (trip_id = 516693)
10. 2.935 26.331 ↓ 6.5 130 1

Nested Loop (cost=92.20..15,679.29 rows=20 width=195) (actual time=2.601..26.331 rows=130 loops=1)

11. 14.157 14.753 ↓ 3.4 2,881 1

Bitmap Heap Scan on v_transit_roads (cost=91.63..8,373.54 rows=850 width=187) (actual time=0.754..14.753 rows=2,881 loops=1)

  • Recheck Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=599
12. 0.596 0.596 ↓ 1.1 2,882 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..91.42 rows=2,551 width=0) (actual time=0.596..0.596 rows=2,882 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
13. 8.643 8.643 ↓ 0.0 0 2,881

Index Only Scan using idx_road_segments_keyrev on road_segments (cost=0.57..8.59 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=2,881)

  • Index Cond: ((trip_id = 516693) AND (link_id = v_transit_roads.link_id))
  • Heap Fetches: 130
14. 0.000 0.000 ↓ 0.0 0 130

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=130)

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