explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QME

Settings
# exclusive inclusive rows x rows loops node
1. 4.563 175,718.705 ↑ 1.0 1 1

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

  • Functions: 278
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 40.858 ms, Inlining 23.975 ms, Optimization 924.851 ms, Emission 568.677 ms, Total 1558.361 ms"Execution Time: 175771.158 ms
2. 0.808 175,714.142 ↑ 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=175,671.536..175,714.142 rows=1,126 loops=1)

3. 58.754 175,713.334 ↑ 4.7 1,126 1

GroupAggregate (cost=29,103,455.14..29,106,215.89 rows=5,247 width=255) (actual time=175,671.512..175,713.334 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. 16.957 175,654.580 ↑ 3.2 7,996 1

Sort (cost=29,103,455.14..29,103,519.21 rows=25,630 width=211) (actual time=175,653.138..175,654.580 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. 3.307 175,637.623 ↑ 3.2 7,996 1

Nested Loop Left Join (cost=138,155.80..29,101,578.31 rows=25,630 width=211) (actual time=10,088.788..175,637.623 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
6. 1,560.584 175,634.316 ↑ 3.2 7,996 1

Hash Join (cost=138,155.80..29,096,452.31 rows=25,630 width=211) (actual time=10,088.782..175,634.316 rows=7,996 loops=1)

  • Hash Cond: (road_segments_partitioned_0.link_id = v_transit_roads.link_id)
7. 798.688 174,054.585 ↓ 1.7 7,329,912 1

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

8. 12.224 12.224 ↑ 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.033..12.224 rows=4,113 loops=1)

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

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

10. 19,688.931 19,688.931 ↑ 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=3.446..4.787 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
11. 11,401.236 11,401.236 ↑ 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=1.816..2.772 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
12. 9,225.459 9,225.459 ↑ 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=1.518..2.243 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
13. 6,774.111 6,774.111 ↑ 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=1.053..1.647 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
14. 5,865.138 5,865.138 ↑ 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=1.033..1.426 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
15. 5,137.137 5,137.137 ↑ 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.907..1.249 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
16. 5,153.589 5,153.589 ↑ 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.867..1.253 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
17. 3,903.237 3,903.237 ↑ 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.630..0.949 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
18. 2,529.495 2,529.495 ↑ 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.452..0.615 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
19. 3,537.180 3,537.180 ↑ 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.469..0.860 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
20. 2,903.778 2,903.778 ↑ 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.532..0.706 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
21. 2,710.467 2,710.467 ↑ 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.375..0.659 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
22. 3,899.124 3,899.124 ↑ 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.606..0.948 rows=39 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
23. 3,483.711 3,483.711 ↑ 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.444..0.847 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
24. 2,916.117 2,916.117 ↑ 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.505..0.709 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
25. 2,652.885 2,652.885 ↑ 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.381..0.645 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
26. 1,813.833 1,813.833 ↑ 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.407..0.441 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
27. 3,442.581 3,442.581 ↑ 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.607..0.837 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
28. 2,389.653 2,389.653 ↑ 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.426..0.581 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
29. 2,286.828 2,286.828 ↑ 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.514..0.556 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
30. 2,196.342 2,196.342 ↑ 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.403..0.534 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
31. 1,789.155 1,789.155 ↑ 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.351..0.435 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
32. 2,821.518 2,821.518 ↑ 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.455..0.686 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
33. 2,846.196 2,846.196 ↑ 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.414..0.692 rows=38 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
34. 2,731.032 2,731.032 ↑ 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.470..0.664 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
35. 2,484.252 2,484.252 ↑ 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.423..0.604 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
36. 1,928.997 1,928.997 ↑ 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.375..0.469 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
37. 2,056.500 2,056.500 ↑ 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.374..0.500 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
38. 2,031.822 2,031.822 ↑ 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.411..0.494 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
39. 2,496.591 2,496.591 ↑ 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.393..0.607 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
40. 3,360.321 3,360.321 ↑ 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.448..0.817 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
41. 2,648.772 2,648.772 ↑ 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.455..0.644 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
42. 1,937.223 1,937.223 ↑ 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.405..0.471 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
43. 1,822.059 1,822.059 ↑ 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.377..0.443 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
44. 2,578.851 2,578.851 ↑ 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.444..0.627 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
45. 2,081.178 2,081.178 ↑ 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.394..0.506 rows=34 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
46. 1,595.844 1,595.844 ↑ 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.372..0.388 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
47. 2,377.314 2,377.314 ↑ 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.472..0.578 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
48. 2,208.681 2,208.681 ↑ 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.521..0.537 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
49. 2,439.009 2,439.009 ↑ 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.423..0.593 rows=37 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
50. 3,195.801 3,195.801 ↑ 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.534..0.777 rows=38 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
51. 3,323.304 3,323.304 ↑ 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.600..0.808 rows=38 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
52. 2,130.534 2,130.534 ↑ 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.452..0.518 rows=39 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
53. 1,994.805 1,994.805 ↑ 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.370..0.485 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
54. 2,327.958 2,327.958 ↑ 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.387..0.566 rows=35 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
55. 1,961.901 1,961.901 ↑ 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.442..0.477 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
56. 1,863.189 1,863.189 ↑ 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.438..0.453 rows=33 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
57. 1,468.341 1,468.341 ↑ 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.342..0.357 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
58. 1,838.511 1,838.511 ↑ 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.366..0.447 rows=36 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
59. 2,521.269 2,521.269 ↑ 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.414..0.613 rows=37 loops=4,113)

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 864kB
61. 16.391 16.391 ↑ 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.103..16.391 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