explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2T8Z

Settings
# exclusive inclusive rows x rows loops node
1. 1.273 1,989.863 ↑ 1.0 1 1

Aggregate (cost=3,269,057.02..3,269,057.22 rows=1 width=32) (actual time=1,989.863..1,989.863 rows=1 loops=1)

  • Functions: 290
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 31.700 ms, Inlining 24.110 ms, Optimization 974.249 ms, Emission 605.641 ms, Total 1635.701 ms"Execution Time: 2023.032 ms
2. 0.260 1,988.590 ↑ 18.0 407 1

Subquery Scan on q (cost=3,265,676.28..3,269,036.16 rows=7,344 width=44) (actual time=1,986.061..1,988.590 rows=407 loops=1)

3. 7.014 1,988.330 ↑ 18.0 407 1

HashAggregate (cost=3,265,676.28..3,267,567.36 rows=7,344 width=255) (actual time=1,986.051..1,988.330 rows=407 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. 0.271 1,981.316 ↑ 3,074.9 814 1

Nested Loop Left Join (cost=456,999.72..3,134,272.35 rows=2,502,932 width=211) (actual time=1,976.022..1,981.316 rows=814 loops=1)

  • Join Filter: NULL::boolean
5. 1.266 1,981.045 ↑ 3,074.9 814 1

Hash Join (cost=456,999.72..2,633,685.95 rows=2,502,932 width=211) (actual time=1,976.017..1,981.045 rows=814 loops=1)

  • Hash Cond: (road_segments_partitioned_0.trip_id = road_movements.trip_id)
6. 0.668 23.277 ↑ 3,074.9 814 1

Hash Join (cost=195,699.91..2,365,815.14 rows=2,502,932 width=203) (actual time=18.506..23.277 rows=814 loops=1)

  • Hash Cond: (road_segments_partitioned_0.link_id = v_transit_roads.link_id)
7. 0.380 4.240 ↑ 93,743.4 3,167 1

Nested Loop (cost=2,436.63..1,393,159.18 rows=296,885,370 width=24) (actual time=0.100..4.240 rows=3,167 loops=1)

8. 0.018 0.073 ↑ 28.6 7 1

HashAggregate (cost=2,436.19..2,476.19 rows=200 width=8) (actual time=0.065..0.073 rows=7 loops=1)

  • Group Key: road_segments_partitioned_17_1.trip_id
9. 0.055 0.055 ↑ 290.4 7 1

Index Only Scan using road_segments_partitioned_17_pkey on road_segments_partitioned_17 road_segments_partitioned_17_1 (cost=0.43..2,431.11 rows=2,033 width=8) (actual time=0.040..0.055 rows=7 loops=1)

  • Index Cond: (link_id = 1201710493)
  • Heap Fetches: 7
10. 0.546 3.787 ↑ 11.1 452 7

Append (cost=0.43..5,950.21 rows=5,016 width=16) (actual time=0.010..0.541 rows=452 loops=7)

11. 0.084 0.084 ↑ 8.9 11 7

Index Scan using road_segments_partitioned_0_trip_id_idx on road_segments_partitioned_0 (cost=0.43..106.41 rows=98 width=16) (actual time=0.009..0.012 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
12. 0.077 0.077 ↑ 8.5 11 7

Index Scan using road_segments_partitioned_1_trip_id_idx on road_segments_partitioned_1 (cost=0.43..102.25 rows=94 width=16) (actual time=0.008..0.011 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
13. 0.070 0.070 ↑ 8.5 11 7

Index Scan using road_segments_partitioned_2_trip_id_idx on road_segments_partitioned_2 (cost=0.43..102.25 rows=94 width=16) (actual time=0.007..0.010 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
14. 0.070 0.070 ↑ 12.6 8 7

Index Scan using road_segments_partitioned_3_trip_id_idx on road_segments_partitioned_3 (cost=0.43..109.83 rows=101 width=16) (actual time=0.008..0.010 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
15. 0.063 0.063 ↑ 13.6 8 7

Index Scan using road_segments_partitioned_4_trip_id_idx on road_segments_partitioned_4 (cost=0.43..118.27 rows=109 width=16) (actual time=0.006..0.009 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
16. 0.063 0.063 ↑ 12.4 8 7

Index Scan using road_segments_partitioned_5_trip_id_idx on road_segments_partitioned_5 (cost=0.43..107.57 rows=99 width=16) (actual time=0.006..0.009 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
17. 0.070 0.070 ↑ 8.8 11 7

Index Scan using road_segments_partitioned_6_trip_id_idx on road_segments_partitioned_6 (cost=0.43..105.48 rows=97 width=16) (actual time=0.007..0.010 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
18. 0.070 0.070 ↑ 10.0 10 7

Index Scan using road_segments_partitioned_7_trip_id_idx on road_segments_partitioned_7 (cost=0.43..108.62 rows=100 width=16) (actual time=0.007..0.010 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
19. 0.063 0.063 ↑ 9.1 10 7

Index Scan using road_segments_partitioned_8_trip_id_idx on road_segments_partitioned_8 (cost=0.43..99.01 rows=91 width=16) (actual time=0.006..0.009 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
20. 0.056 0.056 ↑ 17.0 6 7

Index Scan using road_segments_partitioned_9_trip_id_idx on road_segments_partitioned_9 (cost=0.43..110.79 rows=102 width=16) (actual time=0.006..0.008 rows=6 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
21. 0.063 0.063 ↑ 9.5 10 7

Index Scan using road_segments_partitioned_10_trip_id_idx on road_segments_partitioned_10 (cost=0.43..103.23 rows=95 width=16) (actual time=0.007..0.009 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
22. 0.070 0.070 ↑ 10.5 10 7

Index Scan using road_segments_partitioned_11_trip_id_idx on road_segments_partitioned_11 (cost=0.43..114.06 rows=105 width=16) (actual time=0.007..0.010 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
23. 0.063 0.063 ↑ 17.7 6 7

Index Scan using road_segments_partitioned_12_trip_id_idx on road_segments_partitioned_12 (cost=0.43..115.08 rows=106 width=16) (actual time=0.007..0.009 rows=6 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
24. 0.056 0.056 ↑ 12.6 8 7

Index Scan using road_segments_partitioned_13_trip_id_idx on road_segments_partitioned_13 (cost=0.43..109.52 rows=101 width=16) (actual time=0.006..0.008 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
25. 0.063 0.063 ↑ 25.8 4 7

Index Scan using road_segments_partitioned_14_trip_id_idx on road_segments_partitioned_14 (cost=0.43..111.81 rows=103 width=16) (actual time=0.008..0.009 rows=4 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
26. 0.070 0.070 ↑ 11.2 9 7

Index Scan using road_segments_partitioned_15_trip_id_idx on road_segments_partitioned_15 (cost=0.43..109.75 rows=101 width=16) (actual time=0.007..0.010 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
27. 0.070 0.070 ↑ 12.4 8 7

Index Scan using road_segments_partitioned_16_trip_id_idx on road_segments_partitioned_16 (cost=0.43..107.57 rows=99 width=16) (actual time=0.008..0.010 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
28. 0.063 0.063 ↑ 10.2 10 7

Index Scan using road_segments_partitioned_17_trip_id_idx on road_segments_partitioned_17 (cost=0.43..110.80 rows=102 width=16) (actual time=0.006..0.009 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
29. 0.070 0.070 ↑ 10.3 10 7

Index Scan using road_segments_partitioned_18_trip_id_idx on road_segments_partitioned_18 (cost=0.43..111.80 rows=103 width=16) (actual time=0.007..0.010 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
30. 0.056 0.056 ↑ 16.0 6 7

Index Scan using road_segments_partitioned_19_trip_id_idx on road_segments_partitioned_19 (cost=0.43..104.38 rows=96 width=16) (actual time=0.006..0.008 rows=6 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
31. 0.070 0.070 ↑ 11.4 9 7

Index Scan using road_segments_partitioned_20_trip_id_idx on road_segments_partitioned_20 (cost=0.43..111.86 rows=103 width=16) (actual time=0.007..0.010 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
32. 0.070 0.070 ↑ 8.7 12 7

Index Scan using road_segments_partitioned_21_trip_id_idx on road_segments_partitioned_21 (cost=0.43..112.92 rows=104 width=16) (actual time=0.006..0.010 rows=12 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
33. 0.070 0.070 ↑ 9.5 11 7

Index Scan using road_segments_partitioned_22_trip_id_idx on road_segments_partitioned_22 (cost=0.43..112.81 rows=104 width=16) (actual time=0.007..0.010 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
34. 0.063 0.063 ↑ 9.5 11 7

Index Scan using road_segments_partitioned_23_trip_id_idx on road_segments_partitioned_23 (cost=0.43..112.71 rows=104 width=16) (actual time=0.006..0.009 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
35. 0.077 0.077 ↑ 8.2 13 7

Index Scan using road_segments_partitioned_24_trip_id_idx on road_segments_partitioned_24 (cost=0.43..116.20 rows=107 width=16) (actual time=0.006..0.011 rows=13 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
36. 0.056 0.056 ↑ 19.8 5 7

Index Scan using road_segments_partitioned_25_trip_id_idx on road_segments_partitioned_25 (cost=0.43..107.62 rows=99 width=16) (actual time=0.006..0.008 rows=5 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
37. 0.063 0.063 ↑ 12.1 8 7

Index Scan using road_segments_partitioned_26_trip_id_idx on road_segments_partitioned_26 (cost=0.43..105.42 rows=97 width=16) (actual time=0.007..0.009 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
38. 0.063 0.063 ↑ 13.0 8 7

Index Scan using road_segments_partitioned_27_trip_id_idx on road_segments_partitioned_27 (cost=0.43..112.98 rows=104 width=16) (actual time=0.007..0.009 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
39. 0.056 0.056 ↑ 24.0 4 7

Index Scan using road_segments_partitioned_28_trip_id_idx on road_segments_partitioned_28 (cost=0.43..104.40 rows=96 width=16) (actual time=0.006..0.008 rows=4 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
40. 0.056 0.056 ↑ 10.8 9 7

Index Scan using road_segments_partitioned_29_trip_id_idx on road_segments_partitioned_29 (cost=0.43..105.47 rows=97 width=16) (actual time=0.006..0.008 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
41. 0.056 0.056 ↑ 14.3 7 7

Index Scan using road_segments_partitioned_30_trip_id_idx on road_segments_partitioned_30 (cost=0.43..108.69 rows=100 width=16) (actual time=0.006..0.008 rows=7 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
42. 0.070 0.070 ↑ 9.0 11 7

Index Scan using road_segments_partitioned_31_trip_id_idx on road_segments_partitioned_31 (cost=0.43..107.51 rows=99 width=16) (actual time=0.006..0.010 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
43. 0.056 0.056 ↑ 12.8 8 7

Index Scan using road_segments_partitioned_32_trip_id_idx on road_segments_partitioned_32 (cost=0.43..110.77 rows=102 width=16) (actual time=0.006..0.008 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
44. 0.070 0.070 ↑ 10.7 9 7

Index Scan using road_segments_partitioned_33_trip_id_idx on road_segments_partitioned_33 (cost=0.43..104.27 rows=96 width=16) (actual time=0.007..0.010 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
45. 0.063 0.063 ↑ 11.0 9 7

Index Scan using road_segments_partitioned_34_trip_id_idx on road_segments_partitioned_34 (cost=0.43..107.55 rows=99 width=16) (actual time=0.007..0.009 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
46. 0.077 0.077 ↑ 7.7 12 7

Index Scan using road_segments_partitioned_35_trip_id_idx on road_segments_partitioned_35 (cost=0.43..100.05 rows=92 width=16) (actual time=0.007..0.011 rows=12 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
47. 0.070 0.070 ↑ 11.9 8 7

Index Scan using road_segments_partitioned_36_trip_id_idx on road_segments_partitioned_36 (cost=0.43..103.35 rows=95 width=16) (actual time=0.008..0.010 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
48. 0.056 0.056 ↑ 10.7 9 7

Index Scan using road_segments_partitioned_37_trip_id_idx on road_segments_partitioned_37 (cost=0.43..104.29 rows=96 width=16) (actual time=0.006..0.008 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
49. 0.063 0.063 ↑ 10.4 9 7

Index Scan using road_segments_partitioned_38_trip_id_idx on road_segments_partitioned_38 (cost=0.43..102.20 rows=94 width=16) (actual time=0.006..0.009 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
50. 0.070 0.070 ↑ 11.3 9 7

Index Scan using road_segments_partitioned_39_trip_id_idx on road_segments_partitioned_39 (cost=0.43..110.80 rows=102 width=16) (actual time=0.007..0.010 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
51. 0.063 0.063 ↑ 10.7 10 7

Index Scan using road_segments_partitioned_40_trip_id_idx on road_segments_partitioned_40 (cost=0.43..116.16 rows=107 width=16) (actual time=0.006..0.009 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
52. 0.063 0.063 ↑ 11.3 10 7

Index Scan using road_segments_partitioned_41_trip_id_idx on road_segments_partitioned_41 (cost=0.43..122.46 rows=113 width=16) (actual time=0.006..0.009 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
53. 0.056 0.056 ↑ 11.4 9 7

Index Scan using road_segments_partitioned_42_trip_id_idx on road_segments_partitioned_42 (cost=0.43..111.86 rows=103 width=16) (actual time=0.006..0.008 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
54. 0.056 0.056 ↑ 9.9 11 7

Index Scan using road_segments_partitioned_43_trip_id_idx on road_segments_partitioned_43 (cost=0.43..118.19 rows=109 width=16) (actual time=0.006..0.008 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
55. 0.063 0.063 ↑ 12.5 8 7

Index Scan using road_segments_partitioned_44_trip_id_idx on road_segments_partitioned_44 (cost=0.43..108.61 rows=100 width=16) (actual time=0.007..0.009 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
56. 0.063 0.063 ↑ 9.3 11 7

Index Scan using road_segments_partitioned_45_trip_id_idx on road_segments_partitioned_45 (cost=0.43..110.79 rows=102 width=16) (actual time=0.006..0.009 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
57. 0.056 0.056 ↑ 10.6 9 7

Index Scan using road_segments_partitioned_46_trip_id_idx on road_segments_partitioned_46 (cost=0.43..103.30 rows=95 width=16) (actual time=0.006..0.008 rows=9 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
58. 0.070 0.070 ↑ 12.0 8 7

Index Scan using road_segments_partitioned_47_trip_id_idx on road_segments_partitioned_47 (cost=0.43..104.36 rows=96 width=16) (actual time=0.007..0.010 rows=8 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
59. 0.063 0.063 ↑ 9.7 10 7

Index Scan using road_segments_partitioned_48_trip_id_idx on road_segments_partitioned_48 (cost=0.43..105.38 rows=97 width=16) (actual time=0.006..0.009 rows=10 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
60. 0.063 0.063 ↑ 9.8 11 7

Index Scan using road_segments_partitioned_49_trip_id_idx on road_segments_partitioned_49 (cost=0.43..117.14 rows=108 width=16) (actual time=0.006..0.009 rows=11 loops=7)

  • Index Cond: (trip_id = road_segments_partitioned_17_1.trip_id)
61. 2.725 18.369 ↑ 1.5 4,841 1

Hash (cost=191,776.12..191,776.12 rows=7,344 width=187) (actual time=18.369..18.369 rows=4,841 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 828kB
62. 15.644 15.644 ↑ 1.5 4,841 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..191,776.12 rows=7,344 width=187) (actual time=0.135..15.644 rows=4,841 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Filter: st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B53270415C8FE237662F48C1E17ADC72B5327041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C13333FF6E433C70415C8FE237662F48C1E17ADC72B53270415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 2
63. 163.452 1,956.502 ↑ 1.0 582,725 1

Hash (cost=143,298.00..143,298.00 rows=582,725 width=32) (actual time=1,956.501..1,956.502 rows=582,725 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 44613kB
64. 1,793.050 1,793.050 ↑ 1.0 582,725 1

Seq Scan on road_movements (cost=0.00..143,298.00 rows=582,725 width=32) (actual time=1,607.381..1,793.050 rows=582,725 loops=1)

65. 0.000 0.000 ↓ 0.0 0 814

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

  • One-Time Filter: false