explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rDuf

Settings
# exclusive inclusive rows x rows loops node
1. 41,745.781 68,602.893 ↑ 1.0 1 1

Aggregate (cost=60,051.18..60,051.19 rows=1 width=32) (actual time=68,602.892..68,602.893 rows=1 loops=1)

2. 6,473.234 26,857.112 ↓ 731.1 3,819,120 1

Subquery Scan on q (cost=22,245.54..60,038.12 rows=5,224 width=438) (actual time=15,185.053..26,857.112 rows=3,819,120 loops=1)

3. 4,976.767 20,383.878 ↓ 731.1 3,819,120 1

Hash Join (cost=22,245.54..59,985.88 rows=5,224 width=414) (actual time=15,185.017..20,383.878 rows=3,819,120 loops=1)

  • Hash Cond: (road_movements.trip_id = s.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. 251.494 251.494 ↑ 1.0 582,725 1

Seq Scan on road_movements (cost=0.00..32,589.25 rows=582,725 width=314) (actual time=0.015..251.494 rows=582,725 loops=1)

7. 2,822.046 15,155.617 ↓ 731.1 3,819,120 1

Hash (cost=21,269.78..21,269.78 rows=5,224 width=108) (actual time=15,155.617..15,155.617 rows=3,819,120 loops=1)

  • Buckets: 4194304 (originally 8192) Batches: 1 (originally 1) Memory Usage: 713920kB
8. 1,177.016 12,333.571 ↓ 731.1 3,819,120 1

Nested Loop (cost=180.08..21,269.78 rows=5,224 width=108) (actual time=4.036..12,333.571 rows=3,819,120 loops=1)

9. 25.671 30.161 ↓ 417.0 417 1

GroupAggregate (cost=179.50..179.60 rows=1 width=246) (actual time=3.964..30.161 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
10. 0.953 4.490 ↓ 417.0 417 1

Sort (cost=179.50..179.51 rows=1 width=206) (actual time=3.874..4.490 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
11. 0.244 3.537 ↓ 417.0 417 1

Nested Loop Left Join (cost=0.42..179.49 rows=1 width=206) (actual time=0.169..3.537 rows=417 loops=1)

  • Join Filter: NULL::boolean
12. 3.293 3.293 ↓ 417.0 417 1

Index Scan using idx_v_transit_roads_street_name on v_transit_roads (cost=0.42..179.48 rows=1 width=206) (actual time=0.165..3.293 rows=417 loops=1)

  • Index Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
  • Filter: ((geometry && '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry))
  • Rows Removed by Filter: 32
13. 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
14. 11,126.394 11,126.394 ↓ 1.8 9,159 417

Index Scan using idx_road_segments_link_id on road_segments s (cost=0.57..21,037.93 rows=5,224 width=32) (actual time=0.041..26.682 rows=9,159 loops=417)

  • Index Cond: (link_id = ((v_transit_roads.link_id)::integer))
Planning time : 1.779 ms