explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uHOD : transitweb.it.csiro.au CLA road tile test

Settings
# exclusive inclusive rows x rows loops node
1. 5.728 9,132.238 ↑ 1.0 1 1

Aggregate (cost=1,196,570.93..1,196,570.94 rows=1 width=32) (actual time=9,132.238..9,132.238 rows=1 loops=1)

2. 1.117 9,126.510 ↓ 1,333.0 1,333 1

Subquery Scan on q (cost=1,196,553.97..1,196,570.93 rows=1 width=68) (actual time=6,656.498..9,126.510 rows=1,333 loops=1)

3. 1,702.841 9,125.393 ↓ 1,333.0 1,333 1

GroupAggregate (cost=1,196,553.97..1,196,570.92 rows=1 width=234) (actual time=6,656.489..9,125.393 rows=1,333 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.38 rows=3,338 width=66,619) (never executed)

6. 2,407.367 7,422.552 ↓ 2,204.1 744,980 1

Sort (cost=1,195,643.59..1,195,644.44 rows=338 width=201) (actual time=6,656.246..7,422.552 rows=744,980 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: external merge Disk: 136720kB
7. 201.154 5,015.185 ↓ 2,204.1 744,980 1

Nested Loop Left Join (cost=1,187,403.67..1,195,629.40 rows=338 width=201) (actual time=1,221.383..5,015.185 rows=744,980 loops=1)

  • Join Filter: NULL::boolean
8. 605.233 4,814.031 ↓ 2,204.1 744,980 1

Nested Loop Left Join (cost=1,187,403.67..1,195,626.02 rows=338 width=201) (actual time=1,221.381..4,814.031 rows=744,980 loops=1)

9. 2,239.808 3,463.818 ↓ 2,204.1 744,980 1

Hash Right Join (cost=1,187,403.25..1,193,416.07 rows=338 width=198) (actual time=1,221.367..3,463.818 rows=744,980 loops=1)

  • Hash Cond: (v_transit_movement_extras.trip_id = tblsegments.trip_id)
10. 24.160 24.160 ↑ 1.0 298,650 1

Seq Scan on v_transit_movement_extras (cost=0.00..4,889.50 rows=298,650 width=12) (actual time=0.006..24.160 rows=298,650 loops=1)

11. 295.109 1,199.850 ↓ 2,204.1 744,980 1

Hash (cost=1,187,399.02..1,187,399.02 rows=338 width=194) (actual time=1,199.849..1,199.850 rows=744,980 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 64 (originally 1) Memory Usage: 3841kB
12. 89.001 904.741 ↓ 2,204.1 744,980 1

Nested Loop (cost=1,186,945.43..1,187,399.02 rows=338 width=194) (actual time=705.645..904.741 rows=744,980 loops=1)

13. 15.578 713.099 ↓ 1,333.0 1,333 1

Bitmap Heap Scan on v_transit_roads (cost=1,186,944.86..1,186,978.58 rows=1 width=186) (actual time=705.627..713.099 rows=1,333 loops=1)

  • Recheck Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
  • Filter: ((hashed SubPlan 2) AND _st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
  • Rows Removed by Filter: 1272
  • Heap Blocks: exact=579
14. 0.586 0.586 ↓ 325.6 2,605 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..4.37 rows=8 width=0) (actual time=0.586..0.586 rows=2,605 loops=1)

  • Index Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
15.          

SubPlan (forBitmap Heap Scan)

16. 304.592 696.935 ↓ 1.3 35,084 1

HashAggregate (cost=1,186,605.47..1,186,873.49 rows=26,802 width=8) (actual time=691.742..696.935 rows=35,084 loops=1)

  • Group Key: tblsegments_1.link_id
17. 152.100 392.343 ↑ 14.8 1,763,521 1

Nested Loop (cost=1.14..1,121,421.41 rows=26,073,626 width=8) (actual time=0.039..392.343 rows=1,763,521 loops=1)

18. 0.207 0.207 ↑ 10.0 964 1

Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_2 (cost=0.57..321.53 rows=9,655 width=8) (actual time=0.017..0.207 rows=964 loops=1)

  • Index Cond: (link_id = 945895182)
  • Heap Fetches: 0
19. 240.036 240.036 ↑ 1.5 1,829 964

Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1 (cost=0.57..89.30 rows=2,682 width=16) (actual time=0.009..0.249 rows=1,829 loops=964)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
20. 102.641 102.641 ↑ 17.4 559 1,333

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..323.04 rows=9,741 width=16) (actual time=0.006..0.077 rows=559 loops=1,333)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
21. 744.980 744.980 ↑ 1.0 1 744,980

Index Scan using tblmovements_pkey on tblmovements (cost=0.42..6.54 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=744,980)

  • Index Cond: (tblsegments.trip_id = trip_id)
22. 0.000 0.000 ↓ 0.0 0 744,980

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=744,980)

  • One-Time Filter: false