explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nLmc

Settings
# exclusive inclusive rows x rows loops node
1. 0.038 3,473.117 ↑ 4.3 47 1

Merge Left Join (cost=359,915,350.43..378,976,220.11 rows=200 width=242) (actual time=3,471.312..3,473.117 rows=47 loops=1)

  • Merge Cond: ((segments.street_name)::text = (roadtrips.street_name)::text)
  • Functions: 1141
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 158.420 ms, Inlining 373.146 ms, Optimization 6413.639 ms, Emission 3807.789 ms, Total 10752.994 ms"Execution Time: 3822.006 ms
2.          

CTE segments

3. 121.091 3,383.032 ↑ 93,743.4 3,167 1

Gather (cost=144,893.82..54,568,420.73 rows=296,885,370 width=53) (actual time=3,287.479..3,383.032 rows=3,167 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
4. 0.239 3,261.941 ↑ 120,921.1 792 4 / 4

Nested Loop Left Join (cost=143,893.82..24,878,883.73 rows=95,769,474 width=53) (actual time=3,184.334..3,261.941 rows=792 loops=4)

  • Join Filter: NULL::boolean
5. 7.807 3,261.702 ↑ 120,921.1 792 4 / 4

Parallel Hash Join (cost=143,893.82..5,006,717.87 rows=95,769,474 width=57) (actual time=3,184.329..3,261.702 rows=792 loops=4)

  • Hash Cond: (road_segments_partitioned_0.link_id = v_transit_roads.link_id)
6. 0.121 100.970 ↑ 120,921.1 792 4 / 4

Nested Loop (cost=2,517.13..4,613,924.19 rows=95,769,474 width=32) (actual time=25.952..100.970 rows=792 loops=4)

7. 16.263 94.642 ↑ 328.0 2 4 / 4

Hash Join (cost=2,516.69..67,490.23 rows=656 width=32) (actual time=25.892..94.642 rows=2 loops=4)

  • Hash Cond: (road_movements.trip_id = road_segments_partitioned_17_1.trip_id)
8. 76.769 76.769 ↑ 1.3 145,681 4 / 4

Parallel Seq Scan on road_movements (cost=0.00..64,348.16 rows=187,976 width=24) (actual time=0.017..76.769 rows=145,681 loops=4)

9. 0.005 1.610 ↑ 28.6 7 4 / 4

Hash (cost=2,476.19..2,476.19 rows=200 width=8) (actual time=1.610..1.610 rows=7 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.014 1.605 ↑ 28.6 7 4 / 4

HashAggregate (cost=2,436.19..2,476.19 rows=200 width=8) (actual time=1.603..1.605 rows=7 loops=4)

  • Group Key: road_segments_partitioned_17_1.trip_id
11. 1.591 1.591 ↑ 290.4 7 4 / 4

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=1.579..1.591 rows=7 loops=4)

  • Index Cond: (link_id = 1201710493)
  • Heap Fetches: 28
12. 0.263 6.207 ↑ 11.1 452 7 / 4

Append (cost=0.43..5,927.34 rows=5,016 width=16) (actual time=0.030..3.547 rows=452 loops=7)

13. 0.056 0.056 ↑ 8.9 11 7 / 4

Index Scan using road_segments_partitioned_0_trip_id_idx on road_segments_partitioned_0 (cost=0.43..105.96 rows=98 width=16) (actual time=0.027..0.032 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
14. 0.042 0.042 ↑ 8.5 11 7 / 4

Index Scan using road_segments_partitioned_1_trip_id_idx on road_segments_partitioned_1 (cost=0.43..101.80 rows=94 width=16) (actual time=0.020..0.024 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
15. 0.047 0.047 ↑ 8.5 11 7 / 4

Index Scan using road_segments_partitioned_2_trip_id_idx on road_segments_partitioned_2 (cost=0.43..101.80 rows=94 width=16) (actual time=0.023..0.027 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
16. 0.037 0.037 ↑ 12.6 8 7 / 4

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.018..0.021 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
17. 0.035 0.035 ↑ 13.6 8 7 / 4

Index Scan using road_segments_partitioned_4_trip_id_idx on road_segments_partitioned_4 (cost=0.43..117.80 rows=109 width=16) (actual time=0.018..0.020 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
18. 0.038 0.038 ↑ 12.4 8 7 / 4

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.019..0.022 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
19. 0.038 0.038 ↑ 8.8 11 7 / 4

Index Scan using road_segments_partitioned_6_trip_id_idx on road_segments_partitioned_6 (cost=0.43..105.02 rows=97 width=16) (actual time=0.018..0.022 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
20. 0.040 0.040 ↑ 10.0 10 7 / 4

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.019..0.023 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
21. 0.038 0.038 ↑ 9.1 10 7 / 4

Index Scan using road_segments_partitioned_8_trip_id_idx on road_segments_partitioned_8 (cost=0.43..98.58 rows=91 width=16) (actual time=0.019..0.022 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
22. 0.038 0.038 ↑ 17.0 6 7 / 4

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.020..0.022 rows=6 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
23. 0.040 0.040 ↑ 9.5 10 7 / 4

Index Scan using road_segments_partitioned_10_trip_id_idx on road_segments_partitioned_10 (cost=0.43..102.78 rows=95 width=16) (actual time=0.020..0.023 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
24. 0.044 0.044 ↑ 10.5 10 7 / 4

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.021..0.025 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
25. 2.051 2.051 ↑ 17.7 6 7 / 4

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=1.170..1.172 rows=6 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
26. 0.035 0.035 ↑ 12.6 8 7 / 4

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.018..0.020 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
27. 0.037 0.037 ↑ 25.8 4 7 / 4

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.020..0.021 rows=4 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
28. 0.040 0.040 ↑ 11.2 9 7 / 4

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.020..0.023 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
29. 0.030 0.030 ↑ 12.4 8 7 / 4

Index Scan using road_segments_partitioned_16_trip_id_idx on road_segments_partitioned_16 (cost=0.43..107.11 rows=99 width=16) (actual time=0.015..0.017 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
30. 0.042 0.042 ↑ 10.2 10 7 / 4

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.021..0.024 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
31. 0.030 0.030 ↑ 10.3 10 7 / 4

Index Scan using road_segments_partitioned_18_trip_id_idx on road_segments_partitioned_18 (cost=0.43..111.34 rows=103 width=16) (actual time=0.014..0.017 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
32. 0.042 0.042 ↑ 16.0 6 7 / 4

Index Scan using road_segments_partitioned_19_trip_id_idx on road_segments_partitioned_19 (cost=0.43..103.93 rows=96 width=16) (actual time=0.022..0.024 rows=6 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
33. 0.037 0.037 ↑ 11.4 9 7 / 4

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.017..0.021 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
34. 0.038 0.038 ↑ 8.7 12 7 / 4

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.018..0.022 rows=12 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
35. 0.035 0.035 ↑ 9.5 11 7 / 4

Index Scan using road_segments_partitioned_22_trip_id_idx on road_segments_partitioned_22 (cost=0.43..112.35 rows=104 width=16) (actual time=0.016..0.020 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
36. 0.032 0.032 ↑ 9.5 11 7 / 4

Index Scan using road_segments_partitioned_23_trip_id_idx on road_segments_partitioned_23 (cost=0.43..112.25 rows=104 width=16) (actual time=0.015..0.018 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
37. 0.040 0.040 ↑ 8.2 13 7 / 4

Index Scan using road_segments_partitioned_24_trip_id_idx on road_segments_partitioned_24 (cost=0.43..115.73 rows=107 width=16) (actual time=0.018..0.023 rows=13 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
38. 0.033 0.033 ↑ 19.8 5 7 / 4

Index Scan using road_segments_partitioned_25_trip_id_idx on road_segments_partitioned_25 (cost=0.43..107.16 rows=99 width=16) (actual time=0.018..0.019 rows=5 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
39. 0.042 0.042 ↑ 12.1 8 7 / 4

Index Scan using road_segments_partitioned_26_trip_id_idx on road_segments_partitioned_26 (cost=0.43..104.96 rows=97 width=16) (actual time=0.021..0.024 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
40. 0.033 0.033 ↑ 13.0 8 7 / 4

Index Scan using road_segments_partitioned_27_trip_id_idx on road_segments_partitioned_27 (cost=0.43..112.52 rows=104 width=16) (actual time=0.017..0.019 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
41. 0.032 0.032 ↑ 24.0 4 7 / 4

Index Scan using road_segments_partitioned_28_trip_id_idx on road_segments_partitioned_28 (cost=0.43..103.95 rows=96 width=16) (actual time=0.016..0.018 rows=4 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
42. 0.032 0.032 ↑ 10.8 9 7 / 4

Index Scan using road_segments_partitioned_29_trip_id_idx on road_segments_partitioned_29 (cost=0.43..105.02 rows=97 width=16) (actual time=0.015..0.018 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
43. 0.038 0.038 ↑ 14.3 7 7 / 4

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.020..0.022 rows=7 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
44. 0.040 0.040 ↑ 9.0 11 7 / 4

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.020..0.023 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
45. 0.035 0.035 ↑ 12.8 8 7 / 4

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.017..0.020 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
46. 0.042 0.042 ↑ 10.7 9 7 / 4

Index Scan using road_segments_partitioned_33_trip_id_idx on road_segments_partitioned_33 (cost=0.43..103.82 rows=96 width=16) (actual time=0.021..0.024 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
47. 0.045 0.045 ↑ 11.0 9 7 / 4

Index Scan using road_segments_partitioned_34_trip_id_idx on road_segments_partitioned_34 (cost=0.43..107.09 rows=99 width=16) (actual time=0.023..0.026 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
48. 0.038 0.038 ↑ 7.7 12 7 / 4

Index Scan using road_segments_partitioned_35_trip_id_idx on road_segments_partitioned_35 (cost=0.43..99.61 rows=92 width=16) (actual time=0.019..0.022 rows=12 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
49. 0.058 0.058 ↑ 11.9 8 7 / 4

Index Scan using road_segments_partitioned_36_trip_id_idx on road_segments_partitioned_36 (cost=0.43..102.90 rows=95 width=16) (actual time=0.030..0.033 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
50. 0.042 0.042 ↑ 10.7 9 7 / 4

Index Scan using road_segments_partitioned_37_trip_id_idx on road_segments_partitioned_37 (cost=0.43..103.84 rows=96 width=16) (actual time=0.021..0.024 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
51. 0.040 0.040 ↑ 10.4 9 7 / 4

Index Scan using road_segments_partitioned_38_trip_id_idx on road_segments_partitioned_38 (cost=0.43..101.75 rows=94 width=16) (actual time=0.019..0.023 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
52. 0.035 0.035 ↑ 11.3 9 7 / 4

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.018..0.020 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
53. 0.037 0.037 ↑ 10.7 10 7 / 4

Index Scan using road_segments_partitioned_40_trip_id_idx on road_segments_partitioned_40 (cost=0.43..115.69 rows=107 width=16) (actual time=0.018..0.021 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
54. 0.035 0.035 ↑ 11.3 10 7 / 4

Index Scan using road_segments_partitioned_41_trip_id_idx on road_segments_partitioned_41 (cost=0.43..121.98 rows=113 width=16) (actual time=0.017..0.020 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
55. 0.037 0.037 ↑ 11.4 9 7 / 4

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.018..0.021 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
56. 2.044 2.044 ↑ 9.9 11 7 / 4

Index Scan using road_segments_partitioned_43_trip_id_idx on road_segments_partitioned_43 (cost=0.43..117.72 rows=109 width=16) (actual time=1.165..1.168 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
57. 0.040 0.040 ↑ 12.5 8 7 / 4

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.020..0.023 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
58. 0.040 0.040 ↑ 9.3 11 7 / 4

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.020..0.023 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
59. 0.037 0.037 ↑ 10.6 9 7 / 4

Index Scan using road_segments_partitioned_46_trip_id_idx on road_segments_partitioned_46 (cost=0.43..102.85 rows=95 width=16) (actual time=0.019..0.021 rows=9 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
60. 0.032 0.032 ↑ 12.0 8 7 / 4

Index Scan using road_segments_partitioned_47_trip_id_idx on road_segments_partitioned_47 (cost=0.43..103.91 rows=96 width=16) (actual time=0.016..0.018 rows=8 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
61. 0.038 0.038 ↑ 9.7 10 7 / 4

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.019..0.022 rows=10 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
62. 0.035 0.035 ↑ 9.8 11 7 / 4

Index Scan using road_segments_partitioned_49_trip_id_idx on road_segments_partitioned_49 (cost=0.43..116.67 rows=108 width=16) (actual time=0.016..0.020 rows=11 loops=7)

  • Index Cond: (trip_id = road_movements.trip_id)
63. 376.290 3,152.925 ↑ 1.3 217,777 4 / 4

Parallel Hash (cost=84,473.58..84,473.58 rows=281,003 width=33) (actual time=3,152.925..3,152.925 rows=217,777 loops=4)

  • Buckets: 1048576 Batches: 1 Memory Usage: 69920kB
64. 2,776.635 2,776.635 ↑ 1.3 217,777 4 / 4

Parallel Seq Scan on v_transit_roads (cost=0.00..84,473.58 rows=281,003 width=33) (actual time=2,650.185..2,776.635 rows=217,777 loops=4)

65. 0.000 0.000 ↓ 0.0 0 3,167 / 4

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=3,167)

  • One-Time Filter: false
66. 0.217 3,387.257 ↑ 4.3 47 1

GroupAggregate (cost=180,248,878.68..199,249,585.86 rows=200 width=238) (actual time=3,385.515..3,387.257 rows=47 loops=1)

  • Group Key: segments.street_name
67. 1.229 3,387.040 ↑ 21,389.4 1,388 1

GroupAggregate (cost=180,248,878.68..192,272,736.17 rows=29,688,537 width=246) (actual time=3,385.496..3,387.040 rows=1,388 loops=1)

  • Group Key: segments.street_name, segments.link_id, segments.road_km, segments.road_speed
68. 13.382 3,385.811 ↑ 93,743.4 3,167 1

Sort (cost=180,248,878.68..180,991,092.11 rows=296,885,370 width=246) (actual time=3,385.449..3,385.811 rows=3,167 loops=1)

  • Sort Key: segments.street_name, segments.link_id, segments.road_km, segments.road_speed
  • Sort Method: quicksort Memory: 372kB
69. 3,372.429 3,372.429 ↑ 93,743.4 3,167 1

CTE Scan on segments (cost=0.00..118,754,148.00 rows=296,885,370 width=246) (actual time=3,287.485..3,372.429 rows=3,167 loops=1)

70. 0.058 85.822 ↑ 4.3 46 1

GroupAggregate (cost=125,098,051.02..125,158,092.52 rows=200 width=222) (actual time=85.788..85.822 rows=46 loops=1)

  • Group Key: roadtrips.street_name
71. 0.209 85.764 ↑ 72,727.3 110 1

Sort (cost=125,098,051.02..125,118,051.02 rows=8,000,000 width=222) (actual time=85.757..85.764 rows=110 loops=1)

  • Sort Key: roadtrips.street_name
  • Sort Method: quicksort Memory: 31kB
72. 0.070 85.555 ↑ 68,965.5 116 1

Subquery Scan on roadtrips (cost=120,980,788.28..124,180,788.28 rows=8,000,000 width=222) (actual time=1.459..85.555 rows=116 loops=1)

73. 85.159 85.485 ↑ 68,965.5 116 1

HashAggregate (cost=120,980,788.28..122,580,788.28 rows=8,000,000 width=230) (actual time=1.457..85.485 rows=116 loops=1)

  • Group Key: segments_1.street_name, segments_1.trip_id, segments_1.trip_transport_costs
74. 0.326 0.326 ↑ 93,743.4 3,167 1

CTE Scan on segments segments_1 (cost=0.00..118,754,148.00 rows=296,885,370 width=230) (actual time=0.002..0.326 rows=3,167 loops=1)