explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MKhx

Settings
# exclusive inclusive rows x rows loops node
1. 4.509 6,922.641 ↑ 1.0 1 1

Aggregate (cost=23,865,497.01..23,865,497.11 rows=1 width=32) (actual time=6,922.641..6,922.641 rows=1 loops=1)

  • Functions: 278
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 27.537 ms, Inlining 27.843 ms, Optimization 1019.747 ms, Emission 688.757 ms, Total 1763.884 ms"Execution Time: 6951.922 ms
2. 0.883 6,918.132 ↑ 4.7 1,126 1

Subquery Scan on q (cost=23,862,720.64..23,865,481.39 rows=5,247 width=44) (actual time=6,872.369..6,918.132 rows=1,126 loops=1)

3. 43.357 6,917.249 ↑ 4.7 1,126 1

GroupAggregate (cost=23,862,720.64..23,864,956.69 rows=5,247 width=255) (actual time=6,872.345..6,917.249 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. 13.942 6,873.892 ↑ 3.2 7,996 1

Sort (cost=23,862,720.64..23,862,784.71 rows=25,630 width=211) (actual time=6,871.996..6,873.892 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.552 6,859.950 ↑ 3.2 7,996 1

Nested Loop Left Join (cost=137,106.40..23,860,843.81 rows=25,630 width=211) (actual time=1,930.391..6,859.950 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
6. 821.866 6,857.398 ↑ 3.2 7,996 1

Hash Join (cost=137,106.40..23,858,280.81 rows=25,630 width=211) (actual time=1,930.386..6,857.398 rows=7,996 loops=1)

  • Hash Cond: (road_segments_partitioned_0.link_id = v_transit_roads.link_id)
7. 727.087 6,014.283 ↓ 1.7 7,329,912 1

Nested Loop (cost=0.86..23,710,004.98 rows=4,255,157 width=32) (actual time=1,738.252..6,014.283 rows=7,329,912 loops=1)

8. 6.104 6.104 ↑ 1.0 4,113 1

Index Scan using idx_road_movements_commod_id on road_movements (cost=0.42..4,315.55 rows=4,176 width=32) (actual time=0.034..6.104 rows=4,113 loops=1)

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

Append (cost=0.43..5,175.05 rows=5,016 width=16) (actual time=0.430..1.284 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..96.17 rows=98 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

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

Index Scan using road_segments_partitioned_1_trip_id_idx on road_segments_partitioned_1 (cost=0.43..92.41 rows=94 width=16) (actual time=0.006..0.014 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..92.42 rows=94 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

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

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

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

Index Scan using road_segments_partitioned_4_trip_id_idx on road_segments_partitioned_4 (cost=0.43..106.88 rows=109 width=16) (actual time=0.006..0.014 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..97.22 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..95.33 rows=97 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

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

Index Scan using road_segments_partitioned_7_trip_id_idx on road_segments_partitioned_7 (cost=0.43..98.17 rows=100 width=16) (actual time=0.006..0.014 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..89.49 rows=91 width=16) (actual time=0.006..0.012 rows=33 loops=4,113)

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

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

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

Index Scan using road_segments_partitioned_10_trip_id_idx on road_segments_partitioned_10 (cost=0.43..93.29 rows=95 width=16) (actual time=0.006..0.012 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..103.09 rows=105 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

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

Index Scan using road_segments_partitioned_12_trip_id_idx on road_segments_partitioned_12 (cost=0.43..104.01 rows=106 width=16) (actual time=0.006..0.015 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..98.96 rows=101 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

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

Index Scan using road_segments_partitioned_14_trip_id_idx on road_segments_partitioned_14 (cost=0.43..101.05 rows=103 width=16) (actual time=0.007..0.015 rows=35 loops=4,113)

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

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

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

Index Scan using road_segments_partitioned_16_trip_id_idx on road_segments_partitioned_16 (cost=0.43..97.22 rows=99 width=16) (actual time=0.007..0.013 rows=36 loops=4,113)

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

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

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

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

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

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

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

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

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

Index Scan using road_segments_partitioned_21_trip_id_idx on road_segments_partitioned_21 (cost=0.43..102.06 rows=104 width=16) (actual time=0.006..0.016 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..101.94 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..101.84 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..105.02 rows=107 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

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

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

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

Index Scan using road_segments_partitioned_26_trip_id_idx on road_segments_partitioned_26 (cost=0.43..95.27 rows=97 width=16) (actual time=0.006..0.013 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..102.11 rows=104 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

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

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

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

Index Scan using road_segments_partitioned_29_trip_id_idx on road_segments_partitioned_29 (cost=0.43..95.33 rows=97 width=16) (actual time=0.007..0.015 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..98.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. 57.582 57.582 ↑ 2.8 35 4,113

Index Scan using road_segments_partitioned_31_trip_id_idx on road_segments_partitioned_31 (cost=0.43..97.16 rows=99 width=16) (actual time=0.006..0.014 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..100.12 rows=102 width=16) (actual time=0.006..0.013 rows=35 loops=4,113)

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

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

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

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

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

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

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

Index Scan using road_segments_partitioned_36_trip_id_idx on road_segments_partitioned_36 (cost=0.43..93.41 rows=95 width=16) (actual time=0.006..0.014 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..94.25 rows=96 width=16) (actual time=0.006..0.013 rows=33 loops=4,113)

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

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

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

Index Scan using road_segments_partitioned_39_trip_id_idx on road_segments_partitioned_39 (cost=0.43..100.14 rows=102 width=16) (actual time=0.009..0.017 rows=37 loops=4,113)

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

Index Scan using road_segments_partitioned_40_trip_id_idx on road_segments_partitioned_40 (cost=0.43..104.98 rows=107 width=16) (actual time=0.006..0.016 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..110.66 rows=113 width=16) (actual time=0.006..0.013 rows=38 loops=4,113)

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

Index Scan using road_segments_partitioned_42_trip_id_idx on road_segments_partitioned_42 (cost=0.43..101.09 rows=103 width=16) (actual time=0.006..0.013 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..106.81 rows=109 width=16) (actual time=0.006..0.014 rows=36 loops=4,113)

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

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

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

Index Scan using road_segments_partitioned_45_trip_id_idx on road_segments_partitioned_45 (cost=0.43..100.14 rows=102 width=16) (actual time=0.006..0.014 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..93.36 rows=95 width=16) (actual time=0.006..0.013 rows=33 loops=4,113)

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

Index Scan using road_segments_partitioned_47_trip_id_idx on road_segments_partitioned_47 (cost=0.43..94.32 rows=96 width=16) (actual time=0.006..0.014 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..95.23 rows=97 width=16) (actual time=0.006..0.013 rows=36 loops=4,113)

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

Index Scan using road_segments_partitioned_49_trip_id_idx on road_segments_partitioned_49 (cost=0.43..105.86 rows=108 width=16) (actual time=0.007..0.016 rows=37 loops=4,113)

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

Hash (cost=136,567.73..136,567.73 rows=5,247 width=187) (actual time=21.249..21.249 rows=4,871 loops=1)

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

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..136,567.73 rows=5,247 width=187) (actual time=0.126..18.003 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