explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 38K9

Settings
# exclusive inclusive rows x rows loops node
1. 17.687 27,957.935 ↑ 1.0 1 1

Aggregate (cost=39,932.91..39,932.92 rows=1 width=32) (actual time=27,957.935..27,957.935 rows=1 loops=1)

2. 1.788 27,940.248 ↑ 12.5 417 1

Subquery Scan on q (cost=39,501.93..39,919.85 rows=5,224 width=100) (actual time=16,959.645..27,940.248 rows=417 loops=1)

3. 9,749.045 27,938.460 ↑ 12.5 417 1

GroupAggregate (cost=39,501.93..39,867.61 rows=5,224 width=100) (actual time=16,959.553..27,938.460 rows=417 loops=1)

  • Group Key: s.link_id, v_transit_roads.street_name, ((v_transit_roads.road_speed)::smallint), ((v_transit_roads.road_rank)::smallint), v_transit_roads.truck, v_transit_roads.surface, ((v_transit_roads.road_km)::real), ((round(sum((v_transit_roads.trailer_loads)::numeric), 2))::real), ((round(sum((v_transit_roads.tonnes)::numeric), 2))::real), ((round(avg((v_transit_roads.tonnes_per_trailer)::numeric), 2))::real), ((round(sum((v_transit_roads.trip_transport_costs)::numeric), 2))::real), (st_asmvtgeom(v_transit_roads.geometry, 'BOX(16750104.6279688 -3287403.71203125,16906647.661875 -3130860.678125)'::box2d, 4096, 1024, true))
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. 10,352.324 18,189.415 ↓ 731.1 3,819,120 1

Sort (cost=38,591.47..38,604.53 rows=5,224 width=92) (actual time=16,955.051..18,189.415 rows=3,819,120 loops=1)

  • Sort Key: s.link_id, v_transit_roads.street_name, ((v_transit_roads.road_speed)::smallint), ((v_transit_roads.road_rank)::smallint), v_transit_roads.truck, v_transit_roads.surface, ((v_transit_roads.road_km)::real), ((round(sum((v_transit_roads.trailer_loads)::numeric), 2))::real), ((round(sum((v_transit_roads.tonnes)::numeric), 2))::real), ((round(avg((v_transit_roads.tonnes_per_trailer)::numeric), 2))::real), ((round(sum((v_transit_roads.trip_transport_costs)::numeric), 2))::real), (st_asmvtgeom(v_transit_roads.geometry, 'BOX(16750104.6279688 -3287403.71203125,16906647.661875 -3130860.678125)'::box2d, 4096, 1024, true))
  • Sort Method: quicksort Memory: 1089242kB
7. 2,401.730 7,837.091 ↓ 731.1 3,819,120 1

Hash Join (cost=528.53..38,268.86 rows=5,224 width=92) (actual time=5,160.394..7,837.091 rows=3,819,120 loops=1)

  • Hash Cond: (road_movements.trip_id = s.trip_id)
8. 276.018 276.018 ↑ 1.0 582,725 1

Seq Scan on road_movements (cost=0.00..32,589.25 rows=582,725 width=16) (actual time=0.025..276.018 rows=582,725 loops=1)

9. 2,297.121 5,159.343 ↓ 731.1 3,819,120 1

Hash (cost=463.23..463.23 rows=5,224 width=92) (actual time=5,159.343..5,159.343 rows=3,819,120 loops=1)

  • Buckets: 4194304 (originally 8192) Batches: 1 (originally 1) Memory Usage: 654247kB
10. 786.099 2,862.222 ↓ 731.1 3,819,120 1

Nested Loop (cost=175.46..463.23 rows=5,224 width=92) (actual time=4.128..2,862.222 rows=3,819,120 loops=1)

11. 21.370 25.734 ↓ 417.0 417 1

GroupAggregate (cost=174.88..174.98 rows=1 width=246) (actual time=4.056..25.734 rows=417 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
12. 0.701 4.364 ↓ 417.0 417 1

Sort (cost=174.88..174.89 rows=1 width=206) (actual time=4.007..4.364 rows=417 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: 145kB
13. 0.263 3.663 ↓ 417.0 417 1

Nested Loop Left Join (cost=4.73..174.87 rows=1 width=206) (actual time=0.215..3.663 rows=417 loops=1)

  • Join Filter: NULL::boolean
14. 3.309 3.400 ↓ 417.0 417 1

Bitmap Heap Scan on v_transit_roads (cost=4.73..174.86 rows=1 width=206) (actual time=0.212..3.400 rows=417 loops=1)

  • Recheck Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
  • Filter: ((geometry && '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry))
  • Rows Removed by Filter: 32
  • Heap Blocks: exact=119
15. 0.091 0.091 ↓ 11.0 449 1

Bitmap Index Scan on idx_v_transit_roads_street_name (cost=0.00..4.73 rows=41 width=0) (actual time=0.091..0.091 rows=449 loops=1)

  • Index Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
16. 0.000 0.000 ↓ 0.0 0 417

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

  • One-Time Filter: false
17. 2,050.389 2,050.389 ↓ 1.8 9,159 417

Index Only Scan using idx_road_segments_key on road_segments s (cost=0.57..236.00 rows=5,224 width=16) (actual time=0.023..4.917 rows=9,159 loops=417)

  • Index Cond: (link_id = ((v_transit_roads.link_id)::integer))
  • Heap Fetches: 0
Planning time : 1.864 ms