explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cCIG

Settings
# exclusive inclusive rows x rows loops node
1. 2.292 219,899.849 ↑ 1.0 1 1

Aggregate (cost=16,394,076.43..16,394,076.53 rows=1 width=32) (actual time=219,899.849..219,899.849 rows=1 loops=1)

2. 0.431 219,897.557 ↑ 6.6 407 1

Subquery Scan on q (cost=16,392,473.09..16,394,069.72 rows=2,682 width=44) (actual time=219,888.491..219,897.557 rows=407 loops=1)

3. 8.657 219,897.126 ↑ 6.6 407 1

GroupAggregate (cost=16,392,473.09..16,393,801.52 rows=2,682 width=255) (actual time=219,888.452..219,897.126 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..1,218.40 rows=3,404 width=78,632) (never executed)

6. 5.123 219,888.469 ↑ 20.2 814 1

Sort (cost=16,391,254.69..16,391,295.87 rows=16,473 width=211) (actual time=219,888.350..219,888.469 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. 2.437 219,883.346 ↑ 20.2 814 1

Nested Loop Left Join (cost=19,191.85..16,390,100.94 rows=16,473 width=211) (actual time=12,100.563..219,883.346 rows=814 loops=1)

  • Join Filter: NULL::boolean
8. 1,615.249 219,880.909 ↑ 20.2 814 1

Hash Join (cost=19,191.85..16,388,453.64 rows=16,473 width=211) (actual time=12,100.556..219,880.909 rows=814 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 2,018.720 218,056.099 ↓ 4.1 7,519,551 1

Nested Loop (cost=81.67..16,360,840.69 rows=1,828,124 width=195) (actual time=485.191..218,056.099 rows=7,519,551 loops=1)

10. 1,303.789 1,740.832 ↓ 1.8 4,841 1

Bitmap Heap Scan on v_transit_roads (cost=81.10..9,962.66 rows=2,682 width=187) (actual time=452.263..1,740.832 rows=4,841 loops=1)

  • Recheck Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=1116
11. 437.043 437.043 ↑ 1.7 4,843 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..80.43 rows=8,047 width=0) (actual time=437.042..437.043 rows=4,843 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
12. 214,296.547 214,296.547 ↑ 3.4 1,553 4,841

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..5,571.42 rows=5,251 width=16) (actual time=1.411..44.267 rows=1,553 loops=4,841)

  • Index Cond: (link_id = v_transit_roads.link_id)
13. 0.031 209.561 ↑ 750.1 7 1

Hash (cost=18,571.96..18,571.96 rows=5,251 width=40) (actual time=209.561..209.561 rows=7 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 65kB
14. 0.061 209.530 ↑ 750.1 7 1

Nested Loop (cost=1.00..18,571.96 rows=5,251 width=40) (actual time=116.379..209.530 rows=7 loops=1)

15. 169.737 169.737 ↑ 750.1 7 1

Index Only Scan using idx_road_segments_key on road_segments road_segments_1 (cost=0.57..5,811.43 rows=5,251 width=8) (actual time=98.288..169.737 rows=7 loops=1)

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

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

  • Index Cond: (trip_id = road_segments_1.trip_id)
17. 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 : 90.614 ms