explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YNiy

Settings
# exclusive inclusive rows x rows loops node
1. 168.983 162,368.930 ↑ 1.0 1 1

Aggregate (cost=2,545,060.88..2,545,060.89 rows=1 width=32) (actual time=162,368.929..162,368.930 rows=1 loops=1)

2. 24.357 162,199.947 ↓ 4.2 30,066 1

Subquery Scan on q (cost=2,544,483.04..2,545,042.82 rows=7,223 width=44) (actual time=161,912.739..162,199.947 rows=30,066 loops=1)

3. 114,212.240 162,175.590 ↓ 4.2 30,066 1

HashAggregate (cost=2,544,483.04..2,544,970.59 rows=7,223 width=253) (actual time=161,912.727..162,175.590 rows=30,066 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.          

CTE boundaries

5. 0.061 0.061 ↑ 1.0 1 1

Index Scan using idx_v_transit_boundaries_code on v_transit_boundaries (cost=0.28..8.30 rows=1 width=75,373) (actual time=0.059..0.061 rows=1 loops=1)

  • Index Cond: (code = ANY ('{LGA_31000}'::text[]))
6. 13,849.179 47,963.350 ↓ 3.9 19,374,368 1

Nested Loop Left Join (cost=39,874.17..2,285,875.70 rows=4,925,696 width=209) (actual time=793.619..47,963.350 rows=19,374,368 loops=1)

7. 14,030.566 34,114.171 ↓ 3.9 19,374,368 1

Hash Left Join (cost=39,874.17..2,187,361.77 rows=4,925,696 width=209) (actual time=793.549..34,114.171 rows=19,374,368 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
8. 4,224.721 19,295.247 ↓ 3.9 19,374,368 1

Nested Loop (cost=0.86..2,134,558.42 rows=4,925,696 width=193) (actual time=0.224..19,295.247 rows=19,374,368 loops=1)

9. 428.384 428.384 ↓ 4.2 30,066 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..85,125.01 rows=7,223 width=185) (actual time=0.193..428.384 rows=30,066 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
  • Rows Removed by Filter: 1
10. 14,642.142 14,642.142 ↑ 8.1 644 30,066

Index Only Scan using idx_road_segments_key on road_segments (cost=0.57..231.50 rows=5,224 width=16) (actual time=0.012..0.487 rows=644 loops=30,066)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
11. 247.869 788.358 ↑ 1.0 582,725 1

Hash (cost=32,589.25..32,589.25 rows=582,725 width=32) (actual time=788.357..788.358 rows=582,725 loops=1)

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

Seq Scan on road_movements (cost=0.00..32,589.25 rows=582,725 width=32) (actual time=0.049..540.489 rows=582,725 loops=1)

13. 0.000 0.000 ↑ 1.0 1 19,374,368

CTE Scan on boundaries (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=19,374,368)

Planning time : 1.624 ms