explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HKJx

Settings
# exclusive inclusive rows x rows loops node
1. 4.174 1,366.395 ↑ 1.0 1 1

Aggregate (cost=1,417,990.82..1,417,990.92 rows=1 width=32) (actual time=1,366.395..1,366.395 rows=1 loops=1)

  • Functions: 48
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 10.235 ms, Inlining 46.430 ms, Optimization 647.100 ms, Emission 399.208 ms, Total 1102.973 ms
2. 1.270 1,362.221 ↑ 4.3 1,126 1

Subquery Scan on q (cost=1,416,747.68..1,417,976.27 rows=4,818 width=52) (actual time=1,355.327..1,362.221 rows=1,126 loops=1)

3. 50.777 1,360.951 ↑ 4.3 1,126 1

HashAggregate (cost=1,416,747.68..1,417,494.47 rows=4,818 width=187) (actual time=1,354.657..1,360.951 rows=1,126 loops=1)

  • Group Key: road_network_1deg_partitioned_cell_754.link_id, road_network_1deg_partitioned_cell_754.street_name, road_network_1deg_partitioned_cell_754.road_speed, road_network_1deg_partitioned_cell_754.road_rank, road_network_1deg_partitioned_cell_754.truck, road_network_1deg_partitioned_cell_754.surface, road_network_1deg_partitioned_cell_754.road_km, road_network_1deg_partitioned_cell_754.geometry
4. 2.543 1,310.174 ↑ 26.0 7,996 1

Nested Loop Left Join (cost=122,793.60..1,405,825.84 rows=208,035 width=147) (actual time=1,117.773..1,310.174 rows=7,996 loops=1)

  • Join Filter: NULL::boolean
5. 27.062 1,307.631 ↑ 26.0 7,996 1

Hash Join (cost=122,793.60..1,385,022.34 rows=208,035 width=147) (actual time=1,117.767..1,307.631 rows=7,996 loops=1)

  • Hash Cond: (road_segments_1deg_partitioned_cell_754.link_id = road_network_1deg_partitioned_cell_754.link_id)
6. 26.948 1,260.104 ↓ 1.2 241,987 1

Nested Loop (cost=0.86..1,240,645.98 rows=208,035 width=32) (actual time=1,097.270..1,260.104 rows=241,987 loops=1)

7. 3.369 3.369 ↑ 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.031..3.369 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
8. 1,114.623 1,229.787 ↑ 15.5 59 4,113

Append (cost=0.44..204.66 rows=914 width=16) (actual time=0.272..0.299 rows=59 loops=4,113)

9. 74.034 74.034 ↑ 11.3 43 4,113

Index Scan using road_segments_1deg_partitioned_cell_754_trip_id_idx on road_segments_1deg_partitioned_cell_754 (cost=0.44..87.05 rows=484 width=16) (actual time=0.003..0.018 rows=43 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
  • Filter: ((lon >= 152) AND (lon <= 153) AND (lat >= '-27'::integer) AND (lat <= '-27'::integer))
10. 41.130 41.130 ↑ 26.9 16 4,113

Index Scan using road_segments_1deg_partitioned_cell_755_trip_id_idx on road_segments_1deg_partitioned_cell_755 (cost=0.43..71.91 rows=430 width=16) (actual time=0.002..0.010 rows=16 loops=4,113)

  • Index Cond: (trip_id = road_movements.trip_id)
  • Filter: ((lon >= 152) AND (lon <= 153) AND (lat >= '-27'::integer) AND (lat <= '-27'::integer))
11. 2.892 20.465 ↓ 1.0 4,871 1

Hash (cost=122,298.89..122,298.89 rows=4,818 width=123) (actual time=20.465..20.465 rows=4,871 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 864kB
12. 0.538 17.573 ↓ 1.0 4,871 1

Append (cost=0.28..122,298.89 rows=4,818 width=123) (actual time=0.100..17.573 rows=4,871 loops=1)

13. 2.944 2.944 ↓ 1.1 805 1

Index Scan using road_network_1deg_partitioned_cell_754_geometry_idx on road_network_1deg_partitioned_cell_754 (cost=0.28..19,599.38 rows=762 width=139) (actual time=0.100..2.944 rows=805 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: ((lon >= 152) AND (lon <= 153) AND (lat >= '-27'::integer) AND (lat <= '-27'::integer) AND st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry))
14. 14.091 14.091 ↓ 1.0 4,066 1

Index Scan using road_network_1deg_partitioned_cell_755_geometry_idx on road_network_1deg_partitioned_cell_755 (cost=0.28..102,458.61 rows=4,056 width=120) (actual time=0.056..14.091 rows=4,066 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry)
  • Filter: ((lon >= 152) AND (lon <= 153) AND (lat >= '-27'::integer) AND (lat <= '-27'::integer) AND st_intersects(geometry, '0103000020110F000001000000050000003333FF6E433C7041EC51F818D67B48C13333FF6E433C70417B140EFA45C848C185EB216BD14570417B140EFA45C848C185EB216BD1457041EC51F818D67B48C13333FF6E433C7041EC51F818D67B48C1'::geometry))
15. 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
Planning time : 2.921 ms