explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8e03

Settings
# exclusive inclusive rows x rows loops node
1. 3.747 6,502.439 ↑ 1.0 1 1

Aggregate (cost=29,107,280.91..29,107,281.11 rows=1 width=32) (actual time=6,502.439..6,502.439 rows=1 loops=1)

  • Functions: 278
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 28.037 ms, Inlining 25.026 ms, Optimization 963.532 ms, Emission 579.259 ms, Total 1595.854 ms"Execution Time: 6531.722 ms
2. 0.789 6,498.692 ↑ 4.7 1,126 1

Subquery Scan on q (cost=29,103,455.14..29,107,265.29 rows=5,247 width=44) (actual time=6,455.196..6,498.692 rows=1,126 loops=1)

3. 41.926 6,497.903 ↑ 4.7 1,126 1

GroupAggregate (cost=29,103,455.14..29,106,215.89 rows=5,247 width=255) (actual time=6,455.183..6,497.903 rows=1,126 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. 12.122 6,455.977 ↑ 3.2 7,996 1

Sort (cost=29,103,455.14..29,103,519.21 rows=25,630 width=211) (actual time=6,454.949..6,455.977 rows=7,996 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: 2398kB
5. 2.335 6,443.855 ↑ 3.2 7,996 1

Nested Loop Left Join (cost=138,155.80..29,101,578.31 rows=25,630 width=211) (actual time=1,745.853..6,443.855 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
6. 776.585 6,441.520 ↑ 3.2 7,996 1

Hash Join (cost=138,155.80..29,096,452.31 rows=25,630 width=211) (actual time=1,745.848..6,441.520 rows=7,996 loops=1)

  • Hash Cond: (road_segments_partitioned_0.link_id = v_transit_roads.link_id)
7. 703.040 5,644.963 ↓ 1.7 7,329,912 1

Nested Loop (cost=0.86..28,947,126.58 rows=4,255,157 width=32) (actual time=1,569.811..5,644.963 rows=7,329,912 loops=1)

8. 6.323 6.323 ↑ 1.0 4,113 1

Index Scan using idx_road_movements_commod_id on road_movements (cost=0.42..4,733.15 rows=4,176 width=32) (actual time=0.031..6.323 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
9. 2,171.664 4,935.600 ↑ 2.8 1,782 4,113

Append (cost=0.43..5,927.45 rows=5,016 width=16) (actual time=0.389..1.200 rows=1,782 loops=4,113)

10. 57.582 57.582 ↑ 2.7 36 4,113

Index Scan using road_segments_partitioned_0_trip_id_idx on road_segments_partitioned_0 (cost=0.43..105.97 rows=98 width=16) (actual time=0.007..0.014 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
11. 53.469 53.469 ↑ 2.7 35 4,113

Index Scan using road_segments_partitioned_1_trip_id_idx on road_segments_partitioned_1 (cost=0.43..101.81 rows=94 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
12. 57.582 57.582 ↑ 2.6 36 4,113

Index Scan using road_segments_partitioned_2_trip_id_idx on road_segments_partitioned_2 (cost=0.43..101.82 rows=94 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
13. 53.469 53.469 ↑ 2.9 35 4,113

Index Scan using road_segments_partitioned_3_trip_id_idx on road_segments_partitioned_3 (cost=0.43..109.37 rows=101 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
14. 53.469 53.469 ↑ 3.0 36 4,113

Index Scan using road_segments_partitioned_4_trip_id_idx on road_segments_partitioned_4 (cost=0.43..117.78 rows=109 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
15. 53.469 53.469 ↑ 2.9 34 4,113

Index Scan using road_segments_partitioned_5_trip_id_idx on road_segments_partitioned_5 (cost=0.43..107.12 rows=99 width=16) (actual time=0.006..0.013 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
16. 53.469 53.469 ↑ 2.8 35 4,113

Index Scan using road_segments_partitioned_6_trip_id_idx on road_segments_partitioned_6 (cost=0.43..105.03 rows=97 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
17. 53.469 53.469 ↑ 2.8 36 4,113

Index Scan using road_segments_partitioned_7_trip_id_idx on road_segments_partitioned_7 (cost=0.43..108.17 rows=100 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
18. 49.356 49.356 ↑ 2.8 33 4,113

Index Scan using road_segments_partitioned_8_trip_id_idx on road_segments_partitioned_8 (cost=0.43..98.59 rows=91 width=16) (actual time=0.006..0.012 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
19. 49.356 49.356 ↑ 2.8 37 4,113

Index Scan using road_segments_partitioned_9_trip_id_idx on road_segments_partitioned_9 (cost=0.43..110.33 rows=102 width=16) (actual time=0.006..0.012 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
20. 53.469 53.469 ↑ 2.9 33 4,113

Index Scan using road_segments_partitioned_10_trip_id_idx on road_segments_partitioned_10 (cost=0.43..102.79 rows=95 width=16) (actual time=0.007..0.013 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
21. 57.582 57.582 ↑ 2.9 36 4,113

Index Scan using road_segments_partitioned_11_trip_id_idx on road_segments_partitioned_11 (cost=0.43..113.59 rows=105 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
22. 57.582 57.582 ↑ 2.7 39 4,113

Index Scan using road_segments_partitioned_12_trip_id_idx on road_segments_partitioned_12 (cost=0.43..114.61 rows=106 width=16) (actual time=0.006..0.014 rows=39 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
23. 57.582 57.582 ↑ 2.8 36 4,113

Index Scan using road_segments_partitioned_13_trip_id_idx on road_segments_partitioned_13 (cost=0.43..109.06 rows=101 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
24. 53.469 53.469 ↑ 2.9 35 4,113

Index Scan using road_segments_partitioned_14_trip_id_idx on road_segments_partitioned_14 (cost=0.43..111.35 rows=103 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
25. 53.469 53.469 ↑ 2.9 35 4,113

Index Scan using road_segments_partitioned_15_trip_id_idx on road_segments_partitioned_15 (cost=0.43..109.29 rows=101 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
26. 49.356 49.356 ↑ 2.8 36 4,113

Index Scan using road_segments_partitioned_16_trip_id_idx on road_segments_partitioned_16 (cost=0.43..107.12 rows=99 width=16) (actual time=0.006..0.012 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
27. 61.695 61.695 ↑ 2.8 37 4,113

Index Scan using road_segments_partitioned_17_trip_id_idx on road_segments_partitioned_17 (cost=0.43..110.34 rows=102 width=16) (actual time=0.006..0.015 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
28. 61.695 61.695 ↑ 2.9 36 4,113

Index Scan using road_segments_partitioned_18_trip_id_idx on road_segments_partitioned_18 (cost=0.43..111.33 rows=103 width=16) (actual time=0.006..0.015 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
29. 61.695 61.695 ↑ 2.8 34 4,113

Index Scan using road_segments_partitioned_19_trip_id_idx on road_segments_partitioned_19 (cost=0.43..103.94 rows=96 width=16) (actual time=0.006..0.015 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
30. 61.695 61.695 ↑ 2.9 36 4,113

Index Scan using road_segments_partitioned_20_trip_id_idx on road_segments_partitioned_20 (cost=0.43..111.40 rows=103 width=16) (actual time=0.006..0.015 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
31. 61.695 61.695 ↑ 3.0 35 4,113

Index Scan using road_segments_partitioned_21_trip_id_idx on road_segments_partitioned_21 (cost=0.43..112.46 rows=104 width=16) (actual time=0.006..0.015 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
32. 61.695 61.695 ↑ 2.8 37 4,113

Index Scan using road_segments_partitioned_22_trip_id_idx on road_segments_partitioned_22 (cost=0.43..112.34 rows=104 width=16) (actual time=0.006..0.015 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
33. 69.921 69.921 ↑ 2.7 38 4,113

Index Scan using road_segments_partitioned_23_trip_id_idx on road_segments_partitioned_23 (cost=0.43..112.24 rows=104 width=16) (actual time=0.006..0.017 rows=38 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
34. 53.469 53.469 ↑ 3.0 36 4,113

Index Scan using road_segments_partitioned_24_trip_id_idx on road_segments_partitioned_24 (cost=0.43..115.72 rows=107 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
35. 49.356 49.356 ↑ 2.8 35 4,113

Index Scan using road_segments_partitioned_25_trip_id_idx on road_segments_partitioned_25 (cost=0.43..107.17 rows=99 width=16) (actual time=0.006..0.012 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
36. 49.356 49.356 ↑ 2.9 33 4,113

Index Scan using road_segments_partitioned_26_trip_id_idx on road_segments_partitioned_26 (cost=0.43..104.97 rows=97 width=16) (actual time=0.006..0.012 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
37. 57.582 57.582 ↑ 2.9 36 4,113

Index Scan using road_segments_partitioned_27_trip_id_idx on road_segments_partitioned_27 (cost=0.43..112.51 rows=104 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
38. 53.469 53.469 ↑ 2.7 36 4,113

Index Scan using road_segments_partitioned_28_trip_id_idx on road_segments_partitioned_28 (cost=0.43..103.96 rows=96 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
39. 57.582 57.582 ↑ 2.6 37 4,113

Index Scan using road_segments_partitioned_29_trip_id_idx on road_segments_partitioned_29 (cost=0.43..105.03 rows=97 width=16) (actual time=0.006..0.014 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
40. 57.582 57.582 ↑ 2.8 36 4,113

Index Scan using road_segments_partitioned_30_trip_id_idx on road_segments_partitioned_30 (cost=0.43..108.23 rows=100 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
41. 53.469 53.469 ↑ 2.8 35 4,113

Index Scan using road_segments_partitioned_31_trip_id_idx on road_segments_partitioned_31 (cost=0.43..107.06 rows=99 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
42. 53.469 53.469 ↑ 2.9 35 4,113

Index Scan using road_segments_partitioned_32_trip_id_idx on road_segments_partitioned_32 (cost=0.43..110.32 rows=102 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
43. 53.469 53.469 ↑ 2.7 35 4,113

Index Scan using road_segments_partitioned_33_trip_id_idx on road_segments_partitioned_33 (cost=0.43..103.83 rows=96 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
44. 53.469 53.469 ↑ 2.9 34 4,113

Index Scan using road_segments_partitioned_34_trip_id_idx on road_segments_partitioned_34 (cost=0.43..107.10 rows=99 width=16) (actual time=0.006..0.013 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
45. 53.469 53.469 ↑ 2.7 34 4,113

Index Scan using road_segments_partitioned_35_trip_id_idx on road_segments_partitioned_35 (cost=0.43..99.63 rows=92 width=16) (actual time=0.006..0.013 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
46. 53.469 53.469 ↑ 2.7 35 4,113

Index Scan using road_segments_partitioned_36_trip_id_idx on road_segments_partitioned_36 (cost=0.43..102.91 rows=95 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
47. 53.469 53.469 ↑ 2.9 33 4,113

Index Scan using road_segments_partitioned_37_trip_id_idx on road_segments_partitioned_37 (cost=0.43..103.85 rows=96 width=16) (actual time=0.006..0.013 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
48. 49.356 49.356 ↑ 2.7 35 4,113

Index Scan using road_segments_partitioned_38_trip_id_idx on road_segments_partitioned_38 (cost=0.43..101.77 rows=94 width=16) (actual time=0.006..0.012 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
49. 57.582 57.582 ↑ 2.8 37 4,113

Index Scan using road_segments_partitioned_39_trip_id_idx on road_segments_partitioned_39 (cost=0.43..110.34 rows=102 width=16) (actual time=0.006..0.014 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
50. 53.469 53.469 ↑ 2.8 38 4,113

Index Scan using road_segments_partitioned_40_trip_id_idx on road_segments_partitioned_40 (cost=0.43..115.68 rows=107 width=16) (actual time=0.006..0.013 rows=38 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
51. 53.469 53.469 ↑ 3.0 38 4,113

Index Scan using road_segments_partitioned_41_trip_id_idx on road_segments_partitioned_41 (cost=0.43..121.96 rows=113 width=16) (actual time=0.006..0.013 rows=38 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
52. 57.582 57.582 ↑ 2.6 39 4,113

Index Scan using road_segments_partitioned_42_trip_id_idx on road_segments_partitioned_42 (cost=0.43..111.39 rows=103 width=16) (actual time=0.006..0.014 rows=39 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
53. 57.582 57.582 ↑ 3.0 36 4,113

Index Scan using road_segments_partitioned_43_trip_id_idx on road_segments_partitioned_43 (cost=0.43..117.71 rows=109 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
54. 53.469 53.469 ↑ 2.9 35 4,113

Index Scan using road_segments_partitioned_44_trip_id_idx on road_segments_partitioned_44 (cost=0.43..108.15 rows=100 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
55. 53.469 53.469 ↑ 2.8 36 4,113

Index Scan using road_segments_partitioned_45_trip_id_idx on road_segments_partitioned_45 (cost=0.43..110.34 rows=102 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
56. 53.469 53.469 ↑ 2.9 33 4,113

Index Scan using road_segments_partitioned_46_trip_id_idx on road_segments_partitioned_46 (cost=0.43..102.86 rows=95 width=16) (actual time=0.006..0.013 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
57. 53.469 53.469 ↑ 2.7 36 4,113

Index Scan using road_segments_partitioned_47_trip_id_idx on road_segments_partitioned_47 (cost=0.43..103.92 rows=96 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
58. 53.469 53.469 ↑ 2.7 36 4,113

Index Scan using road_segments_partitioned_48_trip_id_idx on road_segments_partitioned_48 (cost=0.43..104.93 rows=97 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
59. 57.582 57.582 ↑ 2.9 37 4,113

Index Scan using road_segments_partitioned_49_trip_id_idx on road_segments_partitioned_49 (cost=0.43..116.66 rows=108 width=16) (actual time=0.006..0.014 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
60. 3.205 19.972 ↑ 1.1 4,871 1

Hash (cost=137,092.43..137,092.43 rows=5,247 width=187) (actual time=19.971..19.972 rows=4,871 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 864kB
61. 16.767 16.767 ↑ 1.1 4,871 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..137,092.43 rows=5,247 width=187) (actual time=0.124..16.767 rows=4,871 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
62. 0.000 0.000 ↓ 0.0 0 7,996

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=7,996)

  • One-Time Filter: false